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.
Q1. To read uncommitted data in a user table in a readable secondary database in an Availability Group…
- Run SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- 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
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
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
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
- 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 👇
- 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