(元々は DBA.StackExchange.com に投稿されましたが、閉鎖されました。こちらの方が関連性が高いと思います。)
アレクサンダーと、ひどい、最悪、ダメ、とても悪い...バックアップ。
セットアップ:
オンプレミスのSQL Server 2016 標準エディションインスタンスが仮想マシンVMWare から。
@@バージョン：
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) 2021 年 3 月 19 日 19:41:38 Copyright (c) Microsoft Corporation Windows Server 2016 Datacenter 10.0 (ビルド 14393: ) (ハイパーバイザー) 上の Standard Edition (64 ビット)
サーバー自体は現在割り当てられています8 つの仮想プロセッサ、 もっている32 GBのメモリ、そしてすべてのディスクはNVMe周りを回る1 GB/秒のI/Oデータベース自体は G: ドライブ上に存在し、バックアップは P: ドライブに別途保存されます。すべてのデータベースの合計サイズは約 500 GB です (バックアップ ファイル自体に圧縮される前)。
メンテナンス プランは、サーバー上のすべてのデータベースの完全バックアップを実行するために、夜間に 1 回 (午後 10 時 30 分頃) 実行されます。サーバー上では、他に異常なことは何も実行されておらず、その時間には特に何も実行されていません。サーバーの電源プランは「バランス」に設定されています (また、「ハード ディスクの電源を切るまでの時間」は 0 分に設定されており、つまり電源を切ることはありません)。
どうしたの：
過去1年ほど、メンテナンス計画ジョブの合計実行時間は約15分でした。分完了までに要する時間は、先週から約40倍、約15分にまで急増しました。時間完了します。
メンテナンス プランの速度が低下した同じ日に私が認識している唯一の変更点は、メンテナンス プランの実行前にマシンに次の Windows 更新プログラムがインストールされたことです。
また、別の VM 上に同様にプロビジョニングされた別の SQL Server インスタンスがあり、同じ Windows アップデートが実行された後、その後もバックアップが遅くなりました。Windows アップデートが直接の原因であると考え、完全にロールバックしましたが、バックアップ メンテナンス プランは依然として非常に遅く実行されます。奇妙なことに、特定のデータベースのバックアップの復元は非常に高速に行われ、NVMe の 1 GB/秒の I/O をほぼ完全に使用します。
試したこと:
Adam Mechanic の sp_whoisactive を使用すると、バックアップ プロセスの最後の待機タイプが常にディスク パフォーマンスの問題を示していることがわかりました。待機タイプに加えて、次のタイプもBACKUPBUFFER常に表示されます。BACKUPIOASYNC_IO_COMPLETION
バックアップ中にサーバー自体のリソース モニターを見ると、ディスク I/O セクションには、使用されている合計 I/O が約 14 MB/秒しかないことが示されています (この問題が発生してから私が見た最高値は 30 MB/秒です)。
この役に立つDiskSpd の使用に関する Brent Ozar の記事、私も同様のパラメータで実行してみました (サーバーに 8 つの仮想プロセッサがあるため、スレッド数を 8 に減らし、書き込みを 50% に設定しました)。これは正確なコマンドですdiskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt"。手動で生成した 1 GB 弱のテキスト ファイルを使用しました。測定された I/O は問題ないようです。しかし、ディスク レイテンシはとんでもない数値を示していました。
DiskSpd の結果は文字通り信じられないようです。さらに読んでいくと、データベースごとにディスク レイテンシ メトリックを返す Paul Randall のクエリを見つけました。結果は次のとおりです。
最悪の書き込みレイテンシは63ミリ秒、最悪の読み取りレイテンシは6ミリ秒でした。これはDiskSpdとの大きな差異のようですが、私の問題の根本原因となるほどひどいものではないようです。さらにクロスチェックするために、サーバー自体でいくつかのPerfMonカウンターを実行しました。このマイクロソフトの記事結果は以下のとおりです。
特に変わったことはありません。私が計測したすべてのカウンターの最大値は 0.007 でした (ミリ秒だと思います)。最後に、インフラストラクチャ チームに、バックアップ ジョブ中に VMWare が記録したディスク レイテンシ メトリックをチェックしてもらいました。結果は次のとおりでした。
最悪の場合、深夜 12 時頃に約 200 ミリ秒のレイテンシの急上昇があり、最高の I/O は 600 KB/秒でした (リソース モニターでは、バックアップが少なくとも約 14 MB/秒の I/O を使用していることが示されているため、これはよくわかりません)。
私が試した他のもの:
大きなデータベースの 1 つ (約 250 GB) を復元しようとしたところ、復元には合計で約 8 分しかかかりませんでした。その後、実行してみたDBCC CHECKDBところ、合計で 16 分かかりました (これが正常かどうかはわかりません)。ただし、リソース モニターには、他に何も実行されていない状態で、同様の I/O の問題 (これまで使用された最大の I/O は 100 MB/秒) が表示されました。
以下は、最初に実行したときとDBCC CHECKDB、5% 完了した後の sp_whoisactive の結果です。すでに 5% 完了した後でも、推定残り時間が約 5 分増加していることに注意してください。
始める：
5% 完了:
これは単なる推定値なので正常だと思いますし、250 GB のデータベースで 16 分というのはそれほど悪くないように思えます (ただし、これが正常かどうかはわかりません)。ただし、サーバーまたは SQL インスタンスで他に何も実行されていない状態では、I/O はドライブの能力の約 10% で最大限に活用されていました。
結果は次のとおりです、DBCC CHECKDBエラーは報告されませんでした。
また、コマンドで奇妙な遅さの問題も発生していますSHRINK。解放するスペースが 5% のデータベース (約 14 GB) を試したところ、SHRINK90% を完了するのに約 1 分しかかかりませんでしたSHRINK。
約 5 分後、完了率は依然として同じままで、トランザクション ログ バックアップ (通常は 1 ～ 2 秒で完了) が約 30 秒間競合状態になっています。
15 分後、 はSHRINKちょうど終了しましたが、トランザクション ログ バックアップはまだ約 6 分間競合状態にあり、完了率は 50% にすぎません。 がSHRINK終了したので、その直後にすぐに終了したと思います。その間ずっと、リソース モニターは I/O がまだ消費されていることを示していました。
その後、コマンドが終了したときにエラーが発生しましたSHRINK:
もう一度試してみたところSHRINK、上記とまったく同じ結果になりました。
次に、P: ドライブ上のファイルに T-SQL バックアップのスクリプトを手動で作成してみましたが、メンテナンス プランのバックアップ ジョブと同様に実行速度が遅くなりました。
結局、約 3 分後にキャンセルしましたが、すぐにロールバックされました。
まとめ：
偶然にも、Windows 更新プログラムをインストールした直後、バックアップ メンテナンス プラン ジョブは毎晩約 40 倍 (15 分から 15 時間) 遅くなりました。これらの Windows 更新プログラムをロールバックしても問題は解決しませんでした。SQL Server の待機タイプ、リソース モニター、および Microsoft DiskSpd はディスクの問題 (特に I/O) を示していますが、Paul Randall のクエリ、PerfMon、および VMWare ログからのその他のすべての測定値ではディスクの問題は報告されていません。特定のデータベースのバックアップの復元は迅速で、ほぼ 1 GB/秒の I/O をすべて使用します。頭を悩ませています...
答え1
この場合、実際にディスクの問題が発生しており、この特定の VM に関しては SQL Server 内部の問題ではありませんでした。実際には、Veeam と VMWare で発生したバグ ケースでした。
何が起こったのかを私が理解していることをまとめると、どうやら私たちの Veeam バックアップが VMWare によって完了として認識されていなかったようです。そのため、毎日サーバーをバックアップする時間になると、VMWare は Veeam に前日の再バックアップを指示し、それが 2 週間にわたって累積的に拡大する問題となっていました。(この説明は間違っていたと思いますが、私が知っているのは大体これくらいです。)
Veeam / VMWare は各スナップショット ファイルを削除する必要があり、各日のファイルは前のファイルよりも大きくなっていたため、レベル 3 サポートが完了するまでに約 26 時間かかりました。その後、VM は再び正常に動作しました。テクニカル サポートによると、どうやらこれは珍しい問題ではないようです。
申し訳ありませんが、これは非常に特殊な問題であり、おそらく他の多くの人の助けにはならないと思いますが、役立つことを願っています。