SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書データパーティション入門

SQL Server 2012 自習書「No.19 データパーティション入門」（HTML 版）

松本美穂と松本崇博が執筆した完全オリジナル SQL Server 2012 自習書シリーズの「No.19 データパーティション入門」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2013年12月29日]

目次へ | 前のページへ | 次のページへ

4.1　パーティションの SWITH とスライディングウィンドウ

◆ パーティションの SWITCH

データパーティションの一番のメリットは、不要になったパーティションを簡単に切り離せる点にあります。この操作は、「パーティションの SWITCH」（パーティションの切り替え）と呼ばれます。SWITCH によるデータ移動は、内部的なポインター変更のみで完了するので、SELECT INTO 操作などのように物理的なデータ移動は発生しません。したがって、SWITCH 操作は瞬間的に完了します。

◆ 古いデータをアーカイブへ移動する「スライディングウィンドウ」シナリオ

SWITCH 機能を利用すれば、古いデータ（パーティション）を定期的にアーカイブテーブルへ移動させることが簡単に行えます。これは、「スライディングウィンドウ」（Sliding Window）と呼ばれ、たとえば、稼動テーブルを 5つのパーティションとして、古いパーティションはアーカイブテーブルへ移動（SWITCH）していくというシナリオであれば、次のようになります。

このようにデータパーティションでは、不要になったパーティションを簡単に切り離せる（SWITCH できる）のが一番のメリットです。データパーティションは、データ削除時の大幅なパフォーマンスの向上と運用管理性の向上に大きく貢献します。

◆ データ削除時のパフォーマンス向上について

データ削除時のパフォーマンス向上については、データパーティションを利用していない場合の内部動作を考えると、理解しやすいと思います。次の図は、データパーティションを利用していない場合に、データを削除したときの様子です。

この図は、売上データの過去 4年分を保存して（稼動テーブルを 4年分として）、4年以上経過したデータ（2008年のデータ）を削除するというシナリオです。また、売上テーブルの「X」列、「Y」列、および「日付」列には、インデックスを作成しているとします。

このような場合に、2008年のデータを削除するには、内部的には該当データのインデックス内のデータも削除されることになり、また、そのすべてのデータに排他ロックがかかることに注意しなければなりません。たとえば、削除するデータが 10万件だった場合には、実際のデータから 10万件削除されるだけでなく、「X」列のインデックスからも 10万件分、「Y」列のインデックスからも 10万件分、「日付」列のインデックスからも 10万件分のデータが削除されることになり、合計 40万件分のデータの削除が発生します。

このように大量のデータ削除が発生した場合は、トランザクションログへの大量のログ書き込みが発生し、ログサイズが大きく膨れ上がります（削除データが 40万件なら、ログには 40万件+α のログレコードが書き出されます）。これでは、トランザクションログ（ディスク）への大量書き込みが発生し、トランザクションの完了に時間がかかることになります。

また、データの削除時には、削除対象となるデータ（インデックス内も含む）に排他ロックをかける必要があるので、大量のロックが獲得されることにもなります。削除対象が 40万件なら 40万件+α の排他ロック（行単位のロックの場合）が獲得されます。しかも、この排他ロックは、40万件分すべてのデータの削除が完了するまでは解放されません。排他ロックの獲得数が多い場合には、ロックエスカレーションが発生して、テーブル全体が排他ロックされる可能性もあります。これでは、同時実行されるトランザクションは、データの削除が完了するまで、アクセスできない（ロック待ちの状態）となってしまいます。

このようにデータパーティションを利用しない場合の大量のデータ削除は、パフォーマンスが著しく低下する可能性があります。なお、SQL Server 2000 までは、こういった状況を回避する方法として、サポート技術情報（KB: Knowledge Base）の文書番号 323630「SQL Server でロックのエスカレーションが原因で発生するブロッキングの問題を解決する」（http://support.microsoft.com/kb/323630/ja）を提供しています。この KB では、次のように DELETE ステートメントによる削除データ（影響となる行数）を SET ROWCOUNT ステートメントによって制限し、ロック数を減らす方法を紹介しています。

-- 5,000件ずつデータを削除
SET ROWCOUNT 5000
DELETE FROM t1 WHERE 削除条件
WHILE（@@ROWCOUNT > 0）
DELETE FROM t1 WHERE 削除条件
SET ROWCOUNT 0

しかしこの方法では、データを少しずつ削除するので、削除に時間がかかってしまうという問題と、運用管理が複雑になってしまうというデメリットがあります（獲得するロック数を減らすことで同時実行トランザクションのパフォーマンスは向上するので、全体としてのスループットは向上します）。

この問題は、データパーティションを利用すれば解決できるので、SQL Server 2005 以降のバージョンを利用している場合は、データパーティション機能を利用することをお勧めします。データパーティションであれば、削除したいパーティションを切り離す（SWITCH する）だけで、削除は一瞬で完了します。

目次へ | 前のページへ | 次のページへ