T-SQL Tuesday #005 – Changing Your Isolation Level

SQL Server Best Practices
SQL Server Best Practices
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).

Continue reading on SirSQL.net.

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *