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,-Ke