?
Solved

Index - When to Reorganize, When to Rebuild

Posted on 2007-11-21
10
Medium Priority
?
5,901 Views
Last Modified: 2009-01-28
I have nightly jobs that Reorganize all indexes on all tables in user databases.  I also update the statistics if necessary.  How can I tell (by querying system views) when an index needs to be rebuilt as it is so fragmented that performing an 'Alter Index ... Reorganize' is not sufficient anymore?

Thanks
0
Comment
Question by:BillPowell
[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
10 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20329517
Don't recall the system view off hand, but you can do this:

DBCC SHOWCONTIG ( ... ) [WITH TABLERESULTS]

and then check the results to determine % fragmented.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20329522
you can schedule a job for reindexing once in a week  DBCC DBREINDEX()
updating the stats are not really an overhead, so cn run it on a daily basis
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20329544
OK, found the dm_ view:

sys.dm_db_index_physical_stats
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 10

Assisted Solution

by:AustinSeven
AustinSeven earned 400 total points
ID: 20329701
Analyse the output from DBCC SHOWCONTIG.   I believe you only need to review two of these values:-

1.  Scan Density   - best if 100%
2.  Logical Scan Fragmentation  - best if 0 (not applicable to secondary indexes)

You can write a sproc to do the SHOWCONTIG and output the results to a table.   You can then think about writing an index maintenance sproc that goes through the results and decides according to a formula which indexes are due for reindexing.    eg. Scan Density <= 70%,  Logical Scan Frag > 15% or whatever you decide.   However, as I found out for myself about 6 years ago when I went through this exercise, there are plenty of issues that need to be understood with indexes.  It's a good learning opportunity though.   For example, in MS articles I read, it stated that if you reindex the clustered index on a table, all the secondary indexes would be automatically reindexed as well.   To some level, I think this is true but the fact is that I only got optimum results from individually reindexing the secondaries after reindexing the clustered index.

AustinSeven
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20329763
OK, so I query something like this:

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent <> 0
ORDER BY avg_fragmentation_in_percent DESC;

Now I get all fragmented indexes, which I can defragment or rebuild as I see fit.  I still dont understand under what circumstances performing an index Defrag/Reorg will be ineffective and when an actual rebuild will be required.  Do I perform a Reorg/Defrag followed by Update Stats, then run this query, and anything that still has fragmentation requires a rebuild??

The reason I dont use DBCC DBREINDEX() once a week is:
1.  Reorganizing the indexes is much faster
2.  Im concerned about the performance hit when rebuilding indexes on tables that have many millions of rows and several indexes.  Instead of auto-rebuilding, I prefer to be notified when defragmenting has failed to produce the desired results, then I can schedule rebuilding around the requirements of the business.

Basically, it was my understanding that the Defragment/Reorganize commands existed as a lightweight index maintenance tool (small footprint), and DBREINDEX() was for situations when the index has degraded to the point where it can no longer be helped by Defragmenting (again, I dont understand the circumstances that would cause this to happen)

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20329890
>> Basically my understanding that Defragment/Reorganize is a a lightweight index maintenance tool and DBREINDEX() was for when the index has degraded to the point where it can no longer be helped by Defragmenting <<

That's my understanding as well.

Also, REBUILD (DBREINDEX) takes the table off-line for the duration of the rebuild (unless ONLINE option is specified).

Check the table immediately after reorganization and if it's still too fragmented for you, then do/schedule a rebuild.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20330098
for rebuild go>> Management
>>Maintenance Plans
right click
>> new plan
view toolbox
>>Drag a "Rebuild Index" task to your blank pane
right click the task
edit
and follow the prompts

Then set up schedule
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20330535
<<Check the table immediately after reorganization and if it's still too fragmented for you, then do/schedule a rebuild.>>

1.  What number is generally accepted to be too fragmented?
2.  At the physical/logical level, why after reorganizing/defragmenting an index would it still indicate that it is fragmented?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 20330646
1.  As always, probably everyone has a different judgment.  I base it on terms of I/O rather than just fragmentation %.  

Say for a very small db -- 128K or less -- as long as extra extent reads are not more than one, I don't worry about other fragmentation on those.

For (very) large dbs (in terms of disk i/o, not that large dbs necessarily cause SQL a problem per se), say 10G and up, then even a 10% overall fragmentation (beyond whatever your normal freespace is) will force 100M of additional bytes to be forced thru the data buffers for a full scan ... so it depends: are full scans done on that db?

To me, for any decent size of db, say 50M+, 25% or more of fragmentation should be dealt with.


2.  A reorganization is much more limited than a rebuild.  It's almost like rearranging a closet by moving a few things around vs pulling everything out and putting it back in fully in order.
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20331676
Well put Scott.

Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

762 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