SQL: defragment,rebuild or show info about indexes in a database

Published by Marian Galie-Andriescu on

This is a sql script I am using quite some time now, from 2010.

It is based on the original work from Microsoft but it is heavily upgraded to give you more options and functionality.

https://msdn.microsoft.com/en-us/library/ms177571.aspx

You can use the script to just show information about the fragmentation level of the indexes, or to give you a list of sql queries to defragment or rebuild the indexes that need it, or to defragment or rebuild the indexes which have a LogicalFrag higher than a specified value. You can even specify a pause in between defragment or rebuild of each index so the sql server load will be better spread in time.

Download the sql script here:

andriescu_sql_defragment_rebuild_indexes_database


-- ******************************************************************************************************
-- * Use this script to get info or defrag or rebuild all indexes in the current database *
-- * M.Galie Andriescu, marian.galie.andriescu@gmail.com *
-- * SQL version 2005 and up *
-- ******************************************************************************************************

DECLARE @maxfrag INT
DECLARE @maxDefragFrag INT
DECLARE @maxRebuildFrag INT
DECLARE @objectid INT
DECLARE @indexname VARCHAR(255)
DECLARE @tablename VARCHAR(255)
DECLARE @logicalfrag DECIMAL
DECLARE @IndexDefragStr NVARCHAR(2000)
DECLARE @IndexDBReindexStr NVARCHAR(2000)
DECLARE @IndexShowContigStr NVARCHAR(2000)
DECLARE @Action Int
DECLARE @fillFactor INT
DECLARE @waitForDelay varchar(9)
DECLARE @allIndexInfo BIT
-- ******************************************************************************************************
-- * Configuration section *
-- ******************************************************************************************************
-- Action can be one of the following:
--
-- -1 : show defrag sql for all indexes with logical fragmentation > @maxDefragFrag
-- -2 : show rebuild sql for all indexes with logical fragmentation > @maxRebuildFrag and using @fillFactor
-- 0 : show information about the indexes
--
-- WARNING: the following settings will defrag indexes without any confirmation asked!
--
-- 1 : defrag all indexes with logical fragmentation > @maxDefragFrag
-- 2 : rebuild all indexes with logical fragmentation > @maxRebuildFrag and using @fillFactor
-- WARNING: only use option 2 with off-line services,
-- in other words stop all application services first!!!
--
-- @waitForDelay = is the time (default 5 seconds) between executing the index de-fragment queries
-- so the sql server will not be used too much.
--
-- @allIndexInfo = 0 Use fast option to return statistics about the existing indexes.
-- This is fast but will not return information about ExtentFrag, CountRows, MinRecSize, AvgRecSize etc
-- = 1 Collect all statistics related to indexes, but this will take a lot more time to process for a big database.
--
-- ******************************************************************************************************
set @Action = 0
set @maxDefragFrag = 20
set @maxRebuildFrag = 80
set @fillFactor = 90
set @waitForDelay = '00:00:05'
set @allIndexInfo = 0
-- ******************************************************************************************************
--drop table ##fraglist
--drop table ##deFragSqlCmd

-- Creating temp table to hold index info
CREATE TABLE ##fraglist
(ObjectName varchar(1024) NULL, IndexName varchar(1024) NULL ,
LogicalFrag decimal(18, 0) NULL , ExtentFrag decimal(18, 0) NULL ,
ObjectId int NULL ,
IndexId int NULL , Lvl int NULL , CountPages int NULL ,
CountRows int NULL , MinRecSize int NULL , MaxRecSize int NULL ,
AvgRecSize int NULL , ForRecCount int NULL , Extents int NULL ,
ExtentSwitches int NULL , AvgFreeBytes int NULL ,
AvgPageDensity int NULL , ScanDensity decimal(18, 0) NULL ,
BestCount int NULL , ActualCount int NULL ,
[Status] INT DEFAULT 0)

CREATE TABLE ##deFragSqlCmd
(ObjectName varchar(255) NULL ,
IndexName varchar(255) NULL ,
LogicalFrag decimal(18, 0) NULL,
DefragSqlCmd NVARCHAR(2000) NULL)

-- Filling the temp table with info
raiserror ('Getting indexes info...', 0,1) with nowait;

IF (@allIndexInfo=1)
BEGIN
--collect all index info, but slow
PRINT 'Collecting all index info...'
INSERT INTO ##fragList(ObjectName,ObjectId,Indexname,IndexID,
Lvl,CountPages,Countrows,MinRecSize,MaxRecsize,
AvgRecSize,ForRecCount,Extents,ExtentSwitches,
AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,
ActualCount,LogicalFrag,ExtentFrag)
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS;');
end else
begin
--this is faster, but less info, as for example we will not know ExtentFrag, CountRows, MinRecSize, AvgRecSize etc
PRINT 'Using fast option to collect index info...'
INSERT INTO ##fragList(ObjectName,ObjectId,Indexname,IndexID,
Lvl,CountPages,Countrows,MinRecSize,MaxRecsize,
AvgRecSize,ForRecCount,Extents,ExtentSwitches,
AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,
ActualCount,LogicalFrag,ExtentFrag)
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST, NO_INFOMSGS;');
end

raiserror ('Finished getting indexes info.', 0,1) with nowait;
-- Removing indexes we are not interested in
-- If you get errors, remove [collate database_default]
delete from ##fraglist where ObjectName collate database_default not in
(select Table_Name from information_schema.tables
where table_type = 'base table') or
indexid IN (0,255)

-- Adding schema name to table name
-- If you get errors, remove [collate database_default]
Update ##fraglist set ObjectName = OBJECT_SCHEMA_NAME(ObjectId) + '.' + RTRIM(LTRIM(ObjectName))

