MSSQLSERVER_833

適用対象: SQL Server Azure SQL Managed Instance

詳細

説明

このメッセージは、SQL Server がディスクからの読み取り要求やディスクへの書き込み要求を発行してからその要求が完了するまでの時間が 15 秒を超えたことを示しています。 SQL Server はこのエラーを報告し、I/O サブシステムに問題があることを示します。 SQL Server などのデータベース管理システム (DBMS) は、ファイル入出力 (I/O) 操作のタイムラインに依存します。 次の項目のいずれかが原因で、I/O 操作がスタックまたはストールし、SQL Server の応答性とパフォーマンスに悪影響を及ぼす可能性があります。

• ハードウェアの障害
• ハードウェアが正しく構成されていません
• ファームウェアの設定
• フィルター ドライバー
• Compression
• バグ
• I/O パスのその他の条件

これらの I/O の問題により、次の動作が発生する可能性があります。

• ブロッキング。
• ラッチの競合とタイムアウト。
• 低応答時間
• リソース境界のストレッチ。
• また、次のような、このメッセージに関連するその他の症状に気付く場合もあります。
  • PAGEIOLATCH 待機の待機時間が長い。
  • システム イベント ログの警告またはエラー。
  • システム モニター カウンターのディスク待機時間の問題を示します。

I/O 操作が 15 秒以上保留されている場合、SQL Server は次の手順を実行します。

1. 操作が保留中であることを検出します。

2. 詳細セクションで説明されているように、SQL Server エラー ログに情報メッセージを書き込みます。

   この情報メッセージのさまざまなセクションの説明を次の表に示します。

考えられる原因

情報メッセージは、現在の負荷で次のいずれかの状態が発生している可能性があることを示します。

• I/O サブシステム (SAN、NAS、直接接続) の構成ミス、またはハードウェア容量に達したために、ワークロードが I/O パス機能を超えています。
• ワークロードが、I/O、CPU、HBA などの現在のシステム機能を超えています。
• I/O パスに誤動作するソフトウェアがあります。 ファームウェアまたはドライバーの問題が考えられます。
• I/O パスには、ハードウェア コンポーネントの誤動作があります。
• オペレーティング システム レベルでのパフォーマンスの問題。
• データベース ファイルの I/O プロセスまたはストレージ パスでのフィルター ドライバーの介入。 たとえば、ウイルス対策プログラムなどです。

SQL Server は、I/O 要求を開始した時刻を記録し、I/O が完了した時刻を記録します。 その差が 15 秒以上の場合、この状態が検出されます。 また、このメッセージで説明および報告される遅延 I/O 条件の原因が SQL Server ではないことも意味します。 この状態は、ストールした I/O と呼ばれます。 ほとんどのディスク要求は、ディスクの標準的な速度で行われます。 この一般的なディスク速度は、ディスク シーク時間とよく呼ばれます。 ほとんどの標準的なディスクのディスク シーク時間は 10 ミリ秒以下になります。 そのため、システム I/O パスが SQL Server に戻る時間は 15 秒です。 詳細については、「 詳細情報 」セクションを参照してください。

ユーザー アクション

次の手順を実行して、このエラーのトラブルシューティングを行います。

1. システム イベント ログで、ハードウェア関連のエラー メッセージを確認します。
2. ハードウェア固有のログが使用可能かどうかを調べます。 オペレーティング システム、ドライバー、または I/O ハードウェアの遅延の原因を特定するために必要な方法と手法を使用します。
3. すべてのデバイス ドライバーとファームウェアを更新するか、I/O サブシステムに関連付けられている他の診断を実行します。
4. ディスク アクセスは、フィルター ドライバー (ウイルス対策プログラムなど) によって遅くなる可能性があります。 アクセス速度を上げるには、エラー メッセージで指定されている SQL Server データ ファイルをアクティブなウイルス スキャンから除外します。 詳細については、「 SQL Server (microsoft.com)を実行しているコンピューターで実行するウイルス対策ソフトウェアを選択する方法」を参照してください。
   • fltmc.exeコマンド ライン ユーティリティを使用してシステムにインストールされているすべてのフィルター ドライバーに対してクエリを実行し、データベース ファイルへのストレージ パスで実行される関数を理解します。
5. パフォーマンス モニターを使用して、次のカウンターを調べます。
   • Average Disk Sec/Transfer
   • Average Disk Queue Length
   • Current Disk Queue Length
6. Storport ETW ログなどの機能を使用してディスク ユニットに対して行われた要求の待機時間を測定することもできます。 Windows Performance Recorder の組み込みプロファイルとして、他の同様のディスク I/O トラブルシューティング キットを使用できます。
7. sys.dm_io_virtual_file_statsを監視し、ストレージ スループットに適したストレージ層と IOPS を選択します。

I/O の問題が原因で発生する SQL Server のパフォーマンスの問題を診断およびトラブルシューティングするためのガイド付きチュートリアルについては、「 I/O の問題が原因で SQL Server のパフォーマンスが低下する問題をトラブルシューティングするを参照してください。

詳細

スタック I/O とストールした I/O

スタック I/O

スタック I/O は、完了しない I/O 要求として定義されます。 頻繁に、スタック I/O はスタック IRP を示します。 スタックした I/O 状態を解決するには、通常、コンピューターを再起動するか、同様の操作を実行する必要があります。 スタック I/O 状態は、通常、次のいずれかの問題を示します。

• ハードウェアの障害。
• I/O パス コンポーネントのバグ。

ストールした I/O

ストールした I/O は、完了する I/O 要求として定義されているか、完了までに過剰な時間がかかります。 通常、ストールした I/O 動作は、次のいずれかの理由で発生します。

• ハードウェア構成。
• ファームウェアの設定。
• トレースして解決するためにハードウェアまたはソフトウェア ベンダーからの支援を必要とするフィルター ドライバーの問題。

SQL Server で I/O がストールし、I/O の記録とレポートが停止する

SQL Server サポートは、スタックまたはストールしている I/O の問題を含む多くのケースを毎年処理します。 これらの I/O の問題は、さまざまな方法で表示されます。 I/O の問題は、診断とデバッグが最も困難な問題の一部であり、Microsoft とお客様からのデバッグにかなりの時間とリソースが必要です。 I/O 要求のレポートと記録は、ファイルごとに設計されています。 ストールした I/O 要求とスタック I/O 要求の検出と報告は、2 つの異なるアクションです。

記録

SQL Server でレコード アクションが発生する瞬間は 2 つあります。 1 つ目は、I/O 操作が完了したときです。 2 番目の瞬間は、レイジー ライターが実行されたときです。 遅延ライターを実行すると、保留中のすべてのデータと保留中のログ ファイル I/O 要求がチェックされます。 I/O 要求が 15 秒のしきい値を超えると、レコード操作が発生します。

レポート

レポートは、5 分以上離れた間隔で行われます。 レポートは、ファイルで次の I/O 要求が行われるときに発生します。 レコード アクションが発生し、最後のレポートが発生してから 5 分以上経過した場合は、「詳細」セクションに記載されている情報メッセージが SQL Server エラー ログに書き込まれます。

15 秒のしきい値は調整できません。 ただし、トレース フラグ 830 を使用して、ストールしている I/O 検出またはスタック I/O 検出を無効にすることはできますが、これを行うことはお勧めしません。

トレース フラグ 830 を使用して、ストールおよびスタック I/O の検出を無効にすることができます。 SQL Server が起動されるたびにこのフラグを有効にするには、-T830 スタートアップ パラメーターを使用します。 現在実行中の SQL Server インスタンスの検出を無効にするには、次のステートメントを使用します。

    dbcc traceon(830, -1)

この設定は、SQL Server プロセスの有効期間にのみ有効です。

I/O 要求パケット (IRP) の追跡

SQL Server では、標準の Microsoft Windows API 呼び出しを使用してデータの読み取りと書き込みを行います。 たとえば、SQL Server では次の関数が使用されます。

• WriteFile
• ReadFile
• WriteFileScatter
• ReadFileGather

読み取りまたは書き込み要求は、I/O 要求パケット (IRP) として Windows によって処理されます。 IRP の状態を確認するには、次の機能の両方を使用します。

• Windows のサポート
• Storport ETW のログ記録

次の項目で利用可能な更新プログラムがあるかどうかを確認することをお勧めします。

• The BIOS
• ファームウェア
• その他の I/O パス コンポーネント

追加のデバッグ 操作を実行する前に、ハードウェア ベンダーに問い合わせてください。 デバッグ セッションには、サードパーティ製のドライバー、ファームウェア、またはフィルター ドライバー コンポーネントが含まれる可能性があります。

