Developing SQL Databases — Question 67

You have a nonpartitioned table that has a single dimension. The table is named dim.Products.Projections.
The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.
Users report that when they query data from dim.Products.Projections, the responses are slower than expected. The issue occurs when a large number of rows are being updated.
You need to prevent the updates from slowing down the queries.
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.

Correct answer: A

Explanation

The correct choice is A, using the NOLOCK option, which allows queries to read data without waiting for locks held by other transactions, thus preventing updates from slowing down query responses. The other options do not specifically address the issue of lock contention during updates, making them less effective in this scenario.