スポンサーリンク

SQLServerで、IISからのDB処理だけが遅い現象が、どうしても解決しない場合は、ヒント文を使用する

C#

SQLServerで時々発生する、IISや関連サービスからのDB処理だけが、極端に遅くなる現象に対応する為、4つのサイクルを回して来たのですが、運用時間帯に、一部の処理で、IISや関連サービスからのDB処理だけ、極端に遅くなる現象が再発してしまいました。

【4つのサイクル】
1、チューニングアドバイザーによる、インデックスと統計情報の抽出と追加。
2、テーブルの統計情報を、週次で更新。
3、ストアドとファンクションを、日次のジョブで毎朝リコンパイル。
4、IISと関連サービスを、日次で毎朝再起動。

【環境】
WEBサーバー:Windows Server 2008 R2 SP1
DBサーバー:Windows Server 2008 R2 SP1、SQLServer 2008 R2 SP2 CU5
WEBサイトは.Net4.0で構築

IISや関連サービスからだと数時間かかっている処理でも、SQLServer Management Studio から実行すると数秒で終わり、関連するストアドとファンクションをリコンパイルすると、IISや関連サービスからの処理も数秒で終わるようになるので、ストアドとファンクションがキャッシュしている実行プランに、問題があるのは確実なのですが、4つのサイクルでは解消しませんでした。

最終手段として、ストアドとファンクション内のSelect文が使用するインデックスを、ヒント文で固定する事で、解消することが出来ました。

具体的には、Management Studioから実行すると速い環境で使用されているインデックスを調査し、ヒント文でそのインデックスを固定する事で、どの環境でも必ず同じインデックスが使われるようにします。

【具体的な手順】
1、SQLServer Management Studio でSQLを右クリックし「推定実行プランの表示」を選択する。
2、表示された実行プランを右クリックし「実行プランのXMLの表示」を選択する。
3、表示されたXMLの中から「Index="」が有る行を検索する。
 XMLからだと、どのSelect 文のどのテーブルが、どのインデックスを使っているか、簡単に
 見つける事ができます。
 プライマリキーは無視して構いません。
4、見つかったインデックスは、ヒント文( WITH (index( )として、FROM句のテーブルの後に
 宣言し、そのSQLで使用されるインデックスを固定します。

ストアドやファンクションそれぞれの中で、各テーブルの別名を、ユニークになる名称にしていると、より簡単に対象のインデックスを見つける事ができます。

【ヒント文の例】
・1つのテーブルに1つのインデックスを指定する場合。
FROM (テーブル名) as (テーブル別名)
WITH (index((インデックス名1)))

・1つのテーブルに複数のインデックスを指定する場合。
FROM (テーブル名) as (テーブル別名)
WITH (index((インデックス名1),(インデックス名2)))

※ヒント文に指定するインデックスは、NONCLUSTURED INDEX のみでよく、プライマリキーを含む CLUSTURED INDEX  を指定する必要はありません。

SQLServerのインデックスと統計情報については、5つのサイクルを回す事で、パフォーマンスの問題を解決できそうです。

【5つのサイクル】

1、チューニングアドバイザーによる、インデックスと統計情報の抽出と追加。
2、テーブルの統計情報を、週次で更新。
3、ストアドとファンクションを、日次のジョブで毎朝リコンパイル。
4、IISと関連サービスを、日次で毎朝再起動。
5、IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する。

 

【関連記事】
チューニングアドバイザーによる、インデックスと統計情報の抽出と追加
テーブルの統計情報を、週次で更新
ストアドとファンクションを、日次のジョブで毎朝リコンパイル
IISと関連サービスを、日次で毎朝再起動。
IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する
SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする
SQLServer 2005 インデックス チューニング ライフサイクル
6ヶ月以上使われていないインデックスを抽出するSQL

コメント

タイトルとURLをコピーしました