With the release of SQL Server 2005 came a new feature called Partitioning. This feature allows one to set up physical storage divisions for a single table so that logically it is one collection but physically the data is stored across several smaller tables. Many people use this feature as a means to improve performance on very large tables.

Not the Problem

Please note that Microsoft officially claims that the "proper" use of Partitioning is merely to load / ETL data in faster for very large tables and not to improve query performance on general queries. However, whatever their intention was in providing this feature, many people are using it to enhance query performance. The intention of this article is to address dealing with tables that are partitioned as opposed to discussing general theory regarding whether you should or should not be using it in the first place.

The Problem

Storing the data in a partitioned table requires the use of the Partition Key (via a Partition Function) so that SQL Server knows which physical File Group to store the data in (as determined by the Partition Scheme which makes use of a Partition Function). When accessing this data via the Partition Key (in the WHERE clause or JOIN) the queries are, if not a little faster then at least the same speed as the single, non-partitioned table. However, when trying to do searches or aggregations across the partitions (i.e. not making use of the Partition Key in the WHERE clause or JOIN) the queries can actually be much slower than with the non-partitioned table. It appears that the optimizer does not deal well with having to scan all of the partitions.

The Background

Like many companies, we have multiple clients in each table identified by a CustomerID INT field. We decided to partition the table in order to break up the clients into physical groups to reduce the overall table size. We implemented an arbitrary PartitionID TINYINT field in each table so that we could balance the amount of data across the partitions since clearly some Customers have radically more data than others and so doing a simple, even distribution of Customers across the partitions might not have produced the best results. We chose to do 10 partitions numbered 0 through 9.

The vast majority of our queries are Customer specific, hence PartitionID specific, and we have seen performance improvements, or at least no degradation, from this implementation. However, there are a few cases when we do look for a value across all CustomerIDs (typically for archival and garbage collection) and these are where we have seen some performance issues arising from our use of Partitioning.

The Specifics

BEFORE

In this case we started out with a table that has 90 million rows and is almost 10 GB in data size (excluding index space). This might not be a large table to some people but it is certainly large enough to be of interest for this topic. The structure of the table (simplified for this example), is:

ExampleTable
(
[ExampleTableID] [bigint] NOT NULL IDENTITY(1, 1),
[CustomerID] [int] NOT NULL,
[IntDate] [int] NOT NULL,
[Field04] [int] NOT NULL,
[Field05] [int] NOT NULL,
[Field06] [int] NULL,
[Field07] [int] NULL,
[Field08] [int] NOT NULL,
[Field09] [int] NOT NULL,
[Field10] [decimal] (15, 4) NOT NULL,
[Field11] [money] NULL,
[Field12] [money] NULL,
[Field13] [decimal](15, 4) NULL,
[Field14] [datetime] NOT NULL
)

The Indexes are:

• a CLUSTERED INDEX on (IntDate ASC, CustomerID ASC) ON [Tables]
• a NONCLUSTERED PRIMARY KEY on (ExampleTableID ASC, CustomerID ASC) ON [Indexes]
• a NONCLUSTERED INDEX on (CustomerID ASC) ON [Indexes]

There are a few other Indexes and Foreign Keys but the above items are what is relevant here.

Given the above structure (no Partitioning yet), the query to get the MIN(IntDate) value works rather quickly:

SELECT MIN(IntDate)
FROM dbo.ExampleTable
-- cost = 0.0032843
-- 1 second first run, 0 seconds additional 3 runs
-- STATISTICS IO info: Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0
-- STATISTICS TIME (Execution only) info: CPU time = 0 ms, elapsed time = 0 ms.

Just to note, the "cost" values in this article refer to the "Estimated Subtree Cost" as returned in the Query Execution Plan. According to http://msdn.microsoft.com/en-us/library/ms178071.aspx, this is "The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree." I use this value as a relative indication of performance; the larger the value the more resources required and hence longer response time / less performant (and for those who say that "performant" isn't in the dictionary, rest assured it is a perfectly cromulent word). For this article all cost values come from Actual, not Estimated, Execution Plans.

AFTER

Once we added the PartitionID TINYINT NOT NULL field we also changed the Indexes to be (much of the Index structure is the same as the non-partitioned table so I simply bolded the changes):

• a CLUSTERED INDEX on (IntDate ASC, CustomerID ASC) ON [PartitionedTables](PartitionID)
• a NONCLUSTERED PRIMARY KEY on (ExampleTableID ASC, CustomerID ASC, PartitionID ASC) ON [PartitionedIndexes](PartitionID)
• a NONCLUSTERED INDEX on (CustomerID ASC) ON [PartitionedIndexes](PartitionID)

Adding PartitionID to the PK is a bit obvious (and required by Partitioning). But for those who are unfamiliar with Partitioning, the ON clause of the Index (assuming you want the Index to be partitioned as well), needs to point to a Partition Function and not a File Group (which is a must for the CLUSTERED Index). In this case we have separate Partition Functions and Partition Schemes for Tables and Indexes to mimic our [Tables] and [Indexes] File Groups for non-partitioned items. While these changes either had no negative impact on the majority of queries (and maybe even helped a few), they had an unexpected effect on the MIN(IntDate) query:

