プロセス数増加によるスローダウン現象

Question

■環境

Windows2003 Server SP2 + SQL Server 2005 SP2 Enterprise Edition (64-bit)　クラスタ環境

■現象

どうしても解決できない事象があります。。。

何がトリガーになっているのか分かりませんが、時々、「CLR_MONITOR」や「CMETHREAD」の待機イベントで

プロセス数が徐々に増えていき、DB自体が遅延してしまいます。（平均、200程度のものが1,000～2,000）

ここで指しているプロセス数は、sysprocessesや、利用状況モニタのspid数とお考えください。カウンタで言うと、UserConnectionsです。

毎日や、決まった時間に起こりませんので、いつ発生するか全く読めません。

こうなると、結構お手上げで、Webサーバの再起動や、最悪はクラスタ切替などをしないと復旧しません。

アプリ側ではCLRプロシージャー関数を呼び出していますので、絡んでいるのは間違いないと思います。

■気になる点

プロセス数が溜まっている時、サーバ全体のメモリ量（タスクマネージャー上のコミット）が増えています。

こちらは増えたから溜まるのではなく、セッション数が多くなったことによる増加分だと考えていますが、認識間違っていますでしょうか？

SQLServerエージェントを再起動すると（レプリケーション等も使用していることから）全体のメモリ容量が減り、処理待ちが流れたりします。

最初はスワップしているような値も見受けられたので、物理メモリ容量が足りていないと考えていたのですが、

32G→46G程度に増やしコミットが37Gで余裕があるにも関わらず同様の現象は起こっております。

尚、Max Server Memoryは現在は32G程度にしています。

■原因

以上のことから推測すると、

・バッファプール以外の、Memory to Learve領域が足りていない（現在は、既定の256MBです。）

・下記等のバグ

http://support.microsoft.akadns.net/kb/940945/ja

などが考えられますが、他に何か考えられますでしょうか？

SP適用や、Memory to Leaverを増やす余地はあるのですが、決定的な理由がないとサービス停止がしずらいのです。

何か根拠となるような検証・調査方法があれば教えて頂きたいです。

Answer 1

CLR_MONITOR待ちになっているのであれば、SQLCLRストアドプロシージャや関数などの中でMonitorクラス(http://msdn.microsoft.com/ja-jp/library/system.threading.monitor(v=VS.80).aspx)が使われていて、それの待ちが長くなっているのではないかと思います。何を待っていて、なぜそのように長くなっているのかを確認してみてはいかがでしょうか。

CMEMTHREAD待ちについては、メモリアロケーションにおけるスレッド間の同期なので、調べるのも簡単ではないと思いますが、http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspxに解説があります(英語ですが)。

セッション数が増えるのは、Webサーバーは通常SQL Serverへのセッションをプールして使いまわしていますので、使いまわせるセッションがなくなり新たなセッションを確立しているためではないかと思います。CLR_MONITOR待ちなどによってSQL Server側で実行している処理に時間がかかるようになれば、Webサーバーが持っていたすべてのセッションが使用中の状態になり、Webサーバーは新しい要求に応えるために新しいセッションを確立する必要が出てくることになります。その結果、SQL Serverへのセッション数が増えるという感じではないでしょうか。

Answer 2

>MKⅢ様

ご回答ありがとうございます。

MONITORクラスについては把握していたのですが、開発者に確認しても使用している認識がなかったのです。

この待機イベントがあるということ＝何かしら使用しているという事は確実なのでしょうか？

詳しい記述については、私も理解していないところがあるのですが、確かにSystem.Threadingを呼んでいる記述はありませんでした。

using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

などが使用されてそうです。（こちらのカテゴリではないかもしれませんが・・・）

後者は、ご指摘の通りの動きで増えていくのだと思います。

Answer 3

フォーラム オペレーターの星 睦美です。
京 さん、投稿ありがとうございます。

質問の事象を解決するためには詳しい状況を調査していく必要がありそうですが、MKIII さんからの情報がトラブルシューティングのひとつになるのではと思います。
今回は私から[回答としてマーク] させていただきました。

日常的に事象が発生してお困りの場合はよろしければマイクロソフトのサポート窓口（有償）へのお問い合わせを検討いただければと思います。

それでは今後ともフォーラムをよろしくお願いします。

---

日本マイクロソフト株式会社　フォーラム オペレーター　星 睦美