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, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent 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:
ALTER INDEX IX_MyTable_IndexName ON MyTable REORGANIZE; ALTER INDEX IX_MyTable_IndexName ON MyTable REBUILD;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 http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/06/20/rebuild-all-the-indexes-of-a-sql-database-in-one-go/):
DECLARE @tsql NVARCHAR(MAX)
DECLARE @fillfactor INT
SET @fillfactor = 90
SELECT @tsql =
STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
FROM
sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE
o.xtype IN ('U','V')
AND i.name IS NOT NULL
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
