開発のあいまに

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

 

bing mapを使い、住所から緯度経度を取得する、サンプルC#ソース

string str住所 = "東京都新宿区西新宿2-8-1";
double f緯度 = 0;
double f経度 = 0;
C01MAP.Get緯度経度(str住所, ref f緯度, ref f経度);

public static void Get緯度経度(string strAdress, ref double dblLatitude, ref double dblLongitude)
{
 string BingMapsKey = "INSERT_YOUR_BING_MAPS_KEY";
 string strURL = string.Format("http://dev.virtualearth.net/REST/v1/Locations?query={0}&key={1}&c=ja-jp&o=xml", strAdress, BingMapsKey);
 XmlDocument geocodeResponse = GetXmlResponse(strURL);

 XmlNamespaceManager nsmgr = new XmlNamespaceManager(geocodeResponse.NameTable);
 nsmgr.AddNamespace("rest", "http://schemas.microsoft.com/search/local/ws/rest/v1");
 XmlNodeList locationElements = geocodeResponse.SelectNodes("//rest:Location", nsmgr);
 string latitude = null;
 string longitude = null;
 if (locationElements.Count == 0)
 {
latitude = "0";
longitude = "0";
 }
 else
 {
XmlNodeList displayGeocodePoints = locationElements[0].SelectNodes(".//rest:GeocodePoint/rest:UsageType[.='None']/parent::node()", nsmgr);
latitude = displayGeocodePoints[0].SelectSingleNode(".//rest:Latitude", nsmgr).InnerText;
longitude = displayGeocodePoints[0].SelectSingleNode(".//rest:Longitude", nsmgr).InnerText;
 }

 dblLatitude = Convert.ToDouble(latitude);
 dblLongitude = Convert.ToDouble(longitude);
}

private static XmlDocument GetXmlResponse(string requestUrl)
{
 Trace.WriteLine("Request URL (XML): " + requestUrl);
 HttpWebRequest request = WebRequest.Create(requestUrl) as HttpWebRequest;

 using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
 {
if (response.StatusCode != HttpStatusCode.OK)
 throw new Exception(String.Format("Server error (HTTP {0}: {1}).", response.StatusCode, response.StatusDescription));

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(response.GetResponseStream());

return xmlDoc;
 }
}

SQLServerの最新パッチ

SQLServer 2008 R2 SP2 の最新パッチがどれなのかは、このブログで確認できる。2013/2/24時点の最新パッチ（累計的な更新プログラム）はCU#5。

SQL Release Services Blog

Windows Server で使えるコマンドリファレンス（マイクロソフト公式）

Windows Server で使えるコマンドのリファレンスを、マイクロソフトが公開しています。本を買う必要が無くなりました。

Windows Server コマンド ライン リファレンス

 

System.Runtime.Serialization エラーが発生したら

Windows Server 2008 R2 SP1　x64 に .NET Framework 4.0 をインストールし、IIS上に.NET Framework 4.0 のWEBサイト構築した後、WCFにアクセスすると、こちらのエラーが発生する事があります。

Could not load file or assembly 'System.Runtime.Serialization, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e, Retargetable=Yes' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

このエラー、もしくは「Could not load file or assembly」関連のエラーが発生する場合は、NDP40-KB2468871-v2-x64.exe　をインストールする事で解決できます。