このドキュメントに記載されている情報 (URL 等のインターネット Web サイトに関する情報を含む ) は 将来予告なしに変更することがあります このドキュメントに記載された内容は情報提供のみを目的としており 明示または黙示に関わらず これらの情報についてマイクロソフトはいかなる責任も負わないもの

2 - SQL の最適化

このドキュメントに記載されている情報 (URL 等のインターネット Web サイトに関する情報を含む ) は 将来予告なしに変更することがあります このドキュメントに記載された内容は情報提供のみを目的としており 明示または黙示に関わらず これらの情報についてマイクロソフトはいかなる責任も負わないものとします お客様が本製品を運用した結果の影響については お客様が負うものとします お客様ご自身の責任において 適用されるすべての著作権関連法規に従ったご使用を願います このドキュメントのいかなる部分も 米国 Microsoft Corporation の書面による許諾を受けることなく その目的を問わず どのような形態であっても 複製または譲渡することは禁じられています ここでいう形態とは 複写や記録など 電子的な または物理的なすべての手段を含みます マイクロソフトは このドキュメントに記載されている内容に関し 特許 特許申請 商標 著作権 またはその他の無体財産権を有する場合があります 別途マイクロソフトのライセンス契約上に明示の規定のない限り このドキュメントはこれらの特許 商標 著作権 またはその他の無体財産権に関する権利をお客様に許諾するものではありません 別途記載されていない場合 このソフトウェアおよび関連するドキュメントで使用している会社 組織 製品 ドメイン名 電子メールアドレス ロゴ 人物 出来事などの名称は架空のものです 実在する会社名 組織名 商品名 個人名などとは一切関係ありません 2010 Microsoft Corporation. All rights reserved. Microsoft SQL Server は 米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です 記載されている会社名 製品名には 各社の商標のものもあります 1

目次 1. 本書の概要... 3 2. 環境作成... 3 3. 何故 SQL は遅延するのか?... 3 4. 遅延原因 1: インデックスが作成されていない... 4 4.1. カーディナリティ... 4 4.1.1. 実行プランの確認方法... 4 4.1.2. カーディナリティの低いカラムでのインデックス... 6 4.1.3. カーディナリティの低いカラムで有効なインデックス... 8 4.2. テーブルサイズ...10 4.2.1. 高度なインデックスの使用方法...10 4.2.2. カバリングインデックス...10 4.2.3. ソート用として使用... 11 5. 遅延原因 2: インデックスが使用されていない...13 5.1. SQL 文が適切に記載されていない...13 5.1.1. 関数を使用した SQL...14 5.1.2. 演算を使用した SQL...16 5.1.3. 暗黙の型変換がおこなわれている SQL...17 5.1.4. 後方一致検索の SQL...19 5.1.5.!= を使用した SQL...20 5.1.6. Is Null を使用した SQL...21 5.2. 最適ではない実行プランによる遅延...23 5.2.1. 統計情報と実行プラン...23 5.2.2. 実データと統計情報の値の乖離による遅延...23 6. チューニングサイクル...28 6.1. 情報収集...28 6.2. 分析...29 6.3. チューニング...29 6.4. テスト 評価...29 2

1. 本書の概要 本書は まだあまり開発経験のないアプリケーション開発者が SQL を最適に保つための SQL の書き方や インデックスを効率的に使用する方法についてまとめています 2. 環境作成 本書で使用するデータベース及びテーブル インデックスは 以下となります CREATE DATABASE DB_Test GO USE DB_Test GO CREATE TABLE T_Emp( ID int NOT NULL, Name Varchar(20), Status Varchar(1), Flag tinyint NOT NULL, UpdDate Datetime, CONSTRAINT PK_ID PRIMARY KEY CLUSTERED(ID)) GO CREATE INDEX IX_Name on T_Emp(Name) CREATE INDEX IX_Status on T_Emp(Status) CREATE INDEX IX_Flag on T_Emp(Flag) CREATE INDEX IX_UpdDate on T_Emp(UpdDate) GO TRUNCATE TABLE T_Emp GO DECLARE @i int = 1 DECLARE @name varchar(10) WHILE @i <= 10000 BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 9990 INSERT INTO T_Emp VALUES( @i,@name,1,0,getdate() ) ELSE INSERT INTO T_Emp VALUES( @i,@name,2,1,null ) SET @i += 1 END GO 3. 何故 SQL は遅延するのか? SQL Server や Oracle を始めとしたリレーショナルデータベースでは データを取得する際に SQL と呼ばれるデータベース言語を使用してデータベースにアクセスします データベースではその SQL を受け取って SQL を解析した後に値を返しますが この時に結果がなかなか返って来ないまま数十分から数時間が経過してしまった という経験をお持ちの方は多いのではないでしょうか では SQL は何故遅延するのでしょうか? SQL が遅延する原因は大きく分けて 2 つあります 1 つはサーバーなどのリソースによる遅延で もう 1 つが SQL による遅延です サーバーリソースによる遅延とは 接続ユーザー数やデータ量が想定外に増加してしまったことにより CPU 使用率の高騰やメモリー不足などサーバーが高負荷状態になり データベースサーバー自体の処理が 3

追いつかなくなってしまうようなケースで アプリケーション開発段階で考慮することが難しい現象と言えます 一方 SQL による遅延とは SQL 実行時に SQL 側に問題がある為大量データの読み込みなどが発生し SQL の遅延を誘発してしまうケースです このような遅延はほとんどの場合がアプリケーション開発時に防げるものであり アプリケーション開発者はデータの読み込み量を抑え 遅延が発生しないように心掛けて開発する必要があります さもないと 運用開始後に遅延が発生してアプリケーションの改修作業が発生するだけでなく アプリケーションのテストやリリース作業 リリース後の監視から評価まで多大な作業工数がかかってしまうことになります では 大量の読み込みとはどのようなことが原因で発生してしまうのでしょうか? 4. 遅延原因 1: インデックスが作成されていない 大量の読み込みが発生する原因として最も多いのが 適切にインデックスが作成されてない為フルスキャンしてしまうケースです SQL には特定の行を取得する際に WHERE 句を使用して条件を指定しますが 1000 万件のテーブルに対して 1 件を抽出するような SQL を実行した場合 インデックスがないと 1000 万件のデータ全てを読み込んでしまうことになります この為 遅延している SQL の条件句にインデックスを作成することで読み込み数は大幅に改善することができます では インデックスは条件句に指定された全てのカラムに作成すればよいかというとそうではありません インデックスはあまり作成しすぎると更新負荷が高くなってしまい 同時実行性が低下してしまう可能性がある為 作成しすぎには注意が必要です そして インデックスを効果的に作成する際のポイントとしては以下 2 つがあります カーディナリティ データ量 4.1. カーディナリティインデックスを作成する場合 最も考慮すべきはその項目がカーディナリティが高いかどうかという点です カーディナリティとは値の種類のことで 性別のように男女 2 種類しかないようなカラムの場合 カーディナリティが低いといい 逆に 顧客番号のようにユニークな値を格納するカラムの場合 カーディナリティが高いといいます カーディナリティが低いカラムに対してインデックスを作成しても データベースはインデックスを使用せずにテーブルフルスキャンを選択してしまいます この点を SQL Server で実際に確認してみましょう この点を確認するには SQL の実行プランを見る必要があります 以下 実行プランの確認方法になります 4.1.1. 実行プランの確認方法 実行プランとは データベースにアクセスする時のアクセス方法や順番のことです 今回は Microsoft SQL Server Management Studio を使用して実行プランを確認します Microsoft SQL Server Management Studio を起動し SQL Server に接続 4

新しいクエリ をクリック 実際の実行プランを含める を押す SQL 文を記載 実行 ボタンを押しクエリを実行 実行プランタブをクリックして実行プランを表示 実行プランの見方は以下になります SQL クエリアナライザのグラフィカルな実行プランの出力は 右から左 上から下に読みます 分析されたバッチ内の各クエリが表示されます 各クエリのコストも バッチの総コストに占める割合として表示されます 5

実行プランにマウスを置くと 詳細が表示 4.1.2. カーディナリティの低いカラムでのインデックスそれでは カーディナリティが低いカラムだとインデックスが使用されないことを確認してみます 今回は T_Emp というテーブルの Flag カラムに 0 と 1 の 2 種類の値を 5000 件ずつ格納して SELECT 文を実行します T_Emp テーブルへのデータ挿入 TRUNCATE TABLE T_Emp GO DECLARE @i int = 1 DECLARE @name varchar(10) WHILE @i <= 10000 BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 5000 INSERT INTO T_Emp VALUES( @i,@name,1,0,getdate() ) ELSE INSERT INTO T_Emp VALUES( @i,@name,2,1,null ) SET @i += 1 END GO 6

