How Bad Are My Statistics?

Here’s a quick query that tells you how many rows have been changed (table & index) since the last statistics update.

Select     OBJECT_NAME(I.id)            As TableName

    ,Sum(Convert(BigInt,I.rowmodctr))    As RowsModSinceStats

    ,C.RowCnt

From     sys.sysindexes                As I

    Left
Outer
Join

    (    Select     OBJECT_NAME(PS.object_id)    As TableName

            ,SUM(PS.row_count)            As RowCnt

        From     sys.dm_db_partition_stats        As PS

            INNER
JOIN

            sys.indexes                As I

                ON I.object_id
= PS.object_id

                AND I.index_id = PS.index_id

        Where I.type_desc IN
(‘CLUSTERED’,‘HEAP’)

            AND I.object_id
> 100

            AND
OBJECT_SCHEMA_NAME(PS.object_id)
<>
‘sys’

        Group
By PS.object_id)    As C

        On
OBJECT_NAME(I.id)
= C.TableName            

Where
Coalesce(C.RowCnt,0)
> 0

Group
By
OBJECT_NAME(I.id)    

    ,C.RowCnt

Order
By TableName

    –RowsModSinceStats Desc

Advertisements