THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Counting Context Switches, PerfMon Counters, And Other Miscellaneous Notes

Context switching can be hard to understand and measure.  As it turns out, you can use the PerfMon counter System >> Context switches/sec to count all of the switches occurring in and managed by SQLOS, regardless of the application(s) where it originated.  Note that this counter tracks all context switches UNLESS you’re using lightweight pooling, in which case some context switching may not be counted.

 

Here’s another couple quick PerfMon tips.  Still like to look at Buffer Cache Hit ratio and expect it to stay high for most applications (> 90%), but I know in my heart it’s largely a waste of time.  Some really strong combination of counters to monitor as an indication of poor query performance include low Page Life Expectancy (less than the default of 300), a very high number of key Locks, and long Average Wait Time for relatively low number of Batches/Sec. 

 

Thanks to Greg Linwood for the tip!  Check out Greg’s cool script for IO by databases at http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/03/15/59.aspx. 

 

And in case you’re looking for good SQL Server 2008 forums, now that the product is RTM, go to: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1.

 

Thanks,

 

-Kev

Published Tuesday, September 02, 2008 5:54 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

You can also monitor context switches per session (per task, per session, actually) using sys.dm_os_tasks in 2005+.  

And if you want to be really in-depth about the whole thing, you could use 2008's X/Events and watch the context_switch_callback_executed event, perhaps with the synchronous event counter target..?  (I have not tried that last approach yet, but it seems like it might be the best possible way to monitor this)

September 2, 2008 9:19 PM
 

James Luetkehoelter said:

I would be extremely cautious when looking at Context Switch/sec and inferring anything from that stat alone. When looking at any of the hardware counters in isolation, you run the risk of misdiagnosing the problem. You should always combine processor, I/O and Memory when assessing hardware bottlenecks. A spike in one can always be caused by a bottleneck in another (case in point - you could have a memory spike and low CPU time, but a processor queue length through the roof - the problem is too many applications requesting processor time, not memory).

September 3, 2008 1:11 PM
 

KKline said:

Excellent comments, Adam and James.

I need to study X/Events a lot more.  I know they're cool, but I don't know how watch them or interpret the results.

-Kev

September 4, 2008 5:08 PM
 

Adam Machanic said:

Kevin,

Watch my blog in the coming months :-)

September 4, 2008 8:49 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement