How To Identify And Defragment Fragmented SQL Indexes

Fragmented SQL indexes can cause of number of issues in a GlobalSearch database, including:

  1. Searching index values is slow or unresponsive.

  2. Inability to Add/Edit Fields.

  3. Opening a document is slow.

  4. Results not displaying in GlobalSearch.

The following scripts can be used to identify and defragment any SQL indexes that might be causing database slowdown. This script can be put into a batch file and run as a scheduled task, run manually or as a stored procedure.

WARNING

It is highly recommended to take backups before performing any SQL operations.  Failure to perform proper backups may result in permanent data loss.

Identifying Fragmented Indexes

Locate Fragmented Indexes Script

SELECT B.name AS TableName , C.name AS IndexName , C.fill_factor AS IndexFillFactor , D.ROWS AS RowsCount , A.avg_fragmentation_in_percent , A.page_count FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.objects B ON A.OBJECT_ID = B.OBJECT_ID INNER JOIN sys.indexes C ON B.OBJECT_ID = C.OBJECT_ID AND A.index_id = C.index_id INNER JOIN sys.partitions D ON B.OBJECT_ID = D.OBJECT_ID AND A.index_id = D.index_id WHERE C.index_id > 0 and A.avg_fragmentation_in_percent >15 ORDER BY TABLENAME

The above script will return a table-set of indexes and list their row count and average fragmentation. Any fragmented indexes with over 15% fragmentation will appear here. If no results are returned it may be because you are running the script against the wrong SQL database or that there are no indexes with fragmentation above 15%. To test this you can comment out (or remove completely) the line second from the bottom with two minus signs (--). This line is the filter to show only those indexes with fragmentation above 15%, removing it will show all of the results. To change the database that you are running the script against (in SSMS) use the dropdown in the tool bar to select the correct SQL database.

Don’t be scared if you see some high numbers – sometimes tables with only a couple hundred rows can return high fragmentation. This shouldn’t be considered too problematic. If however, you see indexes with thousands of rows coming back as highly fragmented (50% or higher) it may be worth running a defragmentation. To do that, you can  run the script below:

The following script loops through the tables using a SQL cursor to rebuild the indexes, reducing the fragmentation on affected tables.

Defragmenting Fragmented Indexes

WARNING

The following scripts may cause SQL performance degradation.  Consider running when the server is not in use.

Below are two scripts that can be used to defragment SQL indexes. After running either script, it is recommended you run the "Locate Fragmented Indexes Script" once again to see if you’ve lessened the fragmentation. Continue to do this until you see fragmentation go away entirely or until larger indexes are showing minimal fragmentation. It may never completely go away, but lessening index fragmentation can definitely help speed up a database and make things run a little smoother.

Method 1 (recommended): Rebuild or Reorganize Indexes Based on Amount Fragmented

--Specify DB, index rebuild FillFactor, and the number of passes you want to perform USE [DBName] DECLARE @FillFactor int = 80 DECLARE @MaxPasses int = 3 --Set up variables DECLARE @IndexName NVARCHAR(max), @TableName NVARCHAR(max), @FragPercent INT, @Query NVARCHAR(max), @PassCount INT = 0, @DebugMsg NVARCHAR(max) --Set up cursor DECLARE IndexCursor CURSOR FOR SELECT B.[name] AS [TableName], C.[name] AS [IndexName], A.[avg_fragmentation_in_percent] AS [FragPercent] FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.objects B ON A.OBJECT_ID = B.OBJECT_ID INNER JOIN sys.indexes C ON B.OBJECT_ID = C.OBJECT_ID AND A.index_id = C.index_id WHERE C.index_id > 0 and A.avg_fragmentation_in_percent >=10 --Perform rebuild/reorg WHILE @PassCount < @MaxPasses BEGIN SET @PassCount = @PassCount + 1 SET @DebugMsg = '' PRINT ('Pass ' + CONVERT(VARCHAR(3),@PassCount) + ' start') OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent WHILE @@FETCH_STATUS = 0 BEGIN SET @Query = 'ALTER INDEX [' + @IndexName + '] ON ' + @TableName IF @FragPercent >= 50 BEGIN SET @Query = @Query + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FillFactor) + ')' SET @DebugMsg = 'Rebuilding ' END ELSE BEGIN SET @Query = @Query + ' REORGANIZE' SET @DebugMsg = 'Reorganizing ' END PRINT(' ' + @DebugMsg + @IndexName + ' on ' + @TableName + ' that is ' + CONVERT(VARCHAR(3),@FragPercent) + '% fragmented') EXEC(@Query) FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent END CLOSE IndexCursor PRINT('Pass ' + CONVERT(VARCHAR(3),@PassCount) + ' end') END DEALLOCATE IndexCursor

Method 2: Rebuild all Indexes in DB

DECLARE @TableName VARCHAR(255) DECLARE @SQL NVARCHAR(500) DECLARE @FILLFACTOR INT SET @FILLFACTOR = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([OBJECT_ID])+'.'+name AS TableName FROM sys.tables WHERE name NOT IN ('') -- add table names ('ssAudit') that you do not want have the indexes rebuilt on. OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FILLFACTOR) + ')' EXEC (@SQL) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO