SQLOS's DMVs Continue

re: SQLOS's DMVs Continue

Glenn Berry — Fri, 29 Sep 2006 19:30:18 GMT

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!

re: SQLOS's DMVs Continue

slavao — Fri, 29 Sep 2006 19:51:27 GMT

"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).

re: SQLOS's DMVs Continue

Mharms — Mon, 16 Oct 2006 19:40:30 GMT

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

re: SQLOS's DMVs Continue

AStephenson — Wed, 24 Jan 2007 13:41:35 GMT

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.

SQLServerPedia » Signal Waits = CPU Pressure…or do they?

SQLServerPedia » Signal Waits = CPU Pressure…or do they? — Sun, 10 Feb 2008 12:06:14 GMT

PingBack from http://sqlserverpedia.com/blog/?p=229

» SQLOS’s DMVs Continue | SQL Server Feeds

» SQLOS’s DMVs Continue | SQL Server Feeds — Sun, 27 Jul 2008 19:14:08 GMT

PingBack from http://www.sqlserverfeeds.com/537/sqloss-dmvs-continue-2/