可視化の方法は様々なものが使用できますが、Azure Data Studio (ADS) にはグラフを作成するための機能がありますので、この機能を利用して SQL Server の統計情報のヒストグラムを可視化する方法を紹介したいと思います。
SQL Server / SQL Database では統計情報のヒストグラム (分布状況) を確認するための方法としては次の 2 種類を使用することができます。
- sys.dm_db_stats_histogram
- SQL Server 2016 以降で利用できます。
- DBCC SHOW_STATISITCS WITH HISTOGRAM
- SQL Server 2016 より前のバージョンでも利用することが可能
SQL Server の統計情報を SSMS で確認したことがある場合は、統計情報のプロパティから次のような情報を確認したことがあるのではないでしょうか。
この情報を 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') -- テーブル名
これで取得した統計情報を元に、ヒストグラムを表示してみます。
(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 以降
このように列内の情報のレンジに応じたデータの取得を行うことができます。
それではこの情報をグラフ化してみたいと思います。
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 でグラフ化することで次のようなグラフを作成することが可能です。
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 のグラフ生成の機能はクエリの実行結果からシンプルなグラフを描画することができまずので、様々な使い方ができるのではないでしょうか。