Last week I wrote a post on Isolation Levels and the Effects on Select Statements, and I had a conversation on Twitter with Rob Farley ( b | t ) regarding one of my explanations.  For READ COMMITTED, I had written the following:

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the original copy of the data (not the modified data).

Rob asked me to read it again, and I realized that this explanation was not accurate for READ COMMITTED.  In fact, what I described was the SNAPSHOT isolation level.

What I should have said for READ COMMITTED was:

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data.

(I have noted and modified my original post with this information.)

To be clear, in a READ COMMITTED isolation level, you will only read committed data.  This is different than in the READ UNCOMMITTED isolation level, where can you read uncommitted, or dirty, data.

Here’s a quick example using the HumanResources.Employees table in the AdventureWorks database.  Let’s look at the output if I select from the table.  Notice that for EmployeeID 3, the Title is Engineering Manager.

Original output from Employees table

In one query window, I run the following:

USE AdventureWorks;
GO

BEGIN TRANSACTION;
UPDATE HumanResources.Employee
SET Title = 'Senior Engineering Manager'
WHERE EmployeeID = 3;

Notice that I haven’t committed the data yet.  Also notice that I didn’t set the isolation level, and that’s because the isolation level of the UPDATE does not affect what happens when I SELECT.

Now I open a second query window and run the following:

USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

SELECT * FROM HumanResources.Employee;

What happens?  I wait.  And I wait.  And I wait.  This query will continue to wait until I either COMMIT or ROLLBACK my UPDATE statement.  Once I do that, the results will return.  But in this case, let’s kill the query, and then run the following statements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

SELECT * FROM HumanResources.Employee;

Now I get data back, and notice that the value for EmployeeID 3 is now Senior Engineering Manager, even though that data hasn’t been committed yet.  Yuck.  A dirty read.

Output after a dirty read

Feel free to change the isolation level for the UPDATE statement to prove out that it doesn’t affect what happens when you SELECT – and take it a step further by checking out the lock that gets taken with the SELECT in both isolation levels (I use sp_lock <SPID>).  Have fun!

Yesterday afternoon I drove up to Southfield, Michigan to present to the Southeast Michigan SQL Server User Group.  Joe Fleming contacted me and asked if I would present my Baselines session, which I presented at the PASS Summit last fall.  I usually don’t pass up a chance to go to Michigan, and this was the first time I presented in front of a different user group.  I’ve presented at many SQLSaturdays, but user groups have a different vibe.  It’s a regular meeting for attendees and it is typically only one presentation.  I haven’t presented since the Summit so it was nice to finally speak again.  I’ve missed it!  The group was very diverse in terms of experience and it was a great group (somewhere between 40 and 50 people).  I had a lot of good questions, including some new ones, and one of the attendees from last night emailed me this morning to let me know that the session was helpful for him.  It was fun, and this morning I realized that I have missed it.  Luckily, I’ve been accepted to speak at Madison’s SQLSaturday in April, and I’ve also submitted for Chicago.  I’m not sure what else will be on the agenda this year, but I hope to be speaking a fair bit.  And Joe, thanks again for the invite.

On a personal note, I was looking forward to this trip because after the meeting I was planning to go to Ann Arbor to see my friend and mentor, Susan.  As I write, I’m in a hotel in Farmington Hills because as I was leaving yesterday, I received a call from one of her graduate students explaining that she was in the hospital.  … … … I didn’t see that one coming.  She is ok, and should be fine long term, but it was a bit of a wake up for me.  Life is short.  You all know this, right?  You do.  But I’ve been thinking about this all morning, which meant I couldn’t let it go and just needed to say what was on my mind.

In my pocket I carry a glass stone with the quote, “Let us live while we live.” (Phillip Doddridge)  It is a reminder to live in the moment, and to live life fully.  In 50 years I don’t want any regrets about things I wish I would have done.  Last night I thought about driving back home, but I was really tired, and I wanted to try and see Susan today, because you just never know.  She’s probably fine, in all honesty, but what if she’s not?

So today, I encourage you to enjoy your life and the people that are in it.  If there’s something that’s missing, go find it.  If there’s something that you’ve wanted to do but are too afraid, get over that hurdle and go do it.  Life is meant to be lived, to be enjoyed.  It’s not always easy, but without a doubt, it’s all worth it.

Thanks for reading.  Now I’m off to Ann Arbor to see Susan, go to Zingerman’s, and pick up some Michigan gear.  Happy Friday all – go tackle the day!

Database locking is a fundamental concept in SQL Server that every Database Administrator and Database Developer should understand.  SQL Server has a pessimistic locking model because it was built for an OLTP environment and assumes that updates will occur frequently.  For a developer, this is important to understand in terms of concurrency: how many users can access the same data simultaneously.  In some cases, you may want concurrency to be high (many people can access the same data at the same time); in other cases you may want concurrency to be low (only one person can access the data at any given time).  A developer must know what level of concurrency is needed, the effects that concurrency has in the database (locks) and how concurrency affects user activity and processing.  A DBA must also understand concurrency, the locking that occurs in a database, how locking can lead to blocking and how to troubleshoot blocking issues.

