July 22, 2013 at 1:13 pm
Hi,
I am new for table partition so please help me to choose right key for cluster index.
example:
Table (colA,colB,colc,.....)
Colc : my partition function is on colc (which is not unique)
(cola,colb) --- unique non clustered index, this columns are used mostly in my company for querying data.
My question:
Can i use [ColA,ColB,Colc(partition key)] these columns as unique composite clustered index to make advantage of partition key or there is any suggestions
i.e New composite key : (Cola+colB+Colc) ---- is that fine ?
Please reply @ sql.lanka@gmail.com
July 22, 2013 at 1:24 pm
sql.lanka (7/22/2013)
I am new for table partition so please help me to choose right key for cluster index.example:
Table (colA,colB,colc,.....)
Colc : my partition function is on colc (which is not unique)
(cola,colb) --- unique non clustered index, this columns are used mostly in my company for querying data.
My question:
Can i use [ColA,ColB,Colc(partition key)] these columns as unique composite clustered index to make advantage of partition key or there is any suggestions
i.e New composite key : (Cola+colB+Colc) ---- is that fine ?
If after careful consideration ColC is your partition key then ColC is your partition key - no matter what your clustered key is.
Just out of curiosity would you share with use the information below?
1- How many rows are currently stored in the table?
2- How many rows are added monthly to the table?
3- Is the table targeted by any purging or archiving process? describe it, please.
4- Is ColC a date? if not, what is it?
5- What is the main reason table partitioning is being considered? (please do not say performance)
Thank you
_____________________________________
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.July 22, 2013 at 1:31 pm
Total record count :54812769
Colc --- date column
purging will be monthly
partition we considered for performance obtain
July 22, 2013 at 1:39 pm
sql.lanka (7/22/2013)
Total record count :54812769Colc --- date column
purging will be monthly
partition we considered for performance obtain
if ColC is the date column used for purging purposes and there is one partition per month then partitioning this table will help with the purging process.
Having said that, you shouldn't expect any performance gain from partitioning the table. Performance gains are achieved by creating a suitable indexing strategy and writing well performing queries not by partitioning a table which actually adds overhead to the equation.
_____________________________________
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.July 22, 2013 at 2:06 pm
Thia table contains 7 months worth of data ,it will not make any performance difference if i split data on monthly basis ?
July 22, 2013 at 2:11 pm
You'll get better performance from clustering on the date -- as the only or first key column -- if date/date range is specified in the queries.
I believe a partition might help performance in this case because of partitioning on date, but you don't need to partition to get those benefits: the proper clustered index will give you a big performance boost without having to partition.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 22, 2013 at 2:57 pm
Thanks for your reply,but if all the data in single file vs splitting data in file groups by portioning which is better ? this is the reason i choose for table partition i may wrong but please suggest me right approach
July 22, 2013 at 3:00 pm
From a performance standpoint, a single table can work just as well as partitioning in many cases as long as you have the correct clustered index. But if you've bought into the dopey "default is identity" for the clustering key, then you may have serious performance issues and partitioning could perhaps gain you some performance there.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 22, 2013 at 4:17 pm
sql.lanka (7/22/2013)
Thanks for your reply,but if all the data in single file vs splitting data in file groups by portioning which is better ? this is the reason i choose for table partition i may wrong but please suggest me right approach
So from what you have said, there is one operation for which performance will improve drastically if you partition: the purge operation, because it can be reduced to a metadata operation.
But for the rest, yes you can spread out those seven filegroups on seven different disks all with a separate controller, but if most queries are against the rows for the current month, partitioning on the date column is not going to matter much. If you partition on some randomly chosen column, you can achieve this effect, but then you also need to have a workload where this matters. And purging will be expenseive.
Partioning is mainly about manageability and not the least making purging simple.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 22, 2013 at 4:46 pm
sql.lanka (7/22/2013)
Thanks for your reply,but if all the data in single file vs splitting data in file groups by portioning which is better ? this is the reason i choose for table partition i may wrong but please suggest me right approach
Partitioning a table does not helps with performance - sound indexing strategy and quality queries do.
_____________________________________
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.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply