SQL Server 2008 Table Partitioning

Question

I have a huge database that has several tables that hold several million records. It's holding engineering data for a client and continually grows. This is impacting performance, even with optimised indexing. So I've been looking at partitioning.

However, I would be looking at partitioning on a version held in a table. In it's most simplistic form the table comprises of:-

VersionID int
SheetID int
CreationDate datetime
SomeDate nvarchar(255)
Version int

And the data would look something like:-

1, 1, 2010-09-01, blah, 1
2, 1, 2010-09-02, more blah, 2
3, 1, 2010-09-04, blah, 3
4, 2, 2010-09-02, more blah, 1


For every new change to a 'sheet' in the system, this table has a new entry added with a new version. Ideally I want to partition the table so that I have the top 2 versions for each 'sheet'. So from the table above I'd want versions 2 & 3 for sheet id 2, and version 1 for sheet id 2, with the rest moved to the partition. From what I've read this doesn't seem possible. Am I right or wrong?

If I'm wrong, then following on from this I have a bunch of tables that all link back to this table. These hold the various versions of data entered. Can I partition these based on the partition of the 'main' table, or does the partition have to be specifically based on a column from the table it refers to?

NB I'm not the most au fait SQL developer, so apologies if this is a completely daft question!

Answer 1

For the quantity of 'several million records' - partitioning would be overkill - I suspect you would be better looking at the query plans of the poor performing queries and reviewing the indexes that are satisfying them.

The goal of partitioning is to provide a way of aging older data without large scale deletions, partition elimination as a performance gain is a by-product and can still be outpaced by a suitable clustered index - depending on the index size / table size.

You can do range partitioning within SQL, but it would be quite a lot of overhead to implement such a variable moving window, if I judge the requirements correctly.

In terms of the final question, to be partitioned on the same value, that value would have to appear in both tables, although technically you can partition on a computed column so if you had a schema bound function that could be used to compute the corresponding value, then that would suffice. (However, I think this is overkill, given the small number of rows.)