• This site is read-only while we complete some maintenance. You can't create or edit pages right now.
Skip to end of metadata
Go to start of metadata

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 Index Script

Locate Fragmented Indexes
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. 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.

WARNING

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

Defragment Indexes

Fix Fragmented Indexes
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
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

After running this script, it is recommended you run the "Identifying Fragmented Index 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.