The isolation level for a database connection directly affects the type of lock that is taken for a read operation, and therefore affects concurrency.  I bring this up because I’ve seen people inadvertently create blocking in a database because they do not understand properties for their database connection.  I don’t want to repeat Books Online, but let’s take a minute to review the more common isolation levels in SQL Server.

READ COMMITTED

This is the default isolation level in SQL Server.  When you initiate any connection to SQL Server (e.g. from Management Studio), this isolation level will be used.  In a READ COMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data. (Note: this statement has been modified since the original post, please see Isolation Levels and the Effects on Select Statements, Part II for more details.)
• Select statements create shared locks which are released as soon as the data is read. If a shared lock exists, data cannot be modified until the lock is released (for more information see Lock Modes in BOL).  Under the right circumstances, this can lead to blocking.

Lead blocker

READ UNCOMMITTED

This the most optimistic isolation level in SQL Server, and in order to utilize it, the SET TRANSACTION ISOLATION LEVEL statement must be executed when a connection is made.  In a READ UNCOMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the modified copy of the data. This is known as a dirty read.

Note: You can create the same behavior by using the NOLOCK hint in queries.  That is not something I would recommend, and NOLOCK is another debate for another time.

A dirty read

SNAPSHOT

This isolation level was added in SQL Server 2005, and is considered a good hybrid of READ COMMITTED and READ UNCOMMITTED as it requires that data that is read during a select statement will be the same value that existed when the transaction started.  In a SNAPSHOT isolation level:

• If an insert, update or delete statement does not commit before the select statement begins, then the output from the select will show the original values.
• Locks are not taken when reading data (unless a database is being recovered).

This isolation level requires setting the ALLOW_SNAPSHOT_ISOLATION database option to ON.

What to remember

As a DBA, it’s important to recognize that when you open Management Studio (SSMS) and open a query window to a database and start to query that database, you can affect the type of locks being taken.  If you run a select statement which takes 10 minutes to return data, you may block other activity in the database.  If you’re troubleshooting a problem, you might make it worse.  If you’re just looking at data, or writing some reports against production, you might create a problem.

What you can do

If you’re writing reports, don’t do it against production.  A good DBA restores backups on a regular basis.  Restore a recent backup of production and write reports against it.  Don’t have the space to restore production?  Utilize a test, development, QA or UA environment and write the reports there.  If the database is not comparable in size and/or data distribution to production, at some point you will need to do performance testing so you can make sure the reports are optimized.  At that time, you really want to find the space to restore that recent production backup, even if it’s only available to you for a couple days of testing.  In six months, you do not want to explain why the reports you wrote are causing performance issues in production.

If you’re just selecting data to troubleshoot or look at data, and are not concerned about dirty reads, you can change the isolation level in SSMS.  You must do this each time you open a new query window by executing

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

For subsequent queries issued in that session, the isolation level will be READ UNCOMMITTED. Again, realize that this must be set for each new query window you open.

Alternatively, you can change the default isolation for SSMS connections. I don’t recommend doing this for every SSMS installation in your environment.  I would only change this on the SSMS installed on my own machine, to which only I access, so that I am the only person affected if I start to see unexpected results.

Within SSMS, select Tools | Options. Then select Query Execution | SQL Server | Advanced. In the SET TRANSACTION ISOLATION LEVEL drop down, change the value to READ UNCOMMITTED. Select OK. All new sessions will have an isolation level of READ UNCOMMITTED.

Changing default isolation in SSMS

What about Oracle?

As you may or may not know, the application which I support can also run on Oracle.  There are numerous differences between SQL Server and Oracle, including isolation levels.  Oracle has an optimistic locking model because it assumes that users spend more time reading data than modifying it.  Oracle utilizes two isolation levels to manage data concurrency and consistency: READ COMMITTED and SERIALIZABLE (see Data Concurrency and Consistency for more information).

The default isolation level is READ COMMITTED and when you initiate any connection to Oracle (e.g. from SQLPlus), this isolation level will be used.  In the READ COMMITTED isolation level:

• When a select statement is issued in Oracle, there are no locks taken on the rows being read.

This behavior is very different than SQL Server. A select statement in Oracle should never block a data modification, and a data modification should not block a select statement either.  Within the Oracle community, the phrase is simply: Readers don’t block writers and writers don’t block readers.  In a SQL Server database, you can create the same behavior by using the SNAPSHOT isolation level.

It's Oracle, no one is bothering anyone else

As such, I have seen only two blocking issues in Oracle in all the years I’ve worked with it, but I’ve seen numerous blocking issues in SQL Server.  In Oracle, both issues occurred because the connection was lost during the middle of an update, and the lock taken by the update blocked subsequent updates to that same data.

Additional resources

In this post I wanted to focus on what a DBA should remember when connecting to a production database, as it relates to isolation level.  I have merely touched upon the impact of isolation level, and if you want to read more, I highly recommend Kendra Little’s isolation levels resource page for more information.

