August 18, 2010 at 2:57 pm
OS: Windows Server 2003 (64bit) SP2
PLATFORM: SQL Server 2005 Ent Edit
Is there anything one can do to speed up the CREATE INDEX process on a large table which already has a Clustered Index in whcih the index that CREATE INDEX is vreating does not include or reference the column on whcih the Clustered Index is based?
We are in the process of doing some purging of old data. The purging toll we aree using was provided by the software vendor whose application uses the database we are purging from. The purge will involve many tables however there is 1 table in particular that is a problem and its because of its size. In short the purge process is failing because it includes a step that creates an index on a table that is over 100 million rows and about 25GB in size, 35 GB if you include the space that is used by the Indexs already on the table. Its failing because the time it takes the index to be created is greater then the time the purge tool is designed to allow for before timing out.
There's nothing I can do about this creat index step including creating the index myself in advance of running the purge tool. If the index already exists then this toll fails with an error almost immediately. We're told it will be a while before the tool is updated to make this Index something the user can create as a pre-requisite step to running the tool and so I'm left trying to find a work-a-round.
The index being created is on 1 column (with no includes) and the column is a foregn key to another sizeable table. The other table is not very large but its also not small.
Suggestions?
So far the only idea I had and I will admit this may be dumb because I've never tried this since I've never had to do this kind of thing before, is to create an index that is identical in structure to the one this purge toll is trying to create but simple use a different name. This way the tool won;t fail because the index already exists (mine will be a different name) but since there is already an index on the same column then perhaps the CREATE INDEX command could use that index to more quickly create itself.
Please be gentle if thats a dumb idea because as i have said this is my just grasping at ideas to try and work aropund this lame issue.
Thanks
I was thinking that I might be able to reduce the time it takes to build thisindex if there were already an index of the exact same make up but with a different name
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 1:41 am
You can do one thing , dont drop/create index instead , DISABLE it before purge process ( it must contain
INSERTION into destination table ) and then REBUILD it at the end.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 19, 2010 at 9:00 am
Bhuvnesh (8/19/2010)
You can do one thing , dont drop/create index instead , DISABLE it before purge process ( it must containINSERTION into destination table ) and then REBUILD it at the end.
Thanks for replying Bhuvnesh but I'm a little confused by your comment. How does disabling the Index help imrpove the the time it takes for the Index to be created? Also know that this Purge tool we have been sent by the Vendor is not a step by step process so once it starts I have no way to pause it and do something like disable the index after its created. That said I would not want to disable it because the Index does help with the purge process because without out the urge woudl take a very long time. So the problem isn't that an index is being created that should not be but that its taking too long to create the index as the purge tool times out before the index can be created.
Thanks again
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 9:02 am
BTW - since I haven't gotten but 1 reply so far I just wanted to say that I'd appreciate any suggestions on where else to go and aks this quesion if someone has a suggestion on where to check into something like this.
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 9:05 am
YSLGuru (8/19/2010)
Also know that this Purge tool we have been sent by the Vendor is not a step by step process so once it starts I have no way to pause it and do something like disable the index after its created.
Is there a timeout setting in the app that you can change? If you can't play with the index create statement (and try things like sort in tempDB) there isn't really a practical way to speed up creation of an index. You can check and see if maybe you're hitting a hardware (memory or disk) bottleneck and throw hardware at the problem, but that's not really practical.
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
August 19, 2010 at 9:11 am
GilaMonster (8/19/2010)
YSLGuru (8/19/2010)
Bhuvnesh (8/19/2010)
Also know that this Purge tool we have been sent by the Vendor is not a step by step process so once it starts I have no way to pause it and do something like disable the index after its created.Is there a timeout setting in the app that you can change? If you can't play with the index create statement (and try things like sort in tempDB) there isn't really a practical way to speed up creation of an index. You can check and see if maybe you're hitting a hardware (memory or disk) bottleneck and throw hardware at the problem, but that's not really practical.
Unfortunately I can't change any part of how the purge tool works; a typical deisgn flaw for anything from this vendor. The plan is to discuss with the programmer (for this vendor) ways to make changes to the tool so as to get around this problem (like making the Index a pre-requisite step done by the user so that the toll does nothing but the purge itself). The problem there is it could be some time, as lomg as several months before a revised version of the tool could come from such a discussion and we need to do something about purging data before then as the next month is the time of the year when we are best able to do something like this because of user load and data access requirements.
I was afraid the answer was what you have said but i wanted to hear it from one of the SQL gurus here first before deciding this was something we would just be stuck with.
I take it you don't believe that an existing index with teh same structure but by a different name would help speed up the create index statement? Since teh table already has a clustered Index I figured this idea was a long shot at best.
thanks Gail
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 9:26 am
YSLGuru (8/19/2010)
I take it you don't believe that an existing index with teh same structure but by a different name would help speed up the create index statement?
It quite likely would, as SQL could then scan that index instead of the cluster to get the data it needs. It's a waste of space, it'll slow down the purge process, but it should speed up the creation of a new index. By how much is another matter...
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
August 19, 2010 at 9:36 am
What is your MAXDOP setting on the server?
Index creation time is improved greatly by parallelism. Most of my OLTP servers have maxdop=1, but when I create indexes, I use maxdop=0 or 4 (0 if off-hours, 4 if during the day). Since you can't alter the create index statement, you'd have to set this at the server level temporarily, assuming you don't already have it set to 0.
August 19, 2010 at 10:05 am
Derrick Smith (8/19/2010)
What is your MAXDOP setting on the server?Index creation time is improved greatly by parallelism. Most of my OLTP servers have maxdop=1, but when I create indexes, I use maxdop=0 or 4 (0 if off-hours, 4 if during the day). Since you can't alter the create index statement, you'd have to set this at the server level temporarily, assuming you don't already have it set to 0.
Thanks for the idea Derrick but it is already set to 0.
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 10:07 am
GilaMonster (8/19/2010)
YSLGuru (8/19/2010)
I take it you don't believe that an existing index with teh same structure but by a different name would help speed up the create index statement?It quite likely would, as SQL could then scan that index instead of the cluster to get the data it needs. It's a waste of space, it'll slow down the purge process, but it should speed up the creation of a new index. By how much is another matter...
Well then there is a shed of light in this mess after all. Luckily this would be a VERY temporary thing so the wasted space isn't a big deal (I'll regain it after the purge). Sadly there's no way I know of to focre SQL to use that Index (since the CREATE INDEX statement is coming from the tool that I can't edit) but it still can't hurt to try it and see what happens.
Thanks Gail
Kindest Regards,
Just say No to Facebook!August 19, 2010 at 10:28 am
YSLGuru (8/19/2010)
Sadly there's no way I know of to focre SQL to use that Index (since the CREATE INDEX statement is coming from the tool that I can't edit)
You wouldn't be able to force it anyway, there's no WITH INDEX hint on a create index statement.
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
August 19, 2010 at 10:48 pm
you can go some optimal settings according to your asking requirements in that index like MAXDOP or SORT_IN_TEMPDB
these might help you.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2010 at 1:31 am
GilaMonster (8/19/2010)
YSLGuru (8/19/2010)
I take it you don't believe that an existing index with teh same structure but by a different name would help speed up the create index statement?It quite likely would, as SQL could then scan that index instead of the cluster to get the data it needs. It's a waste of space, it'll slow down the purge process, but it should speed up the creation of a new index. By how much is another matter...
I believe that there is a very good chance that this will speed up the index creation very much.
Not only will creating the second index only need to scan the existing index, it will also not have to sort the data.
I have made some tests and for some of my large tables creating a second identical index is 3 times faster than creating the original index.
/SG
August 20, 2010 at 1:55 am
Stefan_G (8/20/2010)
I have made some tests and for some of my large tables creating a second identical index is 3 times faster than creating the original index.
i will take this but any reason for it ? how new one will work better then recreating existing one ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2010 at 2:04 am
Bhuvnesh (8/20/2010)
Stefan_G (8/20/2010)
I have made some tests and for some of my large tables creating a second identical index is 3 times faster than creating the original index.
i will take this but any reason for it ? how new one will work better then recreating existing one ?
SQL doesn't have to scan the table to create an index. If there's an existing index that it can scan to get the data it needs for the new index, it will do so.
The index that the OP is talking about isn't been recreated, just created (not there, added by an app)
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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply