Reindex Question

[DBAWinnipeg]

Good Morning everyone

Question for ya....

I'm in a 24/7 environment and reindexing has become quite tricky at times due to large tables and locking , etc

My question is this

Is there any advantage to reindexing individual indexes as opposed to the whole table. My thought process on this is that it takes a shorter amount of time to reindex 1 index then 10 and transactions (though slower then normal) should still be able to go through this table inbetwen the reindex statements.

Am I out in left field on this one?




Thanks in advance!!!

Colin in da 'Peg

 

[hmckillop]

It would only re-index that one index and therefore will take less time to reindex than it would all the indices on the table.

In the past I have created a job which queries each index using DBCC SHOWCONTIG and then based on a preset value for scandensity will then reindex that index. It may slow the process down i.e. sometimes DBCC SHOWCONTIG can take a bit of time to return results it allows you only to reindex the indices that actually need it.
Going one step further I also created a table which contained all of my indices and specific scan density thresholds for each index - thus having total configuration for your re-indexing. If you require the code I can post on here, just post a reply requesting it.

Hope this helps.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[Corran007]

I would be interested in seeing that. It sounds likes it could be very useful.

 

[SQLBill]

hmckillop,

Why don't you put it together (see FAQ183-5275 for formating suggestions) and submit it as a FAQ?

-SQLBill

 

[hmckillop]

I have added the faq now and its being reviewed. I will apologise as its quite long, but hope you find it useful.
If you have any questions, just post again enquiring about the faq, or repost on this thread.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[hmckillop]

The faq is now at:

http://www.tek-tips.com/faqs.cfm?spid=962&sfid=5367

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.