SQL Server – Statistics – Identify Out of Date Statistics

Background

This is a little script that is way overdue.

When SQL Server performance issues arises, there are litany of Index questions that come up.  Are Indexes defragmented, are stats stale, and so on.

 

Investigate Stale Statistics

 SQL Script


; with cteIndexPartition
as
(

    select
              tblSI.object_id
            , tblSI.index_id
            , indexName = tblSI.name
            , NumberofRecords = sum(tblSP.rows)

    from sys.indexes tblSI

            inner join sys.partitions tblSP

                on tblSI.object_id = tblSP.object_id
                and tblSI.index_id = tblSP.index_id

    --Skip Heaps
    where tblSI.index_id > 0

    group by
                tblSI.object_id
              , tblSI.index_id
              , tblSI.name

)

, cteStatisticsColumns
as
(

    select
              tblS.object_id

            , tblS.stats_id

            , STUFF(
                     (
                       select ',' + tblC.name

                      from   sys.stats_columns tblSC
                
                             inner join sys.columns tblC 

                               ON  tblc.[object_id] = tblSC.[object_id] 
                               AND tblC.column_id = tblSC.column_id

                    where tblS.[object_id] = tblSC.[object_id] 

                    AND   tblS.stats_id = tblSC.stats_id

                    FOR XML PATH(''))

                   ,1,1,''
                ) as [columnList]

    from   sys.stats tblS
                
    where  OBJECTPROPERTY(tblS.OBJECT_ID,'IsUserTable') = 1

)
select 

          [schema] = schema_name(tblSO.schema_id)
        , [object] = tblSO.name
        , [stat] = tblS.name
        , [statColumns] = max(cteSC.[columnList])
        , [index] = cteIP.indexname

        , user_created = max(cast(tblS.user_created as tinyint))
        , auto_created = max(cast(tblS.auto_created as tinyint))
        , no_recompute = max(cast(tblS.no_recompute as tinyint))

        , lastUpdated = convert(varchar(30), max(dmvSP.last_updated), 101)

        , [NumberofDays] = datediff(day, max(dmvSP.last_updated), getdate())

        , numberofRecordsInStats = max(dmvSP.[rows])

        , [numberofRecords] = sum(cteIP.NumberofRecords)

        , numberofRecordsModified = max(dmvSP.modification_counter)

        , case
                when (max(dmvSP.[rows]) = 0) then 0
                else cast(max( (dmvSP.modification_counter) * 100.00)
                         / max(dmvSP.[rows]) as decimal(30,2))
          end as [modification%]  

from   sys.objects tblSO

        inner join sys.stats tblS

            on  tblSO.object_id = tblS.object_id

        inner join cteStatisticsColumns cteSC

            on   tblS.object_id = cteSC.object_id
            and  tblS.stats_id = cteSC.stats_id

        left outer join cteIndexPartition cteIP

            on tblS.object_id = cteIP.object_id
            and tblS.stats_id = cteIP.index_id

        CROSS APPLY sys.dm_db_stats_properties
                        (
                              tblS.object_id
                            , tblS.stats_id
                        ) AS dmvSP

where tblSO.[type] = 'U' 

group by
          schema_name(tblSO.schema_id)
        , tblSO.name
        , cteIP.indexname
        , tblS.name

having
        max(dmvSP.modification_counter) > 0

order by 
          max(cast(dmvSP.last_updated as date) ) asc
        , case
                when (max(dmvSP.[rows]) = 0) then 0
                else cast( (max(dmvSP.modification_counter) * 100.00)
                             / max(dmvSP.[rows]) as decimal(30,2))
          end desc

 

Output:

StaleStatistics

 

Columns

 

Column Use More
 Schema Schema name  Schema
 Object Object name  Object
 Index Index name  Index
User Created Was stats created by the user? Create statistics
Auto Created  Was stats auto created by the SQL Server Instance
 No Recompute  Index or Statistics created to not be refreshed by automatically by system ALTER INDEX ALL ON [schema-name].[table-name]
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);Create statistics with norecompute
 Last Updated  Statistics was last updated on the recorded date and time  sys.dm_db_stats_properties.last_update
 Number of Days Number of days since last update  datediff(day,  sys.dm_db_stats_properties.last_update, getdate())
Number of records Number of records in all table’s partition  sys.partitions.rows
 NumberofRecordsInStats Number of records recorded in DMV  sys.dm_db_stats_properties.rows
 numberofRecordsModified Number of records modified in DMV  sys.dm_db_stats_properties.modification_counter
 modification% Modification / Number of records  Number of changes since last Statistics update

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s