Which Transact-SQL statement should you run for each table?

DRAG DROP

You have two database tables. Table1 is a partioned table and Table 2 is a nonpartioned table. Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2. You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.

Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

View Answer

Answer:

Explanation:

Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation C through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different options available:

TABLE AUTO DISABLE

Box 1: Table1, Auto The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level Ceven when the table is partitioned. If you have your table partitioned, and you want to have a Partition Level Lock Escalation (because you have tested your data access pattern, and you don’t cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level.

Box 2: Table 2, DISABLE With the option DISABLE you can completely disable the Lock Escalation for that specific table.

For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks to the HoBT level instead of the table or to disable lock escalation.

References: http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/