You are a database developer for a large automobile manufacturer. Users accessing a large partitioned table in the Prod database complain that their queries take a long time to complete.
You run a SQL Server Profiler trace including the Lock:Escalation event, and notice that an excessive number lock escalations are occurring for the table. You would like to force SQL Server to take partition-level locks when possible to see if this improves query response time.
Which action should you take?
A. Set the READ_COMMITTED_SNAPSHOT database option to OFF.
B. Use sp_configure to set the locks option to a lower value.
C. Use trace flag 1211 to configure lock escalation.
D. Issue an ALTER TABLE statement to set the table’s LOCK_ESCALATION to AUTO.
Correct Answer: D
Explanation/Reference:
To force SQL Server to use partition-level locks for a partitioned table, you should issue an ALTER TABLE statement to set the table’s LOCK_ESCALATION to AUTO. By default, SQL Server will escalate locks to the table level when required. When an excessive number of row-level locks are encountered, SQL Server will escalate the lock to a higher level. This causes the entire table to be locked, which is usually sufficient and reduces memory consumption. However, with partitioned tables, you may not want to lock the entire table, but only a single partition, such as the partition containing rows being modified. This would allow queries against other partitions in the table to execute successfully without being blocked. You can use the LOCK_ESCALATION setting for a table to control how SQL Server will escalate locks. The LOCK_ESCALATION setting can have one of the following values:
TABLE: Lock escalation is performed to the table level, which is the default.
AUTO: Lock escalation is performed to the table level for non-partitioned tables, and to the partition level for partitioned tables.
DISABLE: Lock escalation is disabled, and lock escalation does not typically occur. SQL Server only escalates locks in specific situations where it is absolutely required to ensure data integrity.
In this scenario, you could set the LOCK_ESCALATION setting to AUTO for the table, and partition-level lock escalation would be used.
You should not set the READ_COMMITTED_SNAPSHOT database option to OFF. The READ_COMMITTED_SNAPSHOT database option controls how transactions with the READ COMMITTED isolation level are handled, which would not help in this scenario. When the READ_COMMITTED_SNAPSHOT database option is set to ON, transactions with the READ COMMITTED isolation level use row versioning instead of locking.
You should not use sp_configure to set the locks option to a lower value. The locks configuration option is used to limit the number of locks at the server level.
You can adjust this setting to minimize the amount of memory consumed if necessary. However, the default value of 0 allows SQL Server to dynamically manage the locks as needed.
You should not use trace flag 1211 to configure lock escalation because this would disable lock escalation for the entire instance. With this flag set, SQL Server will not escalate row or page-level locks for any tables in the database.