Status の件数確認 SELECT Flag,COUNT(*) GROUP BY Flag SQL/ 実行プラン WHERE Flag = 0 実行プラン 1. PK_ID を Clustered Index Scan で参照 T_Emp テーブルの Flag というカラムにはインデックス (IX_Flag) が作成されていますが 実行プランを見るとそのインデックスではなくプライマリキー (PK_ID) だけが参照されているのがわかります この PK_ID というプライマリキーは クラスタインデックスというインデックスで T_Emp テーブルそのもののデータが格納されているインデックスになります ( 1) そして 実行プランに Index Scan とあり この Index Scan が PK_ID を全件読み込んだことがわかります つまり この実行プランは PK_ID というプライマリキー ( T_Emp テーブル ) をフルスキャンした ということになります 7

1. クラスタインデックスクラスタインデックスとは リーフブロックにポインタではなく実データを格納しているインデックスのことです 通常のインデックスは リーフブロックにテーブルへのポインタを格納していますが クラスタインデックスはポインタではなく実データをそのまま格納しています この為 プライマリキーを指定してテーブルデータを SELECT するような SQL で有効なインデックスと言えます SQL Server では プライマリキーを作成すると元々存在していたテーブルデータが削除され 全てのテーブルデータがプライマリキー内に格納されるようになります 4.1.3. カーディナリティの低いカラムで有効なインデックスでは カーディナリティの低いカラムにはインデックスをつける必要はないかというとそうとは限らないケースがあります それは インデックスを使用して数件のデータを抽出するような SQL です 例えば 先ほどの Status の件数が 0 が 9,990 件 1 が 10 件格納されていたとします この時に 1 の値を抽出する SELECT 文を実行した場合 10,000 件中 10 件を取得することになる為 インデックスを使用した方がコストが低いと判断されます データ作成 TRUNCATE TABLE T_Emp GO DECLARE @i int = 1 DECLARE @name varchar(10) WHILE @i <= 10000 BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) IF @i <= 9990 INSERT INTO T_Emp VALUES( @i,@name,1,0,getdate() ) ELSE INSERT INTO T_Emp VALUES( @i,@name,2,1,null ) SET @i += 1 END GO Status の件数 SELECT Flag,COUNT(*) GROUP BY Flag 8

SQL/ 実行プラン WHERE Flag = 1 実行プランステップ 1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 上記実行プランを見ると IX_Flag が Index Seek という実行プランで読み込まれているのが分かります Index Seek は条件に指定された値のみ抽出した場合の実行プランであり 今回の場合は 10 件のみ抽出したことになります その後で T_Emp テーブルをその 10 件と Nested Loops で結合し 結果を返しています このように カーディナリティが低いカラムであってもインデックスを使用することで数件まで絞り込める場合 インデックスが使用され 不要な読み込みを回避することができます 9

4.2. テーブルサイズインデックス作成時に考慮すべきもう 1 つのポイントとしてテーブルのサイズがあります インデックスはテーブルサイズが大きいほど効果が高くなります 例えば 100 件の中から 1 件を抽出するのであれば全テーブルデータを参照してもインデックスを使用しても読み込み数や処理時間はそれほどかわりません ですが 100 万件の中から 1 件のデータを抽出する場合 全データを参照してしまうと 読み込みが大量に発生してしまいます このようなテーブルのデータを抽出する際はインデックスを使用することで効率的にデータを取得することができます また 1 行のサイズが大きい場合も テーブルサイズが大きくなる為フルスキャンのコストが高くなってしまいます 例えば 1 行のレコード長が 10bytes のテーブルと 1KB のテーブルでは同じ行数でもテーブルのサイズが全く異なり 当然フルスキャン時のコストも高くなってしまいます このようなテーブルに対してはインデックスの作成を検討した方がよいと言えます 4.2.1. 高度なインデックスの使用方法以上のように 条件句に指定されているカラムと カーディナリティやデータ量を考慮することで効果的なインデックスを作成することができます ですが さらに 以下のようなインデックスを作成することで インデックスをより効果的に使用することができます 4.2.2. カバリングインデックスカバリングインデックスとは SELECT 文で使用する全てのカラムをインデックスに含めることでパフォーマンスを改善させるチューニングテクニックの 1 つです 例えば 以下のような Flag カラムを条件句に指定した場合 実行プランは Flag のインデックスを参照した後で Name 列の値を取得する為 テーブルを参照する必要があります SQL/ 実行プラン SELECT Name WHERE Flag = 1 10

