Index fragmentation

I have seen a lot of nice ways of handling index fragmentations, but in the blog I would try to give you my approach on how to setup index defragmentation on a production server.

First of all, then there is a lot of Things to take into consideration, and that is Things like:

  1. How often do we need to do defragmentation.
  2. What version do we have of SQL server.
  3. How often is our data defragmented.
  4. How large are our indexes.
  5. And so on.

and when we have used a lot of time to think about that, then we will (at least in 90%) of all situations end up with some standardrecommandations:

  1. When an index is more than 10% fragmented, then we must do defragmentation.
  2. When it is between 10% and 30%  fragmented, then we must do a reorganize.
  3. when it is over 30% fragmented, then we must do a rebuild.

This blog is not about answering the questions about when to defragment, how often and so on, this script is just about getting some code that can optimize defragmentation.

We will use three special techonologies called:

  1. Dynamic Management object.
  2. Cursors
  3. Dynamic SQL.

First we must identify fragmentation on each index, and we will use a Dynamic Management object called dm_db_index_physical_stats, and it takes the following parameters:

  1. DatabaseID
  2. ObjectID
  3. IndexID
  4. PartitionID
  5. Mode

the important thing is the mode, because it takes 5 different values, and the two most important is SAMPLED, DETAILED. Use SAMPLED if you have a big database with some heavy indexes. Use DETAILED, when you have a smaller database. DETAILED gives a more accurate view of the indexes, and SAMPLED returns the result faster.

So, if we use that, then we now can create a small TSQL script that returns fragmentation for all indexes in a database.

select DB_NAME(database_ID) asdatabasename
       , T1.object_id as [Table ID]
       , OBJECT_NAME(t1.object_ID) as [Table name]
       , t2.index_id as [Index ID]
       , as [Index name] 
       , max(avg_fragmentation_in_percent) as Fragmentation 
       , case when max(avg_fragmentation_in_percent)<10 then  
           'No action required' 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
             'Index should be REBUILD' 
         end as ActionType
from sys.dm_db_index_physical_stats(db_id('adventureworks'),null,null,null,'SAMPLED')t1 
       inner join sys.indexes t2 on t1.index_id=t2.index_id and t1.object_id=t2.object_id
group by DB_NAME(database_ID) 
         , T1.object_id
         , OBJECT_NAME(t1.object_ID)   
         , t2.index_id  ,

order by case when max(avg_fragmentation_in_percent)<10 then 
           'No action required' 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
             'Index should be REBUILD' 
         end ,[Table name] , [Index ID]

Try to run that script some couple of times and then you can see how it works.

Now we can find the information we needs pr. index, and that is:

  1. Object_ID
  2. Index_ID
  3. Fragmentation in percent

And we can now create a cursor that do the appropriate action. Please not that we also use dynamic SQL to run do this task. The code looks like

Use AdventureWorks
Declare @objectid  varchar(20)
Declare @indexid  varchar(20)
Declare @fragmentation float
Declare sjCursor Cursor  for
              select t2.object_id   
              , t3.index_id   
              ,  max(avg_fragmentation_in_percent) as max_fragmentation_index
              from sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'Sampled') t1   
                     inner join sys.objects t2 on t1.object_id=t2.object_id   
                     inner join sys.indexes t3 on t1.index_id = t3.index_id and t3.object_id=t1.object_id
              group by t2.object_id   
              , t3.index_id
              having max(avg_fragmentation_in_percent)>10
open sjCursor
fetch next from sjcursor into @objectid, @indexid, @fragmentation
while @@FETCH_STATUS=0
  Declare @sqlstatement varchar(500)  
  Declare @objectnavn  Varchar(500)  
  Declare @indexnavn  varchar(500)    
  set @objectnavn=(select '[''].['']'
                   from sys.objects t1
                      inner join sys.schemas t2 on t1.schema_id=t2.schema_id
                   where t1.object_id=@objectid)  
  set @indexnavn = (select '['+name+']'
                    from sys.indexes
                    where object_id=@objectid and index_id=@indexid)  
  if @fragmentation<=30   
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REORGANIZE'  
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REBUILD'
  fetch next from sjcursor into @objectid, @indexid, @fragmentation
end close sjCursor
deallocate sjCursor


THATS IT. run this script on the SQL Server, and you have a smarter way of doing defragmentation.

You could improve the script in many ways, and some of them are

  1. Create another cursor so that you can do multiple databases.
  2. Add a top 100 to the select statement, so that it only defrags the most fragmented indexes.
  3. Fix code to tune performance of the script.

But anyway…… It Works and it is a good way of working.

All scripts on this blog is executed at your own risk!!!!




0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *