SQLパフォーマンスtips(SQLServer)

個人的にSQLServerの学習をしたメモ。
インデックスの構造とか実行計画の見方など、「パフォーマンスチューニング」が出来るようになるために最低限理解する必要があると感じた内容を整理。
※日々UPDATE予定。

I．「実行計画」

１．「実行計画」とは？

(1)「実行計画」とは？

SQLServerがクエリを実行する際、「クエリの結果を得るために、そのクエリをどのように処理するか」というのを表したのが「実行計画」。

(2)「実行計画」から何を読み解けばいい？

「クエリを実行する際に無駄な処理をしていないか」を見ることが必要。
データの読み込み量が少なく済む処理をしていれば当然早く結果を得ることができるし、
データの読み込み量が多い（無駄が多い）処理をしていれば、その分遅いクエリになってしまう。

＜個人的メモ＞

昔参加したセミナー＠お台場で、「SQLは、オプティマイザがプログラミングをするための設計書だ！」と熱弁していた講師がいました。
オプティマイザは書かれたSQLを解釈して実行計画を作成してデータアクセスを行います。
だからオプティマイザに無駄な実行計画を作成させないためにも、「綺麗で効率の良いSQL」を書く必要があります。

Ⅱ．「インデックス」について

１．インデックスの基礎知識

(1)「インデックス」って？

「検索のパフォーマンスを向上させる」ための機能。
インデックスが存在しない場合は、どんなデータを検索する場合でも、必ず表の先頭から最後まで探し続けなければならない。
（この動作のことを、「テーブルスキャン」「全表走査」とよぶ。）

(2)インデックスの仕組み

内部的には、「ツリー(Tree)構造）」で作成される。
最上部を「ルート」ノード、中間部を「中間」ノード、最下部を「リーフ」ノードと呼ぶ。
データは「昇順」に並び替えれて、データの範囲によって枝分かれする。
インデックスには、「クラスター化インデックス」「非クラスター化インデックス」がある。

(3)DBの内部構造

「クラスター化インデックス」「非クラスター化インデックス」の仕組みを理解するにあたって、
DBの内部構造を理解しておくことが重要。
SQLServerのデータベースは、「データファイル(.mdf)」と「トランザクションログファイル(.ldf)」の２種類で構成される。
　
データファイルには、テーブルやデータ、インデックス、ビュー、ストアドプロシージャなどが格納されている。
内部的には「ページ」という8KBの大きさで区切られており、「ディスク入出力の単位」。
データファイル内の連続した８ページは「エクステント」と呼ばれ、テーブルやインデックスに割り当てられる領域の単位になる。
データが追加されるとエクステントが１つ追加され、そのあとに追加されるデータが連続した８ページに格納される。

(4)「クラスター化インデックス」「非クラスター化インデックス」

①「非クラスター化インデックス」

インデックス内はデータが昇順で並び替えられて、データの範囲によって枝分かれしている。
リーフノードには実際のデータへのポインタ(位置情報)が格納され、ポインタには「行識別子(RID：Row ID)」が使用されている。
インデックスページには、「データの値(インデックスを作成した列の値)」が格納されており、
検索時には、この値と検索条件に指定された値との大小関係によって、ツリー構造が走査される。

②「クラスター化インデックス」

非クラスター化インデックスは、リーフレベルには「実際のデータへのポインタ(RID)」が格納されるのに対し、
クラスター化インデックスの場合は、リーフレベルに「実際のデータそのもの」が格納される。
実際のデータをインデックス内へ格納するため、テーブル内で１つしか作成できない。
クラスター化インデックスを利用して検索することを「Clusterd Index Seek」という。

③クラスター化インデックスが存在する場合の「非クラスター化インデックス」の内部構造

クラスター化インデックスが作成されると、非クラスター化インデックスの構造が変更され、
リーフレベルへ格納されるポインタが「行識別子：RID」から「クラスター化インデックスの値」へ変更される。
実際のデータを探すことなく（RID Lookupすることなく）インデックスのSeekのみで検索が完了するため、検索が高速に実行できる。
（例）社員テーブルの「社員番号」列にクラスター化インデックスが作成され、「姓」列に非クラスター化インデックスが作成されている場合。
　「姓」と「社員番号」列のみを取得する際に実データを探す必要がないため、高速に処理が実行できる。

＜個人的メモ＞

やはり「処理に不要な列」は、無駄にSELECTしないほうが良い、ということ。
不要な列を取得するために実データにわざわざアクセスするのは、処理の無駄。

(5)カバリングインデックス（複合インデックス）

複数カラムをインデックスのキーに指定すると、「複合インデックス」になる。
例えば「user_id」「item_id」という２つのカラムをインデックスキーに指定するような場合。
利用したいデータが「user_id」「item_id」のみであった場合、「欲しいデータはインデックスツリーが保持している」ため、
実データ領域にアクセスする必要がなくなる。
このことを生かして「取得するデータをインデックスに全て含めてしまおう」という考えを、「カバリングインデックス」という。

作成時に「一番最初に指定した列」でツリー構造が作成されるため、
WHERE句の検索条件で指定される列を、一番最初に指定しておくようにする。
　
また、カバリングインデックスは、中間ページとルートページにも2つ目以降に指定した列の値が格納されるため、
その列データのサイズが大きい場合にはインデックスサイズが大きくなり、パフォーマンス低下につながるケースがある。
「付加列インデックス(includeオプション)」の場合は、中間とルートへ値を格納することなく、リーフのみへ値を格納することができる。

【SQL】

CREATE INDEX インデックス名
ON テーブル名(列1, 列2, …)

(6)付加列インデックス(includeオプション)

リーフノードのみに値を格納する。
それによりページジャンプを防ぐとともに、インデックスのサイズを抑えることができる。

【SQL】

CREATE INDEX インデックス名
ON テーブル名（列名）
INCLUDE（リーフへ含めたい列名1, 列名2, ...）

Ⅲ．実行計画の見方

(1)「実行計画」ってどうやったら表示できる？

【SQL】

SET STATISTICS PROFILE ON

(3)インデックスとテーブルへのアクセス（Index Seek、Index Scanなど）

https://use-the-index-luke.com/ja/sql/explain-plan/sql-server/operations
実行計画には、「インデックスとテーブルへのアクセス方法」が表れている。
SQLServerの用語は非常にシンプルで、以下の通りになっている。
・「Scan」：インデックス全体を読み込む。
・「Seek」：インデックスあるいはテーブルの指定された一部のみにアクセスするために、Bツリーや物理アドレス（RID)を使う処理。

①Index Seek、Clusterd Index Seek

Bツリーの走査に加えて、一致するエントリを探すのにリーフノードチェーンをたどる。

②Index Scan、Clusterd Index Scan

インデックスの全体、つまり全行を、インデックスの順番に沿って読む。

③Key Lookup(Clusterd)

クラスタ化インデックスから1行を取り出す。（Oracleの「INDEX UNIQUE SCAN」と同じ）

④RID Lookup(Heap)

テーブルから1行を取り出す。（Oracleの「TABLE ACCESS BY INDEX ROWID」と同じ）

⑤Table Scan

フルテーブルスキャンとして知られる処理。
テーブル全体つまり全行・全列を、ディスクに保存されている通りに読む。

(4)結合処理（Nested Loops、Hash Matchなど）

①Nested Loops

片方のテーブルから結果を取り出し、その結果をもう１つの各行に対して問い合わせて、2つのテーブルを結合する。
SQLServerではインデックスアクセスの後にテーブルデータを取り出す際にも、入れ子のループ処理を使う。
内部クエリを実行する際に、Bツリー走査が大量発生してしまうという弱点がある。

②Hash Match

結合の片方から候補となるレコードをハッシュテーブルにロードし、それを結合の片方のテーブルと突き合わせる。
ハッシュ結合のパフォーマンスを最適化するには、「ハッシュテーブルのサイズを小さくする」方法がある。
データベースがより少ないレコードしかハッシュテーブルに読み込まずに済むように、条件を追加することが考えられる。
また、「本当に必要な列だけSELECTする」ことで、ハッシュテーブルのサイズを小さくすることも可能。

③Merge Join

並び替えられた２つのリストを、ジッパーのようにマージする。

(5)ソートとグルーピング

①Sort

order by句に従って、結果をソートする。
この処理は、中間結果（パイプライン化されていないもの）をマテリアライズするために、非常に多くのメモリを消費する。

②Sort(Top N Sort)

order by句に従って、結果をソートする。
パイプライン化された実行ができない場合は、最初のN件のみを選択するクエリとして実行する。

③Stream Aggregate

group by句に従って、並び替え済みの結果セットをまとめる。
この処理は中間結果をバッファせず、パイプライン化して実行する。

④Hash Match(Aggregate)

レコードをグルーピングするのに一時的なハッシュテーブルを使う。
この処理では、データセットは事前にソートされている必要はないが、中間結果（パイプライン化されていないもの）を
マテリアライズするために非常に多くのメモリを必要とする。
出力は、一定のルールに沿って並べられているわけではないものになる。

(6)最初のN件のみを選択するクエリ

①Top

必要な行数が取り出せた時点で、内部的な処理を中断する。
Sortのような、パイプライン化されていない処理を途中で中断する場合の動作は、非常に非効率になる。

(7)制御フローとデータフロー

Ⅳ.クエリチューニング

１．「Index Scan」から「Index Seek」

「Index Scan」は、順番に沿ってインデックスを全部見ている状態。インデックスの意味を成していない。
「Index Seek」は、正しくBツリー走査をしている状態。

2．Key LookupやRID Lookupを排除する

3．カバリングインデックスと付加列インデックス(include)を使い分ける

・カバリングインデックス
「追加した列」も含めてキー順に並び替えられる。
列を更新すれば、列値の変更だけでなく、変更後の値に応じて「インデックスレコードの物理的な位置も変更」される。

・付加列インデックス(include)
　「追加した列」を更新した場合でも、「インデックスレコードの位置」を変更する必要はない。
（インデックスのキーとして追加されるわけではなく、リーフノードに「値」のみ付加されて格納されるため。）

　「追加したい列」が頻繁に更新される場合や、「追加したい列」を検索条件として指定することがない場合は、
　INCLUDE列として追加した方が更新時のオーバーヘッドもなく、メリットが大きい。

Ⅴ.ロックと読み取り一貫性

１．「ロック」の基礎知識

(1)ロックの種類

SQLServerのロックには、以下の2つがある。

①排他(eXclusive)ロック

あるトランザクションが実行している更新系のステートメント(UPDATE/INSERT/DELETE)によるデータ更新に対して、
他のトランザクションから一切アクセスできないようにするロック。
（「占有ロック」や「Write(書き込み)ロック」とも）
ロックは、『トランザクションが完了する』まで保持される。

②共有(Shared)ロック

あるトランザクションが実行している検索(SELECT)に対して、他のトランザクションから更新(UPDATE/INSERT/DELETE)が
できないようにするロック。
他のトランザクションから検索(SELECT)を実行することは可能。(他のトランザクションから検索されても、データに矛盾は発生しないため)
ロックは、『読み取りが完了する』まで保持される。

＜個人的メモ＞

SELECTに長時間かかってしまって長くロックをしてしまった場合、タイミングが悪いとデッドロックが発生してしまうことも。
SELECTでデッドロックが発生してしまった場合は、
　①ダーティーリードで問題ない場合は、ダーティーリードを指定
　②SELECTのパフォーマンス改善
などを検討する必要ありか。

(2)ロックの粒度

「ロックの大きさ」の単位は、以下のとおり。
　　・行（RID）　　　　　 ：行ロック。RIDは、「ROW ID（行識別子）の略
　　・キー（key）　　　　 ：インデックス内の行ロック。
　　・ページ（PAG）　　　：8KBの大きさ。
　　・エクステント（EXT）：連続した８ページ（64KB）
　　・テーブル（TAB）　　：テーブル全体
　　・データベース（DB）　：データベース全体
粒度の大きさはステートメントの内容によって変化する。
基本的には「行ロック（行単位でのロック）」だが、取得するデータが大量の場合には、
ページやテーブル単位でのロックが選択される。

ロックの粒度は、「ロックヒント（オプティマイザヒント）」を使用することで明示的に指定できる。
利用するには、ステートメント内のテーブル名の後ろに「WITH句」を指定する。

　例

UPDATE テーブル名 WITH(ROWLOCK)
SET ... WHERE　...

獲得されるロックの種類は、
　・UPDATE/INSERT/DELETEの場合　→「排他ロック」
　・SELECTの場合　→「共有ロック」
になる。複数のテーブルをJOINしている場合には、テーブルごとにロックヒントを指定する必要がある。

＜個人的メモ＞

「WITH(nolock)」を指定することで、ロックをかけずにSELECTすることができる。（=ダーティーリード）

(3)デッドロック

2つのトランザクションが、「お互いにロック待ちをしている」状態のこと。
SQLServerでは定期的（５秒ごとに１回）チェックを行い、「２つのトランザクションが完了しない状態になること」を防いでいる。
デッドロックを検知した際には、「どちらかのトランザクションをロールバック」することで、永遠に待ち続ける状態を回避している。
　　

＜個人的メモ＞

SQLServerでは「トランザクション全体」をロールバックしているが、Oracleでは「該当のステートメントのみ」ロールバックする。

２．「トランザクションの分離」と「読み取り一貫性」

(1)「トランザクションの分離」

各トランザクションが１つずつ直列に実行されるのであれば、データに矛盾は発生しない。
しかし実際は、同時に複数ユーザが接続し、複数のトランザクションが並列で実行される。
このように、「並列で実行されているトランザクション」を、直列実行されたときと同じように実行されている状態のことを、
「トランザクションが分離（isolation）された状態」とよぶ。

【トランザクション分離レベル】
データの一貫性が保たれるかどうか（データに矛盾が発生する可能性があるか）で４つのレベルが分かれる。
　　①READ UNCOMMITTED
　　②READ COMMITTED
　　③REPEATABLE READ
　　④SERIALIZABLE

【データの矛盾】
複数のトランザクションが同時実行された場合に起こりうるデータの矛盾には３種類ある。
①ダーティーリード
　　②反復読み取り不可
　　③ファントム読み取り

(2)ダーティーリードとREAD UNCOMMITTEDレベル

このレベルでは、UnCommitted（コミットされていない）データを読み取れるようになる。
排他ロックを無視してデータを読み取ることができるため、読み取り時のロック待ちは発生しない。
ただし読み取ったデータがロールバックされた場合は、データに矛盾が発生することになる。

排他ロックを無視してデータを読み取れることは、パフォーマンス上の大きなメリットになる。
ダーティリードによるデータの矛盾は、アプリケーションによっては許容範囲であったり、
運用ルールでカバーできたり、アプリケーション側の工夫でカバーすることができる。
ロック待ちが原因のパフォーマンス低下は、READ UNCOMMITTEDを利用することで解決することが多い。

なお、READ UNCOMMITTEDは内部的には「共有ロックをかけない」という動作をすることで排他ロックと競合しないようにし、
排他ロックを無視してデータを読み取れるようにしている。

【SQL】

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

【ダーティリードとwith(nolock)】
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
READ UNCOMMITEDを指定することは、トランザクション内のすべてのSELECTステートメントで
すべてのテーブルに対して「NOLOCK」を設定するのと同じ効果がある。

(3)反復読み取り不可（Non Repeatable Read）

デフォルトのRead Uncommittedレベルでは発生する可能性のあるデータの矛盾。
一度読み取ったデータが他のトランザクションによって更新され、２度目に読み取った時に異なるデータになっているというもの。
これは、SELECTステートメントによる読み取り時は、読み取り完了後に共有ロックが解放されるため発生する。

「Repeatable Read」（反復読み取り可能）という分離レベルを使用することで回避できる。
内部的には、「トランザクションが完了するまで共有ロックを保持する」という動作をすることで実現している。
　　

(4)更新ロック「悲観的排他制御」

更新ロックは、「更新ロックをブロックできる」という特徴を持つ。
SELECTステートメントの実行時に「WITH(UPDLOCK)」を指定することで、読み取り時にかけるロックを「更新ロック」へ変更することができる。
（Oracleの「SELECT … FOR UPDATE」に相当する）
ただし、待ちが発生するという意味で「同時実行性が下がる」という特徴がある。

(5)「楽観的同時実行制御」

「更新されたかどうかチェックする列」をテーブルに追加し、更新時にチェックすることで、
「自分が更新する前に他の人が更新していないか」を判別することができる。
（例）SELECT時に取得した「更新タイムスタンプ」を、UPDATE時に変更されていないことをチェックすることで、他の人がレコード更新を行っていないことを判別できる。

(6)テーブルスキャンによるロック待ち

テーブルスキャンで全レコードを検索しようとしているときに、1件でも排他ロックがかかっているレコードがあると、
「待ち」が発生してしまうことがある。
＜回避する方法＞
①適切なインデックスを作成し、テーブルスキャンが発生しないようにする
②テーブルスキャンしなくてすむように、WHERE句の条件式を工夫する
③トランザクションを出来る限り短くし、排他ロックを早く解放できるようにする
　　④Repeatable ReadとSerialize分離レベルを出来るだけ避ける
　　⑤更新ロック（UPDLOCK）をなるべく避け、楽観的同時実行制御を実装する
　　⑥NOLOCKヒント（ダーティリード）を使用する

(7)読み取り一貫性

http://higus70.seesaa.net/article/145227121.html
排他ロックがかかっているデータを読み取れるようにする、Oracleではお馴染みの機能。
「SELECTステートメントを発行した時点」または「トランザクションの開始時点」でのデータを読み取れるようにすることを保証する機能。
排他ロックがかかっている更新中（未コミットの確定していない）のデータを参照させないようにし、
更新前のデータ（その時点での正しいデータ）を参照させることで、一貫性を保つという動作。

＜READ_COMMITTED_SNAPSHOTオプション＞
SQLServerでは、「READ COMMITTED」がデフォルトの分離レベルとして設定されているが、
このオプションのON/OFFによって、異なる動作をする。
　　①OFFの場合
　　　　SELECT文によるデータ参照時、以下の動作をする。
　　　　　・共有ロックを取得する
　　　　　・参照が完了すると、共有ロックを解放する
　　　　そのため、参照するデータが他のトランザクションで更新中の場合は、更新処理で取得した排他ロックが参照しようとするデータにかかっており、
　　　　「共有ロック取得待ち」になる。
　②ONの場合
　　　　データ変更する際に、変更前のデータ（最新のCOMMIT済みデータ）をtempdbにコピーし、更新を実行する。
　　　　この更新中〜COMMITまでの間に発生したSELECT文は、（実テーブル行には）排他ロックがかかっているため、tempdbを参照し結果を返す。
　　　　OracleのUNDOセグメントに近い。

Ⅵ.色々なTips

１．インデックスの断片化

(1)「断片化」とは

「リーフページが連続的ではなく、断片的に格納された状態」。
物理的に連続して格納されている場合は「先読み（先行読み取り）機能」が効率良く働き、パフォーマンスよくデータ取得ができる。
断片化により物理的に離れたページにデータが存在すると、インデックスで検索するときのパフォーマンスが落ちる。

(2)断片化の調査： dm_db_index_physical_stats

【SQL】

SELECT * FROM sys.dm_db_index_physical_stats
(データベースID, テーブルID, インデックスID, パーティション番号, 'スキャンモード')

例：

SELECT avg_fragmentation_in_percent, index_id, index_level, page_count
FROM sys.dm_db_index_physical_stats
(DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL, 'DETAILED')

(3)断片化の解消

3つの方法がある。
・「インデックスのオフライン再構築」
内部的には、「新しい領域へインデックスを再作成し、古いインデックスを削除する」ことで、断片化を解消する。
よって、「インデックス再構築中はインデックス全体がロックされる」ため、
別のトランザクションからインデックスに対してアクセスすることができない。

【SQL】

ALTER INDEX インデックス名
ON テーブル名 REBUILD

・「インデックスのオンライン再構築」（Enterpriseエディションのみ）
再構築中に、ユーザがアクセスすることができる。

【SQL】

ALTER INDEX インデックス名
ON テーブル名 REBUILD WITH ONLINE = ON

・「インデックスの再編成（REORGANIZE）」
リーフページの断片化のみを解消し、再編成の実行中もユーザがアクセスすることができる。

【SQL】

ALTER INDEX インデックス名
ON テーブル名 REORGANIZE

(4)「再構築」と「再編成」の違い

「再編成」は、同じ領域を使用して、それぞれのページを比較して並び替えを行うことで断片化を解消する。
対して「再構築」は、新しい領域にインデックスを再作成する。
おおまかな指針は、「断片化の割合が30%未満なら”再編成”、それ以上なら”再構築”」。

２．ロックの状況確認

(1)「利用状況モニター」

「ロック待ちになっている接続（プロセス）」を簡単に調べることができる。
　　・「待機の種類」：何をしようとして待機しているか？
　　・「待機リソース」：ロックをかけようとしている先が、今どういう状況になっているか？
　　・「ブロック元」：排他ロックをかけている側のセッションIDを確認できる

(2)「レポート機能」

標準レポートの「ブロックされているすべてのトランザクション数」レポートを表示すると、
「現在ブロックしているトランザクション」を基準に、「そのトランザクションにブロックされているステートメント」を表示できる。

(3)SQLステートメントでのロック状況の確認（dm_tran_locks）

【SQL】

SELECT * FROM sys.dm_tran_locks 

・request_mode：「S」（共有）、「X」（排他）
・request_status：「GRANT」（獲得）、「WAIT」（待ち）

参考URL

SQLServer と Oracle の異なるロック思想
[RDBMS][SQL]トランザクション分離レベルについて極力分かりやすく解説
不整合が起きてはならない場合、トランザクションはシリアライザブル