Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption

In most cases, people utilize SQL Server features and capabilities in a common and typical usage pattern.  But there are some instances where environments do something just a little different, or fill the bucket to the absolute brim.  With the advent of AlwaysOn Availability Groups, it is such a robust high availability and disaster recovery…


Are my actual worker threads exceeding the sp_configure ‘max worker threads’ value?

Tom Stringer (@SQLife) was working on some HADR testing for a customer to simulate many availability groups and introduce significant load into the system to measure overhead and such. In his quest to do that he was seeing behavior that he couldn’t really explain and so worked with him to uncover what was happening under…


SQL Server Resource Governor Monitoring reports

Resource Governor is an extremely cool and powerful feature that, when implemented, can manage the consumption of CPU and memory resources.  I will not be going into the details of Resource Governor, or even how to set it up as this is extensively documented in Books Online.  What I will be talking about today is…


An overview of High Availability and Disaster Recovery solutions available for SQL Server

With the more recent releases of SQL 2012 and Windows 2012, we are seeing the landscape of available options for SQL HA/DR specific architectures is improving significantly. Below is a summary of the high availability and disaster recovery solutions available for a SQL environment.   To begin, it is important to outline some key terminology….


Flow Control in Always On Availability Groups – what does it mean and how do I monitor it?

A question was posed to me whether Flow Control which existed in Mirroring was still relevant for Availability groups. Flow Control is primarily a mechanism to gate or throttle messages to avoid use of excessive resource on the primary or secondary. When we are in “Flow Control” mode, sending of log block messages from the…


Identifying the cause of SQL Server IO bottlenecks using XPerf

In a previous blog post (Troubleshooting SQL High CPU usage using Xperf), we covered the xperf basics, what types of scenarios are appropriate for xperf, and more specifically, how to look at CPU sampling within xperf.   In general, user CPU time would be investigated using the standard SQL Server tools such as Profiler/Extended Events, DMV’s,…


SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

We have introduced the System Health Session in SQL 2008 in order to capture some critical Events to make post mortem analysis much easier. However in SQL 2008, by default the system_health session was only collected to a ring buffer and not persisted to disk. SQL 2012 truly provides more of a black box recorder…


SQL 2012 AlwaysOn Availability groups Automatic Failover doesn’t occur or does it – A look at the logs

  I was dealing with an issue where we had an AlwaysOn availability group with 2 replicas configured for Automatic failover. There was a 30 second glitch where folks could not connect to the Database on the primary and automatic failover did not kick in. Well, that was our initial impression at least. The purpose…