Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning: Storage alignment vs. Non-Storage alignment Expand / Collapse
Author
Message
Posted Monday, April 5, 2010 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:28 PM
Points: 5, Visits: 77
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...
Post #896768
Posted Monday, April 5, 2010 12:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #896944
Posted Monday, April 5, 2010 7:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:28 PM
Points: 5, Visits: 77
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.
Post #897182
Posted Friday, March 4, 2011 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
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
Post #1073389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse