日曜日のお昼から仕事しようと思ったけど、何か気が乗らないのでネットみてたら、Cumulative Update 2 for SQL Server 2016 SP1 がリリースされていたのでちょっとお試ししてみる - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　sys.dm_db_stats_histogram が DMV に追加されたようです。
ryuchan.hatenablog.com

　SQL Server 2016 SP1 CU2 の環境で下記のクエリを実行してみましょう。

SELECT
    s.*,
    sh.*
FROM 
    sys.stats s
    CROSS APPLY sys.dm_db_stats_histogram(s.object_id,s.stats_id) sh

f:id:koogucc11:20170326142506p:plain

f:id:koogucc11:20170326142515p:plain

　何かいいサンプル作りたいなぁと思いましたが、面倒なので ( 子供の面倒と仕事がある... ) MSSQL Tiger Team のブログに非常に良いサンプルがありました。早速見てみましょう。
blogs.msdn.microsoft.com

　はい、本当にそう思います。まわりにそんな人いたらいいんですが....

Statistics being the building blocks on which the Query Optimizer reasons to compile a good enough plan to resolve queries, it’s very common that anyone doing query performance troubleshooting needs to use DBCC SHOW_STATISTICS to understand how statistics are being used, and how accurately they represent data distribution.

　それを見ようとする人もなかなかいませんが...

Let’s say you wanted to understand where the estimation came from, then naturally you will want to look for whatever stats object references that single column (using a single column predicate for simplicity sake). If you need to programmatically access this data, then usually you would dump DBCC SHOW_STATISTICS … WITH HISTOGRAM to a table, and then use it from there. That is not ideal.

　そして、今回 CU2 で dm_db_stats_histogram が追加されました。

With the latest release of SQL Server 2016 SP1 CU2, we added a new Dynamic Management Function (DMF) sys.dm_db_stats_histogram, which is similar to running the above DBCC statement. Note: this DMF is also available in SQL Server vNext CTP 1.3.

　DBCC SHOW_STATISTICS … WITH STATS_HEADER を sys.dm_db_stats_properties で参照することができます。

This further completes the story we started with sys.dm_db_stats_properties, which has a similar output to running DBCC SHOW_STATISTICS … WITH STATS_HEADER.

　ここが肝です。指定された述語から統計情報を用いて算出された推定行数を求めることができます。

But to make it more interesting, here’s one example on how you can leverage these DMFs inline, to get information on which stat and respective histogram steps cover my predicate, in the scope of my table and column:

SELECT
    ss.name,
    ss.stats_id,
    shr.steps,
    shr.rows,
    shr.rows_sampled,
    shr.modification_counter,
    shr.last_updated,
    SUM(sh.range_rows + sh.equal_rows) AS predicate_step_rows
FROM
    sys.stats ss
    INNER JOIN
        sys.stats_columns sc
    ON  ss.stats_id = sc.stats_id
    AND ss.object_id = sc.object_id
    INNER JOIN
        sys.all_columns ac
    ON  ac.column_id = sc.column_id
    AND ac.object_id = sc.object_id CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE
    ss.[object_id] = OBJECT_ID('FactResellerSales')
AND ac.name = 'OrderDate'
AND sh.range_high_key BETWEEN CAST('20110101' AS DATE) AND CAST('20110606' AS DATE)
GROUP BY
    ss.name,
    ss.stats_id,
    shr.steps,
    shr.rows,
    shr.rows_sampled,
    shr.modification_counter,
    shr.last_updated