SQL Server 2005 のパーティション分割されたテーブルとインデックス

  • [アーティクル]

Kimberly L. Tripp
Founder, SQLskills.com

January 2005

対象 :
   SQL Server 2005

概要 : SQL Server 2005 のテーブル ベースのパーティション分割機能は、高い柔軟性とパフォーマンスを実現し、パーティション分割されたテーブルの作成や保守を簡素化します。 ここでは、論理的な手動でのテーブルのパーティション分割から最新のパーティション分割機能に至るまで、機能の進展を振り返ります。また、SQL Server 2005 を使用してパーティション分割されたテーブルをデザイン、実装、保守する理由とそのタイミング、および方法について説明します。  

この資料について   このドキュメントに記載されている機能および計画は、次バージョンの SQL Server に関する現時点での方向性を示したものです。 これらの情報はこの製品の仕様ではなく、将来変更されることがあります。 明示または黙示に関わらず、これらの機能が最終的な製品版に含まれる保証はありません。

このドキュメントは一部の機能について、読者が SQL Server 2000 の機能とサービスに精通していることを前提としています。 背景情報については、SQL Server Web サイトまたは SQL Server 2000 Resource Kit (英語) を参照してください。

本書は製品仕様ではありません。

関連するコード サンプル SQL2005PartitioningScripts.exe をダウンロードしてください。

目次

パーティションを使用する理由
パーティション分割の歴史
   SQL Server 7.0 より前のリリースのオブジェクトのパーティション分割
   SQL Server 7.0 のパーティション分割されたビュー
   SQL Server 2000 のパーティション分割されたビュー
   SQL Server 2005 のパーティション分割されたテーブル
定義と用語
   範囲パーティション
   パーティション分割キーの定義
   インデックスのパーティション分割
   パーティション特有の機能 (分割、マージ、切り替え)
パーティション分割されたテーブルの作成手順
   オブジェクトをパーティション分割するかどうかの決定
   パーティション分割キーとパーティション数の決定
   複数のファイルグループを使用するかどうかの決定
   ファイルグループの作成
   範囲パーティションのパーティション関数の作成
   パーティション構成の作成
   パーティション分割されたテーブルの作成
   インデックスの作成 (パーティション分割を行うかどうか)
作業全体の流れ : ケース スタディ
   範囲パーティション分割 : 売上データ
   パーティション分割されたテーブルの結合
   スライド方式のシナリオ
   リスト形式のパーティション分割 : 地域データ
まとめ
   この資料で使用したスクリプト

パーティションを使用する理由

パーティションとは何でしょう。また、なぜパーティションを使用するのでしょうか。 簡単に言うと、サイズの大きなテーブルやアクセス パターンが複数あるテーブルのスケーラビリティと管理の容易性を向上させるためのものです。 通常は、顧客や売上などエンティティに関する情報を格納するためにテーブルを作成し、各テーブルにはそのエンティティだけを説明する属性を設定します。 エンティティごとに 1 つのテーブルを作成することは、デザインや理解の点で最も容易ですが、このようなテーブルは、特にテーブルが大きくなると、パフォーマンス、スケーラビリティ、管理の容易性の面からは必ずしも最適とは言えません。

大規模テーブルとは、どのようなもので構成されているのでしょうか。 大規模データベース (VLDB) のサイズは数百 GB、ときには数 TB に上りますが、VLDB という用語は、必ずしもデータベース内の各テーブルのサイズを示すものではありません。 大規模データベースとは、希望するパフォーマンスを実現できないデータベースや、運用や保守コストがあらかじめ決められた保守要件や予算を超えるデータベースを指します。 この定義はテーブルにも当てはまります。テーブルも、他のユーザーのアクティビティや保守操作によって可用性が制限される場合は、大規模であると見なすことができます。 たとえば、毎日、毎週、または毎月であっても、2 時間の保守作業中に、パフォーマンスが大幅に落ちたり、アクセスできなくなる場合、その売上テーブルは大規模テーブルであると見なされます。 定期的なダウンタイムは許容できる場合もありますが、デザインを見直し、パーティション分割を実装することでダウンタイムを回避または最小限にとどめられることがよくあります。 VLDB という用語は、データベースにしか使われませんが、パーティション分割を行う場合は、テーブルのサイズに気を配ることがより重要です。

サイズだけでなく、複数のアクセス パターンを持つテーブルも、テーブル内の個別の行セットが異なる使用パターンを持つ場合に、パフォーマンスと可用性が問題になることがあります。 常に使用パターンが複数あるわけではありません (また、これはパーティション分割が必要になる要件でもありません) が、使用パターンが複数ある場合は、パーティション分割により管理の容易性、パフォーマンス、可用性を向上できる場合があります。 また、売上テーブルを例に使うと、今月のデータは読み取り/書き込みを可能に、それ以前のデータ (多くの場合、テーブル内で占める割合の高いデータ) は読み取り専用にできます。 このようにデータの使用方法が異なる場合や、テーブルからのデータの読み取り/書き込み時の保守オーバーヘッドが非常に高い場合は、ユーザー要求に対してテーブルの応答が悪くなる可能性があります。 これは、結果として、サーバーの可用性とスケーラビリティを制限することになります。

また、大きなデータセットが異なる方法で使用されている場合は、静的データに対して保守操作が頻繁に行われます。 これは、パフォーマンスの問題や、ブロッキングの問題、バックアップ (空き容量、時間、操作コスト) など、手痛い影響を生じるほか、サーバー全体のスケーラビリティにも悪影響を与えます。

パーティション分割はどのように有効なのでしょうか。 パーティション分割は、テーブルやインデックスが肥大化した場合に、データをより小さく管理しやすいセクションに分割することで効力を発揮します。 この資料では、行方向のパーティション分割を取り上げます。行方向のパーティション分割では、大きな行グループが複数の異なるパーティションに保存されます。 パーティション分割されるセットの定義は、ユーザーのニーズに応じて、カスタマイズ、定義、管理します。 Microsoft SQL Server 2005 を使用すると、定義された範囲またはリストを使って、特定のデータ使用パターンに基づいてテーブルをパーティション分割できます。 また、SQL Server 2005 では、新しいテーブルとインデックスの構造を基にデザインされた機能が追加されており、パーティション分割されたテーブルやインデックスの長期的な管理をさまざまな方法で実行できます。

さらに、大規模テーブルが、複数の CPU が搭載されたシステムに配置されている場合は、テーブルをパーティション分割することで、並列処理によりパフォーマンスが向上する可能性があります。 巨大なデータセット (たとえば数百万行) に対して大規模な操作を行う場合のパフォーマンスは、個別のサブセットに同時に複数の操作を実行することで改善できます。 パーティションを用いたパフォーマンスの向上の例は、これまでのリリースでも集計操作において見ることができます。 たとえば、SQL Server では、1 つの大規模テーブルを集計するのではなく、個別のパーティションを集計して、その集計をさらに集計することができました。 SQL Server 2005 では、パーティション分割を行うだけで、大きなデータセットを結合するクエリのパフォーマンスを向上できる可能性があります。SQL Server 2000 は、サブセットに対する並列結合操作をサポートしていましたが、実行中にサブセットを作成する必要がありました。 SQL Server 2005 では、同じパーティション分割キーとパーティション関数を使ってパーティション分割された関連するテーブル (Order テーブルと OrderDetails テーブルなど) は、配置されていると言われます。 オプティマイザが、パーティション分割された連携テーブル 2 つが結合されていることを検出すると、SQL Server 2005 では、まず同じパーティション上にあるデータを結合し、その結果をさらに結合できます。 このため、SQL Server 2005 ではマルチ CPU コンピュータをより有効に使用できます。

パーティション分割の歴史

パーティション分割という概念は、SQL Server にとって新しいものではありません。 実際、何らかのパーティション分割は、SQL Server のどのリリースでも可能でした。 ただし、パーティション分割は多くの場合、パーティション構成の作成や保守の支援を特に目的とした機能がないと、非常に手間がかかり、十分に活用できませんでした。 また、ユーザーも開発者も、(複雑なデータベース デザインにより) スキーマを誤解しがちで、メリットは減殺されています。 しかし、パーティション分割によりパフォーマンスを大幅に向上できるので、SQL Server 7.0 から導入されたパーティション分割機能は継続的に強化がはかられています。SQL Server 7.0 では、パーティション分割されたビューを用いて一種のパーティション分割が可能になりました。 SQL Server 2005 では、パーティション分割されたテーブルによる大規模データセットのパーティション分割機能により、大きなメリットが提供されるようになりました。

SQL Server 7.0 より前のリリースのオブジェクトのパーティション分割

SQL Server 6.5 以前では、パーティション分割を行う場合に、最初からデザインの一部に取り入れ、データ アクセス コーディングとクエリ処理のすべてを組み込む必要がありました。 複数のテーブルを作成し、ストアド プロシージャ、ビュー、クライアント アプリケーションを使って適切なテーブルへのアクセスを管理することで、操作によってはパフォーマンスを改善できることも多くありましたが、デザインが複雑にならざるを得ませんでした。 ユーザーや開発者一人一人が、正しいテーブルを認識し、適切に参照しなければなりませんでした。 各パーティションは個別に作成、管理され、ビューを使ってアクセスが簡素化されていましたが、このソリューションではわずかなパフォーマンスの向上しか実現できませんでした。 UNION を使ってビューを構築し、ユーザーやアプリケーションからのアクセスを簡素化している場合、クエリ プロセッサは基になるテーブルにすべてアクセスし、どのデータを結果セットに含めるかを決定しなければなりませんでした。 基になるテーブルのサブセットの一部しか必要でない場合は、適切なテーブルだけを参照できるように、各ユーザーや開発者がデザインを理解している必要がありました。

SQL Server 7.0 のパーティション分割されたビュー

SQL Server 7.0 より前のリリースでは、パーティションを手動で作成する際に生じる課題は、主にパフォーマンスに関するものでした。 ビューを使うことでアプリケーションのデザイン、ユーザー アクセス、およびクエリ記述は簡素化できますが、パフォーマンスの向上は望めませんでした。 SQL Server 7.0 のリリースでは、クエリ オプティマイザが不要なテーブルをクエリ プランから除外 (つまり、パーティションを削除) し、UNION ビューが複数のテーブルにアクセスする際のプラン全体のコストを大幅に削減できる制約がビューに組み込まれました。

図 1 は、YearlySales ビューを説明しています。 すべての売上データを 1 つの大規模テーブルに格納するのではなく、12 個のテーブル (SalesJanuary2003 や SalesFebruary2003 など) を定義し、1 年全体 (YearlySales) のビューだけでなく、四半期ごとのビューも作成できるようになっています。

図 1. SQL Server 7.0/2000 のパーティション分割されたビュー

次のクエリを使って YearlySales ビューにアクセスした場合は、SalesJanuary2003 テーブルだけにアクセスします。

SELECT ys.* 
FROM dbo.YearlySales AS ys
WHERE ys.SalesDate = '20030113'

制約が信頼でき、ビューに対するクエリが WHERE 句を使用してパーティション キー (制約が定義されている列) を基に結果を制限している限り、SQL Server はベース テーブルのうち必要なテーブルにしかアクセスしません。 "信頼できる制約" とは、すべてのデータがこの制約により定義されたプロパティに準じていることを SQL Server が保証できる制約を言います。 制約を作成する場合、既定では WITH CHECK 制約が作成されます。 この設定により、テーブルにスキーマ ロックがかけられ、データを制約に対して検証できるようにしています。 検証プロセスにより、既存のデータが検証されて、制約が追加され、スキーマ ロックが解除されると、これ以降の挿入、更新、削除は、設定された制約に従わなければならなくなります。 この方法を使って信頼できる制約を作成することで、開発者は、目的のテーブルに直接アクセスする必要なく (または認識する必要さえなく)、ビューを使ってデザインを大幅に簡素化できます。 SQL Server は信頼できる制約により、不要なテーブルを実行プランから除外して、パフォーマンスを向上させます。

注   制約はさまざまな状態で "信頼できない" ものになる可能性があります。たとえば、CHECK_CONSTRAINTS 引数を指定せずに一括挿入が実行された場合や、NOCHECK を指定して制約が作成された場合などです。 クエリ プロセッサは制約が信頼できない場合、適切なベース テーブル内に要求されたデータが実際に格納されていることを検証できないので、ベース テーブルすべてをスキャンします。

SQL Server 2000 のパーティション分割されたビュー

SQL Server 7.0 では、SELECT ステートメントのデザインを簡素化し、パフォーマンスを向上できますが、データ変更ステートメントには何のメリットももたらされていませんでした。 INSERT ステートメント、UPDATE ステートメント、および DELETE ステートメントは、ベース テーブルに対してしか実行できず、UNION を使ってテーブルから構築したビューには直接実行できませんでした。 SQL Server 2000 では、データ変更ステートメントも、SQL Server 7.0 で導入されたパーティション分割されたビュー機能の恩恵を受けることができます。 データ変更ステートメントが、同じパーティション分割されたビュー構造を使用できるので、ビューを利用して適切なベース テーブルを変更することができます。 これを適切に構成するには、パーティション分割キーとその作成時にさらに制約が必要になりますが、SELECT クエリを直接適切なベース テーブルに送る場合でも、変更クエリを直接適切なベース テーブルに送る場合でも基本原理は同じです。 SQL Server 2000 のパーティション分割の制約、セットアップ、構成、およびベスト プラクティスについては、「Microsoft SQL Server 2000 Data Warehouse におけるパーティションの使用」を参照してください。

SQL Server 2005 のパーティション分割されたテーブル

SQL Server 7.0 と SQL Server 2000 での機能強化により、パーティション分割されたビュー使用時のパフォーマンスは大幅に向上しましたが、パーティション分割されたデータセットの管理、デザイン、または開発が簡素化されたわけではありませんでした。 パーティション分割されたビューを使用する場合は、(ビューが定義されている) ベース テーブルすべてを個別に作成し、管理する必要があります。 ベース テーブルのどれに直接アクセスすべきかを把握しておく必要がないので、アプリケーションのデザインは容易になり、ユーザーにもメリットがありますが、管理するテーブルが多数あり、テーブルごとにデータ整合性制約を課す必要があるため、管理は複雑になります。 この管理上の問題のために、多くの場合、パーティション分割されたビューは、データを取得または読み込む必要がある場合にテーブルを分離するためだけに使用されていました。 データが読み取り専用テーブルに移動された場合や、データが読み取り専用テーブルから削除された場合の操作は、時間がかかり、ログ領域が必要で、ブロッキングが発生することも多く、効率の悪いものでした。

また、以前のバージョンのパーティション分割方法では、開発者が個別のテーブルとインデックスを作成し、その後ビューを使ってこれらを結合 (UNION) していたので、オプティマイザが (インデックスが異なる可能性があるため) 各パーティションのプランを検証し、決定する必要がありました。 したがって、SQL Server 2000 では、クエリの最適化にかかる時間が、処理するパーティションの数に比例して増大しました。

SQL Server 2005 では、各パーティションは本質的に同じインデックスを使用します。 たとえば、当月の OLTP (オンライントランザクション処理) データを各月末に分析テーブルに移動する必要があるというシナリオを考えて見ましょう。 この分析テーブル (読み取り専用クエリだけで使用) は、クラスタ化インデックスを 1 つと非クラスタ化インデックスを 2 つ持つ単一のテーブルであるとします。(既にインデックスが作成されアクティブな単一テーブルに対する) 1 GB の一括読み込みが実行された場合、テーブルまたはインデックスが断片化またはロックされるため、現在のユーザーがブロックされます。 また、行が読み込まれるたびにテーブルとインデックスを保守する必要があるため、読み込み処理にはかなりの時間がかかります。 一括読み込みの時間を短縮できる方法はありますが、他の全ユーザーに直接影響が出る可能性があるほか、同時実行処理を犠牲にする必要があります。

上記のデータを、新しく作成した (空の) インデックスが設定されていない (ヒープ) テーブルに分離した場合は、まず読み込み処理を行い、データの読み込みが完了した後にインデックスを作成できます。 この方法を使うと、多くの場合、10 倍以上のパフォーマンスの向上が見られます。 実際に、インデックスが設定されていないテーブルにデータを読み込む場合は、複数のデータ ファイルを同時に読み込むか、同じファイルから (開始行と終了行を定義し) 分割した複数のデータ ブロックを同時に読み込むことで、マルチ CPU を活用できます。 どちらの場合も並行処理によるメリットにより、高いパフォーマンスの向上を実現できます。

SQL Server のどのリリースでも、パーティション分割によって、より細かい制御が可能になり、全データを 1 箇所にまとめておく必要がなくなりますが、多数のオブジェクトを作成および管理する必要があります。 以前のリリースでも、動的にテーブルを作成および削除し、UNION ビューを変更することで、実用的なパーティション分割戦略を実現することは可能でした。 しかし、SQL Server 2005 では、より洗練されたソリューションを実現できます。既存のパーティション構成の追加パーティションとして、新しくデータを読み込んだパーティションを構成に組み込み、古いパーティションを構成から除外するだけでよいのです。 この処理全体にかかる時間はわずかです。また、一括読み込みやインデックス作成を並列処理すれば、さらに効率を上げることができます。 より重要なことは、パーティションはテーブルから独立して操作されるため、パーティションが追加されるまで、クエリ対象のテーブルには影響がないことです。 その結果、通常は、パーティションの追加には数秒しかかかりません。

データ削除時のパフォーマンスも、大幅に向上されます。 あるデータベースで、新しいデータ (たとえば今月のデータ) が追加されると、古いデータ (前年同月のデータなど) が削除されるというスライド方式のデータセットが必要な場合、このデータの移動処理のパフォーマンスは、パーティション分割を使用することで、大幅に改善されます。 これは、過激な方法に思えるかもしれませんが、パーティション分割を使用しなかった場合との違いを考えて見ましょう。すべてのデータを 1 つのテーブルにまとめた場合、1 GB の古いデータを削除すると、テーブルの各行とそれに関連するインデックスを 1 つずつ処理しなければなりません。 このデータ削除処理により、大量のログ操作が発生し、削除中はログ切捨てを行えないため (ただし、削除は単一の自動コミット トランザクションですが、可能な場合は複数の削除を実行してトランザクション サイズを制御できます)、非常にサイズの大きいログが必要になる可能性があります。 しかし、パーティション分割を使用すれば、同じデータを削除した場合、パーティション テーブルから特定のパーティションを削除し (これは、メタデータ操作です)、スタンドアロンのテーブルの削除または切り捨てを行えばよくなります。

また、パーティションの最適なデザイン方法を理解していなければ、パーティションと合わせてファイル グループを使用することが、パーティション分割の実装には理想的であることにお気づきではないかもしれません。 ファイル グループを使うと、個別のテーブルを異なる物理ディスクに格納できます。 1 つのテーブルが (ファイル グループを使って) 複数のファイルで構成されている場合、物理的なデータの場所は予想できません。 並列処理が行われないシステムでは、ファイル グループによりすべてのディスクを平等に使用し、実際のデータの場所をより意識せずにすむようにすることで、SQL Server はパフォーマンスを向上させます。

注   図 2 は、3 つのファイルで構成される 1 つのファイル グループを示しています。 Orders と OrderDetails の 2 つのテーブルが、このファイル グループに格納されています。 テーブルがファイル グループに格納されると、SQL Server は、ファイル グループ内のオブジェクトに領域が必要になるため、ファイル グループ内のファイルからそれぞれエクステント割り当て (64 KB、つまり 8 KB のページ 8 ページ分) を確保して、各ファイルに均等にデータを読み込みます。 Orders テーブルと OrderDetails テーブルが作成される時点では、このファイル グループは空です。 注文が発生すると、Orders テーブルにデータ (1 つの注文につき 1 行) 入力され、1 品目につき 1 行が OrderDetails テーブルに入力されます。 SQL Server は、File 1 からエクステントを Orders テーブルに割り当て、別のエクステントを File 2 から OrderDetails テーブルに割り当てます。 OrderDetails テーブルは、Orders テーブルよりも早いペースでサイズが増加すると予想されますが、この次の割り当ては、次に領域が必要になったテーブルに対して行われます。 OrderDetails のサイズが増加すると 、次のエクステントを File 3 から取得します。このようにして、SQL Server は "ラウンドロビン方式" でファイル グループ内のファイルの領域を割り当てて行きます。 図 2 では、各テーブルの後にエクステントを、各エクステントの後に対応するファイル グループを示しています。 各エクステントは領域が必要とされた段階で割り当てられ、順番に番号が付けれらています。

図 2. ファイル グループを使用した均等割り当て

SQL Server は、ファイル グループ内のすべてのオブジェクトに均等に割り当てを行います。 任意の操作に対して使用するディスクが多いほど SQL Server はより効果的に実行できますが、管理や保守の観点からすると、特に使用パターンが極めて予想しやすい (また分離される) 状況では、多数のディスクを使用することは最良の手段ではありません。 データがディスク上の特定の場所に格納されるわけではないので、バックアップなどの保守操作時にデータを分離できません。

SQL Server 2005 のパーティション テーブルを使用すると、(関数やパーティション構成を使用して) 同じパーティション分割キーを持つすべての行を直接 (また常に) 任意の場所に格納されるようなテーブルをデザインできます。 関数は、パーティションの境界と、最初の値が置かれるパーティションを定義します。 パーティション関数 LEFT の場合は、最初の値は、最初のパーティションの上部の境界の役目を果たします。 パーティション関数 RIGHT の場合は、最初の値は、2 番目のパーティションの下部の境界の役目を果たします (パーティション関数については、後で詳述します)。 関数が定義できたら、パーティション構成を作成し、(パーティション関数を基に) パーティションとデータベース内の各パーティションの場所との物理マッピングを定義します。 複数のテーブルが (パーティション構成は異なったとしても) 同じ関数を使用している場合、同じパーティション分割キーを持つ行に対しては、同様のグループ化が行われます。 この概念は、配置と呼ばれます。 同じまたは異なる物理ディスク上にある複数のテーブルから同じパーティション キーを持つ行を配置することで、オプティマイザの指示によっては、SQL Server は各テーブルから取得された必要なデータ グループだけを操作できます。 配置を行うには、2 つのパーティション分割されたテーブルまたはインデックスそれぞれのパーティション間になんらかの対応条件が必要です。 パーティション列に対して同等のパーティション関数を使用する必要があります。 以下の場合は、2 つのパーティション関数を使用してデータを配置できます。

  • どちらのパーティション関数も、同じ数の引数とパーティションを使用している。
  • 各関数で使われているパーティション分割キーが、(長さや、指定されている場合は有効桁数と小数点部桁数、照合順序を含め) 同じ種類である。
  • 境界の値 (LEFT または RIGHT による境界条件を含む) が同じである。

注   2 つのパーティション関数がデータを配置するようにデザインされている場合でも、パーティション テーブルと同じ列を使ってインデックスがパーティション分割されていない場合は、インデックスが配置されないことがあります。

併置は、配置よりも強力な形態です。併置では、配置されたオブジェクトが、パーティション列を対象とする等結合述語を使って結合されます。 これは、等結合述語が使用される可能性のあるクエリやサブクエリなどの構造では、重要になります。 パーティション列を基にテーブルを結合するクエリは、一般に高速に処理できるため、併置は有用です。 図 2 の Orders テーブルと OrderDetails テーブルを使って考えて見ましょう。 ファイルに均等にデータを格納する代わりに、3 つのファイル グループをマップするパーティション構成を作成できます。 Orders テーブルと OrderDetails テーブルを定義する際に、これらのテーブルが同じパーティション構成を使用するように定義します。 これにより、パーティション キーに同じ値を持つ関連するデータは、同じファイル内に格納され、結合に必要なデータが分離されます。 複数のテーブルの関連する行が同じ方法でパーティション分割されると、SQL Server はテーブル全体または複数のパーティション (テーブルが異なるパーティション関数を使用している場合) を検索して一致する行を探さなくても、パーティションを結合できます。 この場合、オブジェクトは同じキーを使用しているので単に配置されているだけでなく、同じファイル内に同じデータが格納されているので、ストレージ レベルでも配置されています。

図 3 は、2 つのオブジェクトが同じパーティション構成を使用して、同じパーティション分割キーを持つデータ行がすべて同じファイル グループに格納される様子を示しています。 関連するデータが配置されると、SQL Server 2005 は大規模なデータセットを効果的に並列処理できるようになります。 たとえば、(Orders テーブルと OrderDetails テーブル両方の) 1 月の全売上データを最初のファイル グループに、2 月のデータを 2 番目のファイル グループにといった具合に配置できます。

図 3. ストレージ レベルで配置されたテーブル

SQL Server では、範囲を基にしたパーティション分割が可能で、テーブルやインデックスが同じパーティション構成を使い、より優れた配置を実現できるようにデザインできます。 優れたデザインは、全体的なパフォーマンスを大幅に向上できますが、時間の経過と共にデータの使用方法が変わってしまった場合はどうなるのでしょうか。 また、パーティションを追加しなければならなくなった場合はどうなるのでしょうか。 パーティション テーブルに関係なく、パーティションを追加、削除、および管理できるという、容易な管理を実現することが、SQL Server 2005 の大きなデザイン目標でした。

SQL Server 2005 には、管理、開発、および使いやすさを考慮して簡素化されたパーティション分割機能が実装されています。 パフォーマンスや管理の容易性のメリットとしては、以下のことが挙げられます。

  • パフォーマンスや管理の容易性を向上するための、パーティション分割を必要とする大規模テーブルのデザインや実装が簡素化されます。
  • 他のパーティションのデータ アクセスへの影響を最小限に抑えながら、既存のパーティション テーブルの新しいテーブルにデータを読み込むことができます。
  • 同じデータを新しい空のテーブルに読み込む場合と同じパフォーマンスで、既存のパーティション分割されたテーブルの新しいパーティションにデータを読み込めます。
  • パーティション分割されたテーブルの他の部分へのアクセスの影響を最小限に抑えながら、パーティション分割されたテーブルの一部をアーカイブまたは削除できます。
  • パーティション分割されたテーブルのパーティションを切り替えることで、パーティションを保守できます。
  • 複数の関連するテーブルを対象とした極めて大掛かりな操作のスケーラビリティと並列処理性能を向上できます。
  • すべてのパーティションに対するパフォーマンスを向上させます。
  • 個別のパーティションを個別に最適化する必要がないので、クエリの最適化時間が短縮されます。

定義と用語

SQL Server 2005 のパーティションを実装するには、いくつかの新しい概念、用語、構文を理解する必要があります。 これらの新しい概念を理解するために、まず、作成と格納の面からテーブルの構造を確認しましょう。 以前のリリースでは、テーブルは常に物理的および論理的な概念でしたが、SQL Server 2005 のパーティション分割されたテーブルとインデックスを使うと、テーブルの保存方法や場所に関して複数の選択肢が提供されます。 SQL Server 2005 では、それ以前のリリースと同じ構文を使ってテーブルとインデックスを作成できます。その場合、単一の表形式の構造が、既定のファイル グループまたはユーザーが定義したファイル グループに格納されます。 この方法以外に、SQL Server 2005 では、パーティション構成を基にテーブルとインデックスを作成できます。 パーティション構成は、オブジェクトを 1 つ以上のファイル グループにマップします。 各データの適切な物理的な場所を決める場合に、パーティション構成はパーティション関数を使用します。 パーティション関数により行を指定するためのアルゴリズムを定義し、パーティション構成により、各パーティションを適切な物理場所 (ファイル グループ) に関連付けます。 つまり、テーブルは、論理的な概念であることに変わりありませんが、ディスク上の物理構成は、これまでのバージョンとは完全に異なります。テーブルに構成を適用できるのです。

範囲パーティション

"範囲パーティション" は、特定のカスタマイズ可能なデータ範囲によって定義されるテーブル パーティションです。 範囲パーティションの境界は、開発者が指定し、データの使用パターンの変化に合わせて、変更することができます。 通常、この範囲は、日付や何らかの規則に基づいてグループ化されたデータを基準にして決められます。

範囲パーティションは、主に、データのアーカイブ、意思決定支援 (任意の月や四半期など、特定の範囲のデータのみが必要になることが多い場合)、行のライフサイクルを通じてデータの使用方法が変化するような OLTP および意思決定支援システム (DSS) の連携操作などの目的で使われます。 SQL Server 2005 のパーティション分割されたテーブルとインデックスの最大のメリットは、特にアーカイブや保守を行う場合に、範囲を厳密に指定してデータを操作できることです。 範囲パーティションを使用すると、古いデータを素早くアーカイブし、置き換えることができます。 範囲パーティションは、データ アクセスが発生する主な理由が広範なデータを対象とした意思決定支援のためである場合に最適です。 この場合、データの場所を具体的に指定し、必要に応じて、適切なパーティションだけにアクセスする必要があります。 また、トランザクション データが利用可能になった時点で、このデータを容易に素早く追加できます。 範囲パーティションは、各パーティションの境界条件を定義する必要があるため、基本的に定義がより複雑になります。 また、パーティション構成を作成して、各パーティションを 1 つ以上のファイル グループにマップする必要もあります。 しかし、多くの場合これらは一定のパターンになるので、いったん定義できれば、プログラムを使って容易に保守できます (図 4 参照)。

図 4. 12 個のパーティションを持つ範囲パーティション テーブル

パーティション分割キーの定義

テーブルやインデックスをパーティション分割するための最初の手順は、パーティション分割キーとなるデータを定義することです。 パーティション分割キーは、テーブル内の 1 列で、特定の条件を満たしている必要があります。 パーティション関数は、パーティション分割キー (データの論理的分離とも言います) が基にするデータ型を定義します。 パーティション関数はこのキーを定義しますが、ディスク上のデータの物理的な場所については指定しません。 データの格納場所は、パーティション構成により決まります。 つまり、パーティション構成は、データを 1 つ以上のファイル グループにマップし、ファイル グループがデータを特定のファイル (ディスク) にマップします。 パーティション構成は、関数を使用する場合、常に次のように動作します。たとえば、関数が 5 つのパーティションを定義している場合、パーティション構成は必ず 5 つのファイル グループを使用します。 ファイル グループが異なっている必要はありませんが、複数のディスクを使用して、できれば複数の CPU を使用した方が、優れたパフォーマンスが得られます。 パーティション構成をテーブルに適用する場合は、パーティション関数の引数として列を定義します。

範囲パーティションの場合、データセットは論理的なデータドリブン境界によって分割されます。 実際に、データ パーティションは完全には均等にならない可能性があります。 分析対象となる境界 (範囲) が特定されるような形でテーブルが使用される場合に、データの使用方法によって範囲パーティションは規定されます。 範囲関数のパーティション分割キーは、1 列だけでもかまいません。その場合、このパーティション関数では、(データの整合性や制約のために) テーブル内に該当するデータがない場合でも領域全体が対象とされます。 つまり、各パーティションの境界は定義されますが、最初のパーティションと最後のパーティションには、左端 (境界条件の最低値よりも低い値) と右端 (境界条件の最高値よりも高い値) の行が含まれる可能性があります。 したがって、値の領域を特定のデータセットに制限するには、パーティションに CHECK 制約を組み合わせる必要があります。 ビジネス ルールを適用する CHECK 制約とデータ統合制約を使用することで、データセットを無制限の範囲ではなく、制限された範囲に限定できます。 範囲パーティションは、保守と管理において定期的に広範なデータのアーカイブが必要になる場合や、範囲のサブセット内にある大量のデータにクエリがアクセスする場合に理想的です。

インデックスのパーティション分割

テーブルのデータセットのパーティション分割だけでなく、インデックスもパーティション分割できます。 テーブルとインデックスの両方を、同じ関数を使ってパーティション分割すると、多くの場合最高のパフォーマンスが得られます。インデックスとテーブルが同じパーティション関数と列を同じ順番で使用している場合、テーブルとインデックスは配置されます。 既にパーティション分割されたテーブルに対してインデックスを作成する場合、明示的に異なるパーティション分割をインデックスに対して行わない限り、SQL Server は自動的に新しいインデックスをテーブルのパーティション構成に合わせます。 テーブルとそのインデックスが配置されている場合、すべてのデータとインデックスが、同じアルゴリズムにより分割されているので、SQL Server はパーティション テーブルのパーティションの移動をより効率的に行うことができます。

テーブルとインデックスが同じ関数で定義されているだけでなく、同じパーティション構成を採用している場合は、"ストレージレベルで配置" されていると見なされます。 ストレージレベルの配置のメリットの 1 つは、同じ境界内のすべてのデータが、同じ物理ディスクに格納されることです。 この場合、ある時間帯だけバックアップを切り離して行ったり、データの変動状態により、バックアップの頻度や種類などの方針を変えることができます。 その他のメリットは、同じファイルまたはファイル グループ内のテーブルとインデックスが結合または集計される際に認めることができます。 SQL Server は、パーティション全体で操作を並列処理することでパフォーマンスを向上させます。 ストレージ レベルの配置が行われ、複数の CPU で処理を行う場合は、必要なデータがすてべ同じディスク上にあるので、データ アクセスが競合せず、各プロセッサにより、直接特定のファイルまたはファイル グループが処理されます。 これにより、中断されることなく、より多くのプロセスが並列処理されます。

詳細については、SQL Server Books Online の「Special Guidelines for Partitioned Indexes」を参照してください。

パーティション特有の機能 (分割、マージ、切り替え)

パーティション分割されたテーブルの使用を支援するため、パーティションの管理に関する新しい機能と概念が導入されています。 パーティションは規模の大きいテーブルに対して使用するので、パーティション関数の作成時に指定したパーティション数は、時間の経過と共に変化します。 新しい分割オプションと ALTER TABLE ステートメントを併用すると、新しいパーティションをテーブルに追加できます。 パーティションを分割する際、データを新しいパーティションに移動できますが、パフォーマンスを保つため、行は移動しないようにします。 このシナリオについては、この資料で後ほど取り上げるケース スタディで説明します。

逆に、パーティションを削除する場合は、データを構成から除外し、境界点をマージします。 範囲パーティションの場合は、削除する境界点を指定することで、マージ要求を行います。 ある一定の期間のデータだけが必要で、データのアーカイブが定期的に (たとえば毎月) 行われる場合は、当月のデータが入手できた段階で、任意のデータ パーティション (最も古い月のデータ) をアーカイブすることもできます。 たとえば、1 年分のデータを利用するようにし、各月末に当月分のデータを構成に追加し、最も古い月のデータを構成から除外して入れ替え、当月の読み取り/書き込み OLTP と前月以前の読み取り専用データを分けることもできます。 以下のシナリオで説明するように、このプロセスを最も効率よく実行できる操作手順があります。

1 年分の読み取り専用データを利用可能にしているとします。 現在、テーブルには 2003 年 9 月から 2004 年 8 月までのデータが保持されています。当月の 2004 年 9 月分のデータは別のデータベースに格納されていて、OLTP 用に最適化されています。 読み取り専用テーブルには、13 個のパーティションがあります。そのうちの 12 個のパーティションにはデータ (2003 年 9 月 ～ 2004 年 8 月分) が格納されていて、最後の 1 パーティションは空のパーティションです。 この最後のパーティションは、範囲パーティションでは常に領域全体 (左端と右端の両方) が含まれるため、空になっています。 データをスライド方式のシナリオで管理する場合は、常に新しいデータを格納するための空のパーティションを分割しておくことになるでしょう。 LEFT 境界点が定義されたパーティション関数の場合、この空のパーティションは論理的に右端が存在します。 最後のパーティションを空にしておくことで、この空パーティションを (新しいデータの読み込み用に) 分割でき、(新しいデータを格納するためにパーティションを分割する際に) 追加される新しいファイル グループに最後のパーティションから行を移動する必要がありません (行が存在しないため)。 これは、非常に複雑な概念ですが、目的はデータの追加や削除のすべてをメタデータだけの操作で行うことです。この概念については、この資料のケース スタディで後ほど詳しく説明します。 メタデータだけの操作が行われるようにするには、テーブルの変更箇所を戦略的に管理する必要があるでしょう。 このパーティションを空にしておくため、CHECK 制約を使用して、ベース テーブルのデータを制限します。 この場合、OrderDate は 2003 年 9 月 1 日から 2004 年 9 月 1 日の前日までのデータを格納している必要があります。 最後に定義された境界点が 8 月 31 日の 11:59:59.997 の場合 (なぜ 997 であるかは、後述します)、パーティション関数とこの制約により、最後のパーティションは空のまま保持されます。 これらは概念に過ぎませんが、分割とマージは、ALTER PARTITION FUNCTION により、切り替えは ALTER TABLE により処理されることを理解しておく必要があります。

図 5. データの読み込み/アーカイブ前の範囲パーティション境界

(OLTP データベースで) 10 月のデータ処理が開始されたら、分析に使用するために 9 月のデータをパーティション テーブルに移動します。 テーブルの切り替え処理は極めて短時間で完了できます。また、この準備作業はパーティション テーブルとは関係なく実行できます。 このシナリオについては、後ほどケース スタディで説明しますが、最終的にはパーティション分割されたテーブル内のパーティションとなる "ステージング テーブル" を使用することがポイントです。このシナリオの詳細につては、この資料のケース スタディで後ほど説明します。 このプロセスでは、テーブル内のパーティションを同じファイル グループ内のパーティション分割されていないテーブルに切り替えて除外 (図 6) しています。 このパーティション分割されていないテーブルは、既に同じファイル グループ内にあるため (これは、処理を成功させるための必要条件です)、SQL Server はこの切り替えをメタデータの変更により処理できます。 メタデータだけの変更なので、この処理は数秒以内で完了します。逆に、削除を実行した場合は、数時間かかる可能性があり、大規模テーブルでブロッキングが発生する可能性があります。 このパーティションが構成から除外されても、パーティションは 13 個のままです。ただし、最初の (最も古い) パーティションが空になります。また、最後の (最新で、同じく空) のパーティションに分割する必要があります。

図 6. パーティションの切り替え

最も古いパーティション (2003 年 9 月分) を削除するには、新しいマージ オプション (図 7 参照) を ALTER TABLE と合わせて使用します。 境界点をマージすることで、事実上境界点が削除され、結果としてパーティションが削除されます。 これにより、データが読み込まれるパーティション数が n-1 (この場合は 12) に減少します。 パーティションのマージ処理は、削除する必要がある行がない場合 (マージされる境界点にデータ行がないため)、非常に短時間で完了します。 この場合、最初のバーティションは空なので、最初のパーティションから 2 番目のパーティションに移動する必要がある行はありません。 最初のパーティションが空でない状態で、境界点をマージした場合は、行を最初のパーティションから 2 番目のパーティションに移動する必要があり、これは非常に手間のかかる処理になる可能性があります。 ただし、これは、最もよく利用されるスライド方式シナリオでは回避できます。スライド方式シナリオでは、空のパーティションがアクティブなパーティションとマージされ、行の移動は発生しません。

図 7. パーティションのマージ

最後に、新しいテーブルをパーティション分割されたテーブルの構成に追加する必要があります。 これをメタデータの変更により実現するには、パーティション分割されたテーブルの外部で、新しいテーブルにインデックスを読み込み、構築する必要があります。 パーティションを構成に追加するには、最後にある最新の空のパーティションを 2 つに分割します。 また、テーブルの制約を更新し、新しい範囲を受け入れられるようにする必要があります。 再び、パーティション分割されたテーブル内のパーティション数は 13 個になりました。 スライド方式シナリオでは、LEFT パーティション関数が定義された最後のパーティションは、常に空になります。

図 8. パーティションの分割

これで、新しく読み込む 2004 年 9 月分のデータを 12 番目のパーティションに追加する準備が整いました。

図 9. パーティション構成への追加

最終的なテーブルの状態は、次のようになります。

図 10. データの読み込み/アーカイブ後の範囲パーティション境界

1 度に追加または削除できるパーティションは 1 つだけなので、複数のパーティションを追加または削除する必要があるテーブルは、作成し直す必要があります。 この新しいパーティション構造に変更するには、まず、新しいパーティション分割されたテーブルを作成し、データを新しく作成したテーブルに読み込みます。 これは、分割のたびにテーブル全体を調整するよりも、効率のよい方法です。 この処理は、新しいパーティション関数と新しいパーティション構成を使用し、データを新しいパーティション分割されたテーブルに移動することで実現します。 データを移動するには、まず INSERT "新しいテーブル" SELECT "列リスト" FROM "古いテーブル" を使ってデータをコピーし、その後、元のテーブルを削除します。 この処理の実行中は、ユーザーによる変更が行われないようにして、データが失われることがないようにします。

詳細については、SQL Server Books Online の「ALTER PARTITION FUNCTION」および「ALTER TABLE」を参照してください。

パーティション分割されたテーブルの作成手順

パーティション分割されたテーブルの価値を理解できたところで、ここではパーティション分割されたテーブルの実装手順と、この処理で使われる機能について説明します。 この手順の論理フローは以下のとおりです。

図 11. パーティション分割されたテーブルまたはインデックスの作成手順

オブジェクトをパーティション分割するかどうかの決定

パーティション分割には大きなメリットがありますが、管理上のオーバーヘッドが発生し、オブジェクトの実装が複雑になるので、メリットよりも負担が大きくなる可能性があります。 特に、小さなテーブルや、現時点でパフォーマンス要件や保守要件を満たしているテーブルには、パーティションは必要ないでしょう。 前述した売上シナリオでは、パーティション分割を使って、行やデータを移動する手間を省いています。パーティション分割を実装するかどうかを決める場合は、このような手間になる作業があるかどうかを検討してください。

パーティション分割キーとパーティション数の決定

大規模なデータ サブセットのパフォーマンスと管理の容易性の向上をはかる場合に、決まったアクセス パターンがある場合は、範囲パーティションを利用することで、競合を緩和し、読み取り専用データが保守を必要としない場合の保守処理を削減できます。 パーティション数を決めるには、データ内に論理的なグループやパターンが存在しているかどうかを確認します。 多くの場合は、1 度に決められた 2、3 のサブセットしか処理しないのであれば、範囲を定義し、適切なデータだけ (特定のパーティションのみ) を処理するようにクエリを分離します。

詳細については、SQL Server Books Online の「Designing Partitioned Tables and Indexes」を参照してください。

複数のファイル グループを使用するかどうかの決定

パフォーマンスと保守の最適化をはかるには、ファイル グループを使用してデータを分割します。 ファイル グループの数は、ある程度はハードウェア リソースによって決まります。通常、パーティションと同数のファイル グループを用意することが最適です。多くの場合、各ファイル グループは異なるディスク上に置かれます。 ただし、これは、基本的にデータセット全体に対して分析が実行されることが多いシステムだけに関係します。 マルチ CPU システムでは、SQL Server は複数のパーティションを並列処理できるので、大規模で複雑なレポートや分析の処理時間は全体的に見て大幅に短縮されます。 この場合、並列処理によるメリットと、パーティション分割されたテーブルのパーティションの切り替えによるメリットの両方を享受できます。

ファイル グループの作成

I/O の負荷分散を考慮し、複数のファイルにパーティション分割されたテーブルを格納する場合は、少なくともファイル グループを 1 つ作成する必要があります。 ファイル グループは、1 つ以上のファイルで構成します。また、各パーティションはファイル グループにマップする必要があります。 複数のパーティションに対して 1 つのファイル グループを使用できますが、バックアップをより細かく制御する場合など、より適切なデータ管理を行うためには、関連するまたは論理的にグループ化できるデータは同じファイル グループに存在するようにパーティション分割されたテーブルをデザインします。 ALTER DATABASE を使用すると、論理ファイル グループ名を追加し、ファイルを追加できます。 AdventureWorks データベースのファイル グループ名 2003Q3 を作成するには、次のように ALTER DATABASE を使用します。

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

ファイル グループが作成されると、ALTER DATABASE を使ってファイルをファイル グループに追加することができます。

ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'2003Q3',
  FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]

