SQL Server Maintenance: The Overlooked Index

Quite a lot has been written about SQL Server index maintenance. One area that doesn't get much attention is full text index maintenance. Full text indexes can suffer from fragmentation, which can lead to degraded performance. Let's look at one way to address this.

Full-Text Index Structure

As the MSDN documentation tells us, a logical full text index consists of one or more internal tables called fragments. When data from the source table is updated, additional fragments are created. Internally, full text queries need to "read" all of the fragments. As a result, too many fragments can lead to degraded performance.

The query below will identify full text catalogs with fragmented full text indexes. It returns the name of the full text catalog, the schema and table name a full text index is built on, and the number of fragments in the full text index.

SELECT c.Name FullTextCatalogName, OBJECT_SCHEMA_NAME(i.object_id) SchemaName,
 OBJECT_NAME(i.object_id) TableName, COUNT(*) FragmentCount
FROM sys.fulltext_catalogs c
JOIN  sys.fulltext_indexes i
 ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN sys.fulltext_index_fragments f
 ON f.table_id = i.object_id 
GROUP BY c.Name, i.object_id, f.table_id 
HAVING COUNT(*) > 1

To improve performance, the multiple fragments can be "merged" into a single fragment. This can be done via ALTER FULLTEXT CATALOG with the REORGANIZE option. The script below creates a stored procedure that iterates over non-system databases and runs some dynamic tsql. Full text catalogs that have at least one full text index with multiple fragments are reorganized.

CREATE PROCEDURE dbo.ReorganizeFullTextCatalogs
AS 
/*
    Purpose: 
    Reorganizes the FullText Catalogs (as needed) on all user databases.
 
    Inputs: None

    History:
    02/25/2014 DMason Created
    http://DaveMason.me/
 
*/
DECLARE @MajorVersion INT

SELECT @MajorVersion = 
 CAST(LEFT(d.ProductVersion, CHARINDEX('.', d.ProductVersion) - 1) AS INT)
FROM ( SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) AS ProductVersion ) d

--SQL 2008 or later.
IF @MajorVersion < 10
    RETURN;

--This is the tsql statement that gets executed on each db.
DECLARE @InnerSql NVARCHAR(MAX) 
SET @InnerSql =
    'DECLARE @Tsql NVARCHAR(MAX)
    DECLARE @FtcName SYSNAME
    DECLARE curFtcName CURSOR FAST_FORWARD READ_ONLY FOR
        SELECT DISTINCT FullTextCatalogName
        FROM (
            SELECT c.Name FullTextCatalogName, COUNT(*) FragmentCount
            FROM sys.fulltext_catalogs c
            JOIN  sys.fulltext_indexes i
                ON i.fulltext_catalog_id = c.fulltext_catalog_id
            JOIN sys.fulltext_index_fragments f
                ON f.table_id = i.object_id 
            GROUP BY c.Name, f.table_id 
            HAVING COUNT(*) > 1
        ) TableIndexFragments

    OPEN curFtcName
    FETCH NEXT FROM curFtcName INTO @FtcName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Tsql = ''''ALTER FULLTEXT CATALOG ['''' + @FtcName + ''''] REORGANIZE''''
        PRINT @Tsql
        EXEC (@Tsql)
        FETCH NEXT FROM curFtcName INTO @FtcName
    END

    CLOSE curFtcName
    DEALLOCATE curFtcName'
-------------------------------------------------------
--Iterate through the db's.
DECLARE @Tsql NVARCHAR(MAX) 
DECLARE @DB SYSNAME
DECLARE curDB CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT name
    FROM sys.databases
    WHERE User_Access_Desc = 'MULTI_USER'
    AND State_Desc = 'ONLINE'
    AND Is_Read_Only = 0
    AND Is_In_Standby = 0
    AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
    ORDER BY name

OPEN curDB
FETCH NEXT FROM curDB INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Tsql = '[' + @DB + ']..sp_executesql N''' + @InnerSql + ''''
    EXEC sp_executesql @Tsql;

    FETCH NEXT FROM curDB INTO @DB
END

CLOSE curDB
DEALLOCATE curDB
GO

Personal Experience

My first encounter with full text indexes and degraded performance was related to an enhancement I made to an aspx page years ago. I wanted all of the search fields to use an AutoComplete AJAX extender to mimic the behavior you see when you type a few letters into the search field on Google.com or Bing.com. A traditional non-clustered index wasn't sufficient for the "Location Address" field, so I settled on a full text index--it worked very well.

After some amount of time (I don't remember how long), performance slowed considerably. I was surprised to find the full text index for "Location Address" had a large number of fragments. I wish I had kept some notes on my findings. I can't remember how may fragments there were, but I'm thinking it was in the 15-20 range. If memory serves me, Orange Co., FL has about 400,000 physical location addresses. The underlying table had one row per location address. Knowing me, the indexed column was probably VARCHAR(100) or VARCHAR(128). This does't seem like a huge amount of data, so I was surprised the full text searches were slow, even with 15-20 fragments. Reorganizing the related full text catalog made a world of difference. Performance improved drastically.

Questions I Haven't Answered Yet

A full text catalog can contain multiple full text indexes. Is there a way to reorganize just a single full text index? Does SQL Server automatically reorganize a full text index that has only one fragment, even though it's not necessary? (Perhaps another blog post is in order.)

Other Thoughts

The set of data I was working with was small. I could reorganize the full text catalog quickly. If you are working with considerably larger sets of data, proceed with caution. If you want to run the stored proc above, start out in a test environment to see how long it takes. In my production environments, I have a maintenance window on the weekends when I run the code. I don't bother during the week.