How to use multiple cores in large SQL Server queries?

Question

I have two SQL servers, one for production and one as an archive. Every night, we have an SQL task that launches and copies data about production days to the archive. As we grow, this process lasts longer and longer. When I observe how the archive process is running on the archive server, I see that it uses only one core. And since there are eight cores in this box, this is a huge waste of resources. The work is done at 3 am, so he can take any resources that he can find.

So, what do I need to do if I figure out how to structure SQL Server jobs so that they can use multiple cores, but I cannot find any literature to solve this problem. We are starting SQL Server 2005, but I could, of course, click on the update if 2008 takes care of this problem.

Answer 1

Do you have an automatic maintenance plan for updating statistics, restoring indexes, etc.? If not, SQL Server can still build its query plans in older statistics of smaller tables.

SQL Server automatically creates concurrent query plans if certain conditions are met. From an article on MSDN :

1.Is SQL Server , (SMP)? .

2. , SQL Server ? SQL parallelism . parallelism , .

3. ? . . , parallelism. parallelism , SQL Server parallelism .

4. ? . , , . , , , . , parallelism , SQL Server parallelism. , 5, sp_configure.

5. , ? , . , . , , .

:

SQL Server ?

​​ parallelism 1?

- EDIT -

? , SQL Server.

, ?

, ?

Answer 2

- ( , Perfmon). 100% - , , , . , - , , / ? (, ? - "" ?)

( , ), , - . , . :

• , , . , -, , Id 10000 20000
• . ? , 10000-12500, 12501-15000, 15001-17500, 17501-20000. " ".
• SQL , , 3:00 AM, 3:01, 3:02 3:03. , , , .
• , . , . , .

, , . , , ... X- Y-MBytes, 2- , 2Y , , . , .

-, Perfmon (Performance Monitor, System Monitor, "Performance" ) " ". 2 3, . (, , , .)

Answer 3

, CPU. , , , , .

, ( ), ( ) . , , , , . ( ), , .

- / , .

Answer 4

Have you tried SQLBulkCopy or viewed Bulk Insert ( http://msdn.microsoft.com/en-us/library/ms188365.aspx )?

Answer 5

Are you sure that the task is not connected with input / output (and not with binding to the CPU)? This explains why it only runs on one processor. Here's an article and another article on how to troubleshoot so that you can determine what slows you down before taking on the problem with the processor.