• [アーティクル]

SQL に関する Q&A行オーバーフロー、差分バックアップなど

Paul S. Randal

質問 最近、あるアプリケーションを、SQL Server 2005 で実行できるようにアップグレードしました。これにより、8,060 バイトを超える行を使用できるようになったので、ユーザーは SQL Server エラーを発生させることなく、より大きなデータ フィールドを作成できるようになりました。現在、このアプリケーションを運用環境で使用していますが、一部のスキャン クエリでパフォーマンスに関する問題が発生します。このスキャン クエリは、スキーマを変更するまでは正常に動作していました。さまざまなインデックスの断片化を確認しましたが、すべて問題ありません。SQL Server 2005 でクエリの動作が遅くなるのはなぜでしょうか。

回答 お使いの行オーバーフローという機能は、行のサイズを 8,060 バイトより大きくする場合に非常に役立つ機能です。ただし、ほとんどの行のサイズが大きくなりすぎる場合にはあまり適しておらず、ご質問のように、クエリのパフォーマンスが低下する可能性があります。

この理由は、行のサイズが大きくなりすぎると、その行の可変長列のいずれかが "行外" に移動されるからです。つまり、データ ページやインデックス ページの行から、列がテキスト ページに移動されます。その列の以前の値は、データ ファイル内でその値が新しく格納された場所を参照するポインタに置き換えられます。

これは、xml、text、image、varchar(max) 型など、通常のラージ オブジェクト (LOB) 列を格納する場合とまったく同じメカニズムです。注意する必要があるのは、テーブル スキーマに複数の可変長列が含まれていて、複数の行のサイズが大きくなりすぎた場合、同じ列が行外に移動されるとは限らないことです。

このメカニズムが原因で、パフォーマンスに関する問題が発生することがあります。列が行外に移動された場合、テーブル内の 1 行から可変長列を取得するクエリで、突然追加の I/O が必要になる (値が格納された行外の場所を含むテキスト ページを読み込む目的で実行されます) ことがあります。サイズの大きすぎる行が複数存在する場合、行外に移動される値の数によっては、複数の行から同じ可変長列を取得するクエリのパフォーマンスを予想することが困難になることがあります。

今回のご質問の場合、行オーバーフローとその影響が原因で、可変長列を含む選択リストの範囲スキャンやテーブル スキャンを実行するクエリのパフォーマンスが低下しています。インデックスが完全に断片化しているかどうかは関係ありません。つまり、可変長列が行外に移動されると、行外の値を含むテキスト ページを読み取るために I/O をランダムに実行する必要があるので、以前効率的に行われていたスキャンが実質的に中断されます。

それでも、頻繁に行のサイズが大きくならない場合は、行オーバーフローが非常に役立ちます。ただし、クエリのパフォーマンスが大幅に低下する場合は、この機能をあまり頻繁に使用しない設計を行ってください。

質問 最近、記憶域ネットワーク (SAN) レプリケーションよりも低コストで全体的な冗長性を確保する手段として、2 つのフェールオーバー クラスタ間のデータベース ミラーリングを導入しました。データセンターは同じ市内にあるので、同期ミラーリングを使用できます。問題は、ローカル クラスタでフェールオーバーが発生すると、ミラー化されたデータベースがリモート クラスタにフェールオーバーすることです。これは、希望している動作ではありません。この動作が発生しないようにするにはどうすればよいでしょうか。希望するのは、ローカル クラスタが利用できない場合のみフェールオーバーが発生する動作です。

回答 可用性を向上させるには、監視サーバーを使用してミラーリングを構成し、プリンシパル サーバーが利用できなくなった場合にフェールオーバーを自動的に発生させます。これは、ローカル クラスタ全体が機能しなくなった場合に、データベース ミラーリングを 2 つ目のクラスタにフェールオーバーしてアプリケーションの続行を可能にするという考え方に基づいています。

問題は、クラスタのフェールオーバーが行われるときに発生します。フェールオーバーにかかる時間は、データベース ミラーリングの既定のタイムアウト設定よりも長くなります。監視サーバーとミラー サーバー (つまり、2 つ目のクラスタ上のアクティブな SQL Server インスタンス) によって、プリンシパル サーバーから切断されていることが確認された後、ミラー サーバーによって 2 つ目のクラスタへのミラーリング フェールオーバーが開始されます。

