REBUILD INDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

REBUILD INDEX

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-24 : 08:15:31

hello experts,
How often is it a good practice to rebuild indexes?
thanx in advance

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 08:55:33

as with most questions like this... it depends on alot of factors.

How big a table, how much changing happens, etc...

You can build a procedure that will defrag indexes (or drop and create) and schedule it... I normally schedule mine to defrag nightly.

The best place to start, is to get an idea of how quickly the indexes go bad (fragmented).

Check out 'DBCC ShowContig' in SQL Help

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

ekelmans
Starting Member

7 Posts

Posted - 2011-03-27 : 07:49:51

If you are worried about fragmentation and forwards, scan for them.

A good rule of thumb is: <5% do nothing, between 5 and 30 defrag, >30% rebuild it

If you look around a bit you can find many scripts that will do the above on a DB by DB, and Index by Index basis

grtz, t:)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 04:58:25

you can use following script for this purpose. But make sure that it should only be executed in off peak hours.
http://connectsql.blogspot.com/2011/01/sql-server-simple-method-to-resolve-all.html?utm_source=BP_recent
OR
you can check all index fragmetation by executing following query
select * from sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'Sampled')

if avg_fragmentation_in_percent is between 20 and 40 (Use Reorganize)
if avg_fragmentation_in_percent is greater then 40 (Use Rebuild)

Keep in mind that indexes with less then 8 pages will always show high fragmentatino ... WHY
http://connectsql.blogspot.com/2011/03/sql-server-small-tables-clustered.html?utm_source=BP_recent

--------------------------
http://connectsql.blogspot.com/

fanzhouqi
Starting Member

4 Posts

Posted - 2011-04-01 : 04:05:46

dbcc INDEXDEFRAG weekly
dbcc reindex monthly

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 00:49:42

In SQL Server 2005 and later you will use

Alter Statement to rebuild or reorganize the specific indexes

Rebuild
When avg_fragementation_percent > 30 percent

Reorganize
When avg_fragementation_percent < 30 percent

For Auto Rebuilding
http://aureus-salah.com/2010/08/05/sql-server-automate-index-rebuilding/

you can use Microsoft Integration Service to generate these alter statement automatically on the specific database

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources