April 5, 2010 at 9:03 am
Hello,
I have a 6 billion row table that I want to partition. My question lies with the clustered index and partition key. I want to partition on date, but cluster index on client, date. This would cause the indexes to be aligned, but storage would not be. Partitioning on day makes alot of sense for maintenance reasons, but our reporting relies more on Client, Date.
Does anyone see any consequences with this set up? My worries is the maintenance of the clustered index. Would the clustered index need to be maintained on the 6 billion rows, or would the partition swapping help maintain it?
Thanks...
April 5, 2010 at 12:28 pm
Taking into consideration most queries rely on cluster index on client+date - as I can see it partitioning by date will certainly hurt performance during querying.
As a result of partitioning by date column SQL Server will build as many client+date cluster indexes trees as date partitions are created. You can check this by checking sys.partitions system table - you will find one client-date entry per date partition.
In this case I think you have to decide what is more important to you, a partitioning schema that helps during maintenance or a partitioning schema that helps during querying.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 5, 2010 at 7:06 pm
Thanks for the quick response, Paul. There are about 150 clients and 200 weeks. That's about 30,000 client/date combinations being created if I were to partition on date and cluster on client/date. Is this not an acceptable number?
Thank you for your insight.
March 4, 2011 at 9:01 am
Frank-461314 (4/5/2010)
Hello,I have a 6 billion row table that I want to partition. My question lies with the clustered index and partition key. I want to partition on date, but cluster index on client, date. This would cause the indexes to be aligned, but storage would not be. Partitioning on day makes alot of sense for maintenance reasons, but our reporting relies more on Client, Date.
Does anyone see any consequences with this set up? My worries is the maintenance of the clustered index. Would the clustered index need to be maintained on the 6 billion rows, or would the partition swapping help maintain it?
Thanks...
I know this is old, but, if the reporting is mainly on client and date, isn't that the same as reporting on date and client? That is, the queries will include both the date (or a date range) and the client ID. So partitioning and aligning on the date should be fine, I would think.
That is, I would cluster on date and then client. Also, this makes adding new data faster since tables are not split so data can be inserted in client order.
If this is not right, someone please let me know. Thanks.
David Walker
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply