Index Maintenance across all Databases in an Instance

Article
07/30/2009

I needed to make a job for a customer that rebuilds all indexes in an instance that have a fragmentation of more than 10%. Usually we use a maintenance plan for rebuilding indexes. Although that is a fine approach is has one disadvantage: it rebuilds all indexes in a database regardless of the level of fragmentation. I do not like that because it takes up resources that are not necessary. It also takes far longer to complete than my job.

As a basis I used the code from the BOL on the sys.dm_db_index_physical_stats page. Here you can find the following piece of code:

-- Ensure a USE <databasename> statement has been executed first.

SET
NOCOUNT
ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id
AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM
sys.dm_db_index_physical_stats
(DB_ID(),
NULL,
NULL
,
NULL,
'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE
partitions
CURSOR
FOR
SELECT
*
FROM #work_to_do;

-- Open the cursor.

OPEN
partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH
NEXT

FROM
partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF
@@FETCH_STATUS
< 0 BREAK;

SELECT @objectname =
QUOTENAME(o.name), @schemaname =
QUOTENAME(s.name)

FROM
sys.objects
AS o

JOIN
sys.schemas
as s ON s.schema_id
= o.schema_id

WHERE o.object_id
= @objectid;

SELECT @indexname =
QUOTENAME(name)

FROM
sys.indexes

WHERE
object_id
= @objectid AND index_id = @indexid;

SELECT @partitioncount =
count
(*)

FROM
sys.partitions

WHERE
object_id
= @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command =
N'ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REORGANIZE';

IF @frag >= 30.0

SET @command =
N'ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REBUILD';

IF @partitioncount > 1

SET @command = @command +
N' PARTITION='
+
CAST(@partitionnum AS
nvarchar(10));

EXEC (@command);

PRINT
N'Executed: '
+ @command;

END;

-- Close and deallocate the cursor.

CLOSE
partitions;

DEALLOCATE
partitions;

-- Drop the temporary table.

DROP
TABLE #work_to_do;

This code runs in only one database so I needed to add a cursor to run through all databases on the instance. The code above also differentiates partitions in an index. I didn't need that, so I stripped it out. I als stripped out the reorganize code. On an enterprise edition I prefer online rebuilds.

I came up with the following code:

-- Index rebuild online on all databases on an instance

SET
NOCOUNT
ON

DECLARE @db as
sysname;

DECLARE @db_id as
int;

DECLARE @cmd1 as
nvarchar(1000);

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @command nvarchar(4000);

DECLARE @errornumber int;

DECLARE DatabaseList CURSOR
FAST_FORWARD
FOR

SELECT [name], [database_id] FROM
sys.databases

WHERE [name] NOT
IN
('tempdb','model')

AND state_desc =
'ONLINE'

ORDER
BY [name]

OPEN DatabaseList

FETCH
NEXT
FROM DatabaseList INTO @db, @db_id

WHILE (@@fetch_status
= 0)

BEGIN

PRINT
N' ';

PRINT
N'Database: '
+ @db;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

EXEC ('USE '
+ @db +
';

SELECT p.object_id AS objectid

, p.index_id AS indexid

, o.name AS objectName

, s.name AS schemaName

, i.name AS indexName

INTO ##work_to_do

FROM sys.dm_db_index_physical_stats ('
+ @DB_ID +
', NULL, NULL , NULL, ''LIMITED'') AS p

INNER JOIN sys.objects as o

ON p.object_id = o.object_id

INNER JOIN sys.schemas as s

ON s.schema_id = o.schema_id

INNER JOIN sys.indexes i

ON p.object_id = i.object_id

AND i.index_id = p.index_id

WHERE p.avg_fragmentation_in_percent > 10.0 AND p.index_id > 0

AND p.index_id < 25000

AND p.page_count > 1000;')

-- Declare the cursor for the list of fragmented indexes to be processed.

DECLARE FragmentedIndexes CURSOR
FOR
SELECT
*
FROM ##work_to_do;

-- Open the cursor.

OPEN FragmentedIndexes;

-- Loop through the indexes.

WHILE (1=1)

BEGIN;

FETCH
NEXT

FROM FragmentedIndexes

INTO @objectid, @indexid, @objectname, @schemaname, @indexname;

IF
@@FETCH_STATUS
< 0 BREAK;

-- Check for invalid datatypes for online rebuild

-- If those datatypes are found, do normal rebuild

BEGIN
TRY

SET @command =
N'USE '
+ @db +
'; ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REBUILD WITH (ONLINE=ON)';

EXEC (@command);

PRINT
N'Executed: '
+ @command;

END
TRY

BEGIN
CATCH

SELECT @errornumber =
ERROR_NUMBER()

IF @errornumber = 2725

BEGIN

SET @command =
N'USE '
+ @db +
'; ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REBUILD';

EXEC (@command);

PRINT
N'Executed: '
+ @command;

END

END
CATCH

END;

-- Close and deallocate the cursor.

CLOSE FragmentedIndexes;

DEALLOCATE FragmentedIndexes;

-- Drop the temporary table.

DROP
TABLE ##work_to_do;

FETCH
NEXT
FROM DatabaseList INTO @db, @db_id

END

CLOSE DatabaseList;

DEALLOCATE DatabaseList;

Explanation of the code:

I start with creating a cursor for all databases on the instance:

DECLARE DatabaseList CURSOR
FAST_FORWARD
FOR

SELECT [name], [database_id] FROM
sys.databases

WHERE [name] NOT
IN
('tempdb','model')

AND state_desc =
'ONLINE'

ORDER
BY [name]

Of course I exclude the tempdb and the model database. On model I don't expect indexes and tempdb is obvious.

After that I open the database cursor and start processing the databases one at the time.

First I collect the fragmented indexes:

EXEC ('USE '
+ @db +
';

SELECT p.object_id AS objectid

, p.index_id AS indexid

, o.name AS objectName

, s.name AS schemaName

, i.name AS indexName

INTO ##work_to_do

FROM sys.dm_db_index_physical_stats ('
+ @DB_ID +
', NULL, NULL , NULL, ''LIMITED'') AS p

INNER JOIN sys.objects as o

ON p.object_id = o.object_id

INNER JOIN sys.schemas as s

ON s.schema_id = o.schema_id

INNER JOIN sys.indexes i

ON p.object_id = i.object_id

AND i.index_id = p.index_id

WHERE p.avg_fragmentation_in_percent > 10.0 AND p.index_id > 0

AND p.index_id < 25000

AND p.page_count > 1000;')

I use sys.dm_db_index_physical_stats for finding the fragmented indexes. In order to get the name, schemaname and objectname I join the sys.dm_db_index_physical_stats with sys.objects, sys.schemas and sys.indexes.

The result I insert in a global temp table. Global because the exec statement runs on another thread so I can't use a local temp table. I can't see it in the rest of the procedure if I did that.

In the where clause I check on fragmentation over 10%. I also check if the index_id is smaller than 25000. If it is over it is either a XML index or a Spatial index.

The last check I do is on page_count of the index. SQL Server will not rebuild indexes that are smaller than 1000 pages(8MB). It will not do seeks on such small indexes, only scans. So fragmentation won't matter.

With the fragmented indexes in the temp table I can build the statement necessary to defragment the index:

BEGIN
TRY

SET @command =
N'USE '
+ @db +
'; ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REBUILD WITH (ONLINE=ON)';

EXEC (@command);

PRINT
N'Executed: '
+ @command;

END
TRY

BEGIN
CATCH

SELECT @errornumber =
ERROR_NUMBER()

IF @errornumber = 2725

BEGIN

SET @command =
N'USE '
+ @db +
'; ALTER INDEX '
+ @indexname +
N' ON '
+ @schemaname +
N'.'
+ @objectname +
N' REBUILD';

EXEC (@command);

PRINT
N'Executed: '
+ @command;

END

END
CATCH

Since the customer had an enterprise edition I do the rebuild online. That was necessary since the customer had a 24*7 system. Online rebuilds do have limitations: you can't rebuild an index online if the index contains blob datatypes like text, ntext, xml, varchar(max), nvarchar(max), image, etc….

If those datatypes are used in the underlying table you can't do an online rebuild either. That's why I check for errornumber 2725. If that's returned I build the rebuild statement without the online option.

The rest of the code is cleanup code. We should drop the temp table and close and deallocate the cursors(very important!)

I want to add some more errorhandling later on. The temptable should always be dropped. But that's the only thing missing in the code.

If you have any remarks on my code, I'll be delighted to hear it!

Regards,

Menzo

Comments

Anonymous
April 20, 2011
Congratulations on a clever and well thought out example of database maintenance. I am using it across my organization - Your credits are in the headline.
Anonymous
September 17, 2011
I whont to have a help whit my regysteryproblems and stopp problems

Share via

Index Maintenance across all Databases in an Instance

Explanation of the code:

Comments

Additional resources