70-465 Question #145: Real Exam Question with Answer & Explanation

Question

You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table. Which three actions should you perform? Each correct answer presents part of the solution. Choose three.

Options

• AUse (SNAPSHOT] hints in the report queries
• BALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
• CALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
• DSET TRANSACTION ISOLATION LEVEL SNAPSHOT
• EUse (TABLOCK) hints in the report queries
• FSET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• GALTER DATABASE [ProdDB] SET ALLOW.SNAPSHOT ISOLATION ON
• HUse (SNAPSHOT] hints in the update statements

Explanation

To reduce contention on SalesOrderDetail, you need to enable Read Committed Snapshot Isolation (RCSI) at the database level, use SNAPSHOT hints for report queries to read committed versions without blocking, and use SERIALIZABLE isolation for update transactions to ensure data integrity.

Common mistakes.

• C. ALTER DATABASE SET READ_COMMITTED_SNAPSHOT OFF disables RCSI, which is the opposite of what is needed; this would prevent row versioning from being used and increase contention rather than reduce it.
• D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT enables snapshot isolation at the session level for DML statements, but the solution requires SERIALIZABLE isolation for updates to ensure data integrity, not SNAPSHOT isolation which can cause update conflicts.
• E. The TABLOCK hint forces a table-level lock on the target table, which would severely increase contention by blocking all other readers and writers for the duration of the query, directly contradicting the goal of reducing contention.
• G. ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON enables snapshot isolation at the database level but does not enable the row versioning needed for READ_COMMITTED_SNAPSHOT behavior; since the solution uses SNAPSHOT hints with RCSI (option B), this separate setting is not required.
• H. Using SNAPSHOT hints in the update statements would cause updates to read from the version store rather than current committed data, which can lead to update conflicts and lost updates, making it inappropriate and potentially harmful for DML operations.

Concept tested. SQL Server snapshot isolation and row versioning for contention reduction

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

No community discussion yet for this question.