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}