What about my indexes?

No need to say that indexes on tables are important, as they directly affect the performance of your queries and data applications.

Therefor it is also needless to say that maintenance of indexes is as important as a good indexing strategy. About indexing strategies, there are already written thousands and thousands of pages, and it is for this reason that I want t spend a few lines on index fragmentation and usage. So,what about my indexes, how much are they fragmented, are they used at all?

First, a script is shown below, which will give you a result set, showing the indexes and their usage statistics:  

SELECT  TableName = t.name,

                 [RowCount] = si.rows ,

                 IndexName  = ISNULL(i.name, i.type_desc),

                 IndexType  = ISNULL(i.type_desc, ''),

                 IsFilteredIndex = CASE WHEN i.has_filter != 0 THEN 'True' ELSE 'False' END,

                 Filter  = ISNULL(i.filter_definition , ''),

                 [FillFactor] = i.fill_factor,

                 [IndexDepth] = s.index_depth,

                 [FragmentationPercentage] =  ISNULL(CAST(s.avg_fragmentation_in_percent AS DECIMAL(10,2)),0),

                 [FragmentCount] = ISNULL(s.fragment_count,0),

                 [FragmentationSizeInPages] = ISNULL(CAST(s.avg_fragment_size_in_pages AS DECIMAL(10,2)),0),

                 [PageCount] = ISNULL(s.page_count,0),

                 UserSeeks = ISNULL(us.user_seeks,0),

                 UserScans = ISNULL(us.user_scans,0),

                 UserLookups = ISNULL(us.user_lookups,0),

                 UserUpdates = ISNULL(us.user_updates,0),

                 LastUserSeek = us.last_user_seek,

                 LastUserScan = us.last_user_scan,

                 LastUserLookup = us.last_user_lookup,

                 LastUserUpdate = us.last_user_update      

FROM    sys.tables t

INNER JOIN sysindexes si  ON si.id = t.object_id

INNER JOIN sys.indexes i  ON i.object_id=  t.object_id

LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) s

                                                            ON s.object_id = i.object_id

                                                            AND s.index_id = i.index_id

LEFT JOIN sys.dm_db_index_usage_stats us

                                                            ON us.index_id = i.index_id

                                                            AND us.object_id = i.object_id

                                                            AND us.database_id = DB_ID()

WHERE    t.name NOT LIKE '%_tracking'

         AND t.name NOT IN  ('anchor' ,'scope_config' , 'scope_info', 'schema_info')

         AND      si.name LIKE 'PK_%'

         AND i.type_desc NOT LIKE 'HEAP'

ORDER BY t.name ASC, i.name ASC

Secondly, the following script will generate a result set, showing you the index fragmentation:

SELECT  DISTINCT

               SchemaName = s.name,

               TableName = t.name ,

               IndexName = i.Name,

               Fragmentation = ps.avg_fragmentation_in_percent

FROM    sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) ps

INNER JOIN sys.tables t ON t.object_id = ps.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN sys.indexes  i ON i.index_id = ps.index_id

                                               AND i.object_id = t.object_id

WHERE   ps.avg_fragmentation_in_percent > 5

        AND     t.type = 'U' AND i.type <> 0

ORDER BY t.name;

Thirdly, a script will generate T-SQL code, which will allow you to either rebuild or reorganize, based on a 30% fragmentation ration to rebuild. This means that every index which is fragmented more than 30% should be rebuild, all others should be reorganized.

DECLARE @SQL NVARCHAR(2000);

DECLARE  @SchemaName NVARCHAR(10),

                 @TableName NVARCHAR(255),

                 @IndexAction NVARCHAR(20)

DECLARE IndexingCursor CURSOR FOR

SELECT   DISTINCT

                 SchemaName = s.name,

                 TableName = t.name ,

                 IndexAction = CASE        WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD'

                                                            WHEN ps.avg_fragmentation_in_percent <= 30 THEN 'REORGANIZE'

                                             END

FROM    sys.dm_db_index_physical_stats(DB_ID(),0,-1,0, null) ps

INNER JOIN sys.tables t ON t.object_id = ps.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN sys.indexes  i ON i.index_id = ps.index_id

                                                     AND i.object_id = t.object_id

WHERE    ps.avg_fragmentation_in_percent > 5

         AND      t.type = 'U' AND i.type <> 0

ORDER BY t.name;

OPEN IndexingCursor;

FETCH NEXT FROM IndexingCursor

INTO @SchemaName, @TableName, @IndexAction;

WHILE @@FETCH_STATUS = 0

BEGIN

         PRINT  '/* Index maintenance for [' + DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '] */'

         PRINT 'ALTER INDEX ALL ON [' + DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '] ' + @IndexAction + ';'

         PRINT 'UPDATE STATISTICS [' + DB_NAME() +'].[' + @SchemaName + '].[' + @TableName + '];'

        

         FETCH NEXT FROM IndexingCursor

         INTO @SchemaName, @TableName, @IndexAction;

END

CLOSE IndexingCursor

DEALLOCATE IndexingCursor

Have fun!

Jurgen