Solved

Index Rebuild or Index Defrag?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now