SQL2005 partitioning

Question

SQL2005 partitioning

David.Poole

SSC Guru

Points: 76001
More actions
December 20, 2005 at 2:18 am

#92528

Are there any metrics for performance boosts using SQL2005 partitioning?
I won't have the luxury of specifying different physical devices for the differing partitions so I am not sure as to how much of a benefit I will gain from the partitioning function.
I have several huge tables of which only a few million rows are relevant to the nightly processes.
Is SQL2005 intelligent enough to know that the data resides within a specific partition?
LinkedIn Profile

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

There are lots of inforamtion about table partitioning. If your application just accesses the latest records (a few millions, right?), you need to find out the partition column (datetime column of the record, for example). You can estimate the time frame for the last/current partition according to the hotspot data. You can have just two partitions, one for current accessing data, one for historical data. Since the partition will have separate b-tree, the access to it will be faster. You can setup a job to split the current partition into two and merge the older one of the two with the historical partition, let's say weekly.

For better performance , one best practice is to align all indexes of a partition with its data.

If you have lots of partitions, the performacne of data accessing data will be affected. I remeber one article talked about this based on its test.

And yes, SQL2005 is intelligent enough to know from which partition to load data for your query.

MarcO-421911 SSC Veteran Points: 211 More actions · Answer 2

If you can make use of Partition Elimination then Yes it is. Here is a prity good article. http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx

MarcO-421911 SSC Veteran Points: 211 More actions · Answer 3

If you have a table partitioned on Date partioned table and the primary key clustered index of the table is needs to have both Date and ID. If each date is in a seprate partion and the index is Date, ID does that mean that the selectivity on the index is bad since all the values in each partition are the same?

The partion function would look something like

CREATE PARTITION FUNCTION PFN_DimFact(Datetime) as RANGE RIGHT FOR VALUES( '2007-03-01 00:00:00.000', '2007-03-02 00:00:00.000', '2007-03-03 00:00:00.000');