📊

SQLServerでのクエリが期待通り動かないときに再現する手順

2023/07/15に公開

SQL Server

query

ssms

tech

概要

SQLServer でクエリが遅くなるケース、類似したクエリは問題ないけど、このクエリは遅いなど、ハッキリとした原因がわからないことはありますが、条件が最適でない、最適な Index がない、最適なクエリでないなどが原因のケースもあります。

そういう場合、テスト用のテーブル作成、テストデータ作成、問題のクエリ実行、プランの参照することで原因がわかる場合もあると思いますが、どうすればいいかなど再現する手順が煩雑になる印象があります。それを回避するため典型的なケースでの検証用の手順になります。
実際には、複雑なケースも多々あると思いますが、シンプルなケースベースで作成しているので、確認したいことに合わせて、スキーマ（テーブルデザイン、Index など）を調整し、クエリも目的に合わせてい調整することで問題の再現が可能と考えています。

SQLServer のインストール手順、DB 作成手順などは、説明を省いているので、必要な方は識者の公開情報参照すれば・・・と思います。

前提

SSMS（SQLServer）をローカルにインストールする前提です。
おそらく、DB 機能を提供している各種クラウドサービスでも可能と思いますが、テストのために大きなデータを作成する経緯を考えるとローカルでの準備の方が個人的には安心。

SQLServer2019 はインストール済みを想定
SQLServer2019 で「loclaldb」作成済みでそこに、「新しいクエリ」で画面を開いている前提

インストール詳細については、以下などが参考になります。

2017、2019、2022 などの差異については、施行範囲ではどれでも問題ないと思います。

手順

手順概要

問題発生時の、究明の手順、問題になっているクエリ、問題になっていないクエリ、各々に対応するテーブル、ダミーデータ準備して、上の手順に従って、各々作成するイメージになります。

ステップ	役割	補足
① ダミーテーブル作成	「③」で実行し、問題再現できるテーブル構造を作成	異なるクエリなら２テーブルの場合もある。単一テーブルの場合もある。目的次第です。
② ダミーデータの登録	「③」で実行時に問題が把握できるデータを準備	クエリで WHILE など利用するこでダミーデータを作る
③ クエリの実行	問題になっているクエリの実行。再現できなければ、「①」にもどって、別テーブル、クエリを準備。	`SET STATISTICS XML ON` しておくことで、クエリプラン入手可能
④ クエリプランの分析	クエリプランを入手。チャートで概要確認し、詳細は Xml で確認。

実データ利用可能なら、実際のテーブルの CreateSql を「ダミーテーブル作成」で実行、実際に問題の起きているデータを BcpOut,BcpIn して「ダミーデータの登録」、問題のクエリを「クエリの実行」で行なったあと、クエリプラン確認する流れになると思います。

手順 ①：ダミーテーブル作成

問題の起こっているクエリで利用するテーブルを作成する。正常時、異常時の比較がテーブル単位で必要な場合は、複数テーブルの作成になります。
今回は単一テーブルの作成シナリオを想定。

--テーブル作成
CREATE TABLE [persontable](
	[id] [char](10) COLLATE Japanese_BIN NOT NULL,
	[name] [char](64) COLLATE Japanese_BIN NULL,
 	[address] [char](64) COLLATE Japanese_BIN NULL,
--クラスタ化インデックス追加
 CONSTRAINT [PK__persontable] PRIMARY KEY CLUSTERED
(
	[id] ASC
)
) ON [PRIMARY]
GO

--インデックスの追加
CREATE NONCLUSTERED INDEX [index2] ON [persontable]
(
	[name] ASC
) ON [PRIMARY]
GO

手順 ②：ダミーデータの登録

問題の発生するデータを作成。CSV 形式で BCPIN 等も出可能。ただしクエリの中でもWHILE等を利用すれば、ダミーデータは自由に作成できる印象があります。

DECLARE @index INTEGER
DECLARE @name char(64)
DECLARE @address char(64)

--ループ用変数を初期化
SET @index = 0

WHILE @index < 10000
BEGIN
    --ループ用変数をインクリメント
    SET @index = @index + 1
    SET @name = 'テスト' + CONVERT(char, @index) + ' ' + '太郎' + CONVERT(char, @index)
	SET @address = '住所' + CONVERT(char, @index)
    insert persontable values (@index , @name, @address)
END