SELECT MIN(IntDate)
FROM dbo.ExampleTablePartitioned
-- cost = 388.341
-- 12 seconds first run, 11 seconds additional 3 runs
-- STATISTICS IO info: Scan count 11, logical reads 246493, physical reads 0, read-ahead reads 246484
-- STATISTICS TIME (Execution only) info: CPU time = 29296 ms, elapsed time = 11412 ms.

This was also a huge drain on CPU resources as we learned from MOM using Windows Performance Counters: a machine which averages 15% CPU utilization jumped to an average of 75%. Ouch!

AFTER REEXAMINED

Before I go on to the solution I need to address one point regarding the partitioning. Some might say that this would have worked better if we would have put the PartitionID field into the CLUSTERED INDEX and not just the NONCLUSTERED PRIMARY KEY. That seems fair enough so I tried that as well. I copied the table and created just the CLUSTERED INDEX and NONCLUSTERED PRIMARY KEY. The only difference here (outside of the name, of course) is that the CLUSTERED INDEX was now:

(IntDate ASC, CustomerID ASC, PartitionID ASC) ON[PartitionedTables](PartitionID)

SELECT MIN(IntDate)
FROM dbo.ExampleTablePartitionedDiffCluster
-- cost = 461.74
-- 17 second first run, 17 seconds additional 3 runs
-- STATISTICS IO info: Scan count 11, logical reads 345536, physical reads 0, read-ahead reads 345536
-- STATISTICS TIME (Execution only) info: CPU time = 29532 ms, elapsed time = 17694 ms.

Wow, that is even worse! I looked at the Execution Plan of the ExampleTablePartitioned query and found that it was using the CustomerID-only NONCLUSTERED INDEX (hence why I listed it above) so I added that back in for this table (with the same index structure) and ran the query again:

SELECT MIN(IntDate)
FROM dbo.ExampleTablePartitionedDiffCluster
-- cost = 389.001
-- 12 seconds first run, 11 seconds additional 3 runs
-- STATISTICS IO info: Scan count 11, logical reads 247196, physical reads 0, read-ahead reads 247196.
-- STATISTICS TIME (Execution only) info: CPU time = 29016 ms, elapsed time = 11788 ms.

Now the same query against this table is performing the same as the original partitioned table that did not have the PartitionID field in the CLUSTERED Index. So, adding PartitionID to the CLUSTERED Index did not help. However, I do find it interesting that the query cost is slightly higher here (389.001 compared to 388.341) but that is most likely due to the increased size of the CLUSTERED Index and hence the increased size of the CustomerID-only NONCLUSTERED Index.

The Solution

Ok, now for the fun stuff. Clearly something had to be done to improve the performance of these cross-partition queries so I tried something maybe non-intuitive: I figured that the optimizer wanted/needed the Partition Key since queries using the Partition Key return instantly, so I tried iterating over the partitions so that we can make use of the PartitionID field.

DECLARE @IntDates TABLE (IntDate INT)
DECLARE @PartitionID TINYINT
SET @PartitionID = 0
WHILE (@PartitionID < 10)
BEGIN
INSERT INTO @IntDates (IntDate)
SELECT  MIN(etp.IntDate)
FROM    dbo.ExampleTablePartitioned etp
WHERE   etp.PartitionID = @PartitionID
SET @PartitionID = (@PartitionID + 1)
END
SELECT  MIN(IntDate)
FROM    @IntDates

I chose a Table Variable over a Temp Table to hold the individual MIN(IntDate) values since there would only ever be 10 and I would never JOIN this to a real table.

The Results

This solution--as silly as it might seem--ran in 1 second the first time and 0 seconds each time after that. Each of the ten iterations of SELECT MIN(etp.IntDate) has a query cost of only 0.0132858, so all 10 together are 0.1328580. The final MIN(IndDate) on the Table Variable has a query cost of 0.0032842. The grand total for the set of 11 queries (10 loops plus the final SELECT on the Table Variable) is a cost of 0.1361422. The cost is slightly higher than that of the original non-partitioned table (0.1361422 compared to 0.0032843) but still FAR lower than the 388.341 cost of the simple SELECT MIN() on the entire partitioned table. And this rather odd solution still comes back instantly. Lastly, CPU utilization is now back to the original 15% average.

-- 10 Iterations
-- STATISTICS IO info: (@IntDates): Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0
-- STATISTICS IO info: (ExampleTablePartitioned): Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
-- STATISTICS TIME (Execution only) info: CPU time = 0 ms, elapsed time = 0 ms.

-- Final SELECT
-- STATISTICS IO info (@IntDates): Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
-- STATISTICS TIME (Execution only) info: CPU time = 0 ms, elapsed time = 0 ms.

The Conclusion

Regardless of why or how you are using Partitioning, you will quite likely run into a situation where you need to look across the partitions in a particular query. Doing so has the consequence of being much slower than expected as well as pegging the CPU. In order to get around these two down-sides you should, if possible, gather the relevant data from each partition into either a Table Variable or Temporary Table and then do a final query against that single data source.

Copyright © 2010 Solomon Rutzky