June 29, 2004 at 5:43 pm
Hi everyone, is there a way to stop sql server from rebuilding indexes when updates are made? Thanks in advance for your help.
June 29, 2004 at 5:54 pm
Drop them
Why would you want to? Updte them, I mean. They are not "rebuilt" on updates.
June 29, 2004 at 5:55 pm
I am sorry for being confusing, by rebuild I meant, update, sorry. If anyone knows of a way that we can make large number of updates to a table and defer the index updates, please let me know. I am also interested to know if rebuilding an index (this time by rebuild I do mean rebuild) or dropping/re-creating an index is less of a resource intensive operation. Thanks again.
June 29, 2004 at 5:56 pm
Steve, I stand corrected on the rebuild mishap. Sorry about the confusion.
June 30, 2004 at 5:15 am
It depedns on the size of your data. You cannot deffer the index updates in any way. You could however drop the indexes (non-clustered first then clustered as drop the clustered will cause the non-clustered to rebuild automatically), then when done add them back (this timne clustered first then non-clustered). But as far as load difference the larger the DB the longer it will take. You might even test using the DBCC DBREINDEX to rebuild an index to see how long that takes, if takes longer than updates would on just that index then it is fairly safe to assume the updates are the better option.
June 30, 2004 at 5:37 am
Just to add to Antares, you might find this document interesting.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply