SQL に関する Q&A: パーティション分割、整合性チェックなど

[アーティクル]
08/24/2016

SQL に関する Q&Aパーティション分割、整合性チェックなど

Paul S. Randal

質問 - 運用データベースを SQL Server® 2005 サーバーに誤ってアタッチしてしまったので、現在、SQL Server 2000 を実行している正しいサーバーに運用データベースをアタッチし直そうとしています。データベースを単純に SQL Server 2005 からデタッチして SQL Server 2000 サーバーにアタッチしたり、バックアップと復元によりアタッチし直そうとしたりしましたが、どちらの方法もうまくいきません。どうして SQL Server 2000 サーバーにアタッチできないのでしょうか。このデータベースは、手元にある唯一のデータベースです。

回答 - まずここで注意すべきことは、バックアップを作成することの重要性です。通常、バックアップが必要になるのは、破損などデータ喪失を伴う障害から回復するためであると考えられています。しかし、今回の場合は不慮のアップグレードですが、アップグレード中の問題という、あまり明確ではない障害が発生しています。ただし、この場合も、データベースに何か起きた場合に備えて、常に最新のデータベースの完全バックアップを保持しておく必要があるということが当てはまります。

意図的であるかどうかにかかわらず、アップグレードはやり直しができない操作で、アップグレードによる影響を元に戻すことは非常に困難です。SQL Server を別のバージョンにアップグレードすると、データベースに対して一連のアップグレード処理が実行されます。通常、各処理では、データベースの物理的な変更が行われ、処理ごとにデータベースのバージョン番号が大きくなります。

たとえば、SQL Server 2000 から SQL Server 2005 へデータベースをアップグレードする場合に実行される主な変更の 1 つには、テーブル、インデックス、列、アロケーションなど、データベースのリレーショナル構造と物理構造についての詳細情報を保持しているデータベースのシステムカタログ (多くの場合、システムテーブルやデータベースメタデータと呼ばれます) の構造の変更があります。

このようなアップグレード処理が実行されるごとに、データベースのバージョン番号が大きくなります。たとえば、SQL Server 7.0 データベースのバージョン番号は 515、SQL Server 2000 データベースのバージョン番号は 539、SQL Server 2005 データベースのバージョン番号は 611 (VarDecimal 機能が有効な場合は 612) です。この番号により、データベースに対して最後に実行されたアップグレード処理がどれかを SQL Server が認識できるようにしています。

SQL Server の各リリースでは、新しい SQL Server リリースにアップグレードされたデータベースを読み取ることはできません (たとえば、SQL Server 2000 では SQL Server 2005 にアップグレードされたデータベースを読み取ることはできません)。これは、古いリリースに、アップグレード後の構造とデータベースレイアウトの解釈に必要なコードが含まれていないためです。このことが原因で、SQL Server 2005 にアップグレードされたデータベースを SQL Server 2000 にアタッチし直すことができないという問題が発生しています。

データベースの完全バックアップがない場合、唯一の対処方法は、アップグレードされたデータベースから全データをエクスポートし、このデータを手動で新しい SQL Server 2000 データベースに戻すことです。SQL Server 2005 の新機能を使用していなければ、スクリプトを使用してデータベース構造を書き出して、SQL Server 2000 でデータベースを作成して、データをエクスポートおよびインポートできます。

スクリプトを使用して SQL Server 2005 のデータベースを書き出すには、SQL Server Management Studio のオブジェクトエクスプローラを使用します (データベースを右クリックし、[タスク] をクリックし、[スクリプトの生成] をクリックします)。ウィザードは名前のとおりスクリプトを生成するためのもので、すべてのオブジェクト、インデックス、制約、トリガなどを作成するスクリプトを生成します。

質問 - 最近スキーマを再設計して、メインテーブルでテーブル分割を使用するとパフォーマンスが向上するという話を聞いたので、そうしました。データベースは、120 GB の単一ドライブ上に保存してあり、テーブルは、単一のファイルグループに含まれてます。スライディングウィンドウ処理は実施せず、新しいパーティションが毎週追加されるだけです。また、すべてのデータはオンラインで利用できる必要があります。クエリの大半は、1 週間以内のデータを処理するものですが、過去 1 年にわたるデータを処理するクエリが多少あります。これは、単一のファイルグループ内で行う方が簡単に処理できるように思えます。このような理解で正しいですか。または他に理解する必要のあることはありますか。

回答 - 単一のファイルグループは使いやすいように思えるかもしれませんが、そのような方法ではパーティション分割を使用するメリットは実感できないでしょう。パーティション分割を使用する主な目的は、データベースメンテナンスの効率と障害発生時のデータの可用性を向上することです。また、パーティション分割を使用すると、パフォーマンスを向上するスキーマを作成できるというメリットがあります。

