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]
       , t2.name as [Index name] 
       , max(avg_fragmentation_in_percent) as Fragmentation 
       , case when max(avg_fragmentation_in_percent)<10 then  
           'No action required' 
         else 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
           else  
             'Index should be REBUILD' 
           end 
         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  , t2.name 

order by case when max(avg_fragmentation_in_percent)<10 then 
           'No action required' 
         else 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
           else  
             'Index should be REBUILD' 
           end
         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 
go
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 
begin  
  Declare @sqlstatement varchar(500)  
  Declare @objectnavn  Varchar(500)  
  Declare @indexnavn  varchar(500)    
  set @objectnavn=(select '['+t2.name+'].['+t1.name+']' 
                   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'  
  else   
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REBUILD'
  exec(@sqlstatement) 
  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!!!!

Søren

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s