Section => Index_and_Stats_checks - Sub-Section => Statistics_to_update => sys.dm_db_stats_properties

[DanielAdeniji]

Section => Index_and_Stats_checks - Sub-Section => Statistics_to_update

Uses Dynamic Management Views => sys.dm_db_stats_properties

Disabled Indexes can be included and when they are included they are not properly cited.

To excluded disabled indexes, one needs to include sys.indexes and filter on sys.indexes.is_disabled = 0

[DanielAdeniji]

When populating #tblStatsUpd and relying on sys.dm_db_stats_properties, here is the SQL that includes is_disabled:-

                SET @sqlcmd 
                    = 'USE ' + QUOTENAME(@dbname) + ';'
                        + ' SELECT DISTINCT db_name() AS [DatabaseName], db_id() AS [databaseID] 
                                , mst.[object_id] AS objectID, t.name AS schemaName, OBJECT_NAME(mst.[object_id]) AS tableName
                                , sp.last_updated, rows = NULLIF(sp.[rows], 0), sp.modification_counter
                                , ss.[stats_id], ss.name AS [stat_name]
                                , ss.auto_created, ss.user_created
                                , ss.has_filter, ss.filter_definition, sp.unfiltered_rows, sp.steps
                                , si.is_disabled

                            FROM sys.objects AS o

                            INNER JOIN sys.tables AS mst 
                                ON mst.[object_id] = o.[object_id]

                            INNER JOIN sys.schemas AS t 
                                ON t.[schema_id] = mst.[schema_id]

                            INNER JOIN sys.stats AS ss 
                                ON ss.[object_id] = mst.[object_id]

                            CROSS APPLY sys.dm_db_stats_properties
                                            (
                                                  ss.[object_id]
                                                , ss.[stats_id]
                                            ) AS sp

                            LEFT OUTER JOIN sys.indexes AS si 
                                ON  ss.[object_id] = si.[object_id]
                                AND ss.[stats_id] = si.[index_id]

                            WHERE 
                            (
                                    ( sp.[rows] > 0)
       
                                or ( sp.rows =0)
                            )   
                            AND 
                            (
                                    (sp.[rows] <= 500 AND sp.modification_counter >= 500)

                                OR (sp.[rows] > 500 AND sp.modification_counter >= (500 + sp.[rows] * 0.20))
                            )
                        '