Where to look for a bottleneck after elimitating CPU, memory, and disk

Question

I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run in the shortest amount of time through experimentation (WITH INDEX NOLOCK, FORCE ORDER, LOOP JOIN, MAXDOP 0). I'm happy with the improvement, but I hate not knowing why a query takes as long as it does. I'm perfectly OK with bottlenecks, as long as I know where they are. When this query is running, there are significant periods of time where the obvious resources are all underutilized. What is SQL Server doing at these times?

Answer 1

Try this query: select * from sys.dm_exec_requests where session_id>50. This will show you all the currently executing user queries. Look at the wait_type, last_wait_type, and wait_resource to see what your queries are waiting on.

Also use: select * from sys.dm_os_waiting_tasks where session_id>50. This will show you all your queued tasks. This query also returns a wait_type column. If the CPU usage is low it gives the impression that SQL is not doing anything. Most of the time SQL will be waiting on another non-cpu related resource to be freed up. An inner look can be done with the queries above.

You can read a bit more about it here.

Answer 2

The execution plan and statistics IO will tell you everything you need to know about what is going on.

There are four places the SQL Server can bottleneck and you listed three of them. Disk (logical and physical IO), RAM or CPU. After that it's just crappy code that generates more logical IO than is actually needed.

With all those tuning hints in there you've probably got some code that needs to be cleaned up. You need to look at the execution plan for the query (post it if you'd like) and see where the problems in your query are at. You can see if the IO is logical (cache hits) or physical (disk hits) from looking at the output when set statistics io on is enabled.

Answer 3

Most likely it's running logical reads in preparation for the next insert/creation/action of whatever kind. I've had the EXACT same issue with my DW, where code will just sit around doing what looks like nothing for a while. The end result of that one was that one dimension table had hit whatever threshold in the database that made it take much longer on occasion to pull the data; even forcing a query plan didn't fix the issue. Restructing the query and adding a new index made it perfectly happy.

If you can and it doesn't impact production significantly, try to run a profiler trace behind the code in question and gather the SHOWPLAN results. That should indicate if you have an index issue, or perhaps a query that would benefit from being rewritten.

Answer 4

I would look at the problem by investigating waits. There is a ton of info on the internet about waits, starting from sql server 2005.

Check these out:

• http://msdn.microsoft.com/en-us/library/cc966413.aspx

• http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/

good luck!