• [アーティクル]

SQL に関する Q&AI/O エラー、データベース ミラーリングなど

Paul S. Randal

質問 - 最近、管理しているデータベースに対して定期的に整合性確認を実行するようにしており、ユーザーのクエリによって発生する I/O エラーを検出するために、いくつかの SQL エージェントの警告を追加しました。このデータベースには破損している箇所がないため、確認と警告に関連する実装済みのロジックが機能するかどうかがわかりません。実装したすべての機能が正しく動作することを確認するために、テスト データベース内に破損箇所を作成するにはどうすればよいでしょうか。また、I/O エラーを検出するには、この他にも作業を行う必要がありますか。

回答 - SQL Server® 2000 で、テスト用に破損したデータベースを作成する場合、通常はテスト データベースの sysindexes テーブルから行を手動で削除していました。ただし、SQL Server 2005 では、そのような方法でシステム テーブルを破損させることは非常に困難です。テスト データベースを手動で破損させるための最も良い方法は、データベースがシャットダウンされている状態で、16 進エディタを使用してデータ ファイルを変更することです。この方法を次に示します。

  • データベースをシャットダウンして、データ ファイルがロックされないようにします (ただし、データベースをデタッチしないようにしてください。これは、誤ったページを破損させた場合、そのデータベースを再度アタッチできなくなるためです)。
  • ファイル内の、たとえば 100 ページ (819,200 バイト) 以降のオフセットを選択します。このとき、8192 バイトの境界 (ページの境界) を選択するようにします。これにより、重要なメタデータ ページとアロケーション ビットマップが変更されずに済むため、データベースを起動し、そのデータベースに対して DBCC CHECKDB を実行できるようになります。
  • ファイル内の選択したオフセットに数バイト分 0 を書き込みます。この手法を使用すると、ページ ヘッダーの破損に関する複数のエラーがほぼ確実に発生します。

とは言うものの、破損したテスト データベースを最も速く作成する方法は、他の人が既に作成したデータベースを使用することです。破損した SQL Server 2000 および SQL Server 2005 データベースのサンプル (説明付き) については、私のブログ (go.microsoft.com/fwlink/?LinkId=115151) を参照してください。

I/O エラーを検出するにはどうすればよいかという 2 番目の質問の答えですが、これを行うには、ページのチェックサムを有効にする必要があります。この機能は、I/O サブシステムによって発生したエラーからデータベース ページ全体を保護する方法として、SQL Server 2005 から導入されました。

基本的に、ページがディスクに書き込まれると、最後に SQL Server によってその 8 KB ページ全体のチェックサムが計算され、そのページにスタンプされます。チェックサムが格納されているページがディスクから読み取られると、そのチェックサムは再計算され、既にページに格納されているチェックサムと比較されます。2 つのチェックサムが一致しない場合は、SQL Server 以外の動作が原因でそのページが破損しています。この場合、エラー 824 が発生します。このエラーは、ページの読み取りが発生した接続のエラーとして表示され、SQL Server エラー ログと Windows® のアプリケーション イベント ログに記録されます。

SQL Server 2005 と SQL Server 2008 で作成されるすべてのデータベースでは、ページのチェックサムが既定で有効になります。ただし、SQL Server の以前のバージョンからアップグレードされたデータベースの場合は、手動でこのチェックサムを有効にする必要があります。次のコードを使用すると、ページのチェックサムを有効にすることができます。

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

ヒント : 既定の SQL Server ポートを変更する

既定では、SQL Server インスタンス用に構成されているポートは 1433 です。あるインスタンスによってこのポートが使用されると、別のインスタンスはこのポートを使用できなくなります。したがって、ネットワーク上で TCP を使用してリッスンする 2 番目の (名前付き) インスタンスをインストールする場合は、別のポートが必要になります。また、使用されているポートをわかりにくくするために、管理者がポートを変更する場合もあります (ただし、ポート スキャナによって簡単にポートが検出されてしまうため、この方法はあまり使用されません)。もちろん、その後、異なるポートを使用するようにクライアントを構成する必要があります。これを行うための一般的な方法は 3 つあります。

