Get rid of those NOLOCK hints…

When we encountered excessive blocking issues with reports, one tempting solution was to use the WITH NOLOCK table hint to get the report running smoothly and avoid blocking behavior completely. However, since SQL Server 2005 there is a better solution using Snapshot technology. As always, there is good news and bad news. Which do you want first?

With SQL Server 2000 and prior releases, concurrency (the ability for multiple users to access data at the same time) was controlled via locking. With the default isolation level in SQL Server, a read operation would issue a shared lock and an update operation would issue an exclusive lock. The good news is that only the rows affected would be locked. (In the early days of SQL Server, page-level locking caused all rows on a data page to be locked even if they were not being accessed. Not good.). The bad news was that shared locks would block updates and exclusive locks would block reads resulting in updates being delayed and reports “hanging”.

In order to avoid this blocking behavior, we would run reports when no updates were being performed. Nowadays this is not always possible with the 24x7 design of web-based systems. So another alternative was that we would run the report query using the WITH NOLOCK table hint. This would not only run the query without issuing shared locks, but it would also ignore exclusive locks. The good news would be no blocking, the report would run unhindered and updates would complete faster too. However, since exclusive locks were being ignored, this meant that we were reading uncommitted or “dirty” data. So potentially, we would be reporting on data that was later rolled-back and never committed, such as during a network error. In other words, the report would not be 100% reliable. That’s the bad news.

In SQL Server 2005 and beyond, this is still the default behavior. However, there are alternatives thanks to the latest snapshot technology. The SQL Server database engine now uses TempDB to keep track of changing data via row-versioning so now has a logical “snapshot” of the data as it was before any lock was issued. The end effect is that readers do not block writers and writers do not block readers. But the difference is that all data being read is committed data and 100% reliable.

If you wish to apply this technology to your database at the individual statement level, you can set the database option READ_COMMITTED_SNAPSHOT. From then on, each statement will use the row-versioning cache in TempDB to get the data should a lock be issued on that data. All statements against the database will use this technology, so no application changes are needed for this option.

If you prefer to choose to use this technology selectively, you can first set the database option ALLOW_SNAPSHOT_ISOLATION. When this option is enabled, you can then turn on the snapshot technology on a connection by connection basis by using the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement. This means you can choose which connections use the snapshot technology and by implication which do not. So in our example, you could turn it on just for reporting transactions, if that was the major bottleneck. Again, only committed data is returned so you can rest assured that your reports are accurate.

Microsoft has made a conscious decision to use extra storage and I/O to improve overall performance. The good news is that you can now easily eliminate blocking behavior without having to compromise by reading dirty data. The bad news is that the server is using TempDB much more intensely than before so you will need to keep an eye on its storage and performance requirements.

But now is the time to go into your applications and weed out those NOLOCK hints. The good news definitely outweighs the bad news. And remember you always have a choice.



Recent Posts:

Travel Budget cut? Think Virtual…

Develop Custom Reports in SSMS...

Universal Health Care for SQL Server? SQL Profiler - 2008 style...

More on Server Time Dimensions…

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2009 IDG Communications, Inc.

IT Salary Survey: The results are in