Solved

Index - When to Reorganize, When to Rebuild

Posted on 2007-11-21
10
5,888 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: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
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.

 
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
 
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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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