Microsoft’s Slava Ok has another good blog post that has some good DMV queries on how to measure CPU pressure, IO pressure, and whether NUMA is enabled in SQL Server 2005. In my daily experience, I find the query shown below to be an extremely useful indicator of CPU pressure (far more reliable than conventional measures such as % CPU Utilization or Processor Queue Length in PerfMon):
— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
WHERE scheduler_id < 255
If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern!