Query to rebuild/reorganize all indexes in the database?

Question

Post reply

Query to rebuild/reorganize all indexes in the database?

Vikka

Hall of Fame

Points: 3715
More actions
July 21, 2009 at 10:16 am

#212347

Does somebody has a query to rebuild or reorganized (depending on the level of fragmentation) all the indexes in the database? The only input parameter is a database name.. It should find an indexes itself and make a decision what to do with it...

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Lots here: http://www.sqlservercentral.com/Scripts/Index+Management/

steveb. SSC-Forever Points: 46733 More actions · Answer 2

try this one out ..

http://www.sqlservercentral.com/scripts/31857/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Does somebody has a query to rebuild or reorganized (depending on the level of fragmentation) all the indexes in the database? The only input parameter is a database name.. It should find an indexes itself and make a decision what to do with it...

Hi,

Check the following link.It ll help u.

http://www.sqlservercentral.com/scripts/Administration/68802/

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Vikka Hall of Fame Points: 3715 More actions · Answer 4

Thanks so much!

Can I set

@frag >30 and pages 1000 then rebuild

@frag between 5 and 30 and pages 1000 then reorganize

Would it be correct?

swellguy Mr or Mrs. 500 Points: 526 More actions · Answer 5

I use this package from MS's DB Operations team as part of my standard build. Have found them very reliable and extendible.

http://download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip

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

Heh... don't over look what's in Books Online. If you lookup "DBCC SHOWCONTIG" and scroll down, you'll find a section with the following label...

[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]

That section has the very code you seek in it. 😉 And, yes, it has a cursor in it... it's one of the very few places where a cursor might actually be condoned.

--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)

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

As per MS U r correct.

Thanks so much!
Can I set
case 1:@frag >30 and pages 1000 then rebuild
case 2:@frag between 5 and 30 and pages 1000 then reorganize
Would it be correct?

Hi Vika,

U can use .Its depends.

If u get any error replay me.

e.g

case 2:If Allow page lock=OFF in this case the job was failed and u will get following error.

Msg 2552, Level 16, State 2, Line 1
The index "CIX (partition 1) on table "table_name" cannot be reorganized because page level locking is disabled.

Edit :add more info

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Jeff Moden (7/21/2009)
Heh... don't over look what's in Books Online. If you lookup "DBCC SHOWCONTIG" and scroll down, you'll find a section with the following label...
[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]
That section has the very code you seek in it. 😉 And, yes, it has a cursor in it... it's one of the very few places where a cursor might actually be condoned.

Jeff

It's interesting that in that very same topic, Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" and "Use ALTER INDEX…REORGANIZE to reorder the leaf-level pages of the index in a logical order", and then it proceeds to use the old code in its examples!

John

steveb. SSC-Forever Points: 46733 More actions · Answer 9

The code that is recommended for SQL 2005 can be found in books on line under 'sys.dm_db_index_physical_stats' .

It is very similar to the code in "DBCC SHOWCONTIG" page

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

John Mitchell (7/23/2009)
Jeff Moden (7/21/2009)
Heh... don't over look what's in Books Online. If you lookup "DBCC SHOWCONTIG" and scroll down, you'll find a section with the following label...
[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]
That section has the very code you seek in it. 😉 And, yes, it has a cursor in it... it's one of the very few places where a cursor might actually be condoned.
Jeff
It's interesting that in that very same topic, Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" and "Use ALTER INDEX…REORGANIZE to reorder the leaf-level pages of the index in a logical order", and then it proceeds to use the old code in its examples!
John

Heh... true enough on both points. I tend to make things as backwards compatible to 2k as possible because I saw a survey (again, recently) that says that more than 67% of all IT shops still have SQL Server 2k instances running. It also shows that I really need to install the Dev Edition of 2k8 on my machine so I can catch these caveats as the product progresses.

--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)