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

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More