Index Rebuild Vs Reorganize...?

Question

Index Rebuild Vs Reorganize...?

SQlLogic

SSCrazy Eights

Points: 9685
More actions
July 27, 2012 at 12:44 pm

#278729

HI Guys ..
Just wanted to know how often should do Rebuild Index and Reorganize Index in week or what are the best practices for Index Maintenance ?
- Need to update Statistics after reorganize Index separably in Maintenance plan?
- i am going to do Reorganize Index Every night and Rebuild once in sunday night so is it good one?
--Any good script ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Leonard Rutkowski SSCrazy Points: 2666 More actions · Answer 1

You really only need to reorganize or rebuild, depending on how much fragmentation you have on your indexes, although you can do it as often as you like, if you have the maintenance time.

Go here, http://ola.hallengren.com, for an excellent script. It will determine if and when you need to do rebuild, reorg, or update stats, depending on your needs.

Leonard

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

lrutkowski (7/27/2012)
You really only need to reorganize or rebuild, depending on how much fragmentation you have on your indexes, although you can do it as often as you like, if you have the maintenance time.
Go here, http://ola.hallengren.com, for an excellent script. It will determine if and when you need to do rebuild, reorg, or update stats, depending on your needs.
Leonard

+1

Proper table design (like proper fill_factor and other design aspects) will not lead to much fragmentation and hence indexes are required to be re-build occasionally.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

lrutkowski (7/27/2012)
You really only need to reorganize or rebuild, depending on how much fragmentation you have on your indexes, although you can do it as often as you like, if you have the maintenance time.
Go here, http://ola.hallengren.com, for an excellent script. It will determine if and when you need to do rebuild, reorg, or update stats, depending on your needs.
Leonard

Just to make it easier for others:

http://ola.hallengren.com

mikehillwig SSC Enthusiast Points: 123 More actions · Answer 4

As far as the frequency goes, it all depends on your database. I have some indexes that need to be maintained daily and some that can go weeks without much care or feeding.

Others have said that good design will give you indexes that don't need a lot of TLC. Unfortunately, many of us support applications that don't always have the best designs in place. Thus, we maintain.

Another thing to consider is that this could almost be an academic exercise. Some people cringe at the thought of having any fragmentation on their indexes. Others believe that maintaining indexes has a higher cost than the benefit you'll realize by maintaining them frequently. You'll find to find your happy medium.