CREATE TABLE の ON 句にファイル グループを指定することで、ファイルにテーブルを作成できます。 ただし、テーブルがパーティション分割されていない場合、1 つのテーブルを複数のファイル グループにまたがって作成することはできません。 1 つのファイル グループに 1 つのテーブルを作成する場合は、CREATE TABLE の ON 句を使用します。 パーティション分割されたテーブルを作成する場合は、まずパーティション分割機能のメカニズムを実装する必要があります。 パーティション分割の条件は、パーティション関数としてテーブルとは論理的に分離されます。 このパーティション関数は、テーブルとは別個の定義として存在することになります。この物理的な分離は、複数のオブジェクトが同じパーティション関数を使用できるため、役に立ちます。 したがって、テーブルのパーティション分割の最初の手順は、パーティション関数を作成することです。

範囲パーティションのパーティション関数の作成

範囲パーティションには、境界条件を定義する必要があります。 また、テーブルが CHECK 制約により制限されている場合でも、範囲の上限または下限に指定する値を省略することはできません。 データを定期的にテーブルに追加できるようにするには、テーブルの最後に空のパーティションを用意する必要があります。

範囲パーティションの場合は、まず境界点を定義します。5 つのパーティションを作成する場合、4 つの境界点の値を定義し、各値が最初 (LEFT) のパーティションの上限境界になるか、2 番目 (RIGHT) のパーティションの下限境界になるのかを指定します。 LEFT または RIGHT の指定を基づいて、常に 1 つ空のパーティションが保持されます。これは、このパーティションに明示的に境界点が定義されていないためです。

具体的には、パーティション関数の最初の値 (境界条件) が '20001001' である場合、パーティションの境界の値は次のようになります。

LEFT の場合

最初のパーティションは、'20001001' 以下の全データ

2 番目のパーティションは、'20001001' より大きい全データ

RIGHT の場合

最初のパーティションは、'20001001' 未満の全データ

2 番目のパーティションは、'20001001' 以上の全データ

範囲パーティションは、datetime データ型で定義される可能性が高いため、これに伴い注意が必要な点があります。 datetime を使用する場合は、常に日付と時刻を指定する必要があります。 日付が指定されていても時間の指定がない場合、これは "0" 時と解釈され、12:00 A.M. になります。 LEFT の値にこのデータ型が使われ、データの日付が 10 月 1 日の 12:00 A.M. であった場合、このデータは最初のパーティションに配置され、これ以外の 10 月のデータは 2 番目のパーティションに配置されます。 論理的には、最初の値に RIGHT を使用し、最後の値に LEFT を使用するのが最適です。 以下の 3 つの句は、論理的にまったく同じパーティション構造を作成します。

RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
               '20001231 23:59:59.997', 
               '20010331 23:59:59.997',
               '20010630 23:59:59.997')

または

RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', 
                  '20010101 00:00:00.000', 
                  '20010401 00:00:00.000',
                  '20010701 00:00:00.000')

または

RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')

注   datetime データ型を使用したことで、ここでは少し複雑になりましたが、正しい境界条件を確実に設定する必要があります。 既定の時間は 12:00:00.000 A.M. であるため、RIGHT を使った句は簡潔です。 LEFT の場合、より複雑になったのは、datetime データ型の精度のためです。 23:59:59.997 を指定しなければならない理由は、datetime 型のデータはミリ秒単位の精度を保証しないことにあります。 代わりに、datetime 型のデータでは、3.33 ミリ秒単位で精度が保証されます。 23:59:59.999 の場合この時間自体を指定できません。代わりに、この値は近似値の翌日の 12:00:00.000 A.M. に丸められます。 この丸め処理のために、境界は正確に定義されません。 datetime 型のデータの場合は、特に指定するミリ秒の値について、注意が必要です。

注   パーティション関数の場合、パーティション関数定義の一部にも関数を使用できます。 '20001231 23:59:59.997' を使って時間を明示的に定義するのではなく、DATEADD(ms,-3,'20010101') を使用できます。

詳細については、SQL Server Books Online のTransact-SQL リファレンスの「日付、時刻関数」を参照してください。

4 つのアクティブなパーティションに Orders のデータの 1/4 (日付順に 1/4) を格納し、5 番目のパーティション(パーティション テープルのデータの追加や削除に使用するプレースホルダ) を作成 するには、次のように 4 番目の境界条件に LEFT パーティション関数を使います。

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS 
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
            '20001231 23:59:59.997',
            '20010331 23:59:59.997',
            '20010630 23:59:59.997')

4 つの境界点により、5 つのパーティションが作成されます。 次のセットを確認して、このパーティション関数により作成されるデータセットを確認してください。

境界点 '20000930 23:59:59.997' を LEFT に指定 (パターンを設定):

最も左のパーティションには、'20000930 23:59:59.997' 以下の全データが含まれる

境界点は '20001231 23:59:59.997':

2 番目のパーティションには、'20000930 23:59:59.997' より大きく '20001231 23:59:59.997' 以下である全データが含まれる

境界点は '20010331 23:59:59.997':

3 番目のパーティションには、'20001231 23:59:59.997' ' より大きく '20001231 23:59:59.997' ' 以下である全データが含まれる

境界点は '20010630 23:59:59.997':

4 番目のパーティションには、'20010331 23:59:59.997' より大きく '20010331 23:59:59.997' 以下である全データが含まれる

最後に、5 番目のパーティションには、20010630 23:59:59.997' より大きい全データが含まれる。

パーティション構成の作成

パーティション関数を作成したら、これをパーティション構成に関連付けてパーティションを特定のファイル グループにマップする必要があります。 パーティション構成を定義する場合は、複数のパーティションが同じファイル グループに存在する場合でも、各パーティションにファイル グループの名前を指定する必要があります。 前に作成した範囲パーティション (OrderDateRangePFN) には、5 つのパーティションがあります。最後の空のパーティションは、PRIMARY ファイル グループに作成します。 このパーティションにデータが格納されることはないので、特に場所を指定する必要はありません。

CREATE PARTITION SCHEME OrderDatePScheme 
AS 
PARTITION OrderDateRangePFN 
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

注   すべてのパーティションが同じファイル グループに存在する場合、次のような簡単なより簡潔な構文を使用できます。

CREATE PARTITION SCHEME OrderDatePScheme 
AS 
PARTITION OrderDateRangePFN 
ALL TO ([PRIMARY])

パーティション分割されたテーブルの作成

パーティション関数 (論理構造) およびパーティション構成が定義されたら、これらを利用してテーブルを作成できます。 テーブルは、使用すべきパーティション構成を定義し、パーティション構成は関数を定義します。 この 3 つすべてを関連付けるには、パーティション関数を適用する列を指定する必要があります。 範囲パーティションは、常に、テーブル内の 1 列だけにマップされます。この列は、パーティション関数内で定義されている境界条件のデータ型と一致する必要があります。 また、テーブルが (負の無制限～正の無制限ではなく) データセットを明示的に制限している場合は、CHECK 制約も追加する必要があります。

