SQL : Troubleshooting Index Fragmentation

The following SQL script will identify fragmented indexes and automaticallyrebuild them. Note that this should not be run duringbusy periods/production hours and should only be run against databases which are not partitioned:

{code lang:sql showtitle:false lines:false hidden:false}declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare tables cursor for
select TABLE_SCHEMA + ‘.’ + TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘BASE TABLE’

create table #spt_space
( aidi int,
[rows] int null,
reserved dec(15) null,
AUTOSTATS char(2) null,
[Last Updated] datetime
)
open tables
/*
** Set NORECOMPUTE mask
*/
set @nrc_mask = 16777216

fetch next
from tables
into @tablename
while @@fetch_status = 0
begin

select @id = object_id(@tablename)

insert into #spt_space (aidi, reserved)
select @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
update #spt_space set [rows] = i.[rows]
from #spt_space inner join sysindexes i
on #spt_space.aidi = i.id
where i.indid =indid and i.id = @id

update #spt_space set [AUTOSTATS] =
case (si.status & @nrc_mask)
when @nrc_mask then ‘OFF’
else ‘ON’
end,
[Last Updated] = stats_date(#spt_space.aidi, si.indid)
from sysindexes si inner join #spt_space
on #spt_space.aidi = si.id
where si.id = @id AND — Table
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index
fetch next
from tables
into @tablename
end

— Close and deallocate the cursor
close tables
deallocate tables

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128))
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
–WHERE rows >= 100000

SELECT * FROM #spt_space
DROP TABLE #spt_space
SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

SET @id = 1
WHILE @id <= @Cnt
BEGIN
SELECT @tablename = tablename FROM #bigtables WHERE i = @id
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’)
SET @id = @id + 1
END

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC
DROP Table #bigtables
DROP table #fraglist{/code}

Use the following code to rebuild indexes that are 10% or more fragmented (you can change this threshold by modifying the figure highlighted in RED):

{code lang:sql title:”SQL Query – Fix Fragmentation” lines:false hidden:false}declare @tablename varchar (128) 

declare @id int 
declare @Cnt smallint 
declare @nrc_mask int 
declare tables cursor for 
select TABLE_SCHEMA + ‘.’ + TABLE_NAME 
from INFORMATION_SCHEMA.TABLES 
where TABLE_TYPE = ‘BASE TABLE’ 

create table #spt_space 
( aidi int, 
[rows] int null, 
reserved dec(15) null, 
AUTOSTATS char(2) null, 
[Last Updated] datetime 

open tables 
/* 
** Set NORECOMPUTE mask 
*/ 
set @nrc_mask = 16777216 

fetch next 
from tables 
into @tablename 
while @@fetch_status = 0 
begin 

select @id = object_id(@tablename) 

insert into #spt_space (aidi, reserved) 
select @id, sum(reserved) 
from sysindexes 
where indid in (0, 1, 255) 
and id = @id 
update #spt_space set [rows] = i.[rows] 
from #spt_space inner join sysindexes i 
on #spt_space.aidi = i.id 
where i.indid =indid and i.id = @id 

update #spt_space set [AUTOSTATS] = 
case (si.status & @nrc_mask) 
when @nrc_mask then ‘OFF’ 
else ‘ON’ 
end, 
[Last Updated] = stats_date(#spt_space.aidi, si.indid) 
from sysindexes si inner join #spt_space 
on #spt_space.aidi = si.id 
where si.id = @id AND — Table 
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index 
fetch next 
from tables 
into @tablename 
end 

— Close and deallocate the cursor 
close tables 
deallocate tables 

CREATE TABLE #fraglist ( 
ObjectName CHAR (255), 
ObjectId INT, 
IndexName CHAR (255), 
IndexId INT, 
Lvl INT, 
CountPages INT, 
CountRows INT, 
MinRecSize INT, 
MaxRecSize INT, 
AvgRecSize INT, 
ForRecCount INT, 
Extents INT, 
ExtentSwitches INT, 
AvgFreeBytes INT, 
AvgPageDensity INT, 
ScanDensity DECIMAL, 
BestCount INT, 
ActualCount INT, 
LogicalFrag DECIMAL, 
ExtentFrag DECIMAL) 

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128)) 
INSERT #bigtables(tablename) 
SELECT object_name(aidi) as tablename 
FROM #spt_space 
–WHERE rows >= 100000 

SELECT * FROM #spt_space 
DROP TABLE #spt_space 
SELECT @Cnt = count(*) FROM #bigtables 

DECLARE @DTime datetime 
Set @DTime = GETDATE() 

SET @id = 1 
WHILE @id <= @Cnt 
BEGIN 
SELECT @tablename = tablename FROM #bigtables WHERE i = @id 
INSERT INTO #fraglist 
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) 
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’) 
SET @id = @id + 1 
END 

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC 

DROP Table #bigtables 

/* ********************************* */
/* Perform the REINDEX – NOTE This will lock the Table in which the index resides */
/* ********************************* */

DECLARE @objectid int, @indexname varchar(200), @frag decimal, @SQL varchar(500)
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= 10
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;
— Open the cursor.
OPEN indexes;
— Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT ‘Executing DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname)
+ ”’) – LogicalFrag Currently: ‘ + RTRIM(CONVERT(VARCHAR(15),@frag)) + ‘%’;

SELECT @SQL = ‘DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname) + ”’)’
EXEC (@SQL)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
END;
— Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
— Delete the temporary table.
DROP TABLE #fraglist;
GO

EXEC sp_updatestats

{/code}