SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure Data Studio を使用した統計情報のヒストグラムの可視化

without comments

可視化の方法は様々なものが使用できますが、Azure Data Studio (ADS) にはグラフを作成するための機能がありますので、この機能を利用して SQL Server の統計情報のヒストグラムを可視化する方法を紹介したいと思います。

SQL Server / SQL Database では統計情報のヒストグラム (分布状況) を確認するための方法としては次の 2 種類を使用することができます。

SQL Server の統計情報を SSMS で確認したことがある場合は、統計情報のプロパティから次のような情報を確認したことがあるのではないでしょうか。

image

この情報を T-SQL で取得するための方法が上述の 2 種類となります。

ここから、インデックスの先頭列のデータ分布が表された「インデックス統計」や、テーブル各列に個別に作成された「列統計」のヒストグラムを確認することができます。

T-SQL で取得した情報でも、どのようなデータ分布になっているかを確認することができますが、棒グラフ等で可視化することで一目で突出している値がわかるのではないでしょうか。

統計情報は次のようなクエリで取得できます。

use tpch;
select OBJECT_NAME(object_id) AS object_name, name, stats_id 
from sys.stats where object_id = OBJECT_ID('LINEITEM') -- テーブル名

 

image

これで取得した統計情報を元に、ヒストグラムを表示してみます。

(SQL Server の統計情報のヒストグラムは最大 200 ステップまでとなっていたかと)

DBCC SHOW_STATISTICS (LINEITEM, NCIX_L_COMMENT) WITH HISTOGRAM
select * from sys.dm_db_stats_histogram(object_id('LINEITEM'), 13) -- SQL Server 2016 以降

image

このように列内の情報のレンジに応じたデータの取得を行うことができます。

それではこの情報をグラフ化してみたいと思います。

SQL Server 2016 以降であれば、統計情報を取得するための DMV を利用して次のような動的 PIVOT (Dynamic PIVOT) を実行することができます。

use tpch;
DECLARE @table_name varchar(255) = 'LINEITEM'
DECLARE @stats_id varchar(5) = 13

DECLARE @pvt varchar(max) = (
    SELECT STUFF(C1,1, 2,'') + ']'
    FROM
    (
    SELECT 
        DISTINCT 
        '],[' + REPLACE(REPLACE(CAST(range_high_key AS varchar(1000)), CHAR(10),''), CHAR(13), '')
    from 
        sys.dm_db_stats_histogram(object_id(@table_name), CAST(@stats_id AS int)) 
    FOR XML PATH('')
    ) AS T(C1)
)

DECLARE @sql nvarchar(max) = N'
SELECT
    *
FROM
(
    select 
        REPLACE(REPLACE(CAST(range_high_key AS varchar(1000)), CHAR(10),''''), CHAR(13), '''') AS range_high_key, 
        range_rows
    from 
        sys.dm_db_stats_histogram(object_id(''' + @table_name + '''),' +  @stats_id + ') 
) AS T
PIVOT(
    MAX(range_rows)
    FOR range_high_key IN(' + @pvt + ')
) AS PVT
'

EXECUTE (@sql)

 

この実行結果を ADS でグラフ化することで次のようなグラフを作成することが可能です。

image

DBCC コマンドで実施する場合は次のようなクエリでしょうか。

use tpch;
DECLARE @table_name varchar(255) = 'LINEITEM'
DECLARE @stats_name varchar(255) = 'NCIX_L_COMMENT'

DECLARE @table TABLE(
    range_high_key sql_variant,
    range_rows real,
    equal_rows real,
    distinct_range_rows bigint,
    average_range_rows real
)

INSERT INTO @table EXECUTE('DBCC SHOW_STATISTICS (' + @table_name + ', ' + @stats_name + ') WITH HISTOGRAM')


DECLARE @pvt varchar(max) = (
    SELECT STUFF(C1,1, 2,'') + ']'
    FROM
    (
    SELECT 
        DISTINCT 
        '],[' + REPLACE(REPLACE(CAST(range_high_key AS varchar(1000)), CHAR(10),''), CHAR(13), '')
    from 
        @table
    FOR XML PATH('')
    ) AS T(C1)
)

DECLARE @sql nvarchar(max) = N'
DECLARE @table TABLE(
    range_high_key sql_variant,
    range_rows real,
    equal_rows real,
    distinct_range_rows bigint,
    average_range_rows real
)

INSERT INTO @table EXECUTE(''DBCC SHOW_STATISTICS (' + @table_name + ', ' + @stats_name + ') WITH HISTOGRAM'')

SELECT
    *
FROM
(
    select 
        REPLACE(REPLACE(CAST(range_high_key AS varchar(1000)), CHAR(10),''''), CHAR(13), '''') AS range_high_key, 
        range_rows
    from 
        @table
) AS T
PIVOT(
    MAX(range_rows)
    FOR range_high_key IN(' + @pvt + ')
) AS PVT
'
EXECUTE (@sql)

 

ADS のグラフ生成の機能はクエリの実行結果からシンプルなグラフを描画することができまずので、様々な使い方ができるのではないでしょうか。

Written by Masayuki.Ozawa

4月 30th, 2020 at 11:20 pm

Leave a Reply