「WHILE BEGIN-END」でループを回して、ダミーデータを作成するイメージです。
上記クエリで、自分の環境だと２０秒ほど

手順 ③：クエリの実行

問題の発生しているクエリを実行。同じ（類似）クエリの差異をクエリプランで確認したいときなどは、クエリの種類は増えます。
今回はクエリ ①、クエリ ② で実行時間に差異があるケースを想定。

--- クエリ①
select  id , name , address  from persontable where name like 'テスト999%'
--- クエリ②
select  id , name , address from persontable where address like '住所999%'

GUI で実行する場合には、「実際の実行プランを含める」を ON にした状態で実行するか、クエリの前に「SET STATISTICS XML ON 」を実行するこで、プランの表示可能。結果の後の表の中で「show plan...」をクリックすれば、「実際の実行プランを含める」と同じ画面が確認可能です。

手順 ④ クエリプランの分析

各々のクエリプランに対してどの程度コストがかかっているかを確認。

Visual 化されたチャートでも確認できますが、必要な情報が見つけにくい場合もあるので、自分はチャートで怪しいところ推測、詳細は Xml で表示にします。

Xml で表示するには、チャート画面から Xml 表示を選択することで確認可能

Xml 上で「StatementSubTreeCost」を比較することから入るのが最適だと思います。
下記の内容なら、
クエリ ① はStatementSubTreeCost="0.00821463"クエリ ② はStatementSubTreeCost="0.219467"なので、クエリ ② はクエリ ① の３０倍程度コストがかかることはわかります。
原因については、各クエリのプランを確認し、IndexSeek が期待通りがみるのが、一般的には妥当です。上記のように Index 欠落している場合などは、IndexSeek でなく、IndexScan、TableSacn が発生していることがわかります。

クエリ ①

クエリ ②

QueryPlanXML（※抜粋）

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML ・・・・>
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1.96078" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.00821463" StatementText="select  id , name , address  from persontable where name like 'テスト999%'" StatementType="SELECT" QueryHash="0xD84E7CDCE21A0BD4" QueryPlanHash="0x8848DC469163C6DE" RetrievedFromCache="true" SecurityPolicyApplied="false">
         ・・・・・
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="1.96078" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.219467" StatementText="select  id , name , address from persontable where address like '住所999%'" StatementType="SELECT" QueryHash="0x6A01DE3E6B58C28A" QueryPlanHash="0x4213E71CE84B5A56" RetrievedFromCache="true" SecurityPolicyApplied="false">
		・・・・・
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

期待通りの速度が出な原因は、クエリ ② に対応する Index がないことが原因だと思われます。速度が期待されるクエリという異なら、以下のように Index 追加すことで速度が期待したものになることが推測できます。

--インデックスの追加
CREATE NONCLUSTERED INDEX [index3] ON [persontable]
(
	[address] ASC
)
GO

実際に追加後、クエリプランでクエリ ② のコストを確認してみるとStatementSubTreeCost="0.00821463"になったので、ほぼ、クエリ ① と同じコストで期待値に近づいたと考えられます。追加後のクエリプランも、IndexSeek が使ていること確認可能
クエリ ②Index 追加後

総括

なぜ、そうなるという説明で、上記手順に合わせて、クエリなど作ることが多いです。その際、手順を何度も提示したので、「パターン化」した次第です。
テーブルデザインや、クエリなどケースに応じて変えていくことで問題の再現などが可能になれば、問題、障害の分析などが容易になるのではないかと考えています。

補足

QueryPlan の画像内に「不足しているインデックス」（MissingIndexes）がクエリプラン内に記載されているケースがありますが、これはプランのもとになるクエリに対しての最適な Index が欠落している場合に出てくるもので、本当に最適かどうかは別途検討する必要があります。対象のテーブルがどの様に利用（参照、更新、登録、削除）されるか、どのような頻度で実行されるかに応じて、最適なIndex は検討する必要がある認識です。

クエリプラン自体、状態把握・分析するのには有効なツールだという認識はありますが、SSMS 入れる必要があるので、手間が・・・可能ならどこかに登録して、共有できるツールがあり、簡単に利用可能なら、識者たちに助け求めやすくなるのでは・・・と考えています。

概要

前提

手順

手順概要

手順 ①：ダミーテーブル作成

手順 ②：ダミーデータの登録

手順 ③：クエリの実行

手順 ④ クエリプランの分析

総括

補足

Discussion