最も簡単にこの問題を回避するには、監視サーバーを除外して、ローカル クラスタが機能しなくなったときにデータベース ミラーリングが自動的にフェールオーバーしないようにします。もちろん、この場合、手動でフェールオーバーを開始することになるので、可用性は低下します。

2 つ目の方法は、データベース ミラーリングの既定のタイムアウト設定を変更することです。この設定は、1 秒に 1 回送信される、プリンシパルが応答に失敗してもよい "ping" の回数です。指定された回数を超えると、そのプリンシパルは利用不可能と見なされます。この設定はパートナー タイムアウトと呼ばれ、既定値は 10 です。データベースの現在のタイムアウト値は、次のコードを使用して確認できます。

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

次のコードを使用して、タイムアウト値を変更できます。

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

このシナリオでは、パートナー タイムアウトを、通常ローカル クラスタでクラスタ フェールオーバーが発生するまでにかかる時間よりも大きな値に設定する必要があります。クラスタ フェールオーバーが発生したとき、ミラー化されたデータベース上で復旧を実行するのにかかる時間が一定ではないことを考えると、設定値を特定するのは少し困難ですが、上限値を特定することは可能です。この方法の問題は、タイムアウト値を分単位で設定する必要があることです。分単位の設定は、実際に障害が発生したときには適切でない場合があります。

質問 私のバックアップ戦略には完全バックアップとログ バックアップが含まれていますが、復元時間を短縮するには差分バックアップを追加する必要があると聞きました。私は 1 週間に 1 回完全バックアップを実行し、1 時間に 1 回ログ バックアップを実行しています。毎日実行する差分バックアップを追加しようとしましたが、1 つ妙なことに気が付きました。それは、週末に実行する差分バックアップのサイズが、週に 1 回実行する完全バックアップのサイズとほぼ等しくなることです。差分バックアップは、ログ バックアップと同様に増分作成されると思っていましたが、何か見落としているのでしょうか。

回答 差分バックアップの性質を誤解しているようですね。ログ バックアップと異なり、差分バックアップは増分作成されません。差分バックアップには、前回の完全バックアップ以降に変更されたすべてのデータ ファイル エクステントが含まれます (これは、データベース バックアップ、ファイル グループ バックアップ、およびファイル レベル バックアップにも当てはまります)。

エクステント (連続した 8 個のデータ ファイル ページの論理的な集合) は、なんらかの変更が加えられると、差分マップと呼ばれる特別なビットマップ ページにマークされます。差分マップは、各データ ファイルの 4 GB のまとまりごとに作成されます。差分バックアップの実行時に、バックアップ サブシステムによって、すべての差分マップがスキャンされ、変更されたすべてのエクステントがコピーされますが、差分マップはリセットされません。つまり、一連の差分バックアップ間で、より多くのエクステントが変更されると、後から実行されるバックアップのサイズは大きくなります。差分マップがリセットされるのは、完全バックアップが実行されたときだけです。

アプリケーションのワークロードで、データベースのコンテンツが短期間で (たとえば 1 週間で) 大幅に変更された場合、1 週間に 1 回実行する完全バックアップのサイズは、次回の完全バックアップの直前に実行する差分バックアップのサイズとほぼ等しくなります。これがお使いの環境で発生している現象です。

差分バックアップを実行することによって、障害復旧時の復元時間を短縮するのは正しい考え方です。バックアップ戦略として、1 週間に 1 回完全バックアップを実行し、1 時間に 1 回ログ バックアップを実行している場合、最新の状態に復元するには、次の操作が必要になります。

  • ログ末尾のバックアップ (前回のログ バックアップ以降に作成されたすべてのログが含まれます) を作成します。
  • 前回の完全データベース バックアップを復元します。
  • 前回の完全データベース バックアップ以降に作成されたすべてのログ バックアップを順番に復元します。
  • ログ末尾のバックアップを復元します。

この場合、多くのログ バックアップを復元する必要が生じる可能性があります。特に、次回の完全バックアップの直前に障害が発生した場合、復元するログ バックアップの数は多くなります (最悪のシナリオは、24 + 24 + 24 + 24 + 24 + 24 + 23 個のログ バックアップを復元する場合です)。このバックアップ戦略に、毎日実行する差分バックアップを追加すると、復元シーケンスは次のように変わります。

  • ログ末尾のバックアップ (前回のログ バックアップ以降に作成されたすべてのログが含まれます) を作成します。
  • 前回の完全データベース バックアップを復元します。
  • 前回の差分バックアップを復元します。
  • 前回の差分バックアップ以降に作成されたすべてのログ バックアップを順番に復元します。
  • ログ末尾のバックアップを復元します。

