Best strategy for rebuild index maintenance task

Question

Post reply

Best strategy for rebuild index maintenance task

the_fenix

Right there with Babe

Points: 789
More actions
October 8, 2010 at 10:58 am

#227532

Hi,
I've just set up a weekly maintenance task for rebuilding indexes on one of our servers.
I'm kind of dissapointed to see that it rebuilds ALL indexes from selected databases without taking into account if the index is fragmented or not.
Does anyone have a script for rebuilding ONLY fragmented indexes or a better strategy? Although I've set it up to execute on a low traffic hour but I wouldn't like to have our sites throwing random errors while this process is ongoing.
Rebuild indexes online has some prerequisites that we don't fulfill.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Does your procedure utilize the dynamic management view

sys.dm_db_index_physical_stats to check

"avg_ fragmentation_in_percent".

And then test that value to see if it exceeds say,

30 per cent to select those indexes to defrag?

Also read this forum for a further guidline(s)

http://www.sqlservercentral.com/Forums/Topic1000964-146-1.aspx#bm1001453

Pay particular attention to the post by "GilaMonster"

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

If you search index rebuild on this site, there are numerous scripts to help here.

the_fenix Right there with Babe Points: 789 More actions · Answer 3

bitbucket-25253, thanks for your answer.

My procedure is the standard one for Sql server 2005, it's not a custom procedure. I created a maintenance task "Rebuild Index"

the_fenix Right there with Babe Points: 789 More actions · Answer 4

the_fenix

Right there with Babe

Points: 789

October 8, 2010 at 12:21 pm

#1234447

thanks Steve Jones. I'll take a look

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

I believe the stored procedure on this link will do a good job for your requirements. Tara Kizer has been improving and enhancing this script for several years, so it is a very full featured.

Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

I'm a huge fan of Michelle Ufford's script, if you're looking for another option:

http://sqlfool.com/2010/04/index-defrag-script-v4-0/

the_fenix Right there with Babe Points: 789 More actions · Answer 7

Michael, Derrick: that's exactly what I was looking for. THANKS A LOT!