SQL Server 2008 データウェアハウスシナリオ Tips and Tricks Part 1
ご無沙汰しております、マイクロソフトの植田です。
最近まで、SQL Server 2008のシナリオ検証に長らく携わっておりましたがようやく落ち着きましたので、これから何回かにわたって検証の中で得られたTipsや注意点などについてお伝えできれば、と考えております。今回行われました検証の結果はすべて、以下のサイトで公開させていただく予定ですので、ここではドキュメントの中では詳しく紹介できていない注意点などについて綴っていきたいと思っています。
私が主に参加していたのは「大規模データウェアハウス」シナリオですので、そのシナリオに関連したトピックをご紹介していく予定です。
今回は導入として、テストデータベースの構成などについてご紹介したいと思います。検証で使用したデータベースは以下のテーブルで構成されています。
テーブル名 |
種類（ファクト/ディメンジョン） |
行数 |
サイズ(KB) |
SalesFact |
ファクト |
2,400,000,000 |
2,348,810,000 |
MCalendar |
ディメンジョン |
4,748 |
744 |
MProduct |
ディメンジョン |
43,852 |
13,104 |
MStore |
ディメンジョン |
1,300 |
88 |
MCustomer |
ディメンジョン |
1,050,000 |
76,536 |
MAddress |
ディメンジョン |
122,483 |
8,312 |
MAge |
ディメンジョン |
68 |
8 |
MJob |
ディメンジョン |
97 |
8 |
MSalary |
ディメンジョン |
12 |
8 |
MCheck |
ディメンジョン |
6 |
8 |
※非クラスタ化インデックスは除外しています。サイズはデータ圧縮がかかっていない状態です。
大規模データウェアハウスを表す主な特徴としては以下になります。
ü ファクトテーブルの行数は24億件、サイズは約2TB
ü ファクトテーブルは月単位でパーティション分割されている
ü ファクトテーブルは10年分のデータを保持し、 1か月（ 1パーティション）あたりの行数は約2000万件
大規模なテーブルをパーティション分割する利点はいくつかありますが、特に以下の2点が挙げられます。
1. データ管理における自由度が向上する
2. メンテナンス作業の効率化
1については、例えば一つのパーティションに1つのファイルグループを対応させることにより、パーティション分割されたテーブルの物理的なデータ配置を制御できます。典型的な例として、日時データをキーとしてテーブルをパーティション分割し、時間が経過して古くなったデータはファイル単位で移動や圧縮（シュリンク）、または、削除(*)することによりディスクスペースを制御する、と言ったシナリオが挙げられます。今回の検証ではストレージシステムを2つ用意し、最近5年分のデータはメイン（高性能）のストレージに保持し、それよりも古くなったデータはアーカイブ用のストレージに移動させるシナリオをテストしました。ホワイトペーパーの中では、データベースをオンラインのまま、データファイルを移動させる方法について説明させていただく予定です。
(*)パーティションスイッチを持いてパーティションのデータだけを削除した後にファイルを削除する
2に関しては、データベースのバックアップ/リストアや、SQL Server 2008から導入されたデータ圧縮の処理をパーティション単位で行うことができる点が挙げられます。データベース全体ではなく、パーティション単位でバックアップ/リストアを行うので、当然処理時間は短くなります。また、リストアに関しては、リストア対象のパーティション以外はオンラインで行えますので、特定のパーティションのみの復旧作業であれば、データベース全体をオフラインにする必要はない、などのメリットがあります。
データ圧縮はディスクの使用量を削減する素晴らしい機能ですが、システムの利用のされ方によってはパフォーマンスに影響を与える可能性があります。具体的には更新処理が多くなる場合はオーバヘッドが発生することが予想できます。テーブルがパーティション分割されている場合、データ圧縮を使用することによりメリットが得られる可能性が大きくなります。例えば、テーブルのパーティションの中で、頻繁に更新される領域と、頻繁に参照されるがあまり更新されない領域がある場合、更新されるパーティションにはデータ圧縮を用いず、更新があまり行われないパーティションにはデータ圧縮を適応する、と言った運用方法が考えられます。今回の検証では、最新月のパーティション以外は更新されないため、最新月以外のデータが保持されているパーティションにはデータ圧縮を行うシナリオをテストしました。
以上大規模なテーブルをパーティション分割するメリットについて説明してきました。最後に、パーティション単位でのメンテナンスにおける注意点を１つ紹介して今回のポストを締めたいと思います。
SQL Server 2005 SP2 以降からSQL Server 2008にUpgradeし、1つのパーティションを一つのファイルグループに割り当て、ファイルグループ単位でバックアップを行う運用をしている際には注意が必要です。データベースのバックアップを取得する際、事前にデータベースの整合性をDBCC CHECKDBでチェックする運用が一般的ですが、SQL Server 2005のSP2以降から、DBCC CHECKFILEGROUPによって、複数のファイルグループにまたがってパーティション分割されたテーブルに対して、指定ファイルグループのみにおける整合性チェックが行えるようになりました。詳しくはSQL Server 2005 Books Onlineをご参照ください：DBCC CHECKFILEGROUP（Transact-SQL）。この機能強化により、パーティション単位でバックアップをとる場合、データベース全体の整合性をチェックするのではなく、該当のパーティションが使用するファイルグループのみDBCC CHECKFILEGROUPを行うことにより、整合性チェックの時間を短縮することが可能になりました。しかしSQL Server 2008ではDBCC CHECKFILEGROUPで整合性をチェックできるのは指定したファイルグループにすべてのページが収まっているテーブルのみになるため、複数のファイルグループにまたがってパーティション分割されているテーブルの整合性チェックについてはDBCC CHECKDBを使用する必要があります。
複数のファイルグループにまたがってパーティション分割されているテーブルが使用している一つのファイルグループに対してDBCC CHECKFILEGROUPを実行すると以下のようなメッセージが返されます。
オブジェクト"salesfact" (ID 2105058535)、インデックス"salesfact" (ID 0) の行セットID 72057594038779904 を処理できません。未検証のファイルグループ"FG200401" (ID 2) に属しています。
CHECKFILEGROUP により、データベース'mscqidwh' に0 個のアロケーションエラーと0 個の一貫性エラーが見つかりました。
この場合は上記のオブジェクト（テーブル）salesfactの整合性チェックはスキップされるため、指定したファイルグループに整合性エラーがあるか否かは判断できません。
この機能についてはSQL Server の未来のバージョンで改善される予定です。
次回はSQL Server Integration Servicesを使用したデータロードのトピックを取り上げたいと思っています。よろしくお願いします。
コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。