Solved

Index Rebuild or Index Defrag?

Posted on 2009-05-11
3
462 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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