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.
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 HelpCorey 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 itIf you look around a bit you can find many scripts that will do the above on a DB by DB, and Index by Index basisgrtz, t:) |
 |
|
lionofdezert
Aged Yak Warrior
885 Posts |
|
fanzhouqi
Starting Member
4 Posts |
Posted - 2011-04-01 : 04:05:46
|
dbcc INDEXDEFRAG weeklydbcc 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 indexesRebuildWhen avg_fragementation_percent > 30 percentReorganizeWhen avg_fragementation_percent < 30 percentFor Auto Rebuildinghttp://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 databaseRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
 |
|
|
|
|
|
|