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

2 thoughts on “How Bad Are My Statistics?

  1. Hi Tom,
    If we are interested to view user tables only, then instead of using “I.object_id > 100” we can make a join to sys.objects on object_id column and check for Type = ‘U’.

    Thanks
    Pawan Tewari

Leave a Reply

Fill in your details below or click an icon to log in:

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