SQL PFE has a Pre-Con at the PASS Summit!

At the 2014 PASS Summit in Seattle on Tuesday November 4, Denzil Ribeiro and I (Tim Chapman) will be giving a Pre-Conference seminar entitled “Troubleshoot Customer Performance Problems like a Microsoft Engineer.”  In this full-day session Denzil and I will cover a plethora of troubleshooting tools and methodologies that we use as field engineers when confronted…


Generating a trusted TDE Certificate in the proper format from a Certificate Authority

I recently worked with a customer who was attempting to deploy Transparent Database Encryption using a trusted certificate which was generated by a certificate authority (CA). They were unable to import the certificate using the CREATE CERTIFICATE command as it kept failing with a 15468 error. This blog post attempts to explain this error and…


Inadvertently Promoting a Local Transaction to a Distributed Transaction

I recently dealt with a customer issue where they were troubleshooting MSDTC, and upon hearing the explanation of exactly what they were doing, I was a bit surprised that a distributed transaction was being used. Upon further investigation, they were unintentionally promoting a local transaction to a distributed transaction.  The reason behind this that they…


Connection Pooling for the SQL Server DBA

  There are a handful of questions that DBAs get in the wild that aren’t necessarily under the jurisdiction of the typical DBA.  One of those aspects is connection pooling.  Far too often application teams, or network admins, or <insert non-DBA professional here> approach the DBA with questions regarding connection pooling, and whether or not…


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…


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…