SQLSERLVER 処理におけるフリーズの原因調査方法について

Question

SQLSERVERをＤＢとして膨大な量のデータを処理するシステムの機能があるのですが、処理中にたまにいつも決まった箇所でマシン全体がフリーズしてしまう現象が発生しております（無限ループやエラーによる終了ではありません）。発生頻度は数ヶ月に一度です。

この理由が何か、何か不足しているのか、を調べ改善案を見つけたいのですが何を調査したら良いでしょうか。

正直右も左も分からない状態です。

とてもざっくりとした質問内容で申し訳ありませんがお教えいただけますと助かります。

何卒よろしくお願いいたします。

Answer 1

私が調査するとしたら、ボトルネックの調査→対応という流れになります。

ボトルネックの調査では一次切り分けとして、
・（開発した）アプリの問題？
・DBサーバーの問題？
→CPU？Memory？Strage？Network？

この切り分けでSQL Serverのチューニングで対応可能なのか、ハード的な増強が必要なのかを検討します。ハード云々の前にとりあえずチューニングでトライ＆エラーすることが多いかと思います。
CPUやメモリやStrageのIOの問題があったとしてSQL Serverのチューニングで改善できることは多いです（もちろん、ケースバイケースですが）。

まず、整理しておきたいのはサーバーの物理構成。
・サーバーの物理的なスペックと、使っているOSやSQL Serverのバージョンは？
・DBサーバーはクラスターなど組んでいますか？
・Strageはどのような構成ですか？
・DBサーバーはSQL Serverしか動いていない構成でしょうか？
（「Webサーバーも兼ねてIISが動いている」とか、「バッチを配置していてバッチ処理も同様に動いている」でCPU等のリソースを食っているか？という意味です）

という情報を整理したうえで、調査をはじめます。
SQL Serverが載っているサーバー自体のボトルネック調査です。
パフォーマンスモニタをとりましょう（サーバーのバージョンがわからないので、とりあえずなリンクです↓）。

http://technet.microsoft.com/ja-jp/library/cc749115.aspx

パフォーマンスモニタでほしいのは、問題が発生したときの情報なので、平常時の情報をとってもあまり意味が無いです。
常時パフォーマンスモニタをしかけることをご検討ください。
（多少のリソースをくいます。CPUの10%程度ですがケースバイケース、動かして確認必要です。私は、いつもリモートで取得しています）

カウンターはたくさんあり、何を取得するかを言い出すと多量になってしまうのですが、代表的なところだけあげておきます。
CPUについて、 
・Processor:　%Processor Time
・Process: %Processor Time(sqlserver)
メモリについて、
・Memory:Available Mbyts
ディスクでは、
・Physical Disk: Disk Bytes/sec
・Physical Disk: Disk Read Bytes/sec
・Physical Disk: Disk Write Bytes/sec
SQL Serverのカウンターも取りましょう...

昔調査をしていたころは100項目くらいとってました・・・が最小限でもきりわけはできます・・・。
パフォーマンスモニタを取得することで、どこに問題があるかを可視化できます。（何が問題かは別）

それと合わせて、
SQL Serverの動的管理ビューで情報を取得しましょう（リンクは、SQL Server2012の情報）。

sys.dm_os_wait_stats
sys.dm_exec_query_stats

※注意として、動的管理ビューから取得する統計情報はSQL Serverのインスタンスが再起動してからの情報になるので、フリーズか何かでインスタンスを再起動した後に取得しても意味がありません。

ここで得た情報を元に、対応をすることになります。対応は、情報次第なので明確なことはいえませんが、

経験上よくあるSQL Serverのトラブルパターンであれば、処理中にtempdbがいっぱいになってしまう点や、クエリの実行でディスクIOが多くなって遅延する等で、SQL Serverの設定やクエリチューニングで改善する場合が多かったです。サーバーでのSQL Server以外の処理がリソースを食ってフリーズってのもありますが・・・。

上記の情報は一部にすぎないので、まずは「パフォーマンスモニタ」や「動的管理ビュー」をご自身で確認されてから、調査をしてみてください。

私がいつも参考にしていた情報源もはっておきます。

Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ

Answer 2

「フリーズ」というのが、操作を受け付けなくなって電源の再投入しかないような状況を指すのか、しばらく時間がたつと復帰してくる状況をさすのか、どういう状況でしょう？
再投入しかないような場合はログ取得などがしづらいので調査方法が変わってきてしまうので、そのあたりが曖昧ですと話が発散してしまうので、もう少し詳細な状況を記載して頂けると話がしやすいかと思います。

あと、デフォルトで記録されているログとして以下のようなものがありますので、このあたりに時間的に関連がありそうなものが無いか確認する、というのが第一歩としてはよろしいかと思います。
・OS のイベントログ (管理ツールのイベントビューアーなどで確認できます)
・SQL Server のエラーログ (SQL Server Management Studio のオブジェクトエクスプローラーで管理下にある SQL Server ログなどで確認できます)
なお、それぞれのバージョンによって操作方法や表示が変わるので、上に記載した手順などは必ずしも一致しませんので、その点ご留意ください。
そのあたりが気になるようであれば、バージョンも記載ください。

---

MCITP（Database Developer/Database Administrator）

Answer 3

フォーラム オペレーターの星 睦美です。
田中二真 さん、こんにちは。

BEACHSIDE76 さんとnagino - 引退エンジニア さんの返信を参考にしていただけたのではないかと思います。

こちらの回答をフォーラムのほかのユーザーにも参考情報として共有したいと思いますので、今回は私から[回答としてマーク]させていただきました。もし引き続き質問がありましたら、遠慮なく[回答としてのマークを解除]して返信できます。
(フォーラムではユーザー同士の情報交換が活発に行われるように、役立つ回答には投稿者からの[回答としてマーク] をお願いしています。)

では今後ともSQL Server フォーラムをお役立てください。

---

フォーラム オペレーター　星 睦美 - MSDN Community Support

Answer 4

回答が遅れて申し訳ありません。ご回答頂きありがとうございます！

フリーズと言うのは本当に名前の通りでリモートでアクセス不可、外部からftpで伝送されるテキストファイルを受信できない状態で、しばらくしても復帰する見込みが無い状態です。正直原因が不明で調査法もわからず発生するたびにリカバリをしなくてはなりません・・・

SQLサーバーのインストールされているドライブ（c)の空き容量が1G切っていて検索結果が大きくなるとクエリが空き容量エラーで止まってしまうような現状ですが明確な原因の根拠となるものが無いんです。

エラーログ入手したんですが結局どこみていいかわからなくて原因は不明です・・・

Answer 5

回答が遅れて申し訳ありません。

質問内容が大変抽象的でしたが方向性をお教えいただきありがとうございます！

上記情報を参考に調査を進めたいと思います。

「パフォーマンスモニタ」や「動的管理ビュー」についてですが本番環境の残り容量が1Gを切っている上にメモリも大変少ないのですが実行を掛けても大丈夫でしょうか。現在の状態でも裏でDBサーバーのフルバックアッププラス日々の処理ということでスペックの低いサーバーの動作自体が重く悩んでおります。

Answer 6

SQLサーバーのインストールされているドライブ（c)の空き容量が1G切っていて検索結果が大きくなるとクエリが空き容量エラーで止まってしまうような現状

との事ですが、Databaseファイル（実DB及びtempDB）もCドライブでしょうか？

また、復旧モデルが何に設定されているかも気になります。

もしも全てがCドライブで復旧モデルが完全だったりすると、temp及びldfの肥大化でハードディスクの空きが枯渇してるのかもしれません・・・