[Table Partitioning] what is your lock escalation?

Bare bones query.  This will give you the lock escalation for your partitioned tables.  In our experience we've had good luck with AUTO (which is _not_ the default!), but the reason it's not the default is because some MS customers had deadlock issues with it enabled.  For us, it prevents issues with queries blocking inserts.

Also, let me put a suggestion in your ear: don't go with just one table for inserts & queries.  Create a partitioned table to catch live data, keep a week's worth of data.  Create a mechanism to copy that into your history (what gets queried) table.  Make the history table AUTO, and experiment with your live table.  Compress the history.  Make a view between the two if you need.  Like much on this blog, let my pain be your gain.


SELECT distinct tables.name,
lock_escalation_desc
FROM sys.partitions
inner join sys.tables
on tables.object_id = partitions.object_id
and partition_number >1
order by name