Community Server for Rent a Coder

Fragmentation of your SQL Server indices builds up over time from new data being added to your tables.  This means that the more work your database does...the more it will slow down on scans, meaning that your queries will run slower and slower over time.

So it is a good idea to recreate your indices in SQL Server 2005 regularly (this gets rid of the fragmentation).  You can also do an index defrag...and this can be done online.  However it is not as effective as a complete recreate as it doesn't reorganize everything.  So eventually you will be forced to do a rebuild.

So what are your options?  The new "online rebuilding" of indices in SQL Server is as highly limited as it has been highly touted.  I contacted MSFT support becuase I couldn't use it on half of my tables without the job bombing out.  They informed me that if you have any # of things that appear in every "real database" (like a clustered index!) it won't work.  So this means you are back to the SQL Server 2000 method of taking down your database and recreating the indcies.  Yes this means a maintainence downtime window for your supposedly 24 hour/7 day a week database...and if you don't do it...your database WILL get slower and slower and the user complaints will get louder and louder.  So much for the "self-tuning" advertised in SQL 2005's Executive review.

Anyway, I grudgingly did this and saw some indices vastly improve their performance. But I also noticed that I just couldn't get rid of the fragementation on some tables.  The tech at Microsoft explained that on smaller tables, SQL Server will not be able to get rid of the fragmentation.  I saw rates of 60-80% on tables with a few thousand rows.

Here is an example (from his testing) that shows the problem:

-----------------------

USE [mytest]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].(

    [CustomerID] [nchar](5)  NOT NULL,

    [CompanyName] [nvarchar](40)  NOT NULL,

    [ContactName] [nvarchar](30)  NULL,

    [ContactTitle] [nvarchar](30)  NULL,

    [Address] [nvarchar](60)  NULL,

    [City] [nvarchar](15)  NULL,

    [Region] [nvarchar](15)  NULL,

    [PostalCode] [nvarchar](10)  NULL,

    [Country] [nvarchar](15)  NULL,

    [Phone] [nvarchar](24)  NULL,

    [Fax] [nvarchar](24)  NULL

) ON [PRIMARY]

n        And then we insert about 200 rows data and add a index in management studio: PK_C  clustered, unique, primary key located on PRIMARY    CustomerID

select object_id('c') –-the result is 1637580872

ALTER INDEX ALL on c REBUILD with (FILLFACTOR=100)

SELECT * FROM sys.dm_db_Index_physical_stats(7,1637580872,null,null,null)

--the result avg_fragmentation_in_percent will be 66.6666666666667, refer to attached doc file screen shot 1

And then we run the following command several times:

insert into c select * from c  -- (23296 row(s) affected)

About the result avg_fragmentation_in_percent is 32.2% 

And then we run the following command several times:

insert into c select * from c  -- (186368 row(s) affected)

About the result avg_fragmentation_in_percent is 6.8%.

When we increase the data of the table, we can find the page count will increase and the avg_fragmentation_in_percent value will decrease, it is controlled by SQL Server internal design, if we continue to insert more data, we can find the value will reach nearly 0.

----------------------------------------
So this is something to keep in mind when defragging.

Ian Ippolito
http://www.RentACoder.com