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:
- How often do we need to do defragmentation.
- What version do we have of SQL server.
- How often is our data defragmented.
- How large are our indexes.
- 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:
- When an index is more than 10% fragmented, then we must do defragmentation.
- When it is between 10% and 30%¬† fragmented, then we must do a reorganize.
- 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:
- Dynamic Management object.
- 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:
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:
- 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
- Create another cursor so that you can do multiple databases.
- Add a top 100 to the select statement, so that it only defrags the most fragmented indexes.
- 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!!!!