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?

  • 1
    Can you give code, schema and indexes please?
    – gbn
    Sep 30, 2011 at 17:41
  • @gbn I apologize, but I don't think I can share all that. I know it is a little rude given that I am asking for help. I was just hoping there was something I'm failing to notice - perhaps SQL Server is actively accessing data cached in memory and I don't know of a way to check for that. Sep 30, 2011 at 17:58
  • 1
    SQL Server will always access data from memory. That's why we give it so much memory. See my updated answer.
    – mrdenny
    Oct 2, 2011 at 19:07

4 Answers 4


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.


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.


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.

  • Good idea. I haven't really used profiler much, so it's probably time that I started. I will post my results here. Sep 30, 2011 at 18:00

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:

good luck!

  • Welcome to Database Administrators! Could you provide some more detailed information on how to go about investigating waits instead of just providing links? This would improve the answer. Oct 3, 2011 at 13:25
  • @DTest: Providing more detailed info would be difficult. SQL Server has many wait types defined. Executing a query involves many subsystems and resources. Each time SQL server waits for an unavailable resource a wait is generated. A resource can be: cpu cycles, a lock, a latch, a memory buffer, etc.; sql server can even wait to send data to the client (may it be a .net or mgmt studio). You have to use some query's over system dmv's to understand on what is sql server waiting: you reset the wait counters, run the problematic query and analize waits using select over dmv's. Oct 3, 2011 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.