システム パフォーマンスとクエリ プランのアクション

全体的に、システム パフォーマンスは I/O 処理で重要な役割を果たすことができます。 ストールまたはスタックした I/O 操作のレポートを調査する際は、システムの全般的な正常性を考慮する必要があります。 負荷が過剰になると、I/O 処理を含め、システム全体が遅くなる可能性があります。 問題が発生したときのシステムの動作は、問題の根本原因を特定する上で重要な要素となる可能性があります。 たとえば、問題が発生している間に CPU 使用率が増加したり、高いままになったりした場合、システム プロセスが CPU を非常に多く使用しているため、他のプロセスが悪影響を受けている可能性があります。

パフォーマンス カウンター

I/O パフォーマンスを監視するには、次のパフォーマンス カウンターで特定の I/O パス情報を調べます。

• Average Disk Sec/Transfer
• Average Disk Queue Length
• Current Disk Queue Length

たとえば、SQL Server を実行しているコンピューターの平均ディスク秒/転送時間は、通常 15 ミリ秒未満です。 平均ディスク秒/転送値が上昇した場合、I/O サブシステムが I/O 需要に最適に対応していないことを示します。

SQL Server は、ディスク キューの長さを大きくプッシュする非同期 I/O 機能を最大限に活用するため、パフォーマンス カウンターを使用する際は注意してください。 そのため、ディスク キューの長さが長いだけでは問題は示されません。

Windows システム モニターでは、影響を受ける各ディスクのカウンター "物理ディスク: ディスク バイト/秒" を確認し、各プロセスのカウンター "Process: IO Data Bytes/Sec" と "Process: IO Other Bytes/sec" とアクティビティのレートを比較できます。 これを行って、特定のプロセス セットが過剰な I/O 要求を生成しているかどうかを識別します。 Process オブジェクト内の他のさまざまな I/O 関連カウンターにより、より詳細な情報が表示されます。 SQL Server インスタンスがサーバー上の過剰な I/O 負荷の原因であると判断した場合は、インデックスと並列処理に関する次のセクションを参照してください。 I/O のボトルネックの検出と解決の詳細については、「 I/O の問題が原因で SQL Server のパフォーマンスが低下するをトラブルシューティングする」を参照してください。

インデックスと並列処理

多くの場合、インデックスがないために I/O のバーストが発生します。 この動作により、I/O パスが重大にプッシュされる可能性があります。 インデックスの回転ウィザード (ITW) を使用するパスは、システムの I/O 負荷を解決するのに役立ちます。 クエリがテーブル スキャンではなくインデックスの恩恵を受けたり、並べ替えやハッシュを使用したりする場合、システムは次の利点を得ることができます。

• クエリのパフォーマンス上の利点を直接生み出すアクションを完了するために必要な物理 I/O が削減されます。
• データ キャッシュ内のページの数を減らしておく必要があります。 そのため、データ キャッシュ内にあるページはアクティブなクエリに関連し続けます。
• インデックスが見つからないか、統計が古いために、並べ替えとハッシュが使用されます。 1 つ以上のインデックスを追加することで、tempdb の使用と競合を減らすことができます。
• リソース、並列操作、またはその両方で削減が行われます。 SQL Server ではクエリの並列実行が保証されず、システムの負荷が考慮されるため、すべてのクエリをシリアル実行用に最適化することをお勧めします。 クエリを最適化するには、Query Analyzer を開き、並列処理の最大次数オプションのsp_configure値を 1 に設定します。 すべてのクエリがシリアル操作として迅速に実行されるようにチューニングされている場合、並列実行の方が良い結果になることが多いです。 ただし、多くの場合、並列実行は、データ量が多いために選択されます。 インデックスがない場合は、大きな並べ替えが必要になる場合があります。 並べ替え操作を実行している複数のワーカーは、より迅速な応答を作成します。 ただし、このアクションにより、システムへの負荷が大幅に増加する可能性があります。 多くのワーカーからの読み取り要求が大きいと、CPU 使用率の増加とともに I/O バーストが発生する可能性があります。 多くの場合、インデックスが追加された場合や、別のチューニング アクションが発生した場合に、クエリをチューニングして実行速度を向上させ、使用するリソースを減らすことができます。

SQL Server サポートの実際の例

