You can monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck.
scheduler_id < 255
The number of rows from this script indicate the number of processors available on the SQL Server box. These are logical processors and not to be confused. Try to run a sqlcmd script which are CPU bound and check on this script. You will see how the numbers increase etc. Moreover, as the script runs, goto your SSMS and disable one of the processor and you will see that the tasks are getting assigned automatically to the other processor dynamically. This is the concept of Hot Add CPU :) …
I forgot to mention another interesting column on this table “Load_factor” which can tell you how loaded a specific CPU is. This suggests if you are having CPU bound long queries or not … A lot of interesting information can be got from each of these DMV’s.
This entry was posted on Monday, May 3rd, 2010 at 03:48 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.