Track-It! Database 101: Index Health Unsatisfactory warning

Version 4
    Share This:

    I've had a few calls lately where users have received a warning from the System Health Monitor indicating that the database indexes are fragmented.  A couple of callers even reported that they received this warning only a day or so after rebuilding the indexes.  So, I thought I'd take this opportunity to cover what this warning means, and why one might see it shortly after doing database maintenance.

     

     

     

    What does the warning from the System Health Monitor actually mean?  The System Health Monitor periodically checks the database to see if any of the critical tables that we use heavily have fragmented indexes.  Just as files on a hard drive become fragmented over time, the indexes on our tables and views in the database can as well, and this will result in database performance degradation.  Our System Health Monitor runs a query, and alerts you if any indexes are showing > 20% fragmented.  20% is our default value, but you can adjust that threshold by following TIA04396.

     

    TIA04396 also has links and instructions for resolving the index fragmentation; namely, running a query to rebuild the indexes so that they are more contiguous in the file system (effectively, defragmenting them).  When you run those corrective actions, it's important to stop the Track-It! Service Management service, as several of the tables that we're trying to repair are locked open by the service.

     

     

     

    Why do I get an alert from the System Health right after I completed those actions?  Well, the query that we use was built based on Microsoft recommendations that tables that are under 1000 pages (about 8 MB) NOT get re-indexed.  Here are a couple of excerpts that discuss this:

     

    Microsoft’s best practices on SQL Server 2000 index defragmentation

    “Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”

     

    Questions about SQL Server Reindexing

    If an index is less than 1,000 pages (8 MB) then it may or may not be worth doing maintenance on it. For indexes that are very small, say only a handful of pages, these will always seem to be 'fragmented' which is a result of how SQL Server stores pages for small objects (in mixed extents which will make it look fragmented).

     

    So, if you use our reindex query, it will "defrag" any indexes over 20% fragmented, as long as they are over 1000 pages.  But any that are under 1000 pages won't get reindexed.  And they don't really need to.

     

     

     

    How do I know this is the case in my situation? We have another query you can use to check to see what should be getting reindexed.  This is a safe query, it's informational only (it doesn't make any changes) and it does not require that you get folks out of Track-It! to use.  You do need to modify the SET @db_id = DB_ID(N'TRACKIT_DATA'); line to have your actual database name.

     

     

    /* Shows the index fragmenation of tables if over 20% */

     

    DECLARE @db_id SMALLINT,

                @object_id INT,

                @table_name VARCHAR(255)

    SET @db_id = DB_ID(N'TRACKIT_DATA'); --< Change the database name as needed

    IF @db_id IS NULL

    BEGIN;

        PRINT N'Invalid database';

    END;

    ELSE

    DECLARE ScanIndexes CURSOR FOR

    SELECT object_id,name

    FROM sys.objects

    WHERE type='u'

    AND name IN ('DbChangeLog','DbChangeLogValue','Tasks','Workstat','TaskNote')

    OPEN ScanIndexes

    FETCH NEXT FROM ScanIndexes INTO @object_id,@table_name

    WHILE @@FETCH_STATUS != -1

    BEGIN

          SELECT c.name as [Table Name], b.name as [Index Name], a.avg_fragmentation_in_percent [Fragmentation %]

          FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED') a

          JOIN sys.indexes b

          on a.object_id=b.object_id

          JOIN sys.objects c

          on c.object_id=b.object_id

          and a.index_id=b.index_id

          and a.avg_fragmentation_in_percent >= 20

          and a.page_count >= 1000

          and b.name is not null

          order by a.avg_fragmentation_in_percent desc

    FETCH NEXT FROM ScanIndexes INTO @object_id,@table_name

    END

    CLOSE ScanIndexes

    DEALLOCATE ScanIndexes

     

     

    If you run that as-is, it will show you all of your tables that have indexes over 20% fragmented, as long as they are 1000 pages or larger in size.  These are tables that will benefit from following the corrective actions in TIA04396.

     

    Toward the bottom you'll see the line that looks like this:

     

      and a.page_count >= 1000

     

    You can reverse the operator in that line to:

     

    and a.page_count <= 1000

     

    and have the query show you all of the indexes that have more than 20% fragmentation but are less than 1000 pages. If anything shows up here, it means you do have indexes that are over 20% fragmented (fragmented enough to trip the Database Index Unsatisfactory alert), but they are not large enough to be of concern (too small to reindex).  This is normal on smaller databases.  Because of their smaller size, these indexes do not cause a performance penalty regardless of the level of fragmentation.

     

    If you routinely receive System Health alerts for index fragmentation, and you confirm that it's due to those smaller tables, then you might want to consider turning off the Index Fragmentation portion of the System Health monitor.