3

I have a table that stores accounting transactions for what we call "jobs".

The table looks like this:

(
REGION char(3)
JobNumber char(17)
SeqNum decimal(5,0)
.....
.....
.....
AcctYear decimal(4,0)
)

Unpartitioned, the PK and clustered index is Region, JobNumber, SeqNum.

What we want to do is partition the table by year (AcctYear column), and have each partition located on a separate filegroup. The current and previous year will reside on an SSD and previous years beyond that will reside on cheaper disk storage. Each year has approx. 40 million rows.

I have already partitioned the table, but I am questioning whether I have done it correctly. Let me explain why...

What I have done is added the AcctYear to the end of the PK/Clustered Index (i.e made the PK/CI Region, JobNumber, SeqNum, AcctYear), created the function and schema etc. I have setup 30 partitions, one for every year from 2001 till 2030.

The reason I chose this method of adding the AcctYear to the end of the PK/CI was that I don't always expect the AcctYear to be specified in queries/joins, and if I put the AcctYear at the beginning of the Clustered Index, I would have to either always specify the AcctYear in every query and join, or create another secondary index only on the existing primary key columns.

What makes me doubt what I have done is that when I query for a specific job number AND AcctYear, the Actual Execution Plan shows that it is still looking through the 31 partitions, instead of knowing which partition that data is in. The SHOW STATISTICS IO, also says 31 seeks are being done.

So, have I done this partitioning correctly based on what I want to achieve? or Is there a better way?

Thanks for any help in advance!

Improve this question

2 Answers 2

Reset to default
2

Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble.

First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't specify it.

Second, there are several issue with the optimiser and partitioning that prevents elimination even when it is theoretically possible to achieve it (like in your example). In SQL Server, you can think of a partition as a hidden index column. In your case, if you were to index in such a way that job number was leading the index, your query with both AcctYear and job number might do only a single seek.

If you add the most common queries you expect to your question, it would be possible to advise you on the best partitioning strategy that achieves your goal.

Improve this answer
4
  • Thanks. A common query scenario would be this table joined to the "jobs" table on Region/JobNumber and filtering done on some of the columns in the "jobs" table. The goal of the partitioning though, was to keep the most queried rows (the newest data in our case) on a faster disk, and the older data (i.e less queried) on a slower disk. Ideally I wouldn't specify the accounting year in the join - but because the job is located on the faster disk (through the partitioning), we would see a performance benefit versus not partitioning the table.
    – Adam W
    Dec 9, 2014 at 16:18
  • Is JobNumber an increasing value? i.e. at T(0) are most job numbers smaller than at T(X) ?
    – Thomas Kejser
    Dec 9, 2014 at 16:26
  • No, the Job Number is not necessarily increasing because its an alphanumeric key. XXXNNNNNNNXN Where XXX are characters and the middle N numbers, is a sequential number. However due to the prefix, newer job numbers are not always greater than older ones.
    – user53711
    Dec 9, 2014 at 19:09
  • Would it be possible to create a computed column that rearranges the job number in such a way that it is sequential in nature? If so, you could partition on Job number (which would now correspond to date).
    – Thomas Kejser
    Dec 10, 2014 at 12:16
2

I suspect this is more to do with the data type of AcctYear (DECIMAL(4,0)). When you simply specify WHERE AcctYear = 2014 SQL Server (in my test rig) believes this to be a SMALLINT. You can see this with a few trace flags:

enter image description here

Effective partition elimination relies on data types lining up (amongst other things). Workarounds include changing the datatype of the column (and partition function) to SMALLINT, using a typed variable (eg DECLARE @y DECIMAL(4,0) ) or explicitly casting to your chosen data type, eg

DECLARE @y DECIMAL(4,0) = 2014

SELECT TOP 10 *
FROM dbo.largeTable
WHERE AcctYear = @y

SELECT TOP 10 *
FROM dbo.largeTable
WHERE AcctYear = CAST( 2014 AS DECIMAL(4,0) )

Try these options and let us know how you get on. I was able to achieve partition elimination in this way using my test rig (which I can supply if required):

enter image description here

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.