Locking & READONLY Filegroups vs READONLY Databases
The Topic Locking and Read-Only for filegroups and databases is one of the ongoing myths around SQL Server in forums – and at least half of the information unfortunately wrong. Since I recently fell into the trap myself, I want to write down, how it really is. To have a definite picture, I made 3 test series under 3 different isolation levels:
- Read Commited (the default)
- Read Uncommitted (equal to the often misunderstood “NOLOCK”)
- Repeatable Read
Tested under: Microsoft SQL Server 2012 – 11.0.2376 A Test using SQL Server 2008 R2 produced identical results The base is a simple table, which has a Clustered Index. Altogether it consists of 36 pages (In case you wonder about the name – I had also tested with a single-Page table)
Identical structure so far except the database LockingDemo_RW_FG_RO – here the Table resides on filegroup FG_RO
The first/upper query shows the total amount of data, the lower is used as the test query:
This is what the test looked like (abbreviated):
Parallel to that, an extended events session was running (Lock-escalation isn’t happening in this scenario)
(Locks with granularity / resource_type = DATABASE left out)
1. Read Committed
2. Read Uncommitted
(for obvious reason I left out the sub-totals 🙂
3. Repeatable Read
A note about Statistics und eXclusive Locks on ReadOnly-Databases:
Yes, one can indeed watch X-Locks on Read-Only databases. And this happens when auto-created stats jump in. This works only starting with SQL Server 2012. The statistics themselves are stored in Tempdb. In order to create those, SQL Server generates SCH_S and METADATA locks on several system objects (here: sysallocunits, sysschobjs, sysobjvalues, syssingleobjrefs, sysobjkeycrypts):
This is of course not the most common scenario, but it does happen (especially in AlwaysOn scenarios with read-only secondaries involved) and belongs to a complete picture.
Besides that one can see on first sight that there is no diffference in the Locking behaviour beetween the table on a ReadWrite Filegroup (here Primary) and the table on the ReadOnly filegroup.
Only if the whole database is ReadOnly, SQL Server saves himself the Page- and Key- locks. Even there an Intent shared Lock is placed on the table.
Putting Tables onto a ReadOnly-Filegroup does not save Locks. But it often does make a lot of sense to break up databases in this manner. Just thinking of: less backup, faster restore, NTFS-compression etc.