PASS Summit Unite 2009 Speaker Tip
Many people know that with the NOLOCK hint (equivalent to the READ UNCOMMITTED isolation level), a shared lock isn’t acquired and the query may return uncommitted reads. But this hint actually changes the storage engine’s decision-making process and may cause a query to return the same row multiple times or skip rows.
When the execution plan for a query that reads data has an Index Scan operator with the property Ordered: False, the storage engine can perform the request in one of two ways:
- Using an allocation order scan, based on IAM pages.
- Using an index order scan, based on the index leaf linked list.
If the engine uses an allocation order scan and page splits occur due to insertions or updates, you can get inconsistent reads. Say the scan visits a certain page and then a split in that page causes some rows to move to a new page that the scan hasn’t reached yet. In such a case, the scan will reread the moved rows. Similarly, if the scan hasn’t visited a page yet and the page splits, moving some rows to a page the scan already passed, those rows will be skipped.
Thus, the storage engine usually (e.g., under READ COMMITTED isolation) uses an index order scan to perform the request, even though logical index fragmentation can decrease the index order scan’s performance. If you use the NOLOCK hint, however, the storage engine assumes you’re willing to sacrifice consistency for speed and chooses an allocation order scan.
To avoid these problems and maintain read consistency, you can use READ COMMITTED SNAPSHOT. However, carefully test this isolation level, which uses row versioning and puts overhead on tempdb. SQL Server writes row versions whenever updates and deletes occur. So systems that involve mostly selects and inserts, with infrequent updates and deletes, typically benefit most from row-versioning-based isolations.
Read more from Itzik on his Nov. 6 PASS Summit 2009 post-conference seminar, "Query and Index Tuning for SQL Server 2005 and 2008."
Editor's Note: SQL Server Magazine would like to thank PASS for providing this technical tip.