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.
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.
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,
[RowGUID] [UNIQUEIDENTIFIER] 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.
/* Populate FindAndFixFragmentation table with data from AdventureWorks.Person.Address */
INSERT INTO FindAndFixFragmentation