SQL Server の正常性のチェック
SQL Server 2012で新しくなった正常性チェックでは、sp_server_diagnosticsから返されるコンポーネントごとの正常性の状態をIsAliveがチェックします。このsp_server_diagnosticsから返された状態をユーザーが確認するには、新しく追加された診断ログを使用します。診断ログは既定ではSQL Serverのエラーログが出力されているフォルダに出力されています。ファイル名は次のとおりです。
<ノードのコンピュータ名>_<インスタンス名>_SQLDIAG_<番号>_<時間>.xel
このログ ファイルは拡張子がxelとなっていることからおわかりのように、拡張イベント形式のファイルです。そのため、ファイルを参照するにはSQL Server Management Studioやsys.fn_xe_file_target_read_fileを使用します。拡張イベントについてはこの連載の「トレースはもはや古い？SQL Server 2012 の拡張イベント」をぜひご参考にしてください。
次の図はこのファイルを SQL Server Management Studioから開いた場合の図です。
診断ログにはコンポーネントごとに1行のイベントが出力されています。
イベントデータは次の表のとおりとなります。イベントデータのstate列が3の場合、正常性状態はエラーを表します。
| 列 | 概要 |
| component | コンポーネント名 |
| component_type | コンポーネントの種類（現在は instance のみ） |
| creation_time | このイベントが生成された時間 |
| data | 診断データ |
| failure_condition_level | 現在の failure_condition_levelプロパティの設定値 |
| node_name | ノード名 |
| state | 正常状態の値 |
| state_desc | 正常状態の詳細 |
フェールオーバーが発生した場合、診断ログを確認することでフェールオーバーが発生する原因となったコンポーネントを特定することができます。ではコンポーネントの正常性状態が“エラー”になったのは前回ご紹介したどの条件に合致したためでしょうか。これはdata列に含まれる診断データを確認します。data列はXML形式の情報が含まれており、その中で正常性状態が“エラー”となる条件に関連するタグと属性は次の表のとおりとなります。
| コンポーネント | タグ名 | 属性名 | 概要 |
正常状態 エラーの条件 |
| system | system | sickSpinlockTypeAfterAv | 孤立した spinlock の種類 | “none” 以外 |
| writeAccessViolationCount | メモリの不正な書き込みの発生回数 | >3 | ||
| totalDumpRequests | SQL Server が開始されてからのダンプ出力の回数 | > 100 かつ intervalDumpRequests > 1 | ||
| intervalDumpRequests | sp_server_diagnostics の repeat interval の間にダンプ出力が行われた回数 | > 1 かつ totalDumpRequests > 100 | ||
| resource | resource | processOutOfMemoryPeriod | メモリ不足の状態が継続時間 | >120秒 |
| query_processing | query processing | hasUnresolvableDeadlockOccurred | 解決できないデッドロックの有無 | =1 |
| hasDeadlockedSchedulersOccurred | デッドロック スケジューラの有無 | =1 |
フェールオーバーポリシーには関係しないio_subsystemとeventコンポーネントを含めdata列には様々な情報が含まれています。正常性状態の診断に必要なデータだけでなく、コンピュータやSQL ServerのCPU使用率やメモリの状態などパフォーマンスの情報も含まれています。今回全てをご紹介することはできませんが SQL Serverのエラーログと同様に有用なログとなります。この診断ログは既定で最大10個のファイルが保存され、その後再利用されます。各ファイルの最大サイズは100MBとなっています。これらの設定の変更はALTER SERVER CONFIGRATION SET DIAGNOSTICS LOGコマンドを使用します。このコマンドで診断ログの出力先や出力の有効／無効を設定することも可能です。
ALTER SERVER CONFIGURATION (Transact-SQL)
現在の設定値の確認は動的管理ビュー(DMV)sys.dm_os_server_diagnostics_log_configurationsを参照します。既定の設定値を含むこの DMV の参照結果は次のとおりとなります。
| 列名 | 概要 | 既定値 |
| is_enabled |
ロギングの有効／無効 |
1 |
| path | 診断ログの出力先 | <インスタンス ディレクトリ >\MSSQL\Log |
| max_size | ログ ファイルの最大サイズ | 100 |
| max_files | ログ | 10 |
