SQL に関する Q&A: 移動、パフォーマンス チューニング、バックアップ、およびデータベース ミラーリング
Paul S. Randal
新しいアレイの移動日
Q. 現在使用中の RAID の空き容量がなくなりかけているので、SQL Server 2005 データベースを別の場所に移動する必要があります。新しいアレイは用意できているので、私はデータベースの移動する準備を進めてきました。データベースの 1 つがトランザクション レプリケーションのパブリッシャーだということに先ほど気付きました。このデータベースを移動できないことは、わかっていますが、どうすればよいでしょうか。
A. 良いお知らせがあります。トランザクション レプリケーションを再初期化するかさまざまなシステム テーブルを直接変更しない限り、パブリケーション データベースを移動できないという制約が存在していたのは、SQL Server 2000 (およびそれ以前) だけです。
SQL Server 2005 と SQL Server 2008 では、データベースが SQL Server の同じインスタンスにアタッチされている限り、トランザクション レプリケーションに関して何もしなくてもデータベースを移動できます (この手順はドキュメントにまとめられています)。データベース ファイルがオンラインの間は移動できないので、ある程度のダウンタイムは許容する必要があります。手順は次のとおりです。
まず、次のコードを使用して、データベースをオフラインにします。ユーザーがデータベースに接続している場合、この手順を正常に実行するには、先にこのようなユーザーの接続を切断する必要があります。
ALTER DATABASE MyDatabaseName SET OFFLINE;
次に、データ ファイルを新しい場所にコピーします。問題が発生した場合にすばやくロールバックできるよう、データベースは移動するのではなくコピーします (移動すると、問題発生時には復元を実行する必要があります)。続いて、次のコードを使用して、各ファイルの新しい場所を SQL Server に通知します。
ALTER DATABASE MyDatabaseName
MODIFY FILE
(NAME = N'LogicalFileName',
FILENAME = N'pathname\filename');
すべてのファイルを物理的にコピーして、コピーしたファイルの場所に関する SQL Server の情報を更新したら、次のコードを使用して、データベースをオンラインに戻します。
ALTER DATABASE MyDatabaseName SET ONLINE;
ページ ラッチについて
Q. パフォーマンス チューニングに関して、いくつかの概念を理解できず苦労しています。"ページ ラッチ" の問題を防ぐ必要があるという記述を何度か目にしました。"ページ" や "ラッチ" にどのような意味があるのかも、ページ ラッチが問題になる理由も、わかりません。すべて説明していただけないでしょうか。
A. SQL Server データベースのデータは、すべてデータ ファイルに格納されています。内部では、データ ファイルは "ページ" と呼ばれる 8 KB のまとまりごとに分割されています。ページは、SQL Server で管理できるストレージと I/O の基本単位です。ページは一般的にディスク上のデータ ファイルに格納され、SQL Server のキャッシュ (バッファー プール) では、ページを読み込んでからクエリを処理する必要があります。
SQL Server では、さまざまな種類のページを使用して、さまざまな種類のリレーショナル データ (テーブルの行、非クラスター化インデックス、テキスト データ、LOB データなど) を格納します。また、リレーショナル データを格納するページを SQL Server で整理してアクセスするために必要な内部データ構造の各部を格納するページもあります。
"ラッチ" とは、SQL Server でキャッシュ内のページへのアクセスを同期するために使用する簡単な内部メカニズムです。注意が必要なページ ラッチには、通常のページ ラッチとページ I/O ラッチの 2 種類があります。いずれかの種類のラッチが取得されるのを SQL Server スレッドで待機する必要がある場合、パフォーマンスの問題が発生していることになります。
データ ファイルの一部がディスクから読み取られるのを SQL Server で待機している場合、ページ I/O ラッチ待機が発生することがあります。ページ I/O ラッチの取得に時間がかかりすぎる場合、通常は、基盤となるディスク サブシステムでパフォーマンスの問題が発生している (つまり、ディスク サブシステムに過剰な負荷がかかっている) ことになります。
SQL Server の複数のスレッドで、メモリ内に格納された同一の 8 KB のデータ ファイル ページにアクセスしようとする場合に、ページへのアクセスが競合していると、ページ ラッチ待機が発生することがあります。また、サイズの小さい一時オブジェクトを tempdb データベースで多用している場合に、この問題が最も発生しやすくなります。
ページ ラッチ待機を監視して削減する方法の詳細については、このコラムで取り扱う範囲を超えているので触れませんが、次のドキュメントで詳細を確認できます。
- SQL Server 2008 オンライン ブックの「SQL Server: Wait Statistics オブジェクト」では、システム モニターを使用して待機の統計を監視する方法を紹介しています。
- SQL Server 2008 オンラインブックの「sys.dm_os_wait_stats (Transact-SQL)」では、一般的な SQL Server の待機の種類とその意味、および SQL Server で待機の統計を監視する方法を紹介しています。
- ホワイト ペーパー「SQL Server 2008 でのパフォーマンスの問題のトラブルシューティング (英語)」では、待機の統計など、さまざまなトラブルシューティングに使用できるクエリや手法を紹介しています。
データベース スナップショットの概要
Q. データベース スナップショットについて知ったばかりです。現在、完全復旧モデルとログ バックアップの代わりにデータベース スナップショットを使用することを検討しています。たとえば、1 時間おきにスナップショットを作成することで、問題が発生しても破損したデータを元に戻せるようにするつもりです。これは、復元にかかる手間と時間を大幅に削減できる方法のように思えます。このように変更しても問題ないでしょうか。
A. 残念ですが、問題があります。データベース スナップショットは、包括的な障害復旧戦略の実用的な代替手法でも可能な代替手法でもありません。障害から完全に復旧することに関して、データベース スナップショットにはトランザクション ログ バックアップと同じ機能はありません。データベース スナップショットには、データベースの全ページのコピーではなく、最初にデータベースを作成してから変更されたページのコピーしか含まれていません。つまり、データベースが破損した場合、基になるデータベースがないのでデータベース スナップショットを持っていても意味がありません。データベース スナップショットは、データベースのばらばらなページの集まりにすぎず、復旧には使用できません。
元のデータベースが使用できる状態であれば、データベース スナップショットを使用すると、データベースから誤って削除してしまったデータを元に戻せます。たとえば、データベースから削除したテーブルがスナップショットに存在していれば、スナップショットを使用して、削除したテーブルを再作成できます。
ただし、潜在的なパフォーマンスの問題のため、(30 分おきに実行するトランザクション ログ バックアップの代わりに) 多数のデータベース スナップショットを作成することはお勧めできません。データベース ページ (「ページ ラッチについて」セクションの回答参照) を置き換えるには、その前に、置き換えるバージョンのページがまだ含まれていない既存のデータベース スナップショットすべてにそのページを同期的にコピーしておく必要があります。作成するデータベース スナップショットが多いほど、多数のページのコピーを作成する必要があるので、パフォーマンスが低下します。
多数のデータベース スナップショットを作成することがお勧めできないもう 1 つの理由は、各データベース スナップショットには、変更前のデータベース ページのコピーが含まれていることです。データベースの変更箇所が多くなるほど、データベース スナップショットのサイズが大きくなります。この結果、ディスク領域の問題やパフォーマンスの問題が発生することがあります。
データベース スナップショットは、頻繁に作成するログ バックアップの代替手段として設計されていません。データベース スナップショットがパフォーマンスに及ぼす影響の詳細については、ホワイト ペーパー「大量の I/O が発生するワークロードにおけるデータベース スナップショットのパフォーマンスに関する考慮事項 (英語)」を参照してください。
また、完全復旧モデルとトランザクション ログ バックアップを使用している方は、障害発生時にさかのぼって復旧する機能や特定の時点への復元を使用する機能にきっと興味をお持ちになるでしょう (これらの機能の詳細については、私が執筆した 2009 年 7 月号のコラム「SQL Server: SQL Server のバックアップについて」と 2009 年 11 月号のコラム「SQL Server: バックアップを使用して障害から復旧する」を参照してください)。
ミラーのしくみ
Q. 使用中のデータベースのデータベース ミラーを構成するように依頼されましたが、データベース ミラーリングを使用しても私たちが抱えている問題には効果がないのではないかと心配です。SAN で破損の問題が数回発生したので、私たちはデータベース ミラーリングで破損から保護する計画を立てています。破損したデータが自動的にミラーに送信されることはないのでしょうか。この問題に対してデータベース ミラーリングにはどの程度の効果がありますか。
A. これは、大きな混乱を招く問題です。冗長なコピーを作成するテクノロジは、 (データベース ミラーリングの用語を使用するならば) プリンシパル データベースからミラー データベースに反映される破損の影響を受けやすいように思えますが、実際には、このような問題は発生しません。
肝心なのは、ミラー データベースの運用方法を理解することです。同期メカニズムによって、プリンシパル データベースからミラー データベースにデータベース ページ全体がコピーされるとすれば、破損は間違いなくミラーに反映されます。その結果、プリンシパル データベースの破損したページがミラーに反映されるでしょう。
しかし、データベース ミラーリングではデータベース間でデータベース ページがコピーされないので、この問題が明確に回避されています。データベース ミラーリングは、プリンシパル データベースからミラー データベースにトランザクション ログ レコードをコピーすることで機能します。トランザクション ログ レコードにはデータベース ページに加えられた物理的な変更が記録され、実際のページ自体は含まれていません (トランザクション レコード、ログ記録、および復旧の詳細については、私が執筆した 2009 年 2 月号のコラム「SQL Server: SQL Server のログ記録と復旧について」を参照してください)。
プリンシパル データベースで使用している I/O サブシステムでデータベース ページが破損しても、その破損がミラー データベースに直接反映されることはありません。起こり得る最悪の状況は、SQL Server で (ページのチェックサムが有効ではないために) ページの破損が検出されず、破損した列の値を使用して計算された値がデータベースに格納される場合です。この正しくない計算結果がミラー データベースに反映されることで、二次的な破損の影響が及びます。先ほど説明したように、ページのチェックサムが有効なら、ディスクからページが読み取られるときにこのような破損が検出されるので、二次的な破損は発生しません。
プリンシパル データベースで整合性チェックを実行しても、ミラー データベースの整合性状態に関する情報が収集されず、その逆も同様になる原因も、この動作から説明できます。プリンシパル データベースとミラー データベースは 2 つの独立したデータベースであって、実際のデータベース ページではなくデータベースに対して行われた物理的な変更の記録を送信することで同期されます。
編集者注: 今月のコラムの技術校閲者を務めてくれた SQLskills.com の Kimberly L. Tripp に感謝します。
Paul S. Randal は SQLskills.com (英語) の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Randal は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は Twitter.com/PaulRandal (英語) でフォローできます。