The current T-SQL Tuesday is being hosted by Aaron Nelson (blog|twitter) who has come up with a great topic, that of “Reporting”.

Reporting is huge in scope, and Aaron was very helpful in throwing out a couple of ideas to get started. I went back and forth on what to write about, ultimately I thought I’d put up something which has made a huge difference to me recently as regards blocking issues in my reporting environment.

 

The Problem

Monitoring is one of the mainstays of the DBA life. Thomas LaRock (blog|twitter) dedicated a good section of his must read book “DBA Survivor: How To Be A Rock Star DBA” to it. Given that I am trying to be a good DBA I have various monitoring routines in place, one of these being to check for blocking on my servers.

The main production reporting server (running SQL 2008 EE) was beating me up with emails to the tune of a couple of hundred a day. Sadly we have a tool that allows folks to drag and drop to add items to queries and execute them. This does not create great query plans, we frequently have long running queries and these block the replication procs.

Suffice to say this couldn’t continue, but what to do?

 

The Solution

Not being able to change any of the code for the reports or do anything with the software I was really at an impass, until over the horizon strode changing the locking method on the database as a solution.

 

Transaction Isolation Levels

SQL Server has 5 different ways that you can hold (or not) a lock when attempting to read data.

• Read Uncommitted – holds a schema shared lock
• Read Committed – holds a shared lock that is immediately released
• Repeatable Read – holds a shared lock for the life of the transaction
• Serializable – holds a shared range lock for the life of the transaction
• Snapshot  – holds a schema shared lock and uses row versioning

Within a query or procedure you can specify one of these to use, each will impact how data is accessed.

By default SQL uses the Read Committed isolation level. This holds a lock while the transaction is running to ensure consistency of the data, and is the reason why I was having a blocking nightmare.

Frequently in reporting situations (and I have to admit I am guilty of this) people use the NOLOCK hint, this is the same as the Read Uncommitted isolation level. While it eliminates the locking problem you also run into a situation where you can quite easily get dirty reads and produce the wrong data in a report (because you are reading uncommitted data you could read something someone else is updating within a transaction, they could roll that change back however you would have still read what was in there at the time). I really didn’t want to have dirty reads, and in any case I could not touch the code that was coming in, so that would not have helped. I needed something else.

 

Row Versioning

Row versioning is something not oft used or very well known about for SQL Server, but it can provide huge benefits in situations like this. By changing a couple of database options we can use row versioning to ensure that we get consistent reads and no blocking problems (except in circumstances where we would be looking to change the schema, there are always caveats).

Here’s how it works. When you open a transaction and perform an update an entry is placed into a version store in TempDB. This version store contains the original row. Should anyone come along and attempt to read that data they are redirected to the original row in the version store and so can continue on their merry way without being blocked. Once the transaction is committed the row is removed from the version store and you would be back to reading the actual table again. This is all invisible to the user (in fact this is so invisible that you won’t see reads against the version store or TempDB when turning on statistics or looking at an execution plan).

To enable row versioning on the AdventureWorks2008 database you would run the following script;

ALTER DATABASE AventureWorks2008
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE AventureWorks2008
SET READ_COMMITTED_SNAPSHOT ON
GO

 

In order to enable these options you can’t have any other open transactions in the database, so either kick everyone out or add WITH ROLLBACK IMMEDIATE to the scripts which will kill any open transactions and send them into rollback.

 

Caveats!

See, like I said, there are always caveats.

While this is all good and well you’ll want to keep a close eye on TempDB. The version store lives in TempDB and depending upon the number of transactions flowing in your server and the ongoing activity you might blow it out of the water. Make sure that it’s properly sized and well monitored, use the “Transactions: Free Space In TempDB” perfmon counter to keep track of it. In you perform bulk loads this might not be a good road for you to go down because of this.

Watch your disk performance. A lot more work on TempDB means that those disks are going to get hit more heavily.

Keep an eye on the version store and how big it gets, sys.dm_tran_version_store holds all of those records and can be queried like any other table.

Last, but certainly not least BE WARNED! You will be adding a 14 byte overhead to every row so that it can hold version store information. This might sneak up on you as these 14 bytes are not added until such time as you update a row, so one day you could perform a mass update and actually wind up using a whole lot more disk. The SQLCAT team have posted a really fantastic article at http://blogs.msdn.com/mssqlisv/archive/2009/05/18/why-did-the-size-of-my-indexes-expand-when-i-rebuilt-my-indexes.aspx regarding row versioning and the impact on table size as a result of online and offline indexing. It’s a must read if you are thinking of a trial implementation. 

 

Buck Woody (blog|twitter) has a great disclaimer about never trusting scripts, trying them yourselves in a test environment. I heartily agree with him. Please don’t just throw this on your production server and hope for the best.