Yikes - A read query is blocking everything!

This is something I saw in an OLTP database with an application (.NET) read query scanning a multi-billion row table while holding a full table S lock. The query thought it was using the default isolation level of read committed. Almost every other query in the system was locked in a chain behind this read query as all update queries on the table (arguably the most important table in this OLTP application) also prevented any other read queries from jumping the queue and had them waiting for a lock too.

By default, SQL Server read queries will use a locking isolation level of READ COMMITTED. Most developers just assume this and, understandably, do not bother to set isolation level. With READ COMMITTED, a read query will only lock the current record while scanning many rows. It has an "intent" lock on the table, which is compatible with all other "intent" locks on the table. However, if you are using connection pooling, which most applications do nowadays, it is possible that your query has picked up an isolation level of REPEATABLE READ from a recycled connection. This is quite serious. Now your query won't release locks as it scans through rows, and once it has read a few thousand rows, it will stop taking row/page locks and lock the entire table.  This is called Lock Escalation, as you have taken so many locks on one object, SQL Server says, "that's enough you may as well have one lock on the entire table". Now it gets interesting. Any INSERT, UPDATE, or DELETE query will have to wait on your query, since they are incompatible with your S (share) lock on the table. What happens next is even more interesting (in a nefarious kind of way). All subsequent read queries (except NOLOCK read queries) will wait for a lock too.  They are compatible with the full read lock but they are incompatible with the IX locks of the insert, update, delete queries, so they must wait in queue. I call this the "no queue jumping" rule.

How exactly does your query get a REPEATABLE READ isolation level when it didn't ask for it? The answer is connection pooling. An application program can change the isolation level of the connection and leave the changed isolation on the released connection. The next thread to pick up the connection will have the isolation level left behind. Many people have raised this issue before and there has been a Connect issue supported by many developers.
http://connect.microsoft.com/SQLServer/feedbackdetail/view/243527/sp-reset-connection-doesnt-reset-isolation-level
Unfortunately, I feel, Microsoft's response has been to close the "Bug" with an "as By Design" flag!

So what can we do to avoid queries escalating to full table locks when they assume isolation of READ COMMITTED?

1. The cleanest solution would be for the connection pool to pass on used connections with their default settings. This is unlikely to happen in the near future as indicated above by Microsoft. 
2. Yould could disable connection pooling. This is quite heavy handed and you will pay for it in a slower application as new connections generally take 100's of milliseconds to create.
3. In your application that uses connection pooling, ensure that all connections are reset to the default isolation level before release. Note, sp_reset_connection does not do this. Also note, there might be times when a piece of code does not follow this standard. Unfortunately, the non standard code won't be affected.  It will be the innocent code that takes on the connection that initiates the locking issue.  
4. In your application that uses connection pooling, ensure that all new connections are set to the desired isolation level. Ie, don't rely on a new connection having the default settings. This is probably a good practice, as all code that follows this standard will be OK.
5. Use WITH (NOLOCK) table hint on your read queries. This will certainly avoid the lock escalation. However, there can be interesting data integrity issues in reading with no locks, such as reading the same row twice. I think there is a place for NOLOCK, but I wouldn't advise the use NOLOCK throughout your application. The problem is the use of REPEATABLE READ not READ COMMITTED.
6. Use WITH (READCOMMITTED) table hint on your read queries. This will override REPEATABLE READ isolation level on the connection for this table. So the problem above is avoided, but it is a bit of a task changing all the queries to use this table hint. Note, query hints have a bad rap, which is understandable when they override the optimizer's choice of access path. I see no problem with query hints when they are requesting a specific isolation level.
7. Use READ COMMITTED SNAPSHOT ISOLATION on your database. This is not a solution as the read query is on a connection that has a REPEATABLE READ isolation. As the name suggests, READ COMMITTED SNAPSHOT ISOLATION only works on connections that are using READ COMMITTED. 
8. Use TRANSACTION COMMITTED SNAPSHOT ISOLATION on your database. This is not a solution either, as it does not override the REPEATABLE READ isolation level of the read query.
9. Wait for upgrade to SQL Server 2014.  It looks like the isolation levels are reset on pooled connections.  However, you should check, as this information has simply be posted in an msdn forum, albeit from a SQL team member. 

How can I determine if REPEATABLE READ is being used by my application?

• A SQL Server Profiler trace on ExistingConnection events will provide the isolation level of all active connections. Just look for set transaction isolation level and see what follows. If any of your current connections have REPEATABLE READ you should be concerned. ExistingConnection will only display currently active connections. To see if isolation level is changed, you will need to trace SQL:BatchCompleted and SP:StmtCompleted, and Prepare SQL. Looking for the text '%set%transaction%isolation%level%repeatable%read%'.
• Another useful Profiler trace is to trace Lock:Escalation. I believe that any table lock escalation in your OLTP environment should be of some concern to you, particularly if you have locking or deadlock issues. However, if you have too many to look at, you could filter in/out by Type=5 (Object), objectId (table) and/or Mode=3 (S-share).

