Dec 272012
 

A pattern I see quite a bit, and wish that I didn't, is NOT IN. Let's say you want to find all the patients who have never had a flu shot. Or, in AdventureWorks2012, a similar question might be, "show me all of the customers who have never placed an order." Expressed using NOT IN, a pattern I see all too often.

Continue Reading »

Trimming the Transaction Log Fat

 Posted by on December 17, 2012  10 Responses »
Dec 172012
 

In many SQL Server workloads, especially OLTP, the database’s transaction log can be a bottleneck that adds to the time it takes a transaction to complete. Most people assume that the I/O subsystem is the real bottleneck, with it not being able to keep up with the amount of transaction log being generated by the workload.

Continue Reading »
Dec 122012
 

Recently, Microsoft pushed out Service Pack 1 for SQL Server 2012, and they quickly followed up with Cumulative Update 1 for Service Pack 1. The reason for this is that the service pack – due to longer development and regression testing cycles – did not contain any of the fixes from RTM Cumulative Updates 3 & 4. Since many people have waited – based on mostly speculative bias at this point – to even start testing SQL Server 2012 until Service Pack 1 was released, I thought it might be useful to cover a couple of the scenarios you might come across.

Continue Reading »
Dec 102012
 

I recently encountered high TRANSACTION_MUTEX accumulated wait time on a client system. I couldn’t recall a case where I saw this wait type as near the top of the “high waits” list and I was curious about what factors could push this type of overall wait time.

The Books Online definition of TRANSACTION_MUTEX is that it "occurs during synchronization of access to a transaction by multiple batches." Not many areas within the SQL Server engine expose this type of functionality, so my investigation was narrowed down to the following technologies:

Continue Reading »