THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: More statistics info available!

 

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE()). It also provides the number of rows sampled when the statistics were last updated. This is available through DBCC SHOW_STATISTICS, and in a blog post a couple of months ago, I showed you how to get that output into a table for your own querying. One of the coolest things this new DMV shows is the row modification counter, which used to be available in sysindexes as a count of rows changed, but in SQL 2005 and later, SQL Server keeps track of changes to each column that has statistics on it. These values were not visible before, but now they are! This new object also reports any filter definition and includes rows for all statistics, whether index statistics or column statistics.

As a table valued function, sys.dm_db_stats_properties can be used with the CROSS APPLY operator to give information for all statistics in a database, or you can filter to just return  user objects and not any system objects, as shown in the code here:

SELECT
   sp.object_id, object_name(sp.object_id) as object_name,sp.stats_id, name as stats_name,
    filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.object_id > 100;

The new DMV was easy to miss because it wasn’t added until SQL Server 2008R2 Service Pack 2 and SQL Server 2012 Service Pack1. But since you should always be running with the latest service pack, you should have this great DMV if you’re any version after SQL Server 2008.

Let me know if you find this DMV useful, and what you mainly use it for.

Have fun!

~Kalen

Published Tuesday, April 09, 2013 7:39 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Logan Chen said:

Thanks for the information. I find the most useful info from it is the modification_counter which I have been looking for .... Sometimes, for the political reason, I need to justify to someone why the stats are not accurate:-(

June 13, 2013 2:17 PM
 

Kalen Delaney said:

This post is basically to answer a question asked in class this week: How can we get the last statistics

August 31, 2013 5:56 PM
 

Kalen Delaney said:

This post is basically to answer a question asked in class this week: How can we get the last statistics

August 31, 2013 5:58 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement