Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Index Rebuild or Index Defrag?

Posted on 2009-05-11
3
Medium Priority
?
468 Views
Last Modified: 2012-08-13
I have DBs that have indexes that are fragmented in various levels from 30% to 99% because index maintenance was never scheduled/ created.  I know that it is easier to INDEXDEFRAG than to Rebuild the INDEX (Less time and no long term locks on the table), but there must me a specific point where rebuilding would be better than tryig to defrag the index.  (I know that rebuilding would need to be done in non-production hours or late at night).  But I want to set up a process that checks the fragmentation of an index and if the %of fragmentations is over a specific amount, set that index for rebuilding and if it is less than a specifi % set the index for defraging.   Does anyone have an idea where / what that % might be?  (Lets use a simple index of one column with 500K records as an example).

Thanks in advance
0
Comment
Question by:blberger
3 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 24355406
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 2000 total points
ID: 24355513
Some say....That if the fragmentation is > 30% the re-index; otherwise Rebuild.

HTH
0
 

Author Closing Comment

by:blberger
ID: 31580153
Thank you for a direct answer.  I have read the article pointed out by the other response, but it did not give me the threshold value.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question