First published on MSDN on Apr 26, 2013
The topic I received most in my inbox this week was
redo blocked on a secondary
while attempting to acquire SCH-M (schema modify) lock.
First of all, this is expected behavior and you can monitor for this with your standard blocking activities (sys.dm_exec_requests, blocked process TRC event, blocked process threshold configuration setting(s) and the log_redo_blocked XEvent.)
The SQL Server 2012 implementation of Always On extended the database mirroring (DBM) capabilities by allowing read only queries and backups against a secondary replica. With this new activity comes additional overhead.
1. When a replica is marked for read only capabilities the updated/inserted rows on primary add additional overhead for the row versioning to help support snapshot isolation activities of the read only connections.
2. When queries are run against the secondary the SCH-S (schema stability) lock is held during the query to make sure the schema of the object can’t be changed during the processing of results.
In the case of the blocked, redo the read only clients typically have long running queries and the object is changed (ALTER, create index, …) on the primary. When the DDL activity arrives on the secondary the SCH-M is required to complete the requested, redo change. This causes the redo worker to become blocked on the long running, read only query(s).
You can monitor the redo queue size and other performance counters to determine the relative impact of redo being blocked and make any necessary business decisions to KILL the head blocker(s). It will look no different than a production server with a head blocker that you resolve today.
Microsoft is evaluating, for future builds, the ability to configure a replica to automatically kill a redo blocker, allowing redo to progress.
Bob Dorr - Principal SQL Server Escalation Engineer
