Rebuilding SQL Server Indexes

Highlights:

- Check index fragmentation: average percentage, page count

- ALTER INDEX REBUILD

- Manually set Timeout parameter

- Set counter to control session frozen time

- Sleep to release suspended sessions

- EXEC sp_who2 to check real time deadlocks

1. Background

A lot of systems have part of logic running in Database, and as time passed, data and indexes increased, and then Database performance will decrease.

One of key factor of Database performance is Index.

When data and indexes increase, the fragmentation level increase as well.

According to Microsoft article “Reorganizing and Rebuilding Indexes(https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189858(v=sql.90))”, we should Reorganize indexes when average fragmentation is between 5% and 30%, and we should Rebuild indexes when average fragmentation is greater than 30%.

We can use below SQL to check average fragmentation of indexes which are greater than 30%:

-------------------------------------------------------------------------------------

SELECT dbschemas.[name] AS 'Schema'

,dbtables.[name] AS 'Table'

,dbindexes.[name] AS 'Index'

,indexstats.avg_fragmentation_in_percent

,indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

--AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

--ORDER BY indexstats.page_count DESC;

ORDER BY indexstats.avg_fragmentation_in_percent DESC;

-------------------------------------------------------------------------------------

We also need to pay attention to page count, each page stores about 8KB data, so a big page count means the rebuild time will be quite long.

2. Considerations

1) Use ALTER INDEX index_name or ALTER INDEX ALL

Use ALTER INDEX index_name ON Table REBUILD can only rebuild 1 index with a command.

ALTER INDEX ALL ON Table REBUILD can rebuild all Indexes of a Table with a command, but it require longer shut down time, because the new SQL might be suspended during the rebuild process.

2) Manually set remote timeout to avoid timeout disconnect:

EXEC sp_configure 'remote query timeout', 600 ;

RECONFIGURE ;

Since rebuilding process might take quite long time, which could be longer than default remote timeout time, so it will be better to manually set it.

3) Set counter to control session frozen time

Even if we’re using PRINT() in SQL Server Management Studio(SSMS), the print out messages will only be showed after the whole SQL block has completed execution.

During the SQL execution, the Messages Tab is blank, so during that time we have no idea what’s going on or going wrong.

So it will be better to spilt the whole session into many small sessions, and we can use Counter to control it.

Now we change the SQL of checking index fragmentation, to order by page_count desc:

-------------------------------------------------------------------------------------

SELECT --dbschemas.[name] AS 'Schema',

dbtables.[name] AS 'Table',

dbindexes.[name] AS 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

AND dbindexes.[name] IS NOT NULL

ORDER BY indexstats.page_count DESC;

--ORDER BY indexstats.avg_fragmentation_in_percent DESC;

-------------------------------------------------------------------------------------

So for these indexes with such huge page count, we should rebuild 1 index each time.

And when page count is getting smaller, we can set counter into a bigger number.

4) Set sleep to release suspended sessions.

If we’re running multiple REBUILD operations in same session, we should sleep at least for 5 seconds in between, so the suspended SQLs will be run and released.

By using this:

WAITFOR DELAY '00:00:05';

5) Check real time deadlocks before and during REBUILD.

By running:

EXEC sp_who2

We can see deadlocked sessions(Status=SUSPENDED, or BlkBy IS NOT NULL), the BlkBy points to the session who caused the deadlock.

We should run REBUILD when the moment no deadlocks are found.

Another SQL to list deadlocks:

SELECT * 

FROM sys.dm_exec_requests

WHERE DB_NAME(database_id) = 'KBS_FDM' 

AND blocking_session_id <> 0;

3. Optimized SQL:

-------------------------------------------------------------------------------------

Declare @getTables CURSOR; -- Cursor to list out all indexes have frag % > 30

Declare @TableName varchar(255);

Declare @IndexName varchar(255);

Declare @command nvarchar(4000); -- Detail ALTER INDEX command

DECLARE @DT NVARCHAR(50); -- Datetime to print out

DECLARE @i INT; -- Counter

-- set remote time out

EXEC sp_configure 'remote query timeout', 600 ;

RECONFIGURE ;

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('Rebuilding Index started.');

PRINT('');

--SET @getTables = CURSOR for SELECT name FROM sys.objects WHERE type = (N'U');

--EXEC sp_who2 -- to check existing deadlock(BlkBy)

SET @i = 0;

SET @getTables = CURSOR for

SELECT --dbschemas.[name] AS 'Schema',

dbtables.[name] AS 'TableName',

dbindexes.[name] AS 'IndexName'

--indexstats.avg_fragmentation_in_percent,

--indexstats.page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

AND indexstats.avg_fragmentation_in_percent > 30

AND dbschemas.[name] = 'dbo'

--AND dbtables.[name] = 'RPV_MAGPLANBEL'

AND dbindexes.[name] IS NOT NULL

ORDER BY indexstats.page_count DESC;

--ORDER BY indexstats.avg_fragmentation_in_percent DESC;

OPEN @getTables;

FETCH NEXT FROM @getTables into @TableName, @IndexName;

--WHILE @@FETCH_STATUS = 0

WHILE @i < 1 AND @@FETCH_STATUS = 0 -- Set @i to bigger number when page count is getting smaller

BEGIN;

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

--set @command = N'ALTER INDEX ALL ON ' + @TableName + N' REBUILD;'; -- WITH (ONLINE=ON);'; for enterprise version only

set @command = N'ALTER INDEX ' + @IndexName + N' ON ' + @TableName + N' REBUILD;';

PRINT(@command);

--PRINT('');

--WAITFOR DELAY '00:00:01';

BEGIN TRY  

EXEC (@command);

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('Rebuilding Index completed.');

PRINT('');

END TRY  

BEGIN CATCH  

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT 'CATCHED!';

END CATCH;  

WAITFOR DELAY '00:00:05'; -- wait 5s to release suspended sessions

FETCH NEXT FROM @getTables into @TableName, @IndexName;

SET @i = @i + 1;

END;

CLOSE @getTables;

DEALLOCATE @getTables; 

SELECT @DT = CONVERT(NVARCHAR, GETDATE(), 121); --YYYY-MM-DD HH:MI:SS

PRINT(@DT);

PRINT('All indexes rebuilt.');

-------------------------------------------------------------------------------------

4. PS: WITH (ONLINE=ON) condition.

While running ALTER INDEX (ALL) REBUILD, there’s an optional parameter called ONLINE.

If we set ONLINE=ON, that makes targeted index still available during most time of rebuild process, but that’s only available for SQL Server Enterprise version.