Sperren & READONLY Dateigruppen vs READONLY Datenbanken

Das Thema Sperren im Zusammenhang mit Readonly Filegroups und Datenbanken geistert immer wieder durch die Foren – und mindestens zur Hälfte leider mit Falschinformationen angereichert. Da ich kürzlich selber in die Falle tappte, möchte ich hiermit schwarz auf weiß festhalten, wie es sich wirklich verhält.

Um ein eindeutiges Bild zu erhalten, habe ich Testreihen unter 3 verschiedenen Isolation Levels durchgeführt:

Read Commited (also dem Standard)
Read Uncommitted (gleichbedeuted mit dem ebenfalls gerne missinterpretierten “NOLOCK”)
Repeatable Read

Das Setup:

Getestet unter: Microsoft SQL Server 2012 – 11.0.2376 Ein Test unter SQL Server 2008 R2 ergab identische Ergebnisse Basis ist eine simple Tabelle, welche mit einem Clustered Index versehen ist. Insgesamt besteht sie aus 36 Pages. (Falls ihr Euch über den Namen wundert: ich hatte auch Test mit einer single-Page Tabelle gemacht)

Also ein identischer Aufbau, bis auf die Datenbank LockingDemo_RW_FG_RO – hier ist die Tabelle auf der Filegroup FG_RO

Die die erste/obere Abfrage zeigt die Gesamtdatenmenge, die untere wird als Testabfrage verwendet:

So sah der Testlauf aus (abgekürzt):

Parallel dazu lief eine Extended Events session (Lock-Escalation tritt in diesem Szenario nicht auf)

Resultate

(Sperren mit Granularität / resource_type = DATABASE ausgelassen)

1. Read Committed

2. Read Uncommitted

(aus offensichtlichem Grund habe ich die Zwischensummen weggelassen 🙂

3. Repeatable Read

Eine Bemerkung zu Statistiken und eXklusive-Sperren auf schreibgeschützten Datenbanken:

Ja, tatsächlich kann man auch auf Read-Only Datenbanken hin und wieder X-Locks beobachten. Und zwar wenn auto-created Statistics einspringen. Das funktioniert erst seit SQL Server 2012. Dabei werden die Statistiken selber in der Tempdb hinterlegt. Um sie zu erzeugen, generiert SQL Server SCH_S und METADATA Locks auf diverse Systemobjekte (hier: sysallocunits, sysschobjs, sysobjvalues, syssingleobjrefs, sysobjkeycrypts):

Das ist sicherlich nicht das am meisten übliche Szenario, aber es tritt auf (insbesondere in AlwaysOn Szenarien mit read-only Secondaries) und gehört zu einem vollständigen Bild.

–

Abgesehen davon erkennt man auf den ersten Blick, dass kein Unterschied im Sperrverhalten zwischen der Tabelle auf einer ReadWrite Filegroup (hier Primary) und der Tabelle auf der ReadOnly Filegroup besteht. Nur wenn die gesamte Datenbank ReadOnly ist, spart sich SQL Server die Page- und Key- Locks. Selbst dort jedoch wird ein Intent-Share-Lock auf die Tabelle gesetzt.

Fazit:

Tabellen auf eine ReadOnly-Dateigruppe zu verlegen spart keine Sperren. Aber es macht natürlich trotzdem oft sehr viel Sinn, Datenbanken so aufzuteilen. Ich denke da nur an: Weniger Backup, schnellere Wiederherstellung, NTFS-Komprimierung etc.