CREATE TABLE [dbo].[OrdersRange]  
(
   [PurchaseOrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL ,
   [RevisionNumber] [tinyint] NULL ,
   [ModifiedDate] [datetime] NULL ,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL
      CONSTRAINT OrdersRangeYear
         CHECK ([OrderDate] >= '20030701' 
                         AND [OrderDate] <= '20040630 11:59:59.997'), 
   [TotalDue] [money] NULL
) 
ON OrderDatePScheme (OrderDate)
GO

インデックスの作成 (パーティション分割を行うかどうか)

既定では、パーティション分割されたテーブルに作成するインデックスでも、同じパーティション構成とパーティション列が使われます。 この場合、インデックスはテーブルと配置されます。 必須ではありませんが、テーブルとそのインデックスが配置されると、特にスライド方式のシナリオでは、管理が容易になります。

たとえば、一意インデックスを作成する場合、パーティション列はキー列の 1 つにする必要があります。これにより、一意であることを保証する適切なパーティションの検証が可能になります。 したがって、1 列を基にテーブルをパーティション分割する必要があり、別の列を基に一意インデックスを作成する必要がある場合は、テーブルとインデックスを配置させることはできません。 この場合、インデックスは一意列を基にパーティション分割するか (これが複数列の一意キーである場合は、キー列のいずれか)、パーティション分割が行われません。このインデックスは、パーティション分割されたテーブルのデータの切り替えが行われる時点で、削除および作成される必要があることに注意してください。

注   既存のデータをテーブルに読み込み、このテーブルにインデックスを直ちに追加する場合は、多くの場合、パーティション分割とインデックスの作成が行われていないテーブルにデータを読み込み、インデックスを作成して、読み込み後にデータをパーティション分割する方がパフォーマンスがよくなります。 パーティション構成を基にクラスタ化インデックスを定義することで、読み込み後にテーブルを有効にパーティション分割します。 これは、既存のテーブルをパーティション分割する方法としても優れています。 パーティション分割されていないテーブルとして同じテーブルを作成し、パーティション分割されたクラスタ化インデックスとして、クラスタ化インデックスを作成する場合、CREATE TABLE の ON 句を 1 つのファイル グループを対象とするように置き換えます。 その後、データが読み込まれた後にパーティション構成を基にクラスタ化インデックスを作成します。

作業全体の流れ: ケース スタディ

パーティション分割に関する概念、メリット、コード サンプルに目を通している場合は、この処理をよく理解されているものと思いますが、手順ごとに利用できる固有の設定とオプションがあり、場合によっては、さまざまな条件を満たす必要があります。 ここでは、すべての手順をまとめています。

範囲パーティション分割 : 売上データ

売上データは、多くの場合複数の方法で使用されます。 たとえば、当月のデータはトランザクション データで、前月までのデータは主に分析に使われます。 分析は、通常、月、四半期、年単位のデータを対象に行われます。 アナリストによって、異なるデータが大量に同時に参照される可能性があるので、パーティション分割によりこの作業を分離するほうがよいでしょう。 このシナリオでは、アクティブ データは 283 箇所の支社から収集され、2 つの標準の ASCII 形式のファイルで送信されます。 すべてのファイルは、毎月 1 日の 3:00 A.M. までに、中央のファイル サーバーに送られます。 各ファイルのサイズは異なりますが、平均して 1 月あたり約 86,000 件の売上 (注文) データで構成されます。 1 注文あたりの品目数は平均で 2.63 となるため、OrderDetails ファイルの平均行数は、226,180 行になります。 毎月、Orders には約 2500 万行 、OrderDetails には 6400 万行が追加され、過去データの分析サーバーには、2 年分のデータが分析用に保持されています。 2 年分のデータは、Orders の場合は 6 億行弱、OrderDetails の場合は 15 億行強です。 分析は、同四半期内の各月の比較や、前年同月との比較のために実行されることが多いため、範囲パーティションが使用されています。 各範囲の境界は、月単位です。

図 11 の手順を使用して、OrderDate を基に範囲パーティションを使ってテーブルをパーティション分割します。 この新しいサーバーの要件として、アナリストは、最高で連続した 6 か月分のデータか、最高で当年と前年の 3 か月 (たとえば、2003 年の 1 ～ 3 月と 2004 年の 1 ～ 3 月) 分のデータを集計し、分析する傾向にあることが挙げられます。 ディスク ストライピングを最適化し、ほとんどのデータ グループを分離するため、複数のファイル グループで同じ物理ディスクを使用しますが、ディスクの競合を抑えるため、6 か月単位でファイル グループを調整します。 当月データは 2004 年 10 月分で、283 店すべてが現在の各店の売上データをローカルに管理しています。 サーバーには、2002 年 10 月 ～ 2004 年 9 月末のデータが保持されています。 新しい 16 ウェイのマルチ CPU コンピュータと SAN (ストレージ エリア ネットワーク) を活用するため、ファイル グループに、月ごとに独自のファイルを作成し、これをストライピング ミラー (RAID 1+0) ディスク セットに格納します。 ファイル グループによる論理ドライブに対するデータの物理的なレイアウトについては、次の図 (図 12) を参照してください。月単位でのデータの格納状態を示しています。

図 12. パーティション分割された Orders テーブル

12 個の論理ドライブは個別に RAID 1+0 構成に存在します。したがって、Orders と OrderDetails データに必要なディスク数は、合計で 48 個になります。 ストレージ エリア ネットワークは 78 個のディスクをサポートし、他の 30 個ディスクは、トランザクション ログ、TempDB、システム データベース、および Customers (900 万行) や Products (386,750 行) の規模の小さなテーブルに使われます。 Orders テーブルと OrderDetails テーブルは、いずれも同じ境界条件を使用し、ディスクの構成も同じで、パーティション構成も同じものを使用します。 結果として (図 13 の 2 つの論理ドライブ [Drive E:\ と F:\] を参照してください)、Orders と OrderDetails のデータは、月ごとに同じディスク上に格納されます。

クリックすると図を拡大表示できます。

図 13. ディスク アレイ上の範囲パーティションの配置

複雑に見えますが、これは非常に簡単に作成できます。 このパーティション分割されたテーブルのデザインで最も難しいのは、多数のソースからのデータの配信です。283 店すべてで標準の配信メカニズムを実装する必要があります。 ただし、中央のサーバーでは、Orders テーブルと OrderDetails テーブルをそれぞれ 1 つ定義するだけで済みます。 この 2 つのテーブルをパーティション分割されたテーブルとして作成するには、まず、パーティション関数とパーティション構成を作成します。 パーティション構成は、ディスク上のパーティションの物理的な構成を定義するため、ファイル グループも定義されている必要があります。 このテーブルでは、ファイル グループが必要です。したがって、次の手順ではファイル グループを作成します。 各ファイル グループの構文は、次のものと完全に同じですが、24 個のファイル グループを作成する必要があります。 24 個のファイル グループすべてを作成する完全なスクリプトについては、RangeCaseStudyFilegroups.sql のスクリプトを参照してください。

注 : このスクリプトは適切なドライブ文字を指定しないと実行できませんが、スクリプトにはテストを簡素化するために変更できる "セットアップ" テーブルが含まれています。 複数あるドライブ文字または場所を 1 つのドライブに変更して、構文をテストし、学習することができます。 ファイル サイズを GB ではなく MB に変更してください。また、利用できるディスク空き容量に応じて、初期サイズを小さくすることをお勧めします。

SalesDB データベース用に、24 個のファイルとファイル グループを作成します。 すべて同じ構文を使用しますが、ファイルおよびファイル グループごとに固有の場所、ファイル名、およびファイル グループ名を指定します。

ALTER DATABASE SalesDB    
ADD FILE      
   (NAME = N'SalesDBFG1File1',
        FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
       SIZE = 20GB,
        MAXSIZE = 35GB,
  FILEGROWTH = 5GB) 
TO FILEGROUP [FG1]
GO

24 個のファイルとファイル グループがすべて作成されると、パーティション関数とパーティション構成を定義できるようになります。 作成したファイルとファイル グループを確認するには、sp_helpfile と sp_helpfilegroup をそれぞれ使用します。

パーティション関数は、OrderDate 列を基に定義します。 使用するデータ型は datetime です。OrderDate を基に 2 つのテーブルのパーティション分割を行うため、どちらのテーブルにもこの値が格納されている必要があります。 実際、どちらのテーブルにも同じキー値を使ってパーティション分割を行う場合、パーティション分割キーの値は重複する情報になりますが、配置を行うためには必要であり、通常、これは比較的サイズの小さな列 (datetime データ型は 8 バイト) になります。 この資料の「範囲パーティションのパーティション関数の作成」で説明したとおり、この関数は、LEFT (最初の) パーティションにある最初の境界条件の範囲パーティション関数になります。

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS 
RANGE LEFT FOR VALUES ('20021031 23:59:59.997',      -- Oct 2002
            '20021130 23:59:59.997',   -- Nov 2002
            '20021231 23:59:59.997',   -- Dec 2002
            '20030131 23:59:59.997',   -- Jan 2003
            '20030228 23:59:59.997',   -- Feb 2003
            '20030331 23:59:59.997',   -- Mar 2003
            '20030430 23:59:59.997',   -- Apr 2003
            '20030531 23:59:59.997',   -- May 2003
            '20030630 23:59:59.997',   -- Jun 2003
            '20030731 23:59:59.997',   -- Jul 2003
            '20030831 23:59:59.997',   -- Aug 2003
            '20030930 23:59:59.997',   -- Sep 2003
            '20031031 23:59:59.997',   -- Oct 2003
            '20031130 23:59:59.997',   -- Nov 2003
            '20031231 23:59:59.997',   -- Dec 2003
            '20040131 23:59:59.997',   -- Jan 2004
            '20040229 23:59:59.997',   -- Feb 2004
            '20040331 23:59:59.997',   -- Mar 2004
            '20040430 23:59:59.997',   -- Apr 2004
            '20040531 23:59:59.997',   -- May 2004
            '20040630 23:59:59.997',   -- Jun 2004
            '20040731 23:59:59.997',   -- Jul 2004
            '20040831 23:59:59.997',   -- Aug 2004
            '20040930 23:59:59.997')   -- Sep 2004
GO

左端と右端の両方の境界条件が含まれるので、このパーティション関数では 25 個のパーティションが作成されます。 テーブルには、常に空である 25 番目のパーティションが保持されます。 この空のパーティションには、制約によりテーブルのデータが制限され、データが格納されることはないので、特にファイル グループは必要ありません。 データを適切なディスクに格納するには、パーティション構成を使用して、パーティションをファイル グループにマップします。 このパーティション構成では、データを格納する 24 個のファイル グループの名前を明示的に使用します。また、25 番目の空のパ-ティションには PRIMARY ファイル グループを使用します。

CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS 
PARTITION TwoYearDateRangePFN TO 
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], 
              [FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
              [FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
              [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], 
              [PRIMARY] )
GO

テーブルは、既定のファイル グループまたはユーザー定義ファイル グループをパーティション分割していないテーブルとして使用し、以前のリリースのサポートと同じ構文で、またはパーティション テーブルを作成するパーティション構成を使用して作成できます。 どのオプションが適しているかは、テーブルへのデータ設定方法と、作成されるパーティション数によって異なります。 ヒープにデータを設定した後、クラスタ化インデックスを構築する方が、既にインデックスが作成されたテーブルを読み込むよりも、優れたパフォーマンスを得られる可能性が高くなります。 また、マルチ CPU を使用できる場合は、BULK INSERT を並行処理して、データをテーブルに読み込めるほか、インデックスの構築も並行処理できます。 Orders テーブルの場合は、テーブルを通常どおり作成し、その後 AdventureWorks サンプル データベースからデータを取得する INSERT SELECT ステートメントを使って、既存のデータを読み込みます。 Orders テーブルをパーティション分割されたテーブルとして作成するには、テーブルの ON 句にパーティション構成を指定します。 この Orders テーブルは、次の構文で作成されます。

CREATE TABLE SalesDB.[dbo].[Orders]  
(
   [PurchaseOrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID]   tinyint NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NULL
      CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001' 
         AND [OrderDate] < '20041001'), 
   [TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO

OrderDetails テーブルもこのパーティション構成を使用するので、OrderDate の値を保持している必要があります。OrderDetails テーブルは、次の構文で作成されます。

CREATE TABLE [dbo].[OrderDetails](
   [OrderID] [int] NOT NULL,
   [LineNumber] [smallint] NOT NULL,
   [ProductID] [int] NULL,
   [UnitPrice] [money] NULL,
   [OrderQty] [smallint] NULL,
   [ReceivedQty] [float] NULL,
   [RejectedQty] [float] NULL,
   [OrderDate] [datetime] NOT NULL
      CONSTRAINT OrderDetailsRangeYearCK
         CHECK ([OrderDate] >= '20021001' 
             AND [OrderDate] < '20041001'), 
   [DueDate] [datetime] NULL,
   [ModifiedDate] [datetime] NOT NULL 
      CONSTRAINT [OrderDetailsModifiedDateDFLT] 
         DEFAULT (getdate()),
   [LineTotal]  AS (([UnitPrice]*[OrderQty])),
   [StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO

データの読み込みを行う次の手順は、INSERT ステートメントにより処理されます。 このステートメントは、新しい AdventureWorks データベースを使用して、ここからデータをコピーします。 AdventureWorks サンプル データベースをインストールして、以下のようにデータをコピーしてください。

INSERT dbo.[Orders]
   SELECT o.[PurchaseOrderID] 
         , o.[EmployeeID]
         , o.[VendorID]
         , o.[TaxAmt]
         , o.[Freight] 
         , o.[SubTotal] 
         , o.[Status] 
         , o.[RevisionNumber] 
         , o.[ModifiedDate] 
         , o.[ShipMethodID] 
         , o.[ShipDate] 
         , o.[OrderDate] 
         , o.[TotalDue] 
   FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
      WHERE ([OrderDate] >= '20021001'
             AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
   SELECT    od.PurchaseOrderID
         , od.LineNumber
         , od.ProductID
         , od.UnitPrice
         , od.OrderQty
         , od.ReceivedQty
         , od.RejectedQty
         , o.OrderDate
         , od.DueDate
         , od.ModifiedDate
   FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
      JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
            ON o.PurchaseOrderID = od.PurchaseOrderID
      WHERE (o.[OrderDate] >= '20021001' 
             AND o.[OrderDate] < '20041001')
GO

データがパーティション分割されたテーブルに読み込まれたので、新しい組み込みシステム関数を使って、データが格納されるパーティションを特定します。 次のクエリは、データを格納する各パーティションについて、パーティション内の行数と、OrderDate の最小値と最大値を返すのに便利です。 行がないパーティションは、このクエリの結果として返されません。

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
         AS [Partition Number]
   , min(o.OrderDate) AS [Min Order Date]
   , max(o.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate) 
         AS [Partition Number]
   , min(od.OrderDate) AS [Min Order Date]
   , max(od.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO

最後に、テーブルへのデータ設定が済んだところで、クラスタ化インデックスを構築します。 この場合、クラスタ化インデックスは、主キーを基に定義します。これは、同じパーティション分割キーにより、両方のテーブルが識別されるためです (OrderDetails テーブルでは、一意にするために、インデックスに LineNumber を追加します)。 既定では、パーティション分割されたテーブルに対するインデックスは、同じパーティション構成を使用して構築され、パーティション分割されたテーブルと配置されます。このとき、パーティション構成を指定する必要はありません。

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO

パーティション構成を指定した完全な構文は、以下のようになります。

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
   ON TwoYearDateRangePScheme(OrderDate)
GO

パーティション分割されたテーブルの結合

配置済みのテーブルを結合する場合、SQL Server 2005 では、このテーブルの結合を 1 ステップで行うことも、複数のステップに分けて行うこともできます。後者の場合、まず個々のパーティションを結合し、次にそのサブセットを結合します。 パーティションの結合方法にかかわらず、SQL Server は常にある程度のパーティションの除外が可能かどうかを評価します。

パーティションの除外

次のクエリでは、前のシナリオで作成した Order テーブルと OrderDetails テーブルからデータを照会しています。 クエリは、第 3 四半期の情報だけを返します。 通常、第 3 四半期の各月の受注処理はあまり活発ではありませんが、2004 年度はいくつかの月が受注が最も多い月になりました。 この場合、第 3 四半期の Products (注文された量と注文日) に関する傾向に関心があります。 配置済みのパーティション分割されたテーブルの結合時に、パーティションの除外によるメリットを期待する場合は、各テーブルのパーティション範囲を指定する必要があります。 この場合、Orders テーブルの主キーが、OrderDate と OrderID の複合キーなので、この 2 つのテーブルの結合では、この 2 つのテーブルの OrderDate が同じである必要があることが示されます。 SARG (検索引数) は、両方のパーティション分割されたテーブルに適用されます。 このデータを取得するクエリは、次のようになります。

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od 
      ON o.OrderID = od.OrderID
         AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701' 
   AND o.OrderDate <= '20040930 11:59:59.997'
GO

図 14 で示すとおり、実際の出力または予想されるプラン表示出力を参照する場合、注意すべき主な要素がいくつかあります。 まず、(SQL Server Management Studio を使用して) アクセスするテーブルのいずれかの上にマウスを置くと、"予測実行回数" または "実行回数" のいずれかが表示されます。 この場合、1 四半期 (3 か月) 分のデータを参照できます。 月ごとに専用のパーティションがあり、データ参照はテーブルごとに 1 回で計 3 回実行されます。

クリックすると図を拡大表示できます。

図 14. 実行回数

図 15 に示すように、SQL Server は、すべての不要なパーティションを除外し、適切なデータを含むパーティションだけを選択します。 [引数] セクション内の「PARTITION ID:([PtnIds1017])」を参照し、評価されている内容を確認してください。 "PtnIds1017" 式はどこから来たの疑問に思われるかも知れません。 これは、このクエリでアクセスされるパーティションの論理表現です。 プラン表示の一番上にある [定数スキャン] にマウスを置くと、[引数] に「VALUES(((21)), ((22)), ((23)))」が表示されます。. これは、パーティションの番号を表しています。

図 15. パーティションの除外

これらのパーティションに限り、各パーティションに格納されているデータを確認するには、前にパーティションの確認に使用した新しい組み込みシステム関数にアクセスするクエリを、わずかに変更したものを使用します。

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
         AS [Partition Number]
   , min(o.OrderDate) AS [Min Order Date]
   , max(o.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23)
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO

この時点で、パーティションの除外をグラフィカルに確認できます。 パーティション分割されたテーブルとインデックスは、結合対象とするテーブルと配置されている場合は特に、さらに最適化をはかることができます。 SQL Server は、各パーティションをまず結合することで、複数の結合処理を実行できます。

配置済みテーブルの事前結合

SQL Server は 1 つのクエリ内でパーティションを除外するだけでなく、残された各パーティションの結合を実行します。 各テーブル アクセスの実行数を確認するだけでなく、マージ結合に関する情報も確認してください。 [マージ結合] の上にマウスを置くと、マージ結合が 3 回実行されていることが分かります。

図 16. パーティション分割されたテーブルの結合

図 16 では、別の入れ子になったループ結合が実行されていることに注意してください。 これはマージ結合の後に実行されているように見えますが、実際にはパーティション ID が既に各テーブル シークまたはスキャンに渡されています。この最後の結合は、2 つのデータセットを結合し、最初に定義されたパーティション ID にそれぞれが確実に対応しているようにするためだけのものです。

スライド方式シナリオ

次月 (ここでは 2004 年 10 月) のデータが入手できた段階で、既存のファイル グループを使用し、データの切り替えを行うために特定の順序で実行すべき手順があります。 この売上シナリオでは、現在 FG1 にあるデータは 2002 年 10 月分のデータです。2004 年 10 月のデータが入手できた段階で、利用できる空き領域とアーカイブ要件に応じて 2 つのオプションから操作手順を選びます。 ここで、パーティションの切り替えを迅速に行うには、メタデータだけの変更で切り替えを実現する必要があることを思い出してください。 具体的には、新しいテーブル (パーティションとして表される基になるテーブルまたは対象となるテーブル) を、切り替えを行うファイル グループ上に作成する必要があります。 同じファイル グループ (ここでは FG1) を引き続き使用する場合は、この領域の処理方法とアーカイブ要件を決定する必要があります。 テーブルに完全な 2 年分のデータがなく、領域がある場合に、処理時間を最小限に抑えるには、アーカイブするデータ (2002 年 10 月分) を削除せずに、当月のデータ (2004 年 10 月) を FG1 に読み込みます。 ただし、領域が十分になく、当月のデータとアーカイブ対象月のデータの両方を保持する必要がある場合は、古いパーティションをまずアーカイブ (および除外または削除) します。

いずれにせよ、アーカイブは容易に実行できます。また、おそらく既に実行されているでしょう。 優れたアーカイブ手順は、これを構成から除外する直前ではなく、新しいパーティションが読み込まれ、構成に追加された直後にファイル グループをバックアップすることです。 たとえば、RAID アレイに障害があった場合、ファイル グループは再構築やデータの再読み込みではなく、復元できる可能性があります。 このような場合は、データベースはパーティション分割されたばかりのため、パーティション構造が安定した時点で、完全なバックアップを実行できるでしょう。 完全なデータベースのバックアップだけが、選択肢ではありません。 SQL Server 2005 では、実装可能な多数のバックアップ戦略があります。その多くは、これまで以上のバックアップや復元の細かい制御を可能にしています。 変更されるデータはそれほど多くないため、読み込みが済んだ段階で各ファイル グループをバックアップすることもできます。 実際、これは、パーティションの展開戦略の一部として組み込まれるべきです。 詳細については、SQL Server Books Online の「ファイルとファイル グループのバックアップ」を参照してください。

方針が決定したら、正確なプロセスと構文を理解する必要があります。 この構文と手順は複雑に思えるかもしれませんが、プロセス自体は毎月変わりません。 動的 SQL 実行を使用して、このプロセスは容易に自動化できます。その場合は、次の手順に従います。

  • 構成に追加されるパーティションのステージング テーブルを管理します。
  • 構成から除外されるパーティションの 2 つ目のステージング テーブルを管理します。
  • パーティション分割されたテーブルから古いデータを除外し、新しいデータを追加します。
  • ステージング テーブルを削除します。
  • ファイル グループをバックアップします。

各手順の構文とベスト プラクティスについては、以下で説明します。また、動的 SQL 実行によりこのプロセスを自動化するうえで役立つヒントも提供します。

構成に追加されるパーティションのステージング テーブルの管理

  1. 将来パーティションとして表されることになるステージング テーブルを作成します。 このステージング テーブルには、パーティションを作成するうえで有効なデータだけが読み込まれるような制約を定義する必要があります。 適切なパフォーマンスを実現するためには、データをインデックスが作成されていないヒープに制約なしで読み込み、その後、パーティション分割されたテーブルにテーブルを追加する前に WITH CHECK 制約 (手順 3 参照) を追加します。

    CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]  
(
   [OrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL, 
   [TotalDue] [money] NULL
) ON [FG1]
GO

    自動化する場合 : このテーブルは、常に最新の月になるため、簡単に作成できます。 プロセスが実行される日時により、DATENAME(m, getdate()) などの組み込み関数を使えば、対象となる月を検出するのは容易です。 このテーブルの構造は、既存のテーブルと同じになるため、各月に固有の主な変更は、テーブル名のみです。 ただし、このテーブルはパーティションに追加した後は必要なくなるため、各月で同じ名前を使用することもできます。 データをパーティション分割されるテーブルの構成に追加した後もステージング テーブルは存在していますが、切り替えが完了した段階でこれは削除できます。 その他には、日付範囲を変更する必要があります。 datetime 型データを扱うため、時刻の保存に関して丸め処理の問題があります。プログラムにより適切なミリ秒値を特定できる必要があります。 月末の最新の datetime 値を特定する最も簡単な方法は、処理対象の月に 1 か月を加算後、2 または 3 ミリ秒を減算することです。 59.999 は .000 に丸められ、これは次月の最初の日として解釈されるため、1 ミリ秒だけを減算することは意味がありません。 2 または 3 ミリ秒であれば、- 2 ミリ秒の場合は .997 に丸められ、- 3 ミリ秒の場合は .977 と同値になるため、減算値として使用できます。.997 は格納可能な有効な値です。 これにより、datetime 範囲に適切な終了値を設定できます。

    DECLARE @Month            nchar(2),
      @Year            nchar(4),
      @StagingDateRange      nchar(10)
SELECT @Month = N'11', @Year = N'2004'
SELECT @StagingDateRange = @Year + @Month + N'01'
SELECT dateadd(ms, -2, @StagingDateRange)

    データの切り替えが行われるファイル グループに格納される必要があるため、テーブルは月ごとに作成されます。 操作対象となる適切なファイル グループを特定するには、次のシステム テーブル クエリと前出の $partition 関数を合わせて使います。 除外対象範囲内の任意のデータを指定します。 これは、すべての作業が実行されるパーティションおよびファイルグループです。 下線が引かれている箇所は、実際のテーブル、パーティション関数、および日付に応じて変更する必要があります。

    SELECT ps.name AS PSName, 
      dds.destination_id AS PartitionNumber, 
      fg.name AS FileGroupName
FROM (((sys.tables AS t 
   INNER JOIN sys.indexes AS i 
      ON (t.object_id = i.object_id))
   INNER JOIN sys.partition_schemes AS ps 
      ON (i.data_space_id = ps.data_space_id))
   INNER JOIN sys.destination_data_spaces AS dds 
      ON (ps.data_space_id = dds.partition_scheme_id))
   INNER JOIN sys.filegroups AS fg
      ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.TwoYearDateRangePFN('20021001')

  2. ステージング テーブルにデータを読み込みます。 ファイルの整合性がとられている場合は、このプロセスは、BULK INSERT ステートメントを使って処理できます。

    自動化する場合 : このプロセスは、最も自動化が難しいプロセスです。 すべてのファイルが読み込まれていることを確認する必要があるほか、ファイルの読み込みを並行処理することを検討する必要があります。 どのファイルが読み込まれたかと、ファイルがどこに格納されたかを記録するテーブルが、このプロセスを制御するうえで有効です。 数分おきにファイルを確認し、新しいファイルを選択して、複数の BULK INSERT ステートメントを実行する SQL エージェントのジョブを作成することもできます。

  3. データが読み込まれたら、制約を追加します。 データを信頼できるものにするため、制約は WITH CHECK を使用して追加します。 WITH CHECK は、既定で設定されます。したがって、改めて指定する必要はありませんが、WITH NOCHECK が指定されていないようにしてください。

  4. ステージング テーブルのインデックスを作成します。 いずれパーティションとして追加されるテーブルと同じクラスタ化インデックスである必要があります。

    ALTER TABLE [OrdersOctober2004]
ADD CONSTRAINT OrdersOctober2004PK 
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO

    自動化する場合 : この手順は簡単です。 手順 1 の月とファイル グループの情報を使用して、クラスタ化インデックスを作成できます。

    ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]  
WITH CHECK
ADD CONSTRAINT OrdersRangeYearCK
   CHECK ([OrderDate] >= '20041001' 
      AND [OrderDate] <= '20041031 23:59:59.997')
GO

構成から除外されるパーティションの 2 つ目のステージング テーブルの管理

  1. 2 つ目のステージング テーブルを作成します。 これは、パーティションが構成から除外されるときに、そのデータを保持するための空のテーブルです。

    CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]  
(
   [OrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL, 
   [TotalDue] [money] NULL
) ON [FG1]
GO

  2. ステージング テーブルのインデックスを作成します。 いずれパーティション (このパーティションがこのテーブルになります) として追加されるテーブルと同じクラスタ化インデックスである必要があります。

    ALTER TABLE [OrdersOctober2002]
ADD CONSTRAINT OrdersOctober2002PK 
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO

パーティション分割されたテーブルから古いデータを削除し、新しいデータを追加します。

  1. 古いデータを 2 つめのステージング テーブルにアーカイブします。

    ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
GO

  2. パーティション関数を変更し、2002 年 10 月の境界点を削除します。

    ALTER PARTITION FUNCTION TwoYearDateRangePFN()
MERGE RANGE ('20021031 23:59:59.997')
GO

  3. これにより、ファイル グループとパーティション構成間の関連付けも削除されます。 具体的には、FG1 がパーティション構成から除外されます。 新しいデータを既存の 24 個のパーティションを使って読み込むため、FG1 を "次に使用する" パーティション (分割に使われる次のパーティション) にする必要があります。

    ALTER PARTITION SCHEME TwoYearDateRangePScheme 
              NEXT USED [FG1]
GO

  4. パーティション関数を変更し、2004 年 10 月分の新しい境界点を追加します。

    ALTER PARTITION FUNCTION TwoYearDateRangePFN() 
SPLIT RANGE ('20041031 23:59:59.997')
GO

  5. ベース テーブルに制約がある場合はその定義を変更し、新しい範囲のデータが受け入れられるようにします。 制約を追加すると (データを検証するため) 操作コストが高くなる可能性があるため、ベスト プラクティスとしては、制約を削除して作成し直すのではなく、順次日付を拡張することをお勧めします。 現時点では、制約は 1 つしかありませんが (OrdersRangeYearCK)、日付が未来のため、2 つの制約が設定されます。

    ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMaxOctober2004
   CHECK ([OrderDate] < '20041101')
GO
ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMinNovember2002
   CHECK ([OrderDate] >= '20021101')
GO
ALTER TABLE Orders
DROP CONSTRAINT OrdersRangeYearCK
GO

  6. 新しいデータを最初のステージング テーブルに追加します。

    ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
GO

ステージング テーブルの削除

すべてのデータは、次の最後の手順でアーカイブされるので、それ以降はステージング データは必要ありません。 テーブルの削除は、これらのテーブルを最も時間をかけずに除外できる方法です。

DROP TABLE dbo.OrdersOctober2002
GO
DROP TABLE dbo.OrdersOctober2004
GO

ファイル グループのバックアップ

最後の手順で何をバックアップするかは、各自のバックアップ方針によって決まります。 ファイルまたはファイル グループ ベースのバックアップ方針を選択する場合は、ファイルまたはファイル グループのバックアップを実行する必要があります。 データベース全体をバックアップするバックアップ方針を選択する場合は、データベースの完全バックアップまたは差分バックアップを実行します。

BACKUP DATABASE SalesDB 
   FILEGROUP = 'FG1' 
TO DISK = 'C:\SalesDB\SalesDB.bak'
GO

リスト形式のパーティション分割 : 地域データ

テーブルに複数の地域のデータが格納されていて、ある地域のデータだけの分析が行われることが多い場合や、各地域から定期的にデータを受け取る場合は、リスト形式で定義された範囲パーティションを使用することをお勧めします。 つまり、各地域を表す値を使って明示的に各パーティションを定義する関数を使います。 たとえば、スペインの会社が、スペイン、フランス、ドイツ、イタリア、英国に顧客を抱えているとします。 各地の売上データは、常に国ごとに分析されます。 この会社のテーブルには、国ごとに 5 つのパーティションを設けることができるでしょう。

このリスト形式のパーティションの作成は、日付を使った範囲パーティションとほぼ同じですが、範囲の境界が実際のパーティション分割キー外部の他の値にならない点が異なります。 代わりに、範囲ではなくリストが使われます。 リストであっても、境界条件には、左端と右端を含める必要があります。 パーティションを 5 つ作成するには、パーティション関数で 4 境界だけを指定します。 値に順序を設定する必要はありません (SQL Server が内部で順序を設定します) が、正しい数のパーティションを得るための最も論理的な方法は、パーティションの値に順序を設定し、最後のパーティションの 1 番高い値は (LEFT パーティション関数により定義する際に) 指定しないでおくか、パーティションの値に順序を設定しますが、その際に下から 2 番目の値を先頭とします (RIGHT により定義)。

5 つのパーティションがあるため、ファイル グループも 5 つ用意する必要があります。 ここでは、ファイル グループの名前は、格納されるデータを基に命名します。 スクリプト ファイルの RegionalRangeCaseStudyFilegroups.sql は、この完全な構文を示すスクリプトです。 各ファイル グループは、同じ設定を使って作成されますが、データが均等に格納されない場合は、設定を同じにする必要はありません。 スペイン用のファイル グループとファイルだけを示していますが、他の 4 つのファイル グループとファイルも、同じパラメータを使います。ただし、格納されるドライブと各国のパーティションの名前は異なります。

ALTER DATABASE SalesDB
ADD FILEGROUP [Spain]
GO
ALTER DATABASE SalesDB    
ADD FILE      
    (NAME = N'SalesDBSpain',
        FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
        SIZE = 1MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB) 
TO FILEGROUP [Spain]
GO

以下の手順では、LEFT を使用して 4 パーティションだけの境界条件を指定する関数を作成するためのものです。 ここでは、リストに英国を除くすべての国が指定されています。英国 (UK) が指定されていないのは、これがアルファベット順では最後になるためです。

CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS 
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO

対応する名前のファイル グループに各データを格納するには、パーティション構成のアルファベット順のリストを作成します。 5 つのファイル グループすべてをパーティション構成の構文内に指定する必要があります。

CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS 
PARTITION CustomersCountryPFN 
   TO ([France], [Germany], [Italy], [Spain], [UK])
GO

最後に、作成したばかりの CustomersCountryPScheme を基に、Customers テーブルを作成します。

CREATE TABLE [dbo].[Customers](
   [CustomerID] [nchar](5) NOT NULL,
   [CompanyName] [nvarchar](40) NOT NULL,
   [ContactName] [nvarchar](30) NULL,
   [ContactTitle] [nvarchar](30) NULL,
   [Address] [nvarchar](60) NULL,
   [City] [nvarchar](15) NULL,
   [Region] [nvarchar](15) NULL,
   [PostalCode] [nvarchar](10) NULL,
   [Country] [char](7) NOT NULL,
   [Phone] [nvarchar](24) NULL,
   [Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)
GO

範囲パーティションは、範囲だけをサポートするよう定義されていますが、リスト形式のパーティションなど、他の種類のパーティションを実現することもできます。

まとめ

SQL Server 2005 は、大規模テーブルとインデックスをパーティション分割により容易にかつ整合性を保ちながら管理する方法を提供しています。これにより、アクティブなテーブルの外部でデータのサブセットを管理することができます。 アプリケーションはパーティション構成をまったく意識する必要がないので、管理が簡素化され、パフォーマンスが向上し、アプリケーション ロジックが抽象化されます。 データに論理グループ (範囲またはリスト) があり、大規模なクエリを使って、この事前定義され整合性のある範囲内のデータを分析し、同じ事前定義された範囲内でデータの切り替えを管理する必要がある場合は、範囲パーティションを選択することをお勧めします。 特定の範囲を指定せずに大量のデータを使って分析したり、すべてのクエリがデータ全部ではないにしてもデータのほとんどにアクセスする場合は、特定の格納手段が必要ない複数のファイル グループを使用すると、比較的簡単にパフォーマンスを向上できます。

この資料で使用したスクリプト

この資料のコード サンプルで使用したスクリプトは、SQLServer2005PartitionedTables.zip に格納されています。 zip ファイル内の各ファイルの説明は、以下のとおりです。

RangeCaseStudyScript1-Filegroups.sql : 範囲パーティション分割されたテーブルのケース スタディに必要なファイル グループおよびファイルを作成するための構文が含まれています。 このサンプルをより小さいディスク セットに小さなファイル (GB ではなく MB 単位) で作成できるように、このスクリプトを変更できます。 また、INSERT...SELECT ステートメントによりデータをインポートするコードも含まれ、適切なパーティション関数により格納されたデータの場所を確認できます。

RangeCaseStudyScript2-PartitionedTable.sql : 範囲パーティション分割されたテーブルのケース スタディ関連のパーティション関数、パーティション構成、および範囲パーティション分割されたテーブルを作成するための構文が含まれています。 また、このスクリプトには、適切な制約とインデックスも含まれています。

RangeCaseStudyScript3-JoiningAlignedTables.sql : SQL Server がパーティション分割されたテーブル用に提供するさまざまな結合戦略を実現するクエリが含まれています。

RangeCaseStudyScript4-SlidingWindow.sql : 範囲パーティション分割されたテーブルのケース スタディの毎月の管理に関する構文とプロセスが含まれています。 このスクリプトでは、Orders テーブルのデータを "スライド" 方式で切り替えます。 オプションで、独自に OrderDetails テーブルのデータの移動を行う同様のプロセスを作成します。 ヒント : RangeCaseStudyScript2 で使用されている Insert を参照し、テーブルおよび OrderDetails に挿入するデータの正しい列を確認してください。

RegionalRangeCaseStudyFilegroups.sql : 地域単位にパーティション分割されたテーブルのケース スタディに必要なファイル グループとファイルを作成する構文が含まれています。 実際には、これは、リスト形式のパーティション構成をシミュレートする範囲パーティションです。

RegionalRangeCaseStudyPartitionedTable.sql : 範囲パーティション分割されたテーブルのケース スタディ関連のパーティション関数、パーティション構成、および地域単位にパーティション分割されたテーブルを作成するための構文が含まれています。