How lucky are we? There are two ways of viewing the locking incident.

1. Unfortunate that it happened and get a quick fix asap for this particular query.
2. Fortunate that this relatively small incident occurred, which enabled us to uncover something quite serious (full table locks in an OLTP environment). The issue, has given us an opportunity to discover the dynamics of connection pooling and helping us find an application wide fix to prevent its future occurrence. Also, if there is a future locking incident, we have more knowledge to diagnose that too. I see this as analogous to someone having high blood pressure and going to the doctor for a fix.  The high blood pressure could be seen as annoying and simply needing some pills to lower the blood pressure, or it could be an opportunity of determining the cause of the high blood pressure (be it diet, stress, etc.) and rectifying the underlying cause.

Fix Query Performance by Creating Statistics

SQL Server's optimiser will automatically create statistics on individual columns that are used in predicates.  See picture of database properties to the right.  However, it will not create statistics on column combinations unless they are composite columns in an index.  This is where you might be able to help the optimiser by creating statistics on column combinations.  This technique is most appropriate where the columns in your predicates have a relationship.  For example, my InternetLogtable (1.2 Million rows) contains a row for each internet request.  It has columns ClientAgentId and ClientHostId, where ClientAgent is the name of the browser, and ClientHost is the IP address of the client machine.  As you can imagine, most requests from one ClientHost will tend to have the same ClientAgent.  However, SQL Server's optimiser does not know this.  It has statistics on ClientAgentId and ClientHostID so if when it determines the cost of the following queries it can quite accurately determine how many rows will be returned.  For the first query it estimates 332 rows will be returned.  For the second query it estimates 110 rows will be returned.  In both cases, the estimate is very accurate.  (There do happen to be indexes on each of the individual predicate columns, although even if there weren't indexes, it would automatically create statistics and get similar estimates.) 

select * from internetlogtable with (nolock)

       where ClientAgentId=72383

select * from internetlogtable with (nolock)

      where ClientHostID=417408

However if we combine both predicates, as in the following query

select * from internetlogtable with (nolock)

where ClientAgentId=72383

    and ClientHostID=417408

The optimiser assumes that ClientAgentId and ClientHostId are independent and thus there will be very few, if any, records satisfying both predicates.  Essentially it is guessing that the number of rows returned will be 332/1200000 * 110/1200000.  It rounds the number up to 1.   Here is the query plan without statistics.  Notice how the Estimated Number of Rows is 1.  Actually, when it says 1, it typically is estimating <1 records, but it always rounds up to 1.

Now, what would happen if we create statistics on the two predicate columns?
create statistics StatsAgentIdHostId
  on internetlogtable (ClientHostID,ClientAgentID)

SQL Server's optimiser now has data on the combinations of ClientHostId and ClientAgentId and finds that this particular combination will return about 110 rows.  Here is the query plan.  Notice that the Estimated Number of Rows is now 110.96!  This is much more accurate.

In the query example I have used here, the query plans are different, although not  dramatically different.  However, if this table was joined with a few others, some of which very large, the query plan can change dramatically and make the difference between a sub second query and a several minute query.

Understanding these subtleties of the SQL Server query optimiser will help you tune queries in an efficient manner.  If you were not aware of this technique to create bespoke statistics, you might solve this issue with a new index on the two columns, which would solve this query performance but at a great cost to overall performance.

By the way, here is an illustration of what statistics SQL Server keeps.  There is a lot of information.  For example, there are even as many key ranges as can fit in an 8KB page with the number of distinct keys, equal keys and rows in the range.  Notice the All density column in the second set of statistics.  It is telling us that a single value for ClientHostId, will on average return 0.0001507841 of the total rows.  It also tells us that ClientHostId and ClientAgentId will, on average return 0.0001226091 of the rows.  Not much less!  Ie, if you have the ClientHostId, the ClientAgentId does not filter the rows much more.

I encourage you to get to know the SQL optimiser better.  The more you know about the optimiser, the more you will be able to tune your queries.  And the way to get to know the optimiser is through the query show plan.

You Too Can Learn About R2

Microsoft have just released an updated kit for developers learning about R2. If you want to know more about it and/or download a trial version goto http://go.microsoft.com/?linkid=9710868

There are lots of pptx, demos, videos and labs.

Expect more resources shortly, but this is a really good way to get a head start with R2.