SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning: Storage alignment vs. Non-Storage alignment


Partitioning: Storage alignment vs. Non-Storage alignment

Author
Message
Frank-461314
Frank-461314
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 85
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...
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14869 Visits: 4639
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
Frank-461314
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 85
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
David Walker-278941
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 231
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search