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…

Using SNAPSHOT hints in report queries directs those queries to read row versions from the tempdb version store rather than acquiring shared locks, eliminating read-write contention on the SalesOrderDetail table without requiring changes to the update transactions.

ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON enables the RCSI database-level option, which activates the row versioning infrastructure in tempdb required to support snapshot-based reads, allowing SNAPSHOT hints in queries to function correctly and reducing blocking.

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.

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.

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.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE on the update transactions ensures that the update operations acquire range locks to prevent phantom reads and maintain the highest level of consistency, protecting data integrity while the report queries use snapshot reads to avoid contention.

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.

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.