Sometimes intersection of two simple ideas can make me look out of the window and gasp. Extreme cases lead to Kotov syndrome.

When I was working as full time production DBA there were few moments when my ‘I know - I know’ (which is third and final level of understanding matter) became ‘I think I know’ (second stage, just above ‘black box’) because of particular combination of simple ideas. It didn’t happen often and maybe that’s why these situations are hard to forget:

• Filtered indexes and statistics auto updates
• Enabling RCSI in mirrored database
• Certificate-based users and access to DMOs
• Tail log backup in EMERGENCY mode
• DDL triggers with transactional replication

Now these are among my favourite interview questions that can expose candidate’s thought process. Recently I’ve been asked to review code that contained NOLOCKs in RCSI-enabled database. Was it one of these knots? It looked like it initially and reminded me others (plus gave an idea for this post), but in fact it’s very simple. Quick win first - using NOLOCK hint is a bad idea from maintenance point of view and must be justified in the first place. SET TRANSACTION ISOLATION LEVEL and separating (isolating!) modules based on purpose/access pattern is much better approach. And now interesting part - are there any benefits of using READ UNCOMMITED ISOLATION LEVEL in READ COMMITTED SNAPSHOT ISOLATION LEVEL enabled database? Key to answer is to realise these are two very different isolation levels with different rules. Writers do block writers in both - so for UPDATEs and DELETEs simple no. Next readers - RU will skip version store and go directly to data (that can be uncommitted/dirty). RCSI will go to version store to get the latest committed version of data. In both cases readers don’t acquire locks (ignoring bug that changes behaviour of RCSI introduced in CU 11 for SQL2008R2 SP2). The more active base objects are - the slower reader can be in RSCI (performance sacrifice for consistency) and the higher probability of dirty reads in RU. As simple as that. In RCSI there are special cases to watch out for. They fall under ‘preserving correctness’ category:

• Cascading UPDATEs/DELETEs
• Indexed views maintenance
• Integrity checks (foreign keys updates)

Query optimiser can decide to elevate isolation level for the time of statement (not transaction). For RCSI enabled database it’s good idea to monitor locks (e.g. look for range locks) using profiler/server side trace instead of sys.dm_tran_locks.

Going back to script - I looked at baseline (number of executions per minute, max and average logical reads and writes, max and average elapsed time), checked what stored procedure actually supposed to do and examined profiler output. All that just to recommend removing NOLOCKs.