I recently blogged about an interesting behavior in SQL Server when the database option, Auto Update Statistics, is disabled…and that post requires a sequel.  The summary is that when you have the Auto Update Statistics option disabled for a database, query plans for tables are not recompiled when their statistics are updated.  There is an exception for the inserted and deleted tables that are created by DML INSTEAD OF triggers, but overall this database setting can have a significant impact on a system, which I alluded to in my previous post.  In a customer system I found a query that was doing a full table scan and required 32,000 reads, even though the predicate was unique and statistics had been updated that day with 100% sample.  The SQL Server instance had not been restarted since December (about 45 days before my discovery) and the customer had gone live in December.  My theory was that the full scan query plan was created by the optimizer in December, when there was no data in the system.  The query was executed regularly and never dropped out of the plan cache, therefore it was still being used in January even though by that point there were millions of rows in the table.  To test this, I found the query handle for the plan, and then dropped just that plan from cache.  Immediately I searched for a new query plan for that query, and found the index seek I expected.  The 32,000 read query no longer appeared in the trace I was running.

Now I’m faced with the challenge of figuring out what to recommend to our customers going forward.  For many years we have recommended that customers have Auto Update Statistics disabled, and I listed the reasons previously:

• We do not want statistics updating during production hours if at all possible; we want the customer to manage statistics through a regularly scheduled job.
• We want statistics updated with 100% sample because very often the distribution in critical tables is skewed.  With Auto Update Statistics enabled, the statistics do not update with 100% sample unless the table is less than 8 MB in size.

We cannot continue to make the same recommendation unless we provide more information about the ramifications (notably query plans are not recompiled by an update of statistics); it would be irresponsible of us to not mention this.  Alternatively, we could change our viewpoint entirely and recommend that customers enable Auto Update Statistics.

After a lot of thought, the recommendation is going to go away completely.  That is, we’re going to tell customers that they can either enable or disable the option, it’s their choice and it depends on how they want to manage statistics.  I feel that we need to provide an explanation to customers about why our recommendation has changed, and I also I believe we need to provide a few suggestions on how to manage statistics.  The challenge here is that so many of our customers do not have a full time DBA.  Very often, the application administrator acts as the DBA.  The application administrator will probably not have the time, nor the inclination, to manage statistics at the detailed level to which a full time Production DBA would.  Is there a set of maintenance and configuration options that we can suggest that will work for the Accidental DBA?

Here are the options I have developed, and please read through the entire rest of the post before adding a comment if you disagree:

Option 1

1. Disable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
3. Clear procedure cache or restart the instance after the update of statistics is complete

Option 2*

1. Disable Auto Update Statistics for the database
2. Before running any update statistics jobs, enable the Auto Update Statistics for the database.
3. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
4. When the job is complete, disable the Auto Update Statistics for the database

*Joe Fleming ( t ) initially proposed this option in the comments of my original post

Option 3

1. Enable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)

Before you skip straight to the comments to disagree, hear me out.  Remember that the majority of customers are not 24/7 shops, and do not have a full time DBA.  These customers have databases less than 250 GB, and can take the hit of rebuilding every index and updating statistics every week, every other week or once a month.  Is it overkill?  Probably.  But it works for the majority, which is what we have to target.

Do I love the option of clearing query cache?  No, not at all.  That suggestion probably gives many people a heart attack.  I understand.  We can try to mitigate the effect a bit by just clearing the cache for the one database (DBCC FLUSHPROCINDB(<db_id>)).  But yes, clearing the cache will cause every query to be freshly compiled, and this will utilize a lot more CPU, it will cause query duration to go up initially, and it will affect overall performance.  I get that.  But for a system that is not managed by a full time DBA, I will take this hit in order to ensure that the query plans are based on the current set of data.

Now, the ideal option is:

Option 4

1. Enable Auto Update Statistics for the database
2. Create a job to rebuild indexes and update statistics on a regular basis, dependent upon the level of fragmentation in an index and the need to update statistics because of changes to the data

This option is appropriate for any customer with a full time DBA who is comfortable managing fragmentation and statistics through custom scripts and jobs.  For example, many DBAs use Ola Hallengren’s Index and Statistics Maintenance script, or Michelle Ufford’s Index Defrag script.  To take things a step further, you can monitor when an automatic update of statistics occurs (use the Auto Stats Event Class in Trace), and you can capture snapshots of table row counts (sys.partitions) and index updates (sys.dm_db_index_usage_stats) to understand what tables have data that changes frequently.  High volume or volatile tables (those with a lot of inserts, updates and deletes) may require a more aggressive approach to managing statistics.

Ultimately, there is no silver bullet…no one perfect answer, unless that answer is “It depends.”  But again, you have to know what “it” depends on.  In this case, whether you should have the Auto Update Statistics option enabled or disabled for a database depends on your ability to manage statistics for a database, and understanding that having it disabled can lead to out of date query plans.