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…


SQL PFE PASS Content Now Available!

First off, thanks to everyone who decided to attend a Microsoft SQL PFE session at the PASS Summit in Charlotte. We know you have a lot of great sessions to choose from, and we are thrilled if you happened to have chosen to listen in on one of ours. As promised, we are posting the…

6

SQL PFE @ PASS Summit

I have been a long time supporter and attendee of the PASS Summit.  Last year I even had the pleasure of being a first time speaker.  Unfortunately, this year I am unable to attend, but I wanted to take the time to tell you about a number of my fellow PFEs who will be attending…


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…


SecurityException / ‘The process was terminated’ errors installing SQL 2008 when .Net Framework 4.0 is installed

When installing SQL 2008 on a newer OS or a machine that has .NET Framework 4.0, you can encounter errors if the SQL installation media is running from a UNC path. The application log can throw an error such as the one below:   Log Name:      Application Source:        .NET Runtime Date:          6/30/2013 4:20:04 PM Event…


An Approach to SQL Server Index Tuning

Well-constructed indexes can greatly improve read performance in SQL server, but they can be costly to maintain. There’s the obvious cost of additional time for your periodic index maintenance (rebuilds, reorganization and updating statistics) and the cost of additional storage, but there’s also a cost every time you make an update to indexed data.    Consider…


Forced Parameterization Can Lead to Poor Performance

Something that is a relatively common performance eye opener is when you have a large ad hoc workload, and you’re getting a huge gap in the lack of plan reuse.  You talk to the application team that is responsible for this possibly dreaded scenario and relay your concerns about the high CPU caused by the…


Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for high Resource statements.

Extended events is a powerful feature that allows us to troubleshoot performance issues within SQL Server. One of the ways you can utilize extended events to aggregate statements is described in a blog post below by Bob Dorr SQL Server 2012: RML, XEvent Viewer and Distributed Replay Let’s apply that methodology to a contrived scenario,…