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.