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
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’AnalyzeFragmentation’)
DROP DATABASE [AnalyzeFragmentation]
GO
CREATE DATABASE AnalyzeFragmentation
GO
Create FindAndFixFragmentation Table in AnalyzeFragmentation Database
The next step will be to create a new table named FindAndFixFragmentation within the AnalyzeFragmentation database.
USE AnalyzeFragmentation
GO
IF OBJECT_ID (N’dbo.FindAndFixFragmentation’, N’U’) IS NOT NULL
DROP TABLE dbo.FindAndFixFragmentation;
GO
/* 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,
[RowGUID] [UNIQUEIDENTIFIER] NOT NULL
)
ON [PRIMARY]
GO
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
GO
/* Populate FindAndFixFragmentation table with data from AdventureWorks.Person.Address */
INSERT INTO FindAndFixFragmentation
SELECT
AddressID,
AddressLine1,
City,
PostalCode,
ModifiedDate,
RowGUID
FROM AdventureWorks.Person.Address
GO