SQL server 2012 sp1 – sys.dm_db_stats_properties

It’s been a while since my last blog post, due to a terrible cold that have forced me to bed, but now it’s time for another blog post!

In SQL server 2012 service pack 1 there is a new function that will give you more details about the statistics. As you probably already know, the statistics are very important for the performance in SQL server. It’s used by the optimizer to create an efficient execution plan. The new function can tell you if the statistics needs to be updated or if the sample rate is low.

You can use code like this:

SELECT
	objectName		= obj.name,
	statsName		= [stat].[name],
	statsColumns	= colList.[NAMES],
	[stat].[auto_created],
	[stat].[user_created],
	[stat].[no_recompute],
	[stat].[has_filter],
	[stat].[filter_definition],
	[stat].[is_temporary],
	[sp].[last_updated],
	[sp].[rows],
	[sp].[rows_sampled],
	[sp].[steps],
	[sp].[unfiltered_rows],
	[sp].[modification_counter],
	sampleRate = 1.0 * sp.[rows_sampled] / sp.[rows]
FROM sys.objects AS obj
INNER JOIN sys.stats stat
	 ON stat.object_id = obj.object_id
CROSS APPLY(
	SELECT STUFF(col.NAME,1,1,'') 
	FROM (
		SELECT ',' + c.[name] 
		FROM sys.[stats_columns] AS sc 
		INNER JOIN sys.[columns] AS c
			ON [sc].[column_id] = .[column_id]
			AND [sc].[object_id] = .[object_id]
		WHERE 
			sc.[object_id] = stat.[object_id]
			AND sc.[stats_id] = stat.[stats_id]
		FOR XML PATH('')
		) AS col(NAME)
	) AS colList(NAMES)
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE
	obj.[is_ms_shipped] = 0;

If you missed my previous blog posts about statistics, you can find them here:

http://www.sqlservice.se/sv/start/blogg/sql-server-performance–death-by-bad-statistics.aspx 
http://www.sqlservice.se/sv/start/blogg/sql-server-statistics–local-variables.aspx
http://www.sqlservice.se/sv/start/blogg/sql-server-statistics–memory-request.aspx
http://www.sqlservice.se/sv/start/blogg/sql-server-statistics–read-only-database.aspx
http://www.sqlservice.se/sv/start/blogg/sql-server-statistics–table-variables.aspx
http://www.sqlservice.se/sv/start/blogg/sql-server-statistics–multicolumn-statistics.aspx

Feel free to contact any of other SQL server consultants if you wants to know more about SQL server and statistics.

Regards

/Håkan Winther