実行プランステップ 1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 Flag のインデックス (IX_Flag) が使用されている為高速に処理されていますが IX_Flag インデックスに Name 列がない為 Name を取得する為にテーブルを参照してしまっています この為 この SQL をより効率的に処理させたい場合 SELECT 句にある項目も全てインデックスに含めることで テーブルデータの読み込みを減らすことができ 処理時間も短縮させることができます CREATE INDEX IX_Flag_Name on T_Emp(Flag,Name) GO SQL/ 実行プラン SELECT Name WHERE Flag = 1 実行プランステップ 1. IX_Flag_Name インデックスを Index Seek で参照 4.2.3. ソート用として使用インデックスは値が小さい順に並んでいる為 order by 句のようなソート処理が実行される SQL の場合 そのカラムにインデックスを作成しておくことでソート時の負荷が低くなり処理を高速化させることができます 以下 カバリングインデックスと同様の手順で確認してみます ( カバリングインデックスの項で作成したインデックスは削除して下さい ) 11

SQL/ 実行プラン SELECT Name WHERE Flag = 1 ORDER BY Name 実行プランステップ 1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 3. Sort 処理実行 ORDER BY を使用している為 テーブルデータとインデックスを結合した後 Sort 処理が実行されているのがわかります この為 この Sort 処理のカラムもインデックスに追加すると ソート処理が回避できる為高速に処理されます CREATE INDEX IX_Flag_Name on T_Emp(Flag,Name) GO SQL/ 実行プラン SELECT Name WHERE Flag = 1 ORDER BY Name 12

実行プランステップ 1. IX_Flag_Name を Index Seek で参照 Flag と Name の複合インデックスを作成することで テーブルへのアクセスと Sort 処理が回避され 作 成されたインデックスのみで処理されていることがわかります 5. 遅延原因 2: インデックスが使用されていない インデックスを作成することで 多くの SQL 遅延は改善されますが 遅延が解消されないケースがあります それは インデックスはあるのに使用されていないようなケースです 具体的に言うと SQL が適切に記載されていないケースと 実行プランが最適でない場合です 5.1. SQL 文が適切に記載されていない 一般的に以下のような SQL ではインデックスが作成されていても使用することができません この為 こ のような SQL 文をできるだけ記述しないよう注意して SQL を記述する必要があります 1. 関数を使用した SQL 2. 演算を使用した SQL 3. 暗黙の型変換がおこなわれている SQL 4. 後方一致検索の SQL 5.!= を使用した SQL 6.Is Null を使用した SQL 5,6 については SQL Server の場合インデックスが使用されます これらについて SQL Server で実際に確認しながら見ていくことにしましょう 13

5.1.1. 関数を使用した SQL 条件句に関数を使用したカラムを指定して実行すると インデックスが使用されず全件検索になってしまいます 以下では SUBSTRING 関数を使用して Name 列の頭 6 文字が Name01 という文字列を検索しています 遅延 SQL WHERE SUBSTRING(Name,1,6) = 'Name01' SQL/ 実行プラン 実行プランステップ 1. IX_Name インデックスを Index Scan で参照 2. その結果と PK_ID キーを Nested Loops で結合 インデックススキャンとあるので インデックスが正しく使われているように見えますが Index Scan は インデックスの全データをスキャンする実行プランになります そして その中から条件に該当するデータ を取得し その結果と PK_ID キーを Nested Loops で結合しています SQL の最適化この SQL をインデックスが使用されるよう記述する場合 カラムに対して関数を使用しないように書き換える必要があります 今回の場合 SUBSTRING 関数で 1 文字目から 6 文字のデータを取得してその値と文字列を比較していますので 以下のように Like 検索に変更することで同等の結果を取得することができ インデックスも使用することができます 14

