For who don’t know, it is know possible to remove Lock Escalation, and this is something that be “granularly” done to a table on SQL SERVER 2008 or Higher without the need to use Trace Flags.
Sample sintax ALTER TABLE table_name_goes_gere SET (LOCK_ESCALATION=DISABLE) .
— PT —
Para quem não sabe, já é possível remover “Lock Escalation”, de uma forma mais “granular” em uma tabela no SQL SERVER 2008 ou superior, sem ser preciso usar Trace Flags.
Exemplo de sintaxe: ALTER TABLE table_name_goes_gere SET (LOCK_ESCALATION = DISABLE).
Find bellow the Lock Escalation Thresholds ( from Books on line ).
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:
A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.