You manage a database on an instance of SQL Server 2008. From SQL Server Management Studio, you issue the following query:
SELECT name, lock_escalation_desc FROM sys.tables WHERE name LIKE ‘%History’;
The query displays the following results:
All four history tables are partitioned tables.
With the displayed settings, for which table(s) will lock escalation occur to the partition level?
A. only for the SalesHistory table
B. only for the InvHistory table
C. for the TransHistory and POHistory tables
D. for the TransHistory, POHistory, and InvHistory tables
Correct Answer: B
Explanation/Reference:
In this scenario, lock escalation will occur to the partition level only for the InvHistory table. 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, the InvHistory table has a LOCK_ESCALATION setting of AUTO. Therefore, if you issue an update against a single partition in the table that causes lock escalation, locks will be escalated to the partition level. This will allow users to query other partitions of the InvHistory table as needed. You can use the ALTER TABLE statement to set the lock escalation for a table. For example, the following statement would set the LOCK_ESCALATION setting for the POHistory table to ALTER TABLE POHistory SET (LOCK_ESCALATION=AUTO);
All of the other options are incorrect. The TransHistory and POHistory tables have a LOCK_ESCALATION setting of TABLE, which is the default setting. With this setting, SQL Server will escalate locks to the table level, rather than the partition level. The SalesHistory table has a LOCK_ESCALATION setting of DISABLE, which indicates that lock escalation will not typically occur.
