SQL Server vNext: sys.dm_db_stats_histogram

Since installing SQL Server vNext CTP 1.3 I found out that there is a new way to return statistics histogram for a specific stat which actually is also available with the latest Cumulative Update for SQL Server 2016 Service Pack 1.

Classically you would run the following:

 DBCC SHOW_STATISTICS ("Sales.Customer", AK_Customer_AccountNumber) WITH HISTOGRAM

stats

Output above is exactly what we expect.

Now you have access to  sys.dm_db_stats_histogram.  The result for sys.dm_db_stats_histogram returns information similar to DBCC SHOW_STATISTICS WITH HISTROGRAM where  you have the capability to join onto other system tables and even using predicates to build queries to get specific information.

What is returned is highlighted below.

tables1.JPG

By default you would do something similar to the following where you would pass in the object ID and stats ID:

SELECT * FROM sys.dm_db_stats_histogram(997578592, 3)

hist

Maybe you want to see the first 10 steps only?

 SELECT * FROM sys.dm_db_stats_histogram(997578592, 3)
WHERE step_number BETWEEN 1 AND 10

steps

You could “pad” this out further joining onto other tables.

SELECT sc.name AS [Schema Name],o.name AS [Table Name],s.name AS [Stat Name],o.object_id,
s.stats_id,hist.step_number, hist.range_high_key, hist.range_rows, hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s JOIN sys.objects AS O ON s.object_id = o.object_id
JOIN sys.schemas sc ON o.schema_id = sc.schema_id
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE  s.[name] = N'AK_Customer_AccountNumber';

hist2

Just don’t go “over the top” with the queries…like me!

Expression: PbValGetConst()[CSsVariant::OFF_VER] == CSsVariant::CURR_VER
SPID: 60
Process ID: 5108
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.

 

One thought on “SQL Server vNext: sys.dm_db_stats_histogram

  1. Pingback: SQL Server vNext: sys.dm_db_stats_histogram - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s