Quiz: Snapshot isolation against Availability Group Secondaries

This image has an empty alt attribute; its file name is Readable-secondary.png

Q1. To read uncommitted data in a user table in a readable secondary database in an Availability Group…

  •  Use the WITH (NOLOCK) table hint in your query
  •  You cannot read uncommitted data in this case
  •  Use the WITH (ROWLOCK) table hint in your query
You cannot read uncommitted data in this case. Locking hints and statements changing your isolation level are disregarded when you are querying a readable secondary.

Q2. Fill in the blank…

When querying a readable secondary database, your isolation level will be escalated to _____ isolation.


Q3. When querying a readable secondary database and using an explicit transaction, you will see data consistent with…

  •  The time at which the current statement began
  •  The point in time you first read data during the transaction
  •  The oldest version in the version store in tempdb
  •  The current millisecond
The point in time you first read data during the transaction

Q4. You can tell that a readable secondary uses row versioning by…

  •  None of these
  •  Looking at is_read_only in sys.databases
  •  Looking at is_read_committed_snapshot_on in sys.databases
  •  Looking at snapshot_isolation_state_desc in sys.databases
None of these. None of these columns contain information specific to the readable secondary. The values in these columns reflect the settings on the read-write primary database in the Availability Group, and the settings are simply readable on readable secondary databases.

Q5. One way to see the duration of the oldest active transaction in a specific readable secondary database is to use…

  •  The sys.dm_tran_active_snapshot_database_transactions dynamic management view.
  •  The sys.dm_tran_database_transactions dynamic management view
  •  The Longest Running Transaction Time performance counter
The sys.dm_tran_active_snapshot_database_transactions dynamic management view. In the demos, you can see that DBCC OPENTRAN doesn’t report the oldest transaction for a readable secondary. The Longest Running Transaction Time is the time for the longest running query preventing version store cleanup using Snapshot or RCSI on the entire instance– not a specific database. The sys.dm_tran_database_transactions dynamic management view didn’t show us the duration of the transaction.

Q6. Fill in the blank…

Long running transactions against a readable secondary can prevent _________  cleanup from occurring.

Back to: Snapshot Isolation Against Availability Group Secondaries (28 minutes) > Quiz

Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.