Home » Microsoft » 70-762 » What should you do
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
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/Reference:
Reference
The default lock escalation mode is called TABLE, it implements SQL Server’s lock escalation on all types of tables whether partitioned or not partitioned.
There are two more lock escalation modes AUTO and DISABLE.
The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.
The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
References
httpswww.mssqltips.comsqlservertip4359altering-lock-escalation-for-sql-server-tables
