開発のあいまに

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

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

【４つのサイクル】
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や関連サービスからの処理も数秒で終わるようになるので、ストアドとファンクションがキャッシュしている実行プランに、問題があるのは確実なのですが、４つのサイクルでは解消しませんでした。

最終手段として、ストアドとファンクション内の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つのテーブルに複数のインデックスを指定する場合。
FROM （テーブル名） as （テーブル別名）
WITH (index(（インデックス名１）,（インデックス名2）))

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

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

【５つのサイクル】

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

 

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

SQLServerで、ストアド、ファンクションをリコンパイルしても、IISからのDB処理だけが遅い場合は、IISを再起動する。

SQLServerで、IISからのDB処理だけが遅い場合は、ストアド、ファンクションをリコンパイルするを繰り返しても、「IISからのDB処理だけが遅い」現象が、解消されないことがありました。

その時は、IISを再起動する事で、解消しました。

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

 

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

SQLServerで、IISからのDB処理だけが遅い場合は、ストアド、ファンクションをリコンパイルする

テラバイトクラスのDBシステムを扱っていると、データ量が増えるたびに、特定のSQLが極端に遅くなることがあります。

その時に、遅くなったSQLの実行プランを確認すると、インデックススキャン（Index seek）のコストが跳ね上がり、SQL全体のコストの100%近くを、インデックススキャンが占めるように変化しています。

インデックススキャンのコストは、SQL全体で20%未満になっているのが、妥当な値です。

極端に遅くなっている、もしくは、コストが高くなっているSQLは、SQLServerのチューニングアドバイザーにかけると、インデックスと統計情報が抽出されるので、それを適用する事で解決できます。
※SQLが遅くなっているサーバーで、チューニングアドバイザーを実行せず、別のサーバーでチューングアドバイザーにそのSQLをかけると、必要なインデックスと統計情報が抽出されない事があるので、そこは注意が必要です。

テラバイトクラスのDBシステムを1年以上運用していて、今回初めて、必要なインデックスと統計情報を追加しても、IISからのDB処理だけ処理性能が改善しない、という現象に遭遇しました。
Management Studioからだと、遅くなっているSQLの実行プランは改善され、実行時間も1秒程度なのに対し、WEBサイトからそのSQLを実行すると、5分たっても結果が返って来ませんでした。

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

色々試した結果、WEBサイトから実行した場合のみ遅い現象が発生したら、ストアドのリコンパイルが有効だという事が分かりました。ユーザー定義関数を利用している場合は、関数のリコンパイルも必要です。

ストアドに実行プランをキャッシュする、こちらのアーキテクチャにバグがあって、IIS側からアクセスされた場合に、古い実行プランを使い続けていたのが原因でした。
http://msdn.microsoft.com/ja-jp/library/ms181055(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms191007(v=sql.105).aspx

そういえば、SQLServerはIISのキャッシュを、別扱いするという技術資料を、以前どこかで読んだ気がします。

毎日のように、インデックスと統計情報を追加しているシステムなので、再発防止として、インデックスと統計情報を追加する頻度の高いテーブル、ストアド、ユーザー定義関数は全て、日次で夜間に、sp_recompile を実行するようにしました。
http://msdn.microsoft.com/ja-jp/library/ms181647.aspx

 

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

 

Silverlight4 開発で使える無料のChart（グラフ）機能

Microsoft Visual Studio 2010 Service Pack 1 の Silverlight4開発で、無料のChart（グラフ）を利用したい場合、以下のツールを追加インストールするとことで、グラフを簡単に実装する事ができます。

Microsoft Silverlight 4 Tools for Visual Studio 2010

Silverlight 4 Toolkit

　

インストール後、Microsoft Visual Studio 2010　を起動して、MainPage.xaml　を開き ツールボックス を表示すると、「Chart」コントロールが追加されていることを確認できます。
後は、「Chart」コントロールをMainPage.xaml の画面へドラッグ＆ドロップするだけです。

SQLServer 2005 インデックス チューニング ライフサイクル

１、コーディングされているSelect文を全て、Management Studio のクエリエディタに貼り付ける。

２、ツールバーの「データベース エンジン チューニング アドバイザでのクエリの分析」アイコンをクリックして、「データベース エンジン チューニング アドバイザ」画面を開く。

 

３、「データベース エンジン チューニング アドバイザ」画面では、クエリエディタに記入したSQLのチューニング設定が初期値として入力済みになっている。

４、必要であれば「チューニング オプション」の内容を変更。

５、ツールバーにある「分析の開始」アイコンをクリックして開始。

６、分析対象のデータベースサイズが小さ過ぎたり、大き過ぎたりすると、「ワークロードを使用しています」タスクでエラーになる。その場合は、「チューニング オプション　⇒　詳細設定オプション　⇒　推奨インデックス用の最大領域を定義する」にチェックを付け、実際のＤＢサイズを入力し、再度「分析の開始」アイコンをクリック。

７、分析結果として登録した方が良いインデックスが、「推奨インデックス」にリストアップされるので、全てのインデックスと統計情報を、クエリエディタ画面で実行する。

８、インデックスと統計情報を追加したら、その対象となったテーブルに、sp_recompile を実行する。

InsertやUpdateが遅くならないようにインデックスを張らない方が良いものはないか気になりますが、実験した結果では、推奨される全てのインデックスを張った場合、InsertやUpdateは少し遅くなりますが、Select文は圧倒的に速くなり、トランザクション全体の処理時間が改善するので、何も考えずに全て適用してしまうのが一番です。

ただ、テラバイトクラスのDBの場合は、1つのインデックスを追加しただけで、100GB消費することは有りますので、ディスク容量は考慮した方が良いです。

また、不要なインデックスが増えないように、6ヶ月以上使われていないインデックスを抽出するSQLを定期的に実施た方が良いです。

それと、インデックスと統計情報を追加しても、ストアドが古い実行プランをキャッシュしたままになっている事がありますので、SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする こともにも、注意が必要です。

 

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