共用方式為

SQLOS's DMVs Continue

  • 發行項

sys.dm_os_schedulers

  1. Q. Do I need to by more CPUs?

In order to answer this question you have to find out if your load is really CPU bounded.  Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan.  The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:

 

select

AVG (runnable_tasks_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'

 

Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.

 

  1. Q. What is affinity of my schedulers to CPUs?

 

select

      scheduler_id,

      CAST (cpu_id as varbinary) AS scheduler_affinity_mask

from

sys.dm_os_schedulers

 

  1. Does my machine have either hard or soft NUMA configuration enabled?

 

select

      CASE count( DISTINCT parent_node_id)

      WHEN 1 THEN 'NUMA disabled'

      ELSE 'NUMA enabled'

      END

from

      sys.dm_os_schedulers

where parent_node_id <> 32

  1. Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?

You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if

A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)

B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)

select

AVG (work_queue_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'

 

  1. Q: Is my system I/O bound?

You can answer this question by monitoring length of I/O queues.

select

      pending_disk_io_count

from

      sys.dm_os_schedulers

 

 If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.

Comments

  • Anonymous
    September 29, 2006
    I have found this query to be a very reliable indicator of CPU pressure in SQL Server 2005

    -- Check SQL Server Schedulers to see if they are waiting on CPU
    SELECT scheduler_id, current_tasks_count, runnable_tasks_count
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255

    It is much more useful in my opinion than conventional PerfMon indicators like % CPU utilization or Processor Queue Length. Please keep these posts coming!

  • Anonymous
    September 29, 2006
    "It is much more useful in my opinion than conventional PerfMon indicators like % CPU utilization or Processor Queue Length!"
    This is interesting comment because perfmon's processor queue length won't tell you much due to way SQLOS schedules threads. Remember SQLOS implements nonpreemptive scheduling mechanism. Each scheduler can only have one active, running, task. The rest of the tasks that are ready to run are not running but rather runable and located in scheduler runable queue. From OS perspective they are suspended and will never appear in processor queue. Hence looking at actual processor queue length for SQL Server is pretty much useless because at most it will be one (though keep in mind that sometimes it indeed can be bigger due to some SQL Server preemptive, free floating, tasks that periodically can become active).

  • Anonymous
    October 16, 2006
    Very help ful for sql 2005, but is there a similar thing we can look at with SQL Server 2000?

  • Anonymous
    January 23, 2007
    Yep - it would be great to have similar advice/info for SQL 2000.  At the moment, as using DBCC SQLPERF(UMSTATS), but I'm not sure if I'm reading the results correctly.

  • Anonymous
    February 09, 2008
    PingBack from http://sqlserverpedia.com/blog/?p=229

  • Anonymous
    July 27, 2008
    PingBack from http://www.sqlserverfeeds.com/537/sqloss-dmvs-continue-2/