この戦略を使用すると、多くのログ バックアップを復元する必要がなくなる可能性があります。その理由は、差分バックアップの復元が、実質的には差分バックアップの対象期間中に作成されたすべてのログ バックアップを復元することと同じであるからです。

毎日差分バックアップを実行するシナリオでは、1 週間の最後の日に障害が発生した場合でも、最大で 23 個のログ バックアップを復元するだけで済みます。差分バックアップが増分作成されないことによるデメリットの 1 つは、より多くの領域が必要になることですが、多くの場合、これは復元時間が削減されることを考えれば妥当な代償です。

質問 2 ノード フェールオーバー クラスタの各ノードで、SQL Server 2005 の 1 つのインスタンスを実行しています。一般的な推奨事項に従って、使用可能なメモリの 50% のみを使用するように各インスタンスを構成していますが、両方のインスタンスで同じパフォーマンス レベルを維持するために、ワークロードに使用するメモリを増やす必要があります。メモリの制限値を取り除いたり大きくしたりすると、いずれかのインスタンスがフェールオーバーし、両方のインスタンスが 1 つのノードで実行されたときに問題が発生すると思います。お勧めの解決策を教えてください。

回答 ここでは、2 ノードで 2 インスタンスの場合を想定して回答しますが、以下の説明はすべて、他の複数インスタンス構成 (N 個のノードと N - 1 個の SQL Server インスタンスが存在する N - 1 フェールオーバー クラスタ) にも当てはまります。

多くの人は、両方のインスタンスのワークロードが増加する (50% を超えるサーバー メモリが使用される) 状況を経験しても、フェールオーバーの発生後に、両方のインスタンスが 1 つのノードで実行されることになった場合のワークロードへの影響を考慮しません。特殊な構成を行わなかった場合、各インスタンスにメモリが均等に分散しなくなり、その結果、一方のワークロードが正常に実行され、もう一方のワークロードの実行速度が低下する可能性があります。

SQL Server 2000 の場合は、各インスタンスのメモリ使用量を、最大でクラスタ ノード メモリの 50% に制限することをお勧めします。これは、SQL Server 2000 のメモリ マネージャがメモリ不足に対処できないからです。たとえば、SQL Server にノードのメモリの 80% が割り当てられた場合、それらのメモリは解放されません。つまり、フェールオーバーが発生したときに、起動したばかりの別のインスタンスで使用できるのは、20% のメモリのみです。両方のインスタンスのメモリ使用量を、ノードのメモリの 50% に制限すると、フェールオーバー インスタンス用に 50% のメモリが確保されます。ただし、各インスタンスのワークロードでも、使用できるメモリの量が 50% に制限されます。

SQL Server 2005 (および SQL Server 2008) の場合は、メモリ マネージャがメモリ不足に対処できるので、最大 50% という制限が適切ではなくなります。ただし、何も制限を適用しなかった場合、2 つのインスタンスが 1 つのクラスタ ノード上で実行されたときに、メモリが均等に分散しなくなるまで、両方のインスタンスが互いにメモリを要求することがあります。

質問への回答としては、両方のインスタンスが多くのメモリを解放することを要求されないように、各インスタンスに最小限のメモリ使用量を設定することをお勧めします。2 ノードで 2 インスタンスの構成を使用する場合は、各インスタンスのメモリ使用量をメモリの 40% 以上に設定するのが一般的です。これにより、各インスタンスを個別のノードで実行する場合に、各インスタンスで必要な量のメモリを確保できます。フェールオーバーが発生した場合、各インスタンスによって、ワークロードで一定レベルのパフォーマンスを維持するために、一定量のメモリが確保され、両方のインスタンスによって共有されるメモリが若干残ります。これは、フェールオーバーの発生時に両方のワークロードのパフォーマンスが (予想どおり) 低下する可能性があることを意味しますが、各インスタンスが個別のクラスタ ノードで実行されているときに、パフォーマンスが制限を受けることはほとんどありません

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 で公開されています。