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
*/
“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
Oops, Typo. that’s a feature of this blog. 🙂
A blog that produces typos for you! Now THAT’s a feature! 🙂
Man, now I’m correcting myself! 🙂 “on inserts for primary keys” –> “on primary keys”
You might need to CHECKPOINT in order to see the changes as what’s on Paul’s blog.
Note that I didn’t sissy-fy my quotes, the commenting software did that to me. You will have to fix it after you copy it.
Wow, this blog DOES produce typos!