Modification Count of Statistics

When the size of a database is large or number of rows in a table become more, the built-in auto stats update usually can’t keep the statistics up-to-date. Out dated statistics can’t serve the query optimizer to generate better query planes. Consequentially, performance gets impacted negatively. Maintaining statistcs in a database is a one of the biggests challenge for DBAs. Because they need to know when the statistics updated last time and when they should perform stats update again. However, that’s not sufficient. They also need to know how many accumulated changes have not been reflected in the stats. It’s a simple task back to SQL Server 2000. They can just query column rowmodctr in sys.sysindexes. Unfortunately, this column is broken after SQL Server version 2000. It’s no longer accurate. Even worse, SQL Server 2005 and 2008 do not expose such information at all even though they have it internally.

In SQL Server 2005 and later, an undocumented view exposed that information, sys.system_internals_partition_columns. This has been blogged on sqlskills.com. Joining this view with other DMVs can make an intuitive result.

use AdventureWorks

select	
		p.object_id ObjectID, s.stats_id StatsID, s.name StatsName, 
		sum(case when pc.partition_column_id = sc.column_id then pc.modified_count end) ModificationCount
from sys.system_internals_partition_columns pc
     inner join sys.partitions p on pc.partition_id = p.partition_id and p.index_id in(0,1)
     inner join sys.stats s on s.object_id = p.object_id 
     inner join sys.stats_columns sc on sc.object_id = s.object_id and sc.stats_id = s.stats_id 
where p.object_id in (select object_id from sys.tables)
group by p.object_id, s.stats_id, s.name



/*
ObjectID    StatsID     StatsName                                               ModificationCount
----------- ----------- ----------------------------------------------------------------------------
14623095    1           PK_Store_CustomerID                                     701
14623095    2           AK_Store_rowguid                                        701
14623095    3           IX_Store_SalesPersonID                                  701
18099105    1           PK_ProductPhoto_ProductPhotoID                          101
50099219    2           PK_ProductProductPhoto_ProductID_ProductPhotoID         1008
50099219    3           _WA_Sys_00000002_02FC7413                               504
62623266    1           PK_StoreContact_CustomerID_ContactID                    1506

*/

7 thoughts on “Modification Count of Statistics”

  1. “better query planes” – plans, not airplanes 🙂
    I noticed inserts counts as modifications on inserts for primary keys, but not for regular indexes. I did some modifications on your query to suit my interests, and I didn’t need to use a cte, but it made my modified query a little easier to read:

    ;with stats AS (SELECT OBJECT_NAME(p.object_id) ObjectNm, p.index_id StatsID, s.name StatsName, sum(pc.modified_count) ModificationCount
    FROM sys.system_internals_partition_columns pc
    INNER JOIN sys.partitions p ON pc.partition_id = p.partition_id
    INNER JOIN sys.stats s ON s.object_id = p.object_id AND s.stats_id = p.index_id
    INNER JOIN sys.stats_columns sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id AND sc.stats_column_id = pc.partition_column_id
    GROUP BY p.object_id, p.index_id, s.name)

    SELECT * FROM stats
    WHERE ObjectNm NOT LIKE ‘sys%’ AND ModificationCount != 0
    ORDER BY ObjectNm, StatsName

    Reply

Leave a Comment

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.