パーティション分割の典型的な使用例は、12 個のパーティションがあり、各パーティションが過去 1 年の毎月の売り上げデータを表す売り上げテーブルです。月末に、最も古いパーティションが (アーカイブされるか削除されて) 新しいパーティションに置き換えられます。これが、ご質問にあるスライディングウィンドウ方式のシナリオです。当月のパーティションは、読み取り/書き込み可能に設定され、それ以前のパーティションは読み取り専用になり、各パーティションは個別のファイルグループに格納されます。このスキーマであれば、パーティション分割による全メリットが得られますが、すべての状況で最適なわけではありません。

私の妻の Kimberly が、上記のスキーマに手を加えて、より効率的なインデックス作成を可能にする方法を思い付きました。売り上げテーブルを 2 つのテーブルに分割してみてください。1 つは読み取り/書き込み可能な 1 個のパーティションを保持し、もう 1 つは読み取り専用の 11 個のパーティションを保持するテーブルとし、両方のテーブルを対象にするパーティションビューを使用します。

これにより、読み取り/書き込み可能テーブルに含まれるインデックスの数を抑え、読み取り専用テーブルには、レポートクエリをサポートできるように、より多くのインデックスを含められます。その結果、読み取り/書き込み可能なデータでは大量の非クラスタ化インデックスをメンテナンスする必要がないため、読み取り/書き込み可能データに対するデータ操作言語 (DML) 操作の効率が大幅に向上します。

また、読み取り/書き込みデータに対するクエリで、読み取り専用データを処理する必要がなくなるというメリットもあります。SQL Server 2005 では、特に複雑な述語を使用する場合は、クエリプランでのパーティションの解消は完全であるとは言えませんが、SQL Server 2008 ではこの機能が大幅に強化されています。この詳細については、Kimberly のブログ記事 (sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93) を参照してください。

どういうことかをご説明するために、複数のファイルグループにまたがるパーティション分割によって実現される機能のいくつかについて説明しましょう。

部分的なデータベースの可用性これは、プライマリファイルグループがオンラインである限り、障害回復中でもデータベースにオンラインでアクセスできるようにする機能です。1 つのファイルグループしかない場合、データベースの復元中はデータベース全体の可用性が失われます。しかし、複数のファイルグループにデータを分散すると、復元中にオフラインになるのは障害のあるファイルグループだけなので、アプリケーションでは処理を続行できる可能性があります。

段階的な部分復元この手法は、部分的なデータベースの可用性と似ています。ファイルグループが 1 つしかない場合、復元の単位は 1 ページかデータベース全体のどちらかになりますが、ファイルグループが複数あれば、1 ファイルグループのみを復元することができるので、部分的なデータベースの可用性を実現できます。

パーティション分割されたデータベースのメンテナンス前述のパーティション構成のどちらかを使用することで、すべてのパーティションが単一のファイルグループに保持されている場合でも、パーティションごとにインデックスの断片化を解消できます。しかし、ファイルグループが 1 つしかない場合、ファイルグループ単位での整合性チェックを実行できなくなります。ファイルグループ単位での整合性チェックを実行すると、データベースの整合性チェック (DBCC) での処理に必要なデータ量 (と使用される CPU と I/O リソースの量) が大幅に減少する可能性があります。

簡単に言うと、同じファイルグループ内に複数のパーティションを保持できますが、パーティションとファイルグループを 1 対 1 で対応付けることで、さまざまなメリットが得られます。

質問 - 最近、ハイエンドのデータベースサーバーの 1 つで、恐ろしいことが発生しました。不良メモリボードによって、破損が起きていたのです。アプリケーションにでたらめなデータが表示されるようになって、この問題に気が付きました。DBCC CHECKDB を実行したところ、あらゆる種類の破損を検出しました。残念ながら、バックアップにも破損が含まれていたため、手動で不良データを取り除かなければなりませんでした。

手短に説明すると、不良ハードウェアを交換し、ページチェックサムを有効にしました。定期的に整合性チェックを実行したいのですが、長時間のメンテナンス枠を設けておらず、2.4 TB のデータベースのチェックには時間がかかります。どのような対処が可能でしょうか。

回答 - VLDB (非常にサイズの大きなデータベース) に対して整合性チェックなどのメンテナンスを行う方法についての質問はよく寄せられるようになってきています。多くの DBA は、メンテナンスの時間枠内に DBCC CHECKDB の実行を完了できないことがわかると、その実行を諦めてしまいます (また、データベースが常時稼動されていて、長時間にわたり DBCC CHECKDB に必要な CPU と I/O オーバーヘッドを許容できる時間がない場合もあります)。

整合性チェックを諦めて実行しないのは、絶対にお勧めできませんが、代わりに使用できる方法が 4 種類あります。私は、お客様がこの 4 種類の方法を使用できるようにサポートした経験があるので、それをご紹介しましょう。

