Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5911
  • Last Modified:

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
0
BillPowell
Asked:
BillPowell
2 Solutions
 
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
 
Scott PletcherSenior DBACommented:
OK, found the dm_ view:

sys.dm_db_index_physical_stats
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AustinSevenCommented:
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
 
Scott PletcherSenior 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
 
BillPowellAuthor Commented:
Well put Scott.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now