変更 SQL/ 実行プラン WHERE Name Like 'Name01%' 実行プランステップ 1. X_Name インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 上記を見ると IX_Name に対する Index Scan だった実行プランが Index Seek に変わっています Index Seek は 条件に指定された値のみデータを抽出した場合の実行プランです ( 内部的には SQL が範囲検索の SQL 文に変換されています ( 2)) そして Index Seek で抽出した結果は PK_ID と Nested Loops で結合され その結果が返されています つまり IX_Name インデックスを使用して データを抽出しているということができます 2. 範囲指定に変換されたことは Index Seek の詳細を見ることで確認することができます 15

5.1.2. 演算を使用した SQL インデックスが作成されたカラムに対して演算処理を行うと インデックスが使用されません 以下は ID をマイナス 100 した値が 0 より小さいデータを抽出しています SQL/ 実行プラン WHERE ID - 100 < 0 実行プランステップ 1. ID キーを Clustered Index Scan で参照 先ほどの SQL と同じように Index Scan なので インデックスの全データをスキャンした後 条件に指定 したデータを抽出しています SQL の最適化 本 SQL のように左辺の ID を演算するのではなく 右辺の 0 の値に 100 をプラスするように書き換え ることでカラムに対する演算を回避でき インデックスも使用されます 変更 SQL/ 実行プラン WHERE ID < 0 + 100 16

実行プランステップ 1. PK_ID キーを Index Seek で参照 上記を見ると 実行プランが Index Scan から Clustered Index Seek に変わっていて 条件に指定され た値のみ参照していることがわかります これにより インデックスの不要な読み込みを抑えることができ ます 5.1.3. 暗黙の型変換がおこなわれている SQL 暗黙の型変換 ( 文字列型から数値型に変換 ) されているような場合 インデックスは使用されません #SQL Server の場合 数値型から文字列型に暗黙の型変換される場合は インデックスが使用されます SQL/ 実行プラン WHERE Status = 2 17

実行プランステップ 1. PK_ID キーを Clustered Index Scan で参照本 SQL は T_Emp テーブルの Status の値が 2 のデータを抽出しています Status のカラムは文字列型であり 指定した値は数値で検索している為 このまま実行してしまうと暗黙的に SQL が変換され インデックスが使用されない SQL で実行されてしまいます 上記の通り Status 列を CONVERT_IMPLICIT 関数を使用して数値型に変換しています これによりフル スキャンが実行されてしまいます SQL の最適化 本 SQL は暗黙の型変換が発生しないようシングルクォートで囲むことでインデックスが使用されるように なります SQL/ 実行プラン WHERE Status = '2' 18

実行プランステップ 1. IX_Status インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 上記を見ると IX_Status の Index Seek で実行されており 適切にインデックスを使用していることが わかります 5.1.4. 後方一致検索の SQL 先に確認した前方一致検索であればインデックスは使用されますが 後方一致検索ではインデックスが使用 されません 本 SQL は Name の後ろから 3 文字が 123 のデータを抽出しています SQL/ 実行プラン WHERE Name Like '%123' 19

実行プランステップ 1. IX_Name インデックスを全データ参照 2. PK_ID キーと Nested Loos を全データ参照 本 SQL は IX_Name インデックスをフルスキャンした後 後ろ 3 文字が 123 のデータを抽出しています 後方一致検索の場合 インデックスを使用するように変更することができません この為 この SQL を高速に処理する必要がある場合 別の条件を追加できないか 全文検索インデックスで代用可能か などを検討する必要があります 5.1.5.!= を使用した SQL!= (Not Equal 検索 ) で検索した場合 Oracle ではインデックスが使用されませんが SQL Server の場合 インデックスが使用されるケースがあります それは インデックスを使用した方がフルスキャンよりコストが低いと SQL Server が判断した場合です SQL/ 実行プラン (10 件取得 ) WHERE Flag!= 0 実行プランステップ 1. IX_Flag インデックスを参照 2. その結果と PK_ID キーを Nested Loops で結合 20