管理者がインスタンスのポートを 5555 に変更した場合、1 つ目の選択肢は、MyServername,5555 という構文を使用して、接続先のコンピュータ名と共にインスタンスのポート番号を指定する方法です。そのポートが再度変更された場合は、クライアントも再度 connectionStrings を変更する必要があります。

2 つ目の選択肢は、クライアント上で構成された SQL Server の別名を使用する方法です。別名以外に、サーバー名、ポート名、およびプロトコルも指定する必要があります。構成後は、サーバー名と同じようにその別名を使用して、データベース インスタンスに接続できます。この方法の利点は、設定がレジストリに格納されるため、ドメイン管理者がサーバーの構成に加えた変更を展開できることです。

3 番目の選択肢は、ユーザーが名前付きインスタンスのインスタンス名のみを知っていて、connectionString 内で "コンピュータ名\インスタンス名" を使用して名前を指定する場合に、SQL Server Browser サービスを使用する方法です。SQL Server 2000 では、このサービスは実行されるサービスの一部として既に実装されていますが、SQL Server 2005 では別個のサービスとして構築されました。SQL Server Browser サービスは、コンピュータ上のインスタンスを検出するだけでなく、要求されたインスタンスに適したポート番号を使用して、ポート 1434 に対するユーザー データグラム プロトコル (UDP) の着信要求に応答します。また、クライアントのリダイレクトを有効にしたり、透過的な接続をサポートします。

—Jens K. Suessmeyer、データベース コンサルタント (マイクロソフト)

質問 - データベース内の断片化をすべて解消するために、SQL Server 2005 Enterprise Edition SP2 上で実行されている運用データベース内のすべてのインデックスを再構築する、夜間のメンテナンス プランを構成しましたが、これによってデータベースが過度に拡張されることがわかり、ディスク上の空き領域も少なかったことから、余分な空き領域をすべて圧縮する操作を追加しました。すると、再構築操作が機能しなくなったようです。何が起こっているのでしょうか。

回答 - これは、メンテナンス プランを構成する際によく直面する問題です。圧縮と拡張を繰り返すサイクルに陥っているのです。

インデックスが再構築されると、既存のインデックスが削除される前に、そのインデックスの新しいコピーが作成されます。この手順を完了するには、データベース ファイル内に空き領域が必要です。通常は、現在のインデックスによって使用されているものと同じくらいの領域が必要になります。SQL Server 2000 では、インデックス行の並べ替えを実行する場合でも空き領域 (インデックスのサイズの約 20%) が必要でしたが、SQL Server 2005 では、単純なインデックスの再構築を実行する場合、この領域は不要になりました。

管理者は、インデックスの再構築時に作成された余分な空き領域を取り除くために、メンテナンス プラン内の再構築操作の後に圧縮操作を追加することがあります。ただし、あまり広く知られていることではありませんが、この圧縮操作のアルゴリズムの性質が原因で、インデックスの断片化が発生します。つまり、新しく再構築および最適化されたインデックスはすぐに断片化し、最初に行った再構築の効果がなくなってしまいます。

次回インデックスの再構築が発生したときに、データベース ファイルが再度拡張されることを考えると、データベース内の余分な空き領域を保持し、さらに圧縮操作を実行しないことをお勧めします (また、頻繁にデータベース ファイルが拡張および圧縮されると、OS レベルでファイルの断片化が発生します。これにより、インデックスが断片化した場合と同じように、パフォーマンスが低下します)。

最後に 1 つ、インデックスを再構築する頻度を減らすことを検討するとよいでしょう。以前 SQL Server 2000 に関する記事で紹介した DBCC INDEXDEFRAG や、新しい SQL Server 2005 と SQL Server 2008 の ALTER INDEX REORGANIZE 構文など、別の方法を使用することもできます。

インデックスの断片化に関する説明と、断片化を解消するタイミングに関するガイダンスが記載された、便利なホワイト ペーパーも公開されています (go.microsoft.com/fwlink/?LinkId=115154)。このホワイト ペーパーは SQL Server 2000 用に執筆されたものですが、その概念は現在でも変わっていません。

質問 - 現在組織で障害回復戦略を評価しており、データベース ミラーリングが当社の状況に適した方法であると考えています。保護しようとしているサーバーには、(以前に実施したサーバー統合プロジェクトの結果) 関連し合っていない多くのデータベースが格納されており、これらすべてのデータベースにデータベース ミラーリングを使用する予定です。どれぐらいの数のデータベースをミラーリングすると、パフォーマンスが低下し始めるのでしょうか。

回答 - 質問への回答として非常によく使用する表現ですが、それは "場合によります"。公開されているガイドラインでは、1 つのインスタンスにつき 10 個を超えるデータベースをミラーリングしないことが推奨されていますが、この 10 という数字は大部分のユーザーに当てはまる最大値を大まかに推測したものです。実際に使用しているハードウェア構成と照らし合わせて、次の要因を考慮する必要があります。

  • プリンシパル インスタンスとミラー インスタンスで使用できるメモリのサイズ (これは同じサイズであるのが理想です)
  • プリンシパル インスタンスとミラー インスタンスの処理能力 (これも同じであるのが理想です)
  • ミラー インスタンス上の I/O サブシステムで使用できる帯域幅の広さ (これは、プリンシパル インスタンス上の I/O サブシステムと同じであるのが理想です)
  • 各データベース上のワークロードによって生成されるトランザクション ログの量
  • プリンシパル インスタンスとミラー インスタンスとの間の通信に使用できるネットワーク帯域幅の広さ

最後の 2 つの要因は最も重要です。2 つのインスタンス間の通信に使用できるネットワーク帯域幅の広さが十分でないことが原因で、ミラーリングされるすべてのデータベースから 1 秒あたりに生成されるトランザクション ログの量に対処できない場合、プリンシパル データベースのパフォーマンスは低下します。SQL Server 2008 では、ログ ストリームの圧縮によって、この負荷を若干軽減できます。

次に考慮すべき重要なことは、ミラーリングに必要なメモリとスレッドの量です。ミラー データベースごとに、1 つのスレッドと若干のメモリが必要です。処理能力の低いサーバーでは、大量のデータベースをミラーリングすると、通常のワークロードと合わせて、サーバーへの負荷が非常に大きくなる場合があります。

また、データベース ミラーリングの実行方法も検討する必要があります。同期モードでは、すべてのトランザクション ログ レコードがミラー データベースのトランザクション ログにコピーされるまで、プリンシパル データベース上のトランザクションをコミットできません。したがって、ネットワーク上のトラフィックが多すぎることによって遅延が発生した場合、プリンシパル上でワークロードのパフォーマンスに関する問題が発生する可能性があります。

非同期モードでは、待機時間は発生せず、プリンシパル上でトランザクションをコミットできますが、ミラーへの送信を待機するトランザクション ログの量が、ネットワークの遅延によって増加する可能性があります。これにより、トランザクション ログのサイズに関する問題が発生する場合があります。なお悪いことに、障害が発生した場合、未送信のすべてのトランザクション ログが失われます。したがって、未送信のトランザクション ログの数が多いほど、回復時にデータが失われる可能性が高くなります。

シナリオごとに環境は大きく異なりますが、実際の運用環境におけるいくつかの興味深い例を紹介します。たとえば、ある環境では 150 個のデータベースを保有しており、どのデータベースの使用率も非常に低く、さらにすべてのデータベースが同時に使用されることはありませんでした。このため、150 個のデータベースは、すべて問題なくミラーリングされていました。

これに対して、ある環境では、負荷の高いデータベースを 3 つのみ保有していましたが、ネットワーク接続が不安定でした。このシナリオでは、辛うじて 1 つのデータベースをミラーリングしたところで、ネットワーク帯域幅の不足によってワークロードのパフォーマンスが低下し始めました。

まずは、生成されるログの量を計算することが重要です。現在のネットワーク帯域幅で、ミラーリングする必要があるデータベースの数がサポートされるようであれば、問題ないでしょう。運用環境に移行する前に構成をテストし、特にデータベース メンテナンスなど、トランザクション ログが生成される可能性のあるすべての操作を考慮するようにしてください。

Paul S. Randal は SQLskills.com の代表取締役であり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、『DBCC CHECKDB/repair for SQL Server 2005』を執筆し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、カンファレンスで定期的に発表を行っています。彼のブログは SQLskills.com/blogs/paul で公開されています。

© 2008 Microsoft Corporation and CMP Media, LLC.All rights reserved; 許可なしに一部または全体を複製することは禁止されています.