What can cause slow queries with low resource usage?

I am writing an application which builds a database on a SQL server. The application loads data into some staging tables, then executes a series of stored procedures which merge the staging tables into the main tables.

The problem I'm seeing is that the merge itself is consistently much slower than I expect given the resources available to the server (8 cores, DB stored on RAID0 array of two SSDs, 32GB RAM, no other server processes running on the machine or other users of the SQL Server instance).

To be specific, during the merge:

• Server CPU usage is very low (a few percent)

• There are essentially no writes and reads to the logical volume that the database is stored on (measured in perfmon)

• In a 30s period during the merge, Brent Ozar's wait stats triage script (http://www.brentozar.com/responder/triage-wait-stats-in-sql-server/) reports a maximum wait time of only 0.6s (on the SOS_SCHEDULER_YIELD wait type)

• Inspecting dm_io_virtual_file_stats for both tempdb and the main database before and after a 30s period during the merge shows that IO stall time increases by less than 0.1s, indicating that we are not blocked on IO

• Activity Monitor confirms the IO system is lightly loaded: maximum IO response latency is only 9ms, with very low number of bytes transferred

• Intel Performance Counter Monitor (http://www.intel.com/software/pcm) shows that we are not swamping RAM bandwidth because only about 500MB/s is being transferred (out of a benchmarked maximum on this machine of around 13GB/s)

• Inspecting dm_exec_sessions for the merging session before and after a 30s period during the merge shows that:

• total_elapsed_time increased by ~10s (I assume this is not 30s because dm_exec_sessions only gets updated periodically)

• cpu_time increases by only 0.1s

• memory_usage is only 2 pages both before and after

• 20,000 logical_reads are performed in this 10s period (i.e. 2,000 a second). This seems very low to me given that that only repres

This is most likely caused by the MERGE operation in SQL Server being single threaded. No matter how many resources you throw at it, it wont run faster which is exactly what you are observing.

The solution is to manually parallelise the query by running multiple copies of the MERGE statement at the same time, each operating on their own subset of the data.

The problem is very common in data warehouses running SQL Server. There are a series of good design pattern in this document: http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx (DISCLAIMER: This is written by yours truly, so I am shamelessly plugging this)

StackExchange Database Administrators Q#56139, answer score: 3