Fragmented SQL indexes can cause of number of issues in a GlobalSearch database, including:
Searching index values is slow or unresponsive.
Inability to Add/Edit Fields.
Opening a document is slow.
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.
| Infonote |
|---|
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
| Code Block | ||
|---|---|---|
| ||
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 following script loops through the tables using a SQL cursor to rebuild the indexes, reducing the fragmentation on affected tables.
...
Defragmenting Fragmented Indexes
| Note |
|---|
WARNING The following script 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
| Code Block | ||
|---|---|---|
| ||
--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
| Code Block | ||
|---|---|---|
| ||
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 |
...