0

I want to know whether Enabling the Trace Flag 1211 globally prevents the Lock Escalation even in the event of the Huge Memory Pressure.

I have 2 Questions regarding this:-

  1. After Enabling Trace Flag do we still need to alter each and every Table in the User Database to Disable Page Locks.
  2. Suppose if the answer is Yes then we would still need to disable Page Locks at each and every Non Clustered Indexes on the above Tables to prevent Page locks / Lock Escalation.
1
  • Thanks for the replies...But the Product for which we are using the SQL Server as the Backend has asked to Enable the Trace Flag and i found most of the NC Indexes have Page_Locks Off which is creating a huge Impact for Index Maintenance activities.Hence still reiterating the Question if the Trace Flag is enabled whats the reason for having this setting at the Index level. Aug 18 '20 at 13:02
3

1: No, the trace flag is enough to disable lock escalation. No need to do an ALTER TABLE for every table. If you had to do an ALTER TABLE, then the trace flag would be meaningless. Per the official documentation on that trace flag:

Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.

...

...trace flag 1211 prevents escalation in every case, even under memory pressure...

2: No, no need to disable page locks in order to get rid of lock escalation.

I, of course, agree with the others about how you don't want to use this trace flag. I mainly wanted to provide an answer to the question you posted.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.