Thursday, 11 August 2016

Some Notes about SQL Index Fragmentation

This post is about a recent reading/research that I have made regarding of how to identify ‘bad’ indexes that have been defragmented much and how to fix those to be optimal again. There are many references put on this post such as useful scripts and articles for further reading.

Identifying Defragmented Indexes
Firstly, we would need to find the indexes that have much fragmentation. Below is a useful script from Microsoft Script Center site. This script shows average fragmentation for each index in all tables and indexed views.
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, AS IndexName, indexstats.index_type_desc AS IndexType,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 0--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC

What to Do with the Result?
From the result we can either choose to ignore, reorganise or rebuild each index.

Reorganising an index is to reorder and clean up the index with pre-existing settings. While rebuilding an index is to recreate the index from scratch. When rebuilding an index, new settings can be set as the old index will be deleted. Rebuilding an index is usually more effective than reorganising an index. However rebuilding an index will cost more.

Reorganising an index is always done online while rebuilding is offline (except if using SQL Server Enterprise edition). Stopping a rebuilding operation will make the operation to be rolled back while stopping reorganising operation will just stop the process and leave the done parts.

According to Microsoft guideline, if an index has
- less than 5 % fragmentation -> ignore
- between 5% to 30% fragmentation -> reorganise
- greater than 30 % fragmentation -> rebuild

How to Reorganise / Rebuild Index?
An index can be reorganised or rebuilt with Alter Index command. For example:
To see all options for the command, see this MSDN documentation.

Reorganise / Rebuild all Indexes in the Database
To do this, we can use Maintenance Plan Wizard provided by SQL Server or script.

To create a maintenance plan:
1. expand the Management folder in the target database server
2. right click Maintenance Plans folder and select Maintenance Plan Wizard

For more details about using Maintenance Plan Wizard to rebuild indexes, please see this article 'Rebuilding Indexes using the SSMS Database Maintenance Wizard'.

Otherwise we can use script to reorganise/rebuild indexes. An example of simple script to rebuild all indexes of tables and indexed views in a database (source is
DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT @tsql =
';' + 'ALTER INDEX ALL ON ' + + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
sysobjects o
INNER JOIN sysindexes i
ON =
o.xtype IN ('U','V')
FOR XML PATH('')), 1,1,'')

--PRINT @tsql         
EXEC sp_executesql @tsql

Or we can use more sophisticated script that has been used and proven by many people like Index Defrag Script.

I have Done Rebuilt but the Index Fragmentation is Still High
Fragmentation in an index of a small table may not be reduced even after reorganising or rebuilding because they may be stored on mixed extents that are shared with different objects. We might want to check as well whether by having the index is actually helping to improve query performance or not. If not then this index can be considered to be removed.

References and further reading:
Reorganize and Rebuild Indexes
Rebuild or Reorganize: SQL Server Index Maintenance