LOCK_ESCATATION and partitioned tables

Did you know that by default lock escalation on a partitioned table is to table level and not to partition level. This is a bit weird (IMHO) as the whole point of partitioning a table is to split the data in to manageable chunks.

You need SQL Server 2008+ to do this.

Go to your partitioned table’s database and issue this command where XXXX is the table that you want information for…

SELECT lock_escalation_desc FROM sys.tables WHERE name = ‘XXXX’

If the answer is table and you are running version 2008 then you should probably change this to AUTO which will allow SQL Server to restrict lock escalation to a partition instead of the whole table. Note that this does prevent further escalation to TABLE level so make sure this is your desire.

ALTER TABLE [dbo].[XXXX] SET (LOCK_ESCALATION = AUTO)

I have a table with several years of data in it partitioned on year yet most queries I perform are on the most recent 1 or 2 years. Clearly there are very few cases where I would want to escalate a lock to table level on this data so partition lock escalation suites me.

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , . Bookmark the permalink.

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