Partitioning: Storage alignment vs. Non-Storage alignment

Question

Partitioning: Storage alignment vs. Non-Storage alignment

Frank-461314

SSC Enthusiast

Points: 189
More actions
April 5, 2010 at 9:03 am

#386645

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...

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.

Frank-461314 SSC Enthusiast Points: 189 More actions · Answer 2

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.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 3

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