How do I make use of multiple cores in Large SQL Server Queries?

Question

I have two SQL Servers, one for production, and one as an archive. Every night, we've got a SQL job that runs and copies the days production data over to the archive. As we've grown, this process takes longer and longer and longer. When I watch the utilization on the archive server running the archival process, I see that it only ever makes use of a single core. And since this box has eight cores, this is a huge waste of resources. The job runs at 3AM, so it's free to take any and all resources it can find.

So what I need to do if figure out how to structure SQL Server jobs so they can take advantage of multiple cores, but I can't find any literature on tackling this problem. We're running SQL Server 2005, but I could certainly push for an upgrade if 2008 takes care of this problem.

Answer 1

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

Answer 2

Are you sure the job isn't I/O bound (and not CPU bound)? That would explain why it's running on only one processor. Here's an article and another article on how to troubleshoot this so you can determine what's slowing you down before you assume it's a CPU related issue.

Answer 3

I don't think the problem is CPU. When you move a lot of data, the bottleneck will be disk, you can only write so fast, no matter how good or plentiful your CPUs are.

Imagine you are the smartest guy in the world (have a great CPU) and you need to copy a large book (a work in progress) with paper and pen. you'll only use a small fraction of your brainpower and you can't do the task much faster than a regular guy, since the task has more to do with the speed of your writing hand than your brainpower. As the book gets longer each day (it is a work in progress), the task will take longer, because there is more book to copy.

The only way to improve the speed is to get faster/better disk writing capabilities or reduce the amount of work to do, only copy the new data each day.

Answer 4

Do you have an automated maintenance plan to update statistics, rebuild indexes, etc.? If not, SQL Server may still be building its query plans on your older statistics of smaller tables.

SQL Server generates parallel query plans automatically, if certain conditions are met. From an article on MSDN:

1.Is SQL Server running on a computer with more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP)? Only computers with more than one CPU can use parallel queries.

2.What is the number of concurrent users active on the SQL Server installation at this moment? SQL Server monitors CPU usage and adjusts the degree of parallelism at the query startup time. Lower degrees of parallelism are chosen if CPU usage is high.

3.Is there sufficient memory available for parallel query execution? Each query requires a certain amount of memory to execute. Executing a parallel query requires more memory than a nonparallel query. The amount of memory required for executing a parallel query increases with the degree of parallelism. If the memory requirement of the parallel plan for a given degree of parallelism cannot be satisfied, SQL Server decreases the degree of parallelism automatically or completely abandons the parallel plan for the query in the given workload context and executes the serial plan.

4.What is the type of query executed? Queries heavily consuming CPU cycles are the best candidates for a parallel query. For example, joins of large tables, substantial aggregations, and sorting of large result sets are good candidates. Simple queries, often found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. To distinguish between queries that benefit from parallelism and those that do not benefit, SQL Server compares the estimated cost of executing the query with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure.

5.Is there a sufficient amount of rows processed in the given stream? If the query optimizer determines the number of rows in a stream is too low, it does not introduce exchange operators to distribute the stream. Consequently, the operators in this stream are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination cost exceeds the gains achieved by parallel operator execution.

Other factors:

Is SQL Server configured to have affinity to a single processor?

Is the max degree of parallelism option is set to 1?

-- EDIT --

Have you tried profiling this process? It would be interesting to see the query plan SQL Server generates.

Do you have sample code you can post?

If you have an automated nightly backup job, can you simply restore the backup to the archive?

Answer 5

Performing basic disk I/O operations requires very, very little CPU time (leastways on any box I've run Perfmon on). If you're hitting 100% CPU, then your archiving process is probably CPU-intensive. Are you performing some complex query, embedded function call, or doing something else that would require more than just reading/writing hard drive bytes? (Perhaps your database files are on compressed hard drives? Or some other similar "background" process is interfering?)

One possible way (emphasis on possible because it totally depends on your environment) to spread the load across CPUs is to have several tasks running concurrently. This could be done by breaking the workload up. For a generic archive and making some broad assumptions, it might look like this:

• First step, identify the scope of data to be archived. Say, selected rows and related data from a master table hosting an identity column, where the data to be archived ranges from Id 10000 to 20000
• Break this into chunks. Want to use four CPUs? Try four chunks, 10000-12500, 12501-15000, 15001-17500, 17501-20000. Log these in a "to be done" table.
• Configure four SQL Agent jobs to fire off at, say, 3:00AM, 3:01, 3:02, and 3:03. When each job fires, it gets the next "to be done" set of data, and processes it.
• You could prepare as many chunks of data as you want. You could have as many jobs as you want. A job could keep processing chunks until there were no more in the list.

Note that if you have two or more processes reading data from the same drives and writing it to the same drives, you will eventually become disk-bound. But you can get improved performance, or at least I've seen it on some systems... if a task takes X time to move Y MBytes, two tasks can take less than 2X time to move 2Y MB of data, and this is even more likely to hold if there's a lot of CPU work to be done between the read and the write. But test it on your system to be sure.

To check for disk I/O, use Perfmon (Performance Monitor, System Monitor, "Performance", or whatever they're calling it these days) with the "average disk queue size" counters. If those consistantly exceed 2 or 3, then you're disk bound. (It is, of course, more complex than that, but this will give you a strong start on that problem.)