Solved

Index Rebuild or Index Defrag?

Posted on 2009-05-11
3
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…

739 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