【SQL Server】パフォーマンス問題を解決するための統計情報の理解

ニュース＆ブログ

【SQL Server】パフォーマンス問題を解決するための統計情報の理解

投稿日：2025/01/29

はじめに

　先日、お客様からSQL Serverのパフォーマンス問題について問い合わせをいただきました。

　調査を進めていく中で統計情報が関わっており、再度理解を深めるために調査・検証を行いましたので本記事にて紹介したいと思います。

発生していた問題と原因

　パフォーマンス問題の内容としては、今まで数秒で返ってきたクエリが数分待たないと返ってこないというものです。また、絞り込み条件の値を変えると問題ないことが確認できたため、特定の値を絞り込み条件に指定すると発生する事象のようでした。
　クエリの実行計画や動的管理ビュー（DMV）を調査していくことで統計情報が古いことが原因であると判断し、統計情報の更新を行うことで対処しました。統計情報が古いことによって、クエリパフォーマンスに影響が出ることは、よくある話ですよね。

統計情報とは

　検証環境での再現の前に、統計情報って何だっけ？という点を簡単に説明します。

　SQL Serverにおける統計情報は、データベース内のテーブルやインデックスのデータ分布をまとめたものと考えてください。

　これらを利用することで、ユーザーから発行されたクエリを効率的に実行するための適切な実行計画を作成・選択することができます。デフォルトでは、統計情報はオブジェクトが作成されると自動的に作成されます。また、特定の条件（例えばテーブルのデータが一定以上変更された場合）に基づき、統計情報を自動で更新してくれます。

検証環境で再現

　それでは、統計情報が古いことによって発生するパフォーマンス劣化について再現してみます。

　なお、検証レベルですので、統計情報の自動更新はOFFにして行います。

-- 統計情報の自動更新をOFF
ALTER DATABASE test SET AUTO_UPDATE_STATISTICS OFF;
-- 結果確認
SELECT
 is_auto_create_stats_on AS Auto_Create_Stats,  -- 統計情報自動作成
 is_auto_update_stats_on AS Auto_Update_Stats     -- 統計情報自動更新
FROM sys.databases
WHERE name = N'test';

　今回の検証で使用するテーブルは以下となります。

-- 検証用のテーブルを作成
CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,    -- 自動インクリメントの主キー
    ProductID INT NOT NULL,                  -- 商品ID
    Quantity INT NOT NULL,                   -- 数量
    SaleDate DATETIME NOT NULL               -- 売上日
);
-- 非クラスターインデックスの作成
CREATE NONCLUSTERED INDEX [IDX_Sales_ProductID] ON [dbo].[Sales]([ProductID]);

　データも投入して、以下のデータ分布となっています。

SELECT 
 ProductID,
 COUNT(*) AS Total
FROM Sales
GROUP BY ProductID;

　テーブルを作成したタイミングで統計情報も作成されますが、中身は空っぽです。

DBCC SHOW_STATISTICS([Sales],[IDX_Sales_ProductID]);

　
　統計情報がない場合、的外れな予測行数を使用して実行計画が作成されていることがわかりますね。

-- ProductIDを1で実行
SELECT * FROM Sales WHERE ProductID = 1;
-- キャッシュクリア
DBCC FREEPROCCACHE;

-- ProductIDを2で実行
SELECT * FROM Sales WHERE ProductID = 2;
-- キャッシュクリア
DBCC FREEPROCCACHE;

　
　ProductID=1に関しては、テーブルのレコードの大半を占めるので、Index SeekよりIndex Scanの方がパフォーマンスが良いと思われます。

　それでは、手動で統計情報の更新を行います。
　統計情報を更新する際、全体か一部をスキャンするかを指定することができます。より精度の高い実行計画を生成・選択するためには、全体をスキャンさせる方が良いです。ただし、データ量によって実行時間が大きく変わりますのでご注意を。

UPDATE STATISTICS [Sales]([IDX_Sales_ProductID]) WITH FULLSCAN;

　統計情報更新後は、以下のように正確なテーブルの情報が記録されていることがわかります。

DBCC SHOW_STATISTICS([Sales],[IDX_Sales_ProductID]);

　正しい統計情報を使用したクエリの実行計画は一体どのようになるでしょうか。

　まずProductID=1ですが、統計情報更新前は非クラスターインデックスが採用されていましたが、統計情報更新後はクラスターインデックスのIndex Scanとなっています。

　次にProductID=2は、変わらず非クラスターインデックスのIndex Seekとなっています。どちらも予測行数と実際の行数が完全に一致していることも確認できますね。

Appendix

　統計情報を確認する際に使用するクエリを2つ紹介します。

　1つ目は、DBCC SHOW_STATISTICSの代替として使用できる動的管理ビュー（DMV）です。

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('<オブジェクト名>'), 2); -- 今回はテーブル名を指定
SELECT
 ss.name,
 ss.stats_id,
 shr.steps,
 shr.rows,
 shr.rows_sampled,
 shr.modification_counter,
 shr.last_updated,
 sh.range_rows,
 sh.equal_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('<オブジェクト名>'); -- 今回はテーブル名を指定

　2つ目は、統計情報が前回更新されてからの変更がn回を超えるオブジェクトを確認するクエリです。

SELECT 
 obj.name,
 obj.object_id,
 stat.name,
 stat.stats_id,
 last_updated,
 modification_counter  
FROM sys.objects AS obj   
INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id  
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE modification_counter > 10000;　-- 10000行以上の変更が発生したオブジェクトに絞り込む

まとめ

　パフォーマンスが悪化した際に「統計情報が古い可能性もあるから、一度更新してから～」という話はよく聞きますが、実際に統計情報に格納されているデータや動作確認をしてみると、より理解が深まりますね。

　統計情報の世界は奥が深いので、引き続き調査と検証をしていこうと思います。

中川智文（2022年入社）
株式会社システムサポートフューチャーイノベーション事業本部ソリューションサービス事業部所属
Oracle Database, SQL Serverを中心とした案件に従事