SQL/ 実行プラン (9,990 件取得 ) WHERE Flag!= 1 実行プランステップ 1. PK_ID キーを Clustered Index Scan で参照 上記のように!= を使用した結果が 10,000 件中 10 件を取得するような結果の場合 インデックスが使 用されます 逆に 9,990 件取得する場合 テーブルを全件参照した方が速いと判断してフルスキャンが 実行されます 5.1.6. Is Null を使用した SQL SQL Server の場合 Is Null を使用してもインデックスが使用されるケースがあります これも!= 検索と同様 インデックスを使用した方がフルスキャンより高速に処理可能と SQL Server が判断した場合に使用されます SQL/ 実行プラン (NULL データ抽出 :10 件 ) WHERE UpdDate Is Null 21

実行プランステップ 1. IX_UpdDate インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 SQL/ 実行プラン (NOT NULL のデータ抽出 :9,990 件 ) WHERE UpdDate Is Not Null 実行プランステップ 1. PK_ID キーを Clustered Index Scan で参照 22

5.2. 最適ではない実行プランによる遅延以上の SQL は インデックスが使用されるよう SQL 文を書き換えればパフォーマンスが改善される可能性が高いものでした ですが SQL を書き換えてインデックスが使用される SQL を記述してもインデックスが使用されないケースがあります それは データベースが作成した実行プランが最適ではないケースです 実行プランとは データベースにアクセスする時のアクセス方法や順番のことで 具体的に言うと 複数のテーブルを指定した SQL が実行された際に どのテーブルから参照するか どのインデックスを使用するか どのような結合方法で実行するかを解析した結果のことです データベースでは 幾つかの実行プランを作成した後で その中から最も高速に処理することができる実行プランを選択し実行します このような実行プランの作成や選択はオプティマイザというデータベースの頭脳とも言える箇所で行われています このオプティマイザが高速に処理可能と判断した実行プランが 実際に実行した時に遅い場合がある為 遅延が発生してしまうことになってしまいます そして このような遅延のほとんどが統計情報に問題があるケースになります 5.2.1. 統計情報と実行プラン統計情報とは 表や索引 カラムがどのような状態なのかを表している情報のことで 具体的には テーブルにデータが何件格納されているか 1 行の平均サイズはどのぐらいなのか カラムにはどのような値が多く格納されているのかなどの情報が格納されています SQL Server の統計情報では行数や平均キー長 ヒストグラムなどの情報が格納されています また インデックス作成時にインデックス列内の統計情報が作成され テーブル内のデータが更新されると統計情報も自動的に更新されます ( デフォルト ) 実行プランは SQL 文実行後 オプティマイザの解析処理で作成されます オプティマイザは解析時に複数の実行プランを作成し その中で最適な実行プランを選択していますが その基準となる情報が統計情報です この統計情報が実データと乖離しているような場合 誤った実行プランを選択してしまい処理が遅延してしまうことになります 例えば 1,000 万件のデータが格納されているテーブルに対して 10 件取得する SELECT 文を実行したとします この時に 統計情報が古くこのテーブルに 10 件しか格納されていないという情報になってしまっていた場合 フルスキャンの方がインデックスを参照するより高速に処理可能と判断し フルスキャンを選択してしまう可能性があります SQL Server ではデフォルトでは自動で統計情報を収集しているので このように実データと統計情報の値が乖離するようなことは多くないですが この機能を無効にしている場合や大量データを一度に更新した場合などにこのようなことになる可能性があります 5.2.2. 実データと統計情報の値の乖離による遅延統計情報による遅延について実際に SQL Server で確認してみます 今回は 現象を分かりやすくするために 10 件データ挿入時に統計情報を取得し その後で 20 万件挿入して実データと統計情報の値を乖離させて遅延の現象を再現させてみます 1. T_Emp のテーブルデータ作成今回は誤った統計情報を取得すると遅延してしまう現象を確認する為 自動で統計情報が取得されないよう 統計の自動更新 を無効にします 23

2. T_Emp のテーブルデータ作成 10 件のテーブルデータを作成します TRUNCATE TABLE T_Emp GO DECLARE @i int = 1 DECLARE @name varchar(10) WHILE @i <= 10 BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) INSERT INTO T_Emp VALUES( @i,@name,1,0,getdate() ) SET @i += 1 END GO 3. 統計情報取得 10 件格納時の統計情報を取得します 統計情報の手動取得は UPDATE STATISTICS コマンドを使用します UPDATE STATISTICS T_Emp IX_Flag 統計情報が取得されてるかどうか確認します 確認は DBCC SHOW_STATISTICS コマンドで確認することができます DBCC SHOW_STATISTICS('T_Emp','IX_Flag') 24

Rows が 10 となっていることから この統計情報ではテーブルデータが 10 行格納されていることになっています 実行プランを見ると PK_ID のフルスキャンが実行されていることがわかります WHERE Flag = 0 4. 20 万件のデータ作成 T_Status テーブルに 20 万件になるようデータを挿入します DECLARE @i int = 11 DECLARE @name varchar(10) WHILE @i <= 200000 BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6) INSERT INTO T_Emp VALUES( @i,@name,2,1,getdate() ) SET @i += 1 END GO 25

件数確認 SELECT Flag,COUNT(*) GROUP BY Flag 5. 統計情報の確認 統計情報が更新されてないことを確認します DBCC SHOW_STATISTICS('T_Emp','IX_Flag') 6. SELECT 文実行統計情報が 10 件でテーブルデータが 20 万件格納されている状態で 以下のような Flag を条件に 10 件取得する SQL 文を実行した時の実行プランを確認します WHERE Flag = 0 26

実行プランステップ 1. PK_ID キーを Clustered Index Scan で参照 実行プランを見ると Clustered Index Scan とあり PK_ID ( T_Emp テーブル ) のフルスキャンに なっていることがわかります 7. 統計情報を取得後再実行 統計情報を取得して 統計情報の値を最新の状態にしてから同様の SELECT 文を実行します UPDATE STATISTICS T_Emp IX_Flag DBCC SHOW_STATISTICS('T_Emp','IX_Flag') WHERE Flag = 0 27

実行プランステップ 1. IX_Flag インデックスを Index Seek で参照 2. その結果と PK_ID キーを Nested Loops で結合 このように テーブルデータと統計情報で同期が取れていないと オプティマイザが誤った実行プランを最適と判断してしまいます SQL Server では統計情報は自動更新の為 あまり意識する必要は有りませんが 大量データの更新処理等が実行された時は統計情報を取得して このような遅延処理が起きないよう気をつける必要があります 6. チューニングサイクル以上の点を考慮してアプリケーションを開発することで 運用が開始した後に処理遅延が発生する可能性も少なくなります 但し このように考慮して開発しても実際にはデータ量の増加により予想外に処理が遅延してしまうことがあります このようなことにならない為に 運用開始後に遅延している SQL を発見し対処する そしてまた監視 というようなチューニングサイクルを実施する必要があります チューニングサイクルのフェーズは以下の通りとなります チューニングサイクル 1. 情報収集 2. 分析 3. チューニング 4. テスト 評価 6.1. 情報収集 まず最初に データベースの稼働データや処理時間が長い SQL を取得します データベースの稼働データは稼働 状況の傾向を把握する為に常に取得しておく必要があります 28

具体的な情報の収集方法については 自習書の 監視ツールの基本操作 を参考にして下さい 参考 : http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx 6.2. 分析次に 取得した情報を分析します 分析は データ量や処理量の増加に伴いアプリケーションや SQL が遅延していないか データベースの全体で読み込み量やサーバーの CPU 使用率が増加していないかなど ボトルネックの原因を調査します 例えば 日々処理が遅延する SQL が確認された場合 テーブルのフルスキャンが実行されていないか データ量の増加に伴って読み込みが多くなる処理がないかなどをポイントにボトルネックを特定します 6.3. チューニング遅延原因が把握できたら 遅延を解消する為のチューニングを行います チューニングは本書で見てきたような観点 つまり インデックスが貼ることで改善できるか インデックスが使用されない SQL の書き方になってないか データと統計情報の値が乖離していないかなどを確認し チューニングを実施します 6.4. テスト 評価チューニングした結果をテストし効果を確認します 特に インデックスを作成するチューニングの場合 そのインデックスと同じカラムを参照している SQL にも影響してしまう可能性がある為 そのような SQL の実行プランも確認することが好ましいです また この処理がチューニングされたことにより 別の所で新たにボトルネックが発生する可能性がある為 引き続きデータを取得し 情報収集する必要があります 以上のチューニングサイクルを実施することで データベースのパフォーマンスを向上させ システムを安定的に運用させることができます 29