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: Creating New Tools for Statistics

 

I was just starting to work on a post on column statistics, using one of my favorite metadata functions: sys.dm_db_stats_properties(), when I realized something was missing.

The function requires a stats_id value, which is available from sys.stats. However, sys.stats does not show the column names that each statistics object is attached to. So how can I know which stats_id is the one I want?

So I looked at sp_helpstats, and it shows the column names, but NOT the stats_id!  I spent some time trying to figure out how to translate the stats_name into the column name, so that I could just use sys.stats. For example, if you see a stats_name of _WA_Sys_0000000C_38B96646, the 38B96646 is the HEX representation of the object_id and the 0000000C is the HEX representation of the column_id. I can find the column name with this information, but it’s not quick and easy, especially not to generalize in TSQL code.

What DID turn out to be quick and easy was to rewrite sp_helpstats so that it returns the stats_id. It turned out to be an incredibly simple change. I just added an additional column to the temp table created in the procedure, and then added that column to the INSERT into the temp table and the SELECT from the temp table to return the final results. The cursor already fetched the stats_id column to use in its work; it just didn’t return it.

Note that my script, which I am attaching, has not been thoroughly tested. It has been tested enough for my use when poking around the statistics, and using the sys.dm_db_stats_properties() function.

Perhaps some of you will find this useful. And if not, at least I’ll know where to find the script next time I need it!

~Kalen

Published Tuesday, January 27, 2015 3:56 PM by Kalen Delaney
Filed under: ,

Attachment(s): sp_helpstats2.zip

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

 

Marimuthu said:

Alternate method ( using sys.stats_columns )

SELECT

  OBJECT_NAME(sc2.object_id) AS TableName

, s.name AS StatisticsName

, s.stats_id

, s.auto_created

, ColList = STUFF(( SELECT ( ', ' + c1.name )

FROM sys.stats_columns sc1 JOIN sys.columns c1

ON sc1.object_id = c1.object_id

AND sc1.column_id = c1.column_id

WHERE sc1.object_id = sc2.object_id

AND sc1.stats_id = s.stats_id

ORDER BY

sc1.stats_id,

sc1.stats_column_id,

c1.name

FOR XML PATH( '' )),1,1,'')

FROM sys.stats_columns sc2

JOIN sys.columns c2

ON sc2.object_id = c2.object_id

AND sc2.column_id = c2.column_id

JOIN sys.stats s

ON sc2.object_id = s.object_id

AND sc2.stats_id = s.stats_id

--WHERE sc2.object_id = object_id('TableName') --substitute Tablename

WHERE OBJECTPROPERTY(sc2.object_id, N'IsUserTable')=1

AND SC2.stats_column_id >1

GROUP BY  

sc2.object_id

  , s.name

  , s.stats_id

  , s.auto_created

ORDER BY s.stats_id

January 30, 2015 8:31 PM
 

Kalen Delaney said:

Thanks, Marimuthu, but this doesn't work at all for my table. My sp_helpstats2 shows 2 column statistics rows, but your query returns no rows.

January 30, 2015 11:44 PM
 

tim cronin said:

got an error creating this sp on a sql 2008r2 server, is there a version requirement?

Msg 448, Level 16, State 2, Procedure sp_helpstats2, Line 82

Invalid collation 'catalog_default'.

Msg 448, Level 16, State 2, Procedure sp_helpstats2, Line 83

Invalid collation 'catalog_default'.

sp_MS_marksystemobject: Invalid object name 'sp_helpstats2'

February 5, 2015 1:58 PM
 

Kalen Delaney said:

Tim... I haven't tested on SQL2008 or R2, just 2012 and 2014. However, your error indicates an invalid collation called 'catalog_default'. I had actually never heard of this, so I looked it up in the documentation and saw it was added to help manage contained databases in SQL 2012. So you should just be able to remove the clause 'collate catalog_default' that appears twice in the temp table creation.

I hope this helps.

Kalen

February 6, 2015 3:26 AM
 

jchang said:

its too bad we cannot create system views, I think this would be helpful as a view that could be worked into other queries.

I put out a partial decode of stats_stream at

http://www.qdpma.com/CBO/StatStream.html

It would be nice if MS made the stats_stream binary available via a dmv/f instead of DBCC. We could make a function, and have the density vector or histogram query-able.

In principle we could create arbitrary distribution statistics by generating our own stats_stream binary, but unfortunately MS decided to put a checksum entity since 2005. Perhaps they could be persuaded to have hypothetical indexes ignore the checksum.

February 16, 2015 9:43 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement