Solved

Index - When to Reorganize, When to Rebuild

Posted on 2007-11-21
10
5,883 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
10 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
ID: 20329544
OK, found the dm_ view:

sys.dm_db_index_physical_stats
0
 
LVL 10

Assisted Solution

by:AustinSeven
AustinSeven earned 100 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 69

Expert Comment

by:ScottPletcher
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:
ScottPletcher earned 400 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

17 Experts available now in Live!

Get 1:1 Help Now