Analyze and Fix Index Fragmentation in SQL Server 2008

It is very common that over time SQL Server tables and indexes tend to become fragmented. The fragmentation generally happens when data within the underlying tables on which an index exists is modified. The data modification basically can be an insert, update or a delete operation. The indexes over time become ineffective because they get fragmented. In this article you will see an example of how an index gets fragmented and the steps which database administrator needs to take to fix index fragmentations.

Example to Analyze and Fix Index Fragmentation in SQL Server 2008
Follow the below mentioned steps to see how an index fragmentation occurs on a table which has indexes defined on it. And finally you will see the steps which you need to take to fix index fragmentation issues.

Create AnalyzeFragmentation Database
First let us create a new database named AnalyzeFragmentation for this example. Database can be created by executing the below mentioned TSQL Query.

Use master

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N’AnalyzeFragmentation’)
DROP DATABASE [AnalyzeFragmentation]

CREATE DATABASE AnalyzeFragmentation

Create FindAndFixFragmentation Table in AnalyzeFragmentation Database
The next step will be to create a new table named FindAndFixFragmentation within the AnalyzeFragmentation database.

USE AnalyzeFragmentation

IF OBJECT_ID (N’dbo.FindAndFixFragmentation’, N’U’) IS NOT NULL
    DROP TABLE dbo.FindAndFixFragmentation;

/* Create FindAndFixFragmentation Table*/
CREATE TABLE [dbo].[FindAndFixFragmentation]
 [AddressID] [int] NOT NULL,
 [AddressLine1] [nvarchar](60) NOT NULL,
 [City] [nvarchar](30) NOT NULL,
 [PostalCode] [nvarchar](15) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,

Populate the FindAndFixFragmentation Table using the below TSQL code
The next step will be to populate the FindAndFixFragmentation table which you have created earlier by executing the below mentioned TSQL code. For this example we will be using the data which is available in Person.Address table available in AdventureWorks database.

USE AnalyzeFragmentation

/* Populate FindAndFixFragmentation table with data from AdventureWorks.Person.Address */
INSERT INTO FindAndFixFragmentation
FROM AdventureWorks.Person.Address


Pages: 1 2 3


One Response to “Analyze and Fix Index Fragmentation in SQL Server 2008”

  1. I have fragmented the Indexes even then the fragmentation is there. What is the reason? How to resolve it? How shall i identify all indexes which needs defragmentation? Shall i defragment the indexes which are created for the tables used for replication? If i include the check sys.objects.type_desc=’User Table’ even then the indexes associated with Replication tables are listed there? Also, some tables with name starting with sys are listing. Shall i defragment these sys tables as well?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |