Quiz: Snapshot isolation against Availability Group Secondaries

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

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

1.  Run SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2.  Use the WITH (NOLOCK) table hint in your query
3.  You cannot read uncommitted data in this case
4.  Use the WITH (ROWLOCK) table hint in your query

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…

1.  The time at which the current statement began
2.  The point in time you first read data during the transaction
3.  The oldest version in the version store in tempdb
4.  The current millisecond

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

1.  None of these
2.  Looking at is_read_only in sys.databases
3.  Looking at is_read_committed_snapshot_on in sys.databases
4.  Looking at snapshot_isolation_state_desc in sys.databases

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

1.  The sys.dm_tran_active_snapshot_database_transactions dynamic management view.
2.  The sys.dm_tran_database_transactions dynamic management view
3.  The Longest Running Transaction Time performance counter
4.  DBCC OPENTRAN

Q6. Fill in the blank…

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

Scroll down for the answer key :point_down:

Answer Key

• A1. 3. 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.
• A2. snapshot
• A3. 2. The point in time you first read data during the transaction
• A5. 1. 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.
• A6. 1. 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-1. not a specific database. The sys.dm_tran_database_transactions dynamic management view didn’t show us the duration of the transaction.
• A6. ghost