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

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?

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.

StackExchange Database Administrators Q#6383, answer score: 9