February 8, 2013 at 2:47 pm
I am kind of new in Table Partition. We have a huge table that we would like to do partiion on. My question is how many columns you can use to partion your table? For e.g OrderNumber, CustomerNumber, Date...?? If only one is allowed, how do we chose which one is best for the optimal performance?
February 8, 2013 at 3:07 pm
SQL_Surfer (2/8/2013)
I am kind of new in Table Partition. We have a huge table that we would like to do partiion on. My question is how many columns you can use to partion your table? For e.g OrderNumber, CustomerNumber, Date...?? If only one is allowed, how do we chose which one is best for the optimal performance?
Firstly, if the idea behind partitioning the table is to get better performance then, forget about partitioning. Rarely table partitioning improves performance.
Performance is achieved mostly thru a proper indexing strategy and query fine tuning.
Table partitioning is on my experience a valid strategy to simplify administration - mostly related to table purging.
_____________________________________
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.February 8, 2013 at 4:12 pm
Archiving the data is also what I am trying to achieve as well.
February 8, 2013 at 4:22 pm
SQL_Surfer (2/8/2013)
Archiving the data is also what I am trying to achieve as well.
Good. Now you have a case for table partitioning.
Planning on archiving the data based on a particular date column?
If this is the case, partitioning strategy should be: range partitioning by such date datatype column, one partition per chunk you are planning to archive, like... if archiving/purging is going to pick a whole month then each partition should store one month.
_____________________________________
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.February 9, 2013 at 7:05 am
Currently, my table has different sets of keys. Can theses remain intact? Or do I need to create date as primary key?
February 9, 2013 at 9:36 am
SQL_Surfer (2/9/2013)
Currently, my table has different sets of keys. Can theses remain intact? Or do I need to create date as primary key?
Primary Key and Paritition Key can be independent, as far as underlying index supporting the Primary Key is non-clustered.
_____________________________________
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.February 9, 2013 at 10:38 am
Sorry, I didn't say it correctly. The underlying index supporting the sets of primary key (on few columns) is clustered. So, does this need to be made non-clustered? Because I need to create a clustered index on partion scheme and it is failing since table already has clustered index in it.
February 9, 2013 at 10:50 am
The primary key can be made non-clustered, however any unique index (and that includes the pk) must have the partition key as part of the index key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2013 at 11:28 am
Thanks. I am able to create the partion now. Now I have two concerns.
1) Dropping the existing clustered index and making it non clustered; won't it have impact on the performance of the query against this table?
2) If I delete the records from the underlying table, will they still be stored in the partions? Is it the real application of the partions?
February 9, 2013 at 1:48 pm
SQL_Surfer (2/9/2013)
2) If I delete the records from the underlying table, will they still be stored in the partions? Is it the real application of the partions?
Err... you may want to read up on partitioning again... When you partition, you split the table, it's not something above or extra to the 'underlying' table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2013 at 2:55 pm
Thanks. I like to understand though, how a partioning table helps in archiving? I understand data gets horizontally partioned based on the partition function with partition number; but how does it get archived?
In the query, if the partion column is present, then it can improve the performance by going to specific partion, but what if partion column is not used in the where clause?
February 9, 2013 at 3:20 pm
First section
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply