November 30, 2012 at 12:48 pm
Hi guys,
I have question,
I am thinking to create a INDEX PARTITIONING on my table that has more than 30milliion records. I never done this
before. I need your help.
Question 1 = I already has clustered and non clustered index on this table. I can create Index Partition without
delete any index, am i right?
Question 2 = If above answer is YES, Here is my steps to create index. Right click on Index and hit New Index,
Give and name and Hit add (which column should i add here) or i am goona skip add step and hit filter section,
and use below query
select * from MytableName
where ServiceDate > '2004/01/01'
Please guide me. Thanks for your help in advance.
November 30, 2012 at 12:55 pm
First question...
Why are you partitioning? What's the reason, what's the goal?
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
November 30, 2012 at 1:00 pm
Good questions,
Our application is sometime is very slow and user complain.
First step I took = Index Fregmentation (its help alot)
Second Step i am doing few table that i am using alot have 11450271 rows. I am thinking we don't need old data
and i care about data that is less then 5 years. Before i am doing table Partioning, i would like to try index
partioning to see if this trick helps.
Please guide me if you think there is better way out there.
Thanks in advance
November 30, 2012 at 1:11 pm
If you think that partitioning = magic performance improvement, you'll be sorely disappointed. While it can give performance improvements, the queries may have to be rewritten to take advantage of the partitioning.
If you have performance problems, tune your queries.
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
November 30, 2012 at 1:22 pm
Gila I am 100% agree with you, Due to time issue it is really hard for us to go that path, Yes for feature we have to
use that path that you saying but for now i want to try something quick.
If you could Please help me and answers my questions in first sections that would be big help for me.
Thank You.
November 30, 2012 at 2:54 pm
rocky_498 (11/30/2012)
Gila I am 100% agree with you, Due to time issue it is really hard for us to go that path, Yes for feature we have to use that path that you saying but for now i want to try something quick.
Fine. It's highly unlikely to make any significant performance improvements though. There's no magic bullet to performance tuning.
If you could Please help me and answers my questions in first sections that would be big help for me.
I think you need to do some reading on partitioning...
1) Yes, you can create a new partitioned index without dropping any existing ones. Doesn't mean it's a good idea or will be at all helpful. Especially you don't want to create a partitioned index on a column that is already indexes.
2) No. Firstly don't use the GUI. Second, the first step in partitioning is to create the partition function and partition scheme
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply