SQL Server のクエリチューニングをする際に参考になる情報のメモを。
SQL Server クエリのパフォーマンスを最適化する
クエリのチューニング
実行プランを読む – 基本編 (その 1)
実行プランを読む – 基本編 (その 2)
実行プランを読む – 基本編 (その 3)
どうする? SQL Server のクエリ パフォーマンスが低下した!
クエリチューニングの取り掛かりとしては SlideShare の以下の情報も参考になります。
SQL Server Query Tuning Tips – Get it Right the First Time
Microsoft SQL Server Query Tuning
クエリチューニングの考え方ですが、大きく分けると、
- クエリ自体を改修することで実行効率を向上させる
- クエリは改修せずに実行効率を向上させる
の 2 パターンがあるかと思います。
前者はクエリを書き換えることで実行効率を向上させるものになります。
データの取得方法自体を変更することで、実行効率を向上させるケースとなりますが、大幅な改修が入ることが多く、改修前と同等の実行結果を保証するのが難しいことが多々あるかと思います。
そのため、長期の改善としてクエリ改修が実施されるケースが多いかと。
# ストアドプロシージャ化もこちらに入るかと。
後者については、クエリは改修 (または小改修) せずに、クエリの実行効率を向上させるものになります。
こちらについてはインデックスチューニングが該当するかと。
インデックスチューニングとしては
- Scan 操作を Seek 操作にするためのインデックス設定
- Lookup を省略するためのインデックス設定
- カバリングインデックス
- 付加列インデックスを用いたカバリングインデックス
- インデックス付きビュー
があるかと思います。
これらはインデックスを設定することで検索効率を上げるアプローチになり、基本的には既存のクエリの改修は行わずに実行プランを変更させます。
基本的にはインデックスを付与することで対応を行いますので、検索の効率と引き換えに、データ更新系のオーバーヘッドが高くなります。
また、インデックス付きビューについては、ビューのインデックスの解決 の以下の動作を使用した場合が、クエリの変更を伴わずに検索効率を向上させることになります。
インデックス付きビューは SQL Server のどのエディションでも作成できます。SQL Server Enterprise では、クエリ オプティマイザによる判断でインデックス付きビューが自動的に使用されます。その他のエディションでインデックス付きビューを使用するには、NOEXPAND テーブル ヒントを指定する必要があります。
この動作ですが、以下のようなインデックス付きビューを作成します。
CREATE VIEW IDX_VIEW WITH SCHEMABINDING AS SELECT C_CUSTKEY, N_NATIONKEY, O_ORDERKEY FROM dbo.CUSTOMER INNER JOIN dbo.ORDERS ON CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY INNER JOIN dbo.NATION ON CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY GO CREATE UNIQUE CLUSTERED INDEX CIX_IDX_VIEW ON IDX_VIEW (C_CUSTKEY,N_NATIONKEY,O_ORDERKEY) GO
このようなインデックス付きビューを作成した場合、Enterprise Edition では、
SELECT COUNT(*) FROM dbo.CUSTOMER INNER JOIN dbo.ORDERS ON CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY INNER JOIN dbo.NATION ON CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY GO
というような、インデックス付きビューを明示的に指定していないクエリを実行した場合に、インデックス付きビューを使用したほうがよいと判断されれば自動的にインデックス付きビューを使用してくれます。
この場合はクエリの改修は必要がなくなります。
クエリの小改修を伴うものとしては、
- JOIN のヒント句
- インデックスヒント
- OPTION の指定 (MAXDOP や FORCESEEK)
- RECOMPILE ヒントによる実行時引数に依存しないプランの作成
- プランガイド
# プランガイドは改修を伴わない方に入れてしまってもよいかもしれないですね。
があるかと。
これらは、クエリにオプションを設定する必要がありますので、クエリの小改修が必要となります。
あとはデータメンテナンスとして、
- インデックスの再構成/再構築
- 統計情報の最新化
があるかと。
インデックスの再構築時には 100% のデータサンプリングで統計情報が作成されますので、定期的に再構築を実施している場合には、統計情報の最新かも行われていることになります。
他にもいろいろとあるかと思いますが、クエリチューニングの取り掛かりとしてはこれらから始めてみるとよいかと。