Geek City: Accessing Distribution Statistics

re: Geek City: Accessing Distribution Statistics

sfibich — Tue, 22 Jan 2013 15:14:25 GMT

I’m guessing you already know but SQL 2008 R2 introduces a new DMV sys.dm_db_stats_properties that has almost all of the same properties as the DBCC show_statistics WITH STAT_HEADER returns. The DMV is missing the density which I believe is no longer used by the optimizer, string index, average key length, and filtered expression. The filtered expression column can be picked up from sys.stats filter definition column. It’s not a perfect fit but if you can do with the average key length and the string index flag it’s a nice alternative. You can also pull in the average record length for the 0 level index of a statistic if it has one from the sys.dm_db_index_physical_stats DMV using the DETAILED setting. It’s not exactly the same thing but it may give some insight to the statistic. This combination has been getting me through an high level look at statistics for a database when I’m having performance problems.

I like your solution as it solves the issue for getting some of the more detailed statistics information.

something like...

select

ddsp.*

,ss.filter_definition

,ss.no_recompute

from

sys.dm_db_stats_properties (object_id('FactInternetSales'), 2) as ddsp

inner join

sys.stats ss

ddsp.object_id =ss.object_id

and

ddsp.stats_id =ss.stats_id

re: Geek City: Accessing Distribution Statistics

Kalen Delaney — Tue, 22 Jan 2013 18:08:21 GMT

Thanks sfibich... I actually just found out about the new DMV when this issue of programmatically accessing stats info was being discussed on a private forum. Someone asked why we couldn't use sys.dm_db_stats_properties, and that was the first time I saw it! But as you say, it doesn't have everything.

Thanks again

Kalen

Geek City: More statistics info available!

Kalen Delaney — Wed, 10 Apr 2013 01:39:35 GMT

  I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives

Geek City: More statistics info available!

Kalen Delaney — Wed, 10 Apr 2013 01:40:27 GMT

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more