Tag: "statistics"
Locking & READONLY Filegroups vs READONLY Databases // Sperren & READONLY Dateigruppen vs READONLY Datenbanken
Feb 22nd
| (en) 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:
|
(de) 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:
|
USELockingDemo_RW
go
exec sp_help'BigTable'
Data_located_on_filegroup
PRIMARY
index_name index_description
PK__BigTable__3213E83FFF01B718 clustered, unique, primary key located on PRIMARY
USELockingDemo_RO
…
Data_located_on_filegroup
PRIMARY
index_name index_description
PK__BigTable__3213E83FFF01B718 clustered, unique, primary key located on PRIMARY
| Identical structure so far except the database LockingDemo_RW_FG_RO – here the Table resides on filegroup FG_RO | Also ein identischer Aufbau, bis auf die Datenbank LockingDemo_RW_FG_RO – hier ist die Tabelle auf der Filegroup FG_RO |
USELockingDemo_RW_FG_RO
…
Data_located_on_filegroup
FG_RO
index_name index_description
PK__BigTable__3213E83FC5587D01 clustered, unique, primary key located on FG_RO
| The first/upper query shows the total amount of data, the lower is used as the test query: | Die die erste/obere Abfrage zeigt die Gesamtdatenmenge, die untere wird als Testabfrage verwendet: |
1)
SELECT * FROM BigTable
(1000 row(s) affected)
Table 'BigTable'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2) – The Testquery / die Testabfrage
SELECT * FROM BigTable
WHERE id BETWEEN 100 AND 200
(101 row(s) affected)
Table 'BigTable'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
| This is what the test looked like (abbreviated): | So sah der Testlauf aus (abgekürzt): |
--====================
-- 1
--====================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
USELockingDemo_RW
go
BEGINTRAN
SELECT * FROM BigTable
WHERE id BETWEEN 100 AND 200
COMMITTRAN
USELockingDemo_RO
go
BEGINTRAN
SELECT * FROM BigTable
WHERE id BETWEEN 100 AND 200
COMMITTRAN
USELockingDemo_RW_FG_RO
go
BEGINTRAN
SELECT * FROM BigTable
WHERE id BETWEEN 100 AND 200
COMMITTRAN
USEmaster
--====================
-- 2
--====================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
...
--====================
-- 3
--====================
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
...
| Parallel to that, an extended events session was running (Lock-escalation isn’t happening in this scenario) | Parallel dazu lief eine Extended Events session (Lock-Escalation tritt in diesem Szenario nicht auf) |
CREATE EVENT SESSION [Locking] ON SERVER
ADD EVENT sqlserver.lock_acquired(
ACTION(package0.event_sequence,sqlserver.database_id,sqlserver.is_system,sqlserver.session_id)
WHERE ([sqlserver].[database_id]>=(23) AND [sqlserver].[database_id]<=(25)))
ADD TARGET package0.event_file(SET filename=N'D:\SQLData\SQLData1\Locking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Results(Locks with granularity / resource_type = DATABASE left out) 1. Read Committed |
Resultate(Sperren mit Granularität / resource_type = DATABASE ausgelassen) 1. Read Committed |
2. Read Uncommitted(for obvious reason I left out the sub-totals :-) |
2. Read Uncommitted(aus offensichtlichem Grund habe ich die Zwischensummen weggelassen :-) |
3. Repeatable Read |
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. |
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. |
|
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. 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. |
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, das 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. |
Andreas