DBCC CHECKDB の WITH PHYSICAL_ONLY オプションの使用通常の DBCC CHECKDB では、大量の論理整合性チェックが実行され、CPU を多く使用します (基本的に、CPU を集中的に使用する操作になります)。WITH PHYSICAL_ONLY オプションを使用すると、非常に高速な DBCC CHECKALLOC アロケーションビットマップの整合性チェックを実行し、データベース内のすべての割り当て済みページを読み取って監査し、そのページ上のページチェックサムを強制的にテストするように、整合性チェックが制限されます。これにより、DBCC CHECKDB が I/O を集中的に使用する操作となり、実行時間が大幅に短縮されます (実際、完全な DBCC CHECKDB よりも桁違いに処理時間が短くなる場合があり、大幅に時間を節約できます)。

整合性チェックワークロードの分割この場合は、同じサイズの複数のグループにデータベースのテーブルを分割して (最も簡単な方法は、ページ数でテーブルを分割することです)、毎晩、特定の 1 つのグループ内の全テーブルに対して DBCC CHECKTABLE コマンドを使用して整合性チェックを実行します。たとえば、7 つのグループあり、これらのグループを毎日 1 つずつチェックして、DBCC CHECKALLOC と DBCC CHECKCATALOG を週に 1 度実行した場合、全体の処理には 1 週間かかりますが、DBCC CHECKDB を実行した場合と同じ結果が得られます。

複数のファイルグループによるテーブル分割の使用 VLDB の最もサイズの大きいテーブルを、複数のファイルグループに分割できます。整合性チェック方法の一例としては、読み取り/書き込み可能なパーティションを保持しているファイルグループには毎日 DBCC CHECKFILEGROUP を実行し、読み取り専用パーティションを保持しているファイルグループには週に 1 度 DBCC CHECKFILEGROUP を実行することが考えられます。その根拠は、読み取り専用データは、包括的にバックアップされていて、日常的な処理で使用されないためです。したがって、読み取り専用データの場合は破損がそれほど重大な問題にはならないため、整合性チェックを頻繁に実行する必要がありません。

別のサーバーへの整合性チェックのオフロードこの場合は、別のサーバーに正規のデータベースの完全バックアップを復元し、そのサーバー上で DBCC CHECKDB を実行します。このようにすると整合性チェックのワークロードが運用サーバーから完全にオフロードされるのは明らかですが、欠点は、破損が見つかった場合、運用サーバー上で整合性チェックを実行する必要があることです。しかし、このような状況が発生するのはごくまれなので、整合性チェックを実行する方法としては問題ないでしょう。

ご覧のとおりに、完全な DBCC CHECKDB の実行時に必要な余分なリソース負荷を削減できる、VLDB の整合性チェックを実行する方法は多数あります。前述のとおり、私は、お客様がこの 4 種類の方法を使用できるようにサポートした経験があるので、この質問をお送りいただいた方の環境でも、この方法が役立つと思います。

ヒント : トリガを使用してサーバー側のロジックを実装する

トリガを使用してサーバー側のロジックを実装しなければならない場合がありますが、これには注意すべき落とし穴があります。ここでは、留意すべき重要な事項をいくつか紹介します。

トリガは、行単位でなく、ステートメント単位で実行されます。ですから、トリガロジック内にロジックを追加して、ステートメントが影響する行が複数またはゼロであるシナリオを処理できるようにします (影響を受ける行がない場合でも、トリガはステートメント単位で実行されます)。影響を受けるデータは、データ操作言語 (DML) ステートメント用の仮想テーブル内に保持されます。このテーブルは結合が可能なため、ユーザーはテーブル内のデータを操作できます。
トリガは、トランザクション内で同期的に実行されます。すぐまたは適当な時間内に応答が返されるかどうか確証を持てない、外部アプリケーションを呼び出す場合や外部リソースにアクセスする場合は、このことを考慮する必要があります。たとえば、あるテーブルに対して Update ステートメントを実行し、この操作内でトリガが実行される場合、(Update ステートメントでは必ず実行される) トランザクションは、トリガの全ロジックの処理が完了しないと、完了できません。外部のアプリケーションまたはプロセスからエラーコードが返された場合、(実装されているエラー処理とエラーコードによりますが) SQL Server ではトランザクションを中止し、トランザクションをロールバックする可能性があります。ですから、トリガ内で外部処理を必要とし、それがトランザクションにおいて重要なものでない (または同じ操作単位内で実行する必要がない) 場合は、その外部処理を別プロセスに切り離して、非同期的にデータを取得するようにすることをお勧めします。SQL Server 2005 では、このような処理を非同期に実行できる SQL Server Service Broker が導入されています。
トリガ内のステートメントに起因するエラーは、検出することが非常に困難です。1 つのトランザクション内で複数のテーブルが処理される場合は、エラーが発生した場合にトリガを調べることと、適切なエラー処理を実装することが必要です。データベース内のスキーマを変更した場合は、必ずトリガロジックの追跡も行ってください。追跡しないと、小さなトリガが、全体的なパフォーマンスや安定性に大きな影響を及ぼす可能性があります。スキーマの変更に伴う大半の影響は、Visual Studio® for Database Professionals を使用して確認できます。この製品では、プロジェクトの編集中に自動的にスキーマチェックが実行されるほか、データ型の不整合を確認するスタティックコード分析も実行されます。

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

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

その他のリソース