Defragmentation

I have been asked if there is a way to do a more intelligent fragmentation than just using the normal maintenanceplan, and of course there is. But lets start with the basics and forget about all the hard issues.

My personal opinion is that if an index

  • is above 10% and below 30% fragtmented, then it should be reorganised.
  • is over or equal 30% fragmented then it should be rebuild.

That is my personal opinion, and I know that a lot of other consultants has a lot of other opinions about that. So, in this article then lets stick to my opinion and limit this post to finding a way, where we can go through all indexes and rebuild or reorganise according to this rule (i know that there is a lot of other rules, settings e.g. to think about, but lets start here).

To do this I will use the following techniques:

  1. Cursors.
  2. Dynamic SQL
  3. Dynamic management objects
  4. SQL Server Agent

To run this sample you must have the adventureworks database installed.

What I will do, is that I would go through all indexes and get there fragmentation, and to do that I need a cursor and the dynamic mangement object sys.dm_db_physical_stats.

First I setup my cursor, and a cursor basically consists of two parts, an outer loop and somthing we do in that loop. Imagine that we traverse through a table with 10 countries then the inner loop will be run 10 times, one time for each country:

—– CODE SNIPPET START—–

use adventureworks
go

— We declare some variables that should hold the
— parameters that are send from the outerloop to the code that
— are run for each row in the outer loop.

Declare @objectID varchar(50),
@IndexID
varchar(50)
,
@Fragmentation
real
,
@pagecount
int

— We declare the cursor

Declare traverse cursor for
select object_ID, index_id ,avg_fragmentation_in_percent,page_count
from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null)
where avg_fragmentation_in_percent>=10

— We open the cursor
Open traverse

— We try to fetch the first row of the cursor into the 4 variables

fetch next from traverse into @objectid, @indexid, @fragmentation, @pagecount

— If ok then we do something
while (@@FETCH_STATUS=0)
begin

— We try to fetch the next row of the cursor into the 4 variables
fetch next from traverse into @objectid, @indexid, @fragmentation,@pagecount
end

— Remember to Close the cursor
close traverse

— Remember to Deallocte the cursor
deallocate traverse

—– CODE SNIPPET END —–

 Thats it… We have a cursor that are working, now we can look at two things:

  1. What is the pagecount
  2. what is the fragmentation

If the pagecount is very small then defragmentation is not that important. So let’s stick to the plan. In the cursor we have removed all indexes with a fragmentation below 10, so we only need to consider: Is it higher or lower than 30%?

—– CODE SNIPPET START —–

use adventureworks
go

— We declare some variables that should hold the
— parameters that are send from the outerloop to the code that
— are run for each row in the outer loop.

Declare @objectID varchar(50),
@IndexID varchar(50),
@Fragmentation real,
@pagecount int

— We declare the cursor
Declare traverse cursor for
select object_ID, index_id ,avg_fragmentation_in_percent,page_count
from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null)
where avg_fragmentation_in_percent>=10

— We open the cursor

Open traverse

— We try to fetch the first row of the cursor into the 4 variables

fetch next from traverse into @objectid, @indexid, @fragmentation, @pagecount

— If ok then we do something

while (@@FETCH_STATUS=0)
begin
   — We need more variables
  
Declare @tablename varchar(200),
  
@IndexName varchar(200)
,
   @sql
varchar(5000)

 — We do it a stupid way, but we want to use tablename and indexname
— So we must find it first
select @tablename=‘[‘+sys.schemas.name+‘].[‘+sys.tables.name+‘]’,@IndexName=sys.indexes.name
from sys.tables
                   
inner join sys.indexes on sys.tables.object_id=sys.indexes.object_id
                          
inner join sys.schemas on sys.tables.schema_id=sys.schemas.schema_id
where sys.tables.object_id=@objectID and index_id=@IndexID
— Then we test what the fragmentation is
— We could also test for pagecount
if @Fragmentation<30
begin
set @sql=‘Alter index [‘+@Indexname+‘] on ‘+ @tablename+‘ REORGANIZE’
end
else
begin
set @sql=‘Alter index [‘+@Indexname+‘] on ‘+ @tablename+‘ Rebuild’
end

— Then we execute the code
exec (@sql)
print‘The following code has been executed: +@sql 

— We try to fetch the next row of the cursor into the 4 variables
fetch next from traverse into @objectid, @indexid, @fragmentation, @pagecount

end

— Remember to Close the cursor

close traverse

— Remember to Deallocte the cursor

deallocate traverse

—– CODE SNIPPET END —–

And now the last things to do is to

  1. Remove the print line (or perhaps create a logfile so that you can see what are defragmentated, how and when.
  2. Add a job to the SQL Server Agent.

Very easy, and if you want to then you can try to reduce the size of my code to a minimum. I just wanted to show you some different technologies, in a way where you just can copy it from my blog into your own production environment.

There are a lot of “traps” and performance issues, both when it comes to using a cursor, and when it comes to defragmentation. This is just the beginning!!!!

 

 

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