Index - When to Reorganize, When to Rebuild

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
LVL 11
BillPowellAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherSenior DBACommented:
OK, found the dm_ view:

sys.dm_db_index_physical_stats
0
 
AustinSevenConnect With a Mentor Commented:
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
 
BillPowellAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
James MurrellProduct SpecialistCommented:
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
 
BillPowellAuthor Commented:
<<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
 
BillPowellAuthor Commented:
Well put Scott.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.