INFRASTRUCTURE LEARNINGインフラ学習
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
インフラ学習ガイド
教材・演習を探す/

Escで閉じる · 検索語は送信しません

LEARNINGEN

テーマ別の解説テーマ:SQL Serverの性能と同時実行

SQL Serverのインデックスと実行計画:Seek・Scanと推定・実際の計画を読む

インデックスはデータを探すための構造、実行計画はSQLをどう処理するかの計画です。「Seekなら速い」「Scanなら失敗」と決めず、同じ文書一覧を返すSQLで、結果・アクセス方法・推定・実行時の観測を分けて考えます。

この教材で学ぶこと

インデックスの役割と更新コストを説明し、結果集合と計画・推定値と実行時情報を区別して調査できる。

先に知っておくこと

SELECTは読む列、WHEREは行の条件、ORDER BYは返す順番です。下の5行は既存の文書例から独立にコピーした合成データで、実行計画や処理時間を実測したものではありません。

「どの行を返すか」と「どう探すか」を分ける

SQLの意味から、計画と実行時の観測へ
SQLの意味から、計画と実行時の観測へ1: SQLと条件 — 意味と利用条件を与える — 処理方法の計画. 2: 処理方法の計画 — 実行した場合に情報が得られる — 実行時の情報12SQLと条件処理方法の計画実行時の情報SQLの意味から、計画と実行時の観測へ1: SQLと条件 — 意味と利用条件を与える — 処理方法の計画. 2: 処理方法の計画 — 実行した場合に情報が得られる — 実行時の情報12SQLと条件処理方法の計画実行時の情報
  1. SQLと条件 → 処理方法の計画意味と利用条件を与える
  2. 処理方法の計画 → 実行時の情報実行した場合に情報が得られる
SQLと条件
対象DB・表・条件・必要な列を固定する。
処理方法の計画
表・索引・統計などを材料に方法を選ぶ。
実行時の情報
実際に処理した対象と、その時の行数等を確認。

同じ結果集合を返しても、処理方法は一つとは限りません。オプティマイザーはSQL・スキーマ・利用可能なインデックス・統計等を考慮します。小さな表ではScanが合理的な場合もあります。演算子の名前だけで良し悪しを決めません。

5行から、所有者1の文書を読む

独立したCorpDocs-I.app.Documentsを使います。document_idは一意、owner_idはNULL可、他の更新や行レベルの絞り込みはないと仮定します。ここではdocument_idをキーとするclustered rowstoreと、owner_id・document_idをキーとするnonclustered rowstoreを、比較用の定義として考えます。実際に索引を作成したわけではありません。

固定した文書と所有者
文書ID文書名所有者ID
101Handbook1
102Diagram1
103Budget2
104Archive3
105UnassignedNULL
結果を考えるためのSELECT。ここでは実行しません
SELECT document_id
FROM app.Documents
WHERE owner_id = 1
ORDER BY document_id;

この与件では101と102が条件を満たし、ORDER BYにより101、102の順です。この2行が返ることだけでは、Seekを使ったか、何ページ読んだか、速かったかは分かりません。SQLの結果は固定データから検算できても、実行方法は別の証拠が必要です。

アクセス方法として考えられる違い
比較する見方この例で考えること
表又は索引をScan対象範囲を読み、owner_idの条件を評価する方法
索引をSeekowner_idで範囲を絞る方法。実際の計画採用は未確認
SELECTへtitleも追加仮定した索引のキーにはtitleがない。追加取得や索引設計を検討

速さの候補には、保存と更新の費用がある

ここで扱うrowstore索引の役割
種類データの持ち方確認する負担
clusteredリーフに行データを持つ。表のデータ配置に関係するキーの選択と更新、他の索引への影響
nonclusteredキーと行への参照等を別の構造に持つ保存容量とINSERT・UPDATE・DELETE時の保守
coveringという関係その問い合わせに必要な列を索引から満たせるすべての問い合わせに対する万能な種類ではない

推定計画と実際の計画を、証拠の範囲で読む

計画の種類と分かること
種類分かること残る確認
推定実行計画実行せずに取得するコンパイル時の計画・推定その試行の実際の行数や実行時の待機
実際の実行計画実行した計画に実行時情報を加えたもの対象・パラメーター・時点が調査対象と同じか
計画中のコスト表示オプティマイザーの推定に基づく比較値表示割合を実測時間の割合と混同しない

推定行数と実際の行数が大きく違うときは、入力データの分布、統計、パラメーター等の条件を調べる材料になります。差があるだけで原因を一つに決めず、演算子の実行回数やプロパティも確認します。実際の計画にも推定値は含まれます。

同じ問い合わせと条件で、前後を比較する

  1. 対象DB・SQL・パラメーター・時点と、業務上必要な行集合を揃える。
  2. 計画の種類を確認し、演算子・行数・警告を読む。データ待ち、CPU、I/O等の実際の観測と対応させる。
  3. 索引候補がある場合は読取りだけでなく保存・更新の負担を評価する。
  4. Query Storeを使えるなら履歴の計画と実行統計を確認する。保持・取得設定と集計期間を読み、一回の実行そのものと区別する。
  5. 変更後は同じ条件で結果の正しさと負荷を比較し、他の主要な問い合わせへの影響も確認する。

計画やSQLには、オブジェクト名・リテラル・業務情報が含まれる場合があります。調査資料の共有範囲は管理方針に従って判断します。このサイトへ実データや計画を入力する機能はありません。

結果・計画・観測を分けてみる

選択すると、正誤と理由が表示されます。

固定したSELECTで101と102が返ります。何が分かる?
答えと理由を読む

正解:owner_id=1を満たす結果。実行方法は別に確認。 · 固定した5行から結果は検算できます。計画や性能を実測したわけではありません。

小さい表にScanが表示されたら?
答えと理由を読む

正解:対象量・実行時情報・要求を確認して評価する。 · 演算子の名前だけを、性能の合否へ読み替えません。

UPDATEの実際の計画を取得する操作について正しいのは?
答えと理由を読む

正解:SQL自体を実行するため、変更の影響を確認する。 · 実際の計画は実行時情報を伴います。図だけを見る操作ではありません。

仮定した(owner_id, document_id)索引でtitleも読みたい。次の判断は?
答えと理由を読む

正解:必要列と追加取得・索引設計、保守費用を確認する。 · coveringはそのSQLに必要な列との関係です。更新負担も含めて考えます。

確認して、自分の言葉で説明する

  1. 固定データから101・102の結果を自分で求める。
  2. 実計画なしに断定できない性能の項目を挙げる。
  3. インデックスを追加した場合の読取りと更新の両面を説明する。
  4. 推定計画・実際の計画・Query Store履歴の証拠を区別する。
理解を確かめる: Index Seekが出た。もう「速い」と結論してよい?

いいえ。対象量・追加取得・行数・負荷・結果を確認します。

Seekはアクセス方法の一つです。要求に対する実行全体の負担と結果を、同じ条件の観測から判断します。

出典と確認した条件

説明に用いた一次資料を、確認した内容・条件と本文の参照箇所に分けてまとめています。専門家による監修・実環境での検証は行っていません。