I ran into a situation at work where partition lock escalation solved a particular problem. A table, with about 300 million rows, has a primary key that contains something called a ProcID or Process ID. The process ID is tied to a monthly analytic unit of work for a specific client – more specifically, it identifies client’s loan portfolio reported on a monthly basis. Our client’s portfolios vary greatly in size from roughly a hundred to several million loans. (Before I go any farther, let me explain that this is part of a third-party application. I have no influence over the data model and will not discuss its shortcomings here.)
The clustered index includes the ProcID. Because analysts work on one portfolio or ProcID at a time, clustering on ProcID improves concurrency – except on the very largest client where locks get escalated up to the table level. If an analyst was working on a portfolio for our largest client, other analysts were blocked from their tasks due to table-level lock escalation.
To fix this problem I created a partition function that put our mega-client in one partition and everyone else in the other partition. After that I created an appropriate partition scheme and then enabled partition lock escalation. (See Paul Randal’s excellent article here). With partition lock escalation in place, concurrency has improved dramatically. If an analyst is working on our largest client’s portfolio, other analysts can now work on their portfolios with no blocking. Because of our analyst’s usage patterns, deadlocks are not a possibility.