次の例は、SQL Server サポートと Windows エスカレーション サポートによって処理されています。 これらの例は、参照フレームを提供し、ストールしている I/O 状況やスタック I/O 状況に関する期待値を設定することを目的としています。 また、システムが影響を受けるか、応答する可能性があることを理解するためのフレームワークも提供します。 特定のハードウェアやドライバーのセットは、特定のリスクや他のドライバーに対するリスクの増加を引き起こす可能性はありません。 この点では、すべてのシステムが同じです。

例 1: 45 秒間スタックしているログ書き込み

SQL Server ログ ファイルを定期的に書き込もうとすると、約 45 秒間停止します。 ログの書き込みがタイムリーに完了しません。 この動作により、30 秒のクライアント タイムアウトが発生するブロック条件が作成されます。

アプリケーションが SQL Server にコミットを送信すると、コミットはログ書き込み保留中としてスタックします。 この動作により、クエリはロックを保持し続け、他のクライアントからの受信要求をブロックします。 その後、他のクライアントがタイムアウトし始めます。これにより、クエリのタイムアウトが発生したときにアプリケーションが開いているトランザクションをロールバックしないため、この問題が生じます。 これにより、ロックを保持している何百もの開いているトランザクションが作成されます。 したがって、重大なブロッキング状況が発生します。

トランザクションの処理とブロックの詳細については、次のマイクロソフト サポート技術情報の記事を参照してください。 224453 SQL Server のブロックの問題の理解と解決

アプリケーションは、接続プールを使用して Web サイトを処理します。 ブロックされる接続が増えるにつれて、Web サイトはより多くの接続を作成します。 これらの接続はブロックされ、サイクルは続行されます。

ログの書き込みが完了するまでに約 45 秒かかります。 ただし、この時点では、数百の接続がバックアップされます。 ブロックの問題により、SQL Server とアプリケーションの復旧時間が数分になります。 アプリケーションの問題と組み合わせると、ストールした I/O 状態はシステムに非常に悪影響を及ぼします。

解像度

この問題は、ホスト バス アダプター (HBA) ドライバーでスタックしている I/O 要求に追跡されます。 コンピューターには、フェールオーバーをサポートする複数の HBA カードがあります。 1 つの HBA が遅れているか、記憶域ネットワーク (SAN) と通信していない場合、"フェールオーバー前の再試行" タイムアウト値は 45 秒に構成されます。 タイムアウトを超えると、I/O 要求は 2 番目の HBA にルーティングされます。 2 番目の HBA は要求を処理し、すぐに完了します。 このようなストール状態を防ぐために、ハードウェアの製造元は 5 秒の "フェールオーバー前の再試行" 設定を推奨しています。

例 2: フィルター ドライバーの介入

多くのウイルス対策ソフトウェア プログラムとバックアップ製品では、I/O フィルター ドライバーが使用されています。 これらの I/O フィルター ドライバーは、I/O 要求スタックの一部になり、IRP 要求にアクセスできます。 Microsoft 製品サポート サービスでは、フィルター ドライバーの実装で I/O 状態がスタックしたり、I/O 条件がストールしたりするバグから、さまざまな問題が発生しています。

このような条件の 1 つは、バックアップが発生したときに開かれているファイルのバックアップを許可するバックアップ処理のフィルター ドライバーです。 システム管理者は、ファイル バックアップの選択に SQL Server データ ファイル ディレクトリを含めます。 バックアップが行われると、バックアップはバックアップの開始時にファイルの正しいイメージの収集を試みます。 これにより、I/O 要求が遅延します。 I/O 要求は、ソフトウェアで処理されるため、一度に 1 つだけ完了できます。

バックアップが開始されると、SQL Server の I/O が一度に 1 つずつ完了するように強制されるため、SQL Server のパフォーマンスが大幅に低下します。 一度に 1 つのロジックは、I/O 操作を非同期的に実行できないので、問題が発生します。 そのため、SQL Server が I/O 要求を投稿して続行すると予想される場合、ワーカーは I/O 要求が完了するまで読み取りまたは書き込み呼び出しでスタックします。 フィルター ドライバーのアクションは、SQL Server の先読みなどの処理タスクを効果的に無効にします。 さらに、フィルター ドライバーの別のバグは、バックアップが完了した場合でも、プロセス内の一度に 1 つのアクションを残します。 SQL Server のパフォーマンスを復元する唯一の方法は、フィルター ドライバーの操作なしでファイル ハンドルが解放され、再取得されるように SQL Server を再起動することです。

