Exam 70-762 topic 1 question 161 discussion

Actual exam question from Microsoft's 70-762

Question #: 161
Topic #: 1

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

Show Suggested Answer

Suggested Answer: C 🗳️
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:
https://www.mssqltips.com/sqlservertip/4359/altering-lock-escalation-for-sql-server-tables/

by EdwardWang at Dec. 30, 2019, 9:47 p.m.

Comments

Submit Cancel

New_user

Highly Voted 5 years, 5 months ago

'A' doesn't provide data integrity cause it doesn't lock any rows when they are updated. C looks like the correct answer

upvoted 20 times

HA2020

4 years, 5 months ago

https://stackoverflow.com/questions/39960423/drawbacks-of-disabling-lock-escalation It does not. But it is not advisable as it affects performance and can result in errors as lower-level locks take too much memory.

upvoted 1 times

...

MML

Highly Voted 5 years ago

Consider changing the lock escalation mode to AUTO for the partitioned part and DISABLE for the non-partitioned tables. Our table here is partitioned so Lock_Escalation to Auto is better option here..

upvoted 5 times

...