Developing SQL Databases — Question 119

Your company runs end-of-the-month accounting reports. While the reports run, other financial records are updated in the database.
Users report that the reports take longer than expected to run.
You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only.
What should you do?

Answer options

• A. Use the NOLOCK option.
• B. Execute the DBCC UPDATEUSAGE statement.
• C. Use the max worker threads option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
• F. Set SET XACT_ABORT to ON.
• G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
• H. Use the OUTPUT parameters.

Correct answer: E

Explanation

The correct answer is E, as enabling SET ALLOW_SNAPSHOT_ISOLATION allows transactions to read committed data without blocking other operations, thus improving report generation time. The other options either do not address the issue of accessing committed data efficiently or are unrelated to transaction isolation levels.