Disable Lock Escalation in SQL SERVER 2008 or Higher

No comments

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s