Partitioned tables in SQL Server 2005 -- looking for real-world caveats

Question

Hi all,

I'm currently benchmarking partitioned tables with SQL Server 2005 to compare them to using two tables (a "live" table and an "archive" table) for a processing queue. The partitioning's being performed on a bit column 'archive', so that when the archive bit is set, the row automagically moves.

Initial testing seems to show that both methods are about even, maybe slightly biased in favour of partitions over two tables (10,000 rows), but I'm ramping the volume of data (500,000 rows upwards) and threads (more than 1 and doing different things) up to see what happens then.

However this aside, benchmarking can prove anything if you use the right test :-) So I'm also seeking any real-world experience (positive and negative), including restrictions that the partitions may add, unexpected performance hits or (on the flip side for example) better manageability.

Cheers,

Chris

Answer 1

Don't neglect the fact that partitioning will allow your deployed solution to place the 'archive' partition on a different filegroup, like a bigger but slower disk spindle. A different filegroup also allows partial backup/restore strategies.

Answer 2

Make sure that your logical and physical partitions are aligned as this can slow down partitioning if the logical and physical is out of synch.