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

The Setup:

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)

Results

(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.

Conclusion:

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.

Andreas

0 replies

Want to join the discussion?
Feel free to contribute!