解像度

この問題を解決するために、SQL Server データ ファイルはファイル バックアップ プロセスから削除されます。 ソフトウェアの製造元は、ファイルを "一度に 1 つずつ" モードのままにした問題を修正しました。

例 3: 非表示のエラー

多くのハイエンド システムには、負荷分散や同様のアクティビティを処理するためのマルチチャネル I/O パスがあります。 Microsoft 製品サポートは、I/O 要求が失敗するが、ソフトウェアがエラー状態を正しく処理しない負荷分散ソフトウェアに関する問題を検出しました。 ソフトウェアは無限の再試行を試みることができます。 I/O 操作が停止し、SQL Server が指定したアクションを完了できません。 前に説明したログ書き込み条件と同様に、このような状態がシステムにくさびを付けた後に、システムの動作が低下する可能性があります。

解像度

この問題を解決するには、SQL Server を再起動します。 ただし、場合によっては、オペレーティング システムを再起動して処理を復元する必要があります。 また、I/O ベンダーからソフトウェア更新プログラムを入手することもお勧めします。

例 4: リモート・ストレージ、ミラーリング、および RAID ドライブ

多くのシステムでは、ミラーリングを使用するか、同様の手順を採用してデータ損失を防ぎます。 ミラーリングを使用するシステムの中にはソフトウェアベースのシステムもあれば、ハードウェアベースのシステムもあります。 これらのシステムのMicrosoft サポートによって通常検出される状況は、待機時間の増加です。

I/O 全体の時間の増加は、I/O が完了したと見なされる前に完了する必要があるときに発生します。 リモート・ミラー・インストールの場合、ネットワーク再試行が関与する可能性があります。 ドライブの障害が発生し、RAID システムが再構築されている場合、I/O パターンも中断される可能性があります。

解像度

ミラーまたは RAID 再構築操作の待機時間を短縮するには、厳密な構成設定が必要です。

例 5: 圧縮

Microsoft では、圧縮ドライブ上の SQL Server データ ファイルとログ ファイルはサポートされていません。 NTFS 圧縮は書き込み先ログ (WAL) プロトコルを中断するため、SQL Server では NTFS 圧縮は安全ではありません。 NTFS 圧縮では、I/O 操作ごとに処理を増やす必要もあります。 圧縮では、重大なパフォーマンスの問題が発生する動作と同様に、"一度に 1 つずつ" が作成されます。

解像度

この問題を解決するには、データとログ ファイルを圧縮解除します。

詳細については、「 圧縮ボリューム上のデータベースのサポートを参照してください。

追加のデータ ポイント

PAGEIOLATCH_* と書き込みログの待機sys.dm_os_wait_stats動的管理ビュー (DMV) は、I/O パスのパフォーマンスを調査するための重要なインジケーターです。 PAGEIOLATCH 待機が大量に発生する場合は、SQL Server が I/O サブシステムで待機していることを意味します。 一定の PAGEIOLATCH 待機が一般的であり、予期される動作です。 ただし、PAGEIOLATCH の平均待機時間が一貫して 10 ミリ秒を超える場合は、I/O サブシステムに負荷がかかっている理由を調査する必要があります。 詳細については、以下のドキュメントをご覧ください。

• I/O 問題が SQL Server のパフォーマンス低下を引き起こす問題のトラブルシューティング
• sys.dm_os_waiting_tasks (Transact-SQL)
• sys.dm_exec_requests
• SQL Server 待機の種類リポジトリ

参考文献

• DISKSPD を使用してワークロード ストレージのパフォーマンスをテストする
• 826433 SQL Server 診断を追加して、古い読み取りまたは書き込みの損失による報告されていない I/O の問題を検出する
• ログ記録とデータ ストレージ アルゴリズム

SQL Server では、 SQL Server I/O 信頼性プログラムの要件に関する説明に従って、システムで "安定したメディアへの確実な配信" がサポートされている必要があります。 SQL Server データベース エンジンの入力要件と出力要件の詳細については、「入力/出力の要件データベース エンジンを参照してください。

I/O エラーの詳細については、「Microsoft SQL Server I/O Basics, Chapter 2」 (Microsoft SQL Server I/O の基礎 (第 2 章)) を参照してください。