IndexDefrag script wanted

Question

IndexDefrag script wanted

Jake Shelton

Hall of Fame

Points: 3109
More actions
June 23, 2012 at 12:29 pm

#98467

Hi all,
Is there an IndexDefrag script that I can run against all tables (for 5 particular databases) where fragmentation is greater than 30% and page count greater than 1000? Can't use DBReindex as this must remain an online operation. Not worried about log swelling as they are shipped every 15mins.
Also, is there an another script that will show me before/after values for the fragmentation? Not sure if you can get this from ShowContig.
Cheers,
Jake

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

~~Lookup "sys.dm_db_index_physical_stats" in Books Online, Jake. Example "D" will do it for you. You just need to tweak it to meet your needs.~~

[EDIT] Strike that. I just saw that this was for SQL Server 2000. I'll be back in a minute with a 2000 answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Allright... same idea...

Lookup DBCC SHOWCONTIG in BOL for SQL Server 2000 and tweak example "E" to suit your druthers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 3

Cheers Jeff!

Running it now. Do you know if this is a particularly resource - intensive Proc? I'm told IndexDefrag can bloat the log, and indeed it's gone (for one database) from 2 to 9gb, it's sitting pretty there, but not sure if I should kick it up again to the other 4 db's simultaneously (though I've certainly got enough free space to handle the 20gb bloat)

Any safety concerns if I leave it alone and head off to to the pub? 🙂

This really ain't the sort of thing I want to be doing on a nice Saturday night (damn I'm pathetic!!)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Index defrag isn't so bad. Index rebuild can get pretty intensive. Both need the help of TempDB. If you have the room, there's not much to worry about unless a whole lot of people need instant access.

I'd only do one DB at a time... stage them up and go to the pub.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Speaking of "pathetic"... you're on the way to the pub and I'm on my way to bed because I've spent too much time in front of this bloody machine today. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)