SQL Server 2008 offers partition-level lock escalation excellent, but…

OK, let me start by saying that I absolutely love when a feature improves in granularity options. Better granularity in locks means that contention is reduced and concurrency improved. And even though the overhead to manage smaller locks (and typically more of them) is usually higher – the improved concurrency benefits often significantly outweight the costs. Additionally, design is often simplified as more granular locks typically means you don’t have to work as hard to minimize contention. Let me give you some history…

In the old days (ok, remember, I started working with SQL Server when I was 12 :) :), SQL Server used to have page-level locking (all releases prior to SQL Server 6.5 sp3). In SQL Server 6.5 sp3 they made an internal change to allow “insert row locks” but that was very targeted in what it improved (in terms of locking). However, in SQL Server 7.0 the locking architecture completely changed (as well as the SE and most of the RE) and that’s where they introduced true row-level locking. This resulted in a significantly reduced complexity in table design. No longer did we have to choose clustered indexes to remove page-level locking (and therefore insert hotspots). And, in fact, some designs solely improved their performance by upgrading. The nice thing about internal changes like these is that they mean you can get away without knowing all of the internals, not worry as much about design and yet still get gains in performance. All of which is good.

However, if you do know the internals and you leverage this knowledge then you might be able to see even greater gains. With the change in locking from page to row (as well as based on other changes to the internal dependencies of non-clustered indexes on the clustering key), databases whose indexing strategies changed between 6.5 to 7.0 made the greatest gains in performance. How did they change – I’ve blogged about “the clustered index debate” a few times so I’ll stay away from that one here… but, the key point is that while these changes might allow you to do more with less work – a bit more work to truly leverage the new features/changes might result in the best combination!

And so, that’s what brings me to partition-level lock escalation. This is an absolutely necessary step to truly allowing SQL Server to treat partitions like mini-tables. Here are a few of the concerns I’ve had with regard to SQL Server 2005 table and index partitioning:

* lock escalation can still occur between the read-only and read-write portions of your partitioned table if the read-only portion is accessed by large queries that escalate (in SQL Server 2005 escalation is either row to table or page to table)
* indexes must be exactly the same for all partitions (not related to escalation but it does have bearing on my solution)
* index rebuilds are supported at the partition level; however, partition-level rebuilds must be performed OFFLINE. Only table-level index rebuilds can be performed online (again, not directly related to escalation but it’s another problem around blocking)

And, this last one is very frustrating to me in general as I’m finding more and more environments moving to “real-time analysis” where they want to do queries on as-close-to-current data as possible. In fact, real-time data warehousing in a relational database is one of the primary areas of improvements for SQL Server 2008 with features such as partition-level lock escalation, improvements in indexed views, grouping sets, and star join optimizations – just to name a few.

So, in terms of partition-level lock escalation. Am I happy that it’s going to be there – for sure! However, the other two issues mentioned above might not change. Having different indexes at the partition level is likely through a feature called “Filtered Indexes” which has not yet appeared in any CTPs but it has been discussed at conferences/events. So, we might solve 2 out of 3 but what about online index rebuilds at the partition level? At this point, I’m pretty sure that they won’t be able to solve that for SQL Server 2008… As a result, I would suggest a slightly different architecture. Instead of using only a single partitioned table for both read-only and read-write data, use at least two tables. One table for read-only data and another for read-write data. If you think this might be defeating the purpose of partitioning… then look at these benefits:

* the read-only portion of the table (which is typically the *much* larger portion of the table – can still be managed with partitioning)
* the read-only portion – once separated from the read-write – can have additional indexes for better [range] query performance
* the read-only portion of the table can actually be partitioned into multiple partitioned tables – to give better per-table statistics (statistics are still at the table-level only so even if your partitioning scheme is “monthly” you might want to have tables that represent a year’s worth of data…especially if your trends seem to change year to year)
* large range queries against the read-only portion of the data will only escalate to the “table” (which is now separated from the read-write data)
* the read-write portion of the data can have fewer indexes
* the read-write portion of the data can be placed on different disks (MORE fault tolerant disks) due to the importance/volatility of the data
* finally, and most importantly, the read-write portion of the data can be maintained completely separately from the read-only portion with regard to index rebuilds

So, then how do you make it appear as one table? Use partitioned views over partioned tables and consider using a synonym for the hot/insert table. At the end of each month (or whatever your partitioning strategy uses – daily, weekly, monthly, etc.), “switch” the read-write portion of the table into the read-only portion of the table. You should be able to do all of this with no data movement and the synonym used for inserts will mean that your applications don’t need to change either.

In summary, I do like the partition-level lock escalation feature especially as it doesn’t require rearchitecting your solution/design. However, by creating two or more tables where read-only data is isolated from read-write, you can leverage many other features (like online index rebuilds).

If you’re more interested in hearing about lock escalation at the partition-level check out Paul’s recent blog entry on it: SQL Server 2008: Lock escalation changes.

Have fun and thanks for reading!
kt