Developing SQL Databases — Question 19

You have a table that stores transactions partitioned by year. Users occasionally experience performance issues when they access the table. The table is locked exclusively when the records are updated. You need to prevent exclusive locks on the table and maintain data integrity.
What should you do?

Answer options

• A. Set the LOCK_EXCALATION property to DISABLE
• B. Set the DATA_COMPRESSION property to ROW at the partition level
• C. Set the LOCK_EXCALATION property to AUTO
• D. Set the LOCK_EXCALATION property to TABLE
• E. Set the DATA_COMPRESSION property to PAGE at the partition level

Correct answer: C

Explanation

Setting the LOCK_EXCALATION property to AUTO allows SQL Server to automatically decide when to escalate locks based on the number of locks acquired, which helps in reducing exclusive locks during bulk operations. The other options either disable lock escalation entirely or set it to a broader scope, potentially leading to more performance issues instead of alleviating them.