-- Showing index info before defrag
select * from ##fraglist order by logicalFrag desc

-- Perform requested actions
if (@action = 1) or (@action = 2) or (@action = -1) or (@action = -2)
begin
if (@action = 1) or (@action = -1)
set @maxfrag = @maxDefragFrag
else
set @maxfrag = @maxRebuildFrag

-- Create the cursor to loop through index info
DECLARE ind_Cursor CURSOR FOR
SELECT ObjectName, ObjectId, Indexname, LogicalFrag
FROM ##fraglist WHERE LogicalFrag >= @maxfrag AND Status=0

-- Open the cursor
OPEN ind_Cursor

-- Loop through the indexes
FETCH NEXT FROM ind_Cursor INTO @tablename, @objectid, @indexname, @logicalfrag

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @IndexDefragStr=N'DBCC INDEXDEFRAG (0, '+ QUOTENAME(RTRIM(@tablename),'[') + ',' + QUOTENAME(RTRIM (@indexname)) + ')' + ' WITH NO_INFOMSGS'
SELECT @IndexDBReindexStr=N'DBCC DBREINDEX ('+ QUOTENAME(RTRIM(@tablename),'[') + ',' + QUOTENAME(RTRIM (@indexname)) + ', '+ str(@fillFactor) + ')'
SELECT @IndexShowContigStr=N'DBCC SHOWCONTIG ('+ QUOTENAME(RTRIM(@tablename),'[') + ',' + QUOTENAME(RTRIM (@indexname)) + ')'

if (@action = 1) or (@action = -1)
begin
if (@action = 1)
begin
PRINT 'Start Defrag index = ' + QUOTENAME(RTRIM(@indexname)) + ' from table = ' +
+ QUOTENAME(RTRIM(@tablename)) + ' logical defragmentation is = ' + ltrim(str(@logicalfrag));
EXEC sp_executesql @IndexDefragStr
end else
begin
insert into ##deFragSqlCmd (ObjectName, IndexName, LogicalFrag, DefragSqlCmd)
values (RTRIM(@tablename), RTRIM (@indexname), @logicalfrag, @IndexDefragStr)
end
end else
begin
if (@action = 2)
begin
PRINT 'Start DbReindex index = ' + QUOTENAME(RTRIM(@indexname)) + ' from table = ' +
QUOTENAME(RTRIM(@tablename)) + ' logical defragmentation is = ' + ltrim(str(@logicalfrag));
EXEC sp_executesql @IndexDBReindexStr
end else
begin
insert into ##deFragSqlCmd (ObjectName, IndexName, LogicalFrag, DefragSqlCmd)
values (RTRIM(@tablename), RTRIM (@indexname), @logicalfrag, @IndexDBReindexStr)
end
end

if (@action = 1) or (@action = 2)
begin
EXEC sp_executesql @IndexShowContigStr
WAITFOR DELAY @waitForDelay
end

UPDATE ##fraglist SET Status=1
WHERE Status=0 AND objectName=@tableName AND objectID=@objectID AND IndexName=@indexName

FETCH NEXT FROM ind_Cursor INTO @tablename, @objectid, @indexname, @logicalfrag
END

-- Close and deallocate the cursor
CLOSE ind_Cursor
DEALLOCATE ind_Cursor
END

drop table ##fraglist

if (@action < 0)
begin
select * from ##deFragSqlCmd order by LogicalFrag desc
end
drop table ##deFragSqlCmd

-- ******************************************************************************************************
-- * *
-- * Version Log *
-- * *
-- ******************************************************************************************************
-- * Version 10 *
-- * 21 july 2015 changed the way the table schema is read from the database, now using *
-- * OBJECT_SCHEMA_NAME *
-- * Version 9 *
-- * 2 may 2013 changed fill-factor from 80 to 90 *
-- * Added new parameter allIndexInfo *
-- * Version 8 *
-- * using [database_default] and not [Latin1_General_CI_AS] so as to keep *
-- * the script not independent a certain collation *
-- * Version 7 *
-- * using [collate Latin1_General_CI_AS] *
-- * Version 6 *
-- * 02 dec 2011 : uses temp tables instead of temp DB where it could lead to collation error. *
-- * Version 5 *
-- * 30 nov 2011 : Using option FAST to get info about the indexes. *
-- * : Added delay of 30 seconds between executing a index de-fragmentation. *
-- * Version 4 *
-- * 26 jan 2011 : Added schema to the table name *
-- * Version 3 *
-- * 24 jan 2011 : Added functionality to just print the index defrag sql statements *
-- * Version 2 *
-- * 10 jan 2011 : Removed COLLATE SQL_Latin1_General_CP1_CI_AS from CREATE TABLE ##fraglist *
-- * Version 1 *
-- * 1 aug 2010 : First release. *
-- ******************************************************************************************************
Categories: SQL

Related Posts

SQL

SQL Poetry

This is not strictly a technical article, but nevertheless I decided to post it on my site. I believe quite a few of us, and by us, I mean SQL query writers, sometimes we feel Read more…

SQL

SQL: How to parse Microsoft Transact-SQL Statements in C# and to match the column aliases of a view with its related (dependent) tables.

This is a example on how to use the Microsoft T-SQL parser in C# which is part of the Feature Pack. Microsoft® SQL Server® 2014 Feature Pack: https://www.microsoft.com/en-us/download/details.aspx?id=42295 Microsoft® SQL Server® 2014 Transact-SQL Language Service: Read more…

SQL

SQL: ScriptDb 2.0

ScriptDb is a C# console app that uses SQL Management Objects (SMO) to script database objects. This project is a refinement and improvement of the orignal work of  Jesse Hersch of http://www.elsasoft.org His project can Read more…