相関した datetime 列にアクセスするクエリの最適化

  • [アーティクル]

SQL Server 2005 では、DATE_CORRELATION_OPTIMIZATION データベース SET オプションが導入されました。datetime 列が相関関係にある 2 つのテーブル間で等結合を実行し、述語で日付制約を指定するようなクエリは、このオプションを使用するとパフォーマンスを向上できます。

datetime 列の値が相関関係にあり、DATE_CORRELATION_OPTIMIZATION を有効にするとパフォーマンスが向上するテーブルは、通常、一対多のリレーションシップを構成し、主に、意思決定支援、レポート、またはデータ ウェアハウジングに使用されるものです。

たとえば、AdventureWorks サンプル データベースでは、Purchasing.PurchaseOrderHeader テーブルの OrderDate 列と Purchasing.PurchaseOrderDetail テーブルの DueDate 列が相関関係にあります。PurchaseOrderDetail.DueDate の日付値は、PurchaseOrderHeader.OrderDate の日付値の直後の日付になる傾向があります。

DATE_CORRELATION_OPTIMIZATION データベース オプションが ON に設定されたデータベースに関しては、datetime 列を含んでおり 1 列に対する外部キー制約でリンクされている 2 つのテーブル間の相関関係について、統計情報が SQL Server によって管理されます。既定では、このオプションは OFF に設定されます。

SQL Server では、これらの相関関係の統計情報を、クエリの述語で指定されている日付制約と組み合わせて使用して、結果セットを変更せずに他の制約をクエリに追加できるかどうかを推定します。クエリ オプティマイザでは、クエリ プランを選択する際に、この推定条件が使用されます。追加する制約によっては、SQL Server でクエリを処理する際に読み込むデータが少なくなることがあるので、より高速なクエリ プランが得られる場合があります。両方のテーブルでクラスタ化インデックスが定義されており、相関関係の統計情報が維持されている各 datetime 列がクラスタ化インデックスの最初の列か唯一のキー列の場合にも、パフォーマンスが向上します。

たとえば、次の Transact-SQL スクリプトを実行して、Purchasing.PurchaseOrderDetail と Purchasing.PurchaseOrderHeader の相関情報を維持するように AdventureWorks サンプル データベースを設定したとします。

USE AdventureWorks
GO

-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID)

-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID

-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate)

--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks
   SET DATE_CORRELATION_OPTIMIZATION ON

ここで、次のクエリを実行します。

SELECT *
FROM Purchasing.PurchaseOrderHeader h,
Purchasing.PurchaseOrderDetail d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201'

通常、このクエリにより返される PurchaseOrderDetail.DueDate の値は、PurchaseOrderHeader.OrderDate の値から一定の期間内の値 (14 日以内など) になります。このため、SQL Server では、次のようなクエリを使用して、上記のクエリをより効率よく表現する方法を推定できることがあります。

SELECT *
FROM Purchasing.PurchaseOrderHeader h,
Purchasing.PurchaseOrderDetail d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/02' AND '2/1/02'
AND d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14

2 番目の AND 句で指定した追加の条件式の厳密な形式は、元のクエリとデータベース内のデータ値によって異なります。推定条件を追加すると、クエリ オプティマイザでは、その条件を使用して実行プランが生成されます。この例では、d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14 の条件を満たす行を取得するために、PurchaseOrderDetail.DueDate にクラスタ化インデックスが設定されています。Purchasing.PurchaseOrderDetail に数年分のデータがある場合、このクエリは、元のクエリよりも実行時間が大幅に (数倍) 遅くなることがあります。

DATE_CORRELATION_OPTIMIZATION を有効化することで推定される条件を使用してクエリ プランを実行する前に、データベースの現在の内容に基いて、そのクエリで適切な結果が生成されるかどうかの検証が SQL Server によって行われます。

DATE_CORRELATION_OPTIMIZATION データベース オプションの使用要件

DATE_CORRELATION_OPTIMIZATION データベース オプションを有効にした場合にクエリのパフォーマンスが向上するには、2 つのテーブルで、次のすべての条件が満たされている必要があります。

  • データベース SET オプションが次のように設定されていること。ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、および QUOTED IDENTIFIER は ON に設定。NUMERIC_ROUNDABORT は OFF に設定。
  • テーブル間に単一列の外部キー リレーションシップが存在すること。
  • どちらのテーブルにも、NOT NULL として定義されている datetime 列が存在すること。
  • 少なくとも 1 つの datetime 列がクラスタ化インデックスのキー列 (インデックス キーが複合要素の場合は、最初のキー列) であるか、パーティション テーブルの場合はパーティション分割列であること。
  • 両方のテーブルの所有者が同じユーザーであること。

DATE_CORRELATION_OPTIMIZATION データベース オプションを ON にする場合は、次のことを考慮してください。

  • SQL Server では、相関情報は統計という形式で維持されます。この統計情報は、該当するテーブルで INSERT、UPDATE、および DELETE 操作を実行中に SQL Server により更新されます。この更新処理により、これらの操作のパフォーマンスが低下する場合があります。そのため、データベースが頻繁に更新される場合は、DATE_CORRELATION_OPTIMIZATION を有効にしないことをお勧めします。
  • 相関関係の統計情報が維持される datetime 列の 1 つがクラスタ化インデックスの最初の列でも唯一のキー列でもない場合は、この列にクラスタ化インデックスを作成します。通常、これにより、相関関係の統計情報で対応できる種類のクエリでは、パフォーマンスが向上します。主キー列にクラスタ化インデックスが既に存在する場合は、クラスタ化インデックスと主キーで異なる列セットを使用するようにテーブルを変更できます。
  • 次の状況では、DATE_CORRELATION_OPTIMIZATION データベース オプションを有効にしてもクエリのパフォーマンスは向上しません。
    • 上記に記載した相関関係の統計情報を維持するための条件を満たす組み合わせのテーブルがない。
    • 相関関係の統計情報を維持するための条件を満たす組み合わせのテーブルがあっても、これらのテーブルを結合するクエリで、述語に日付制約が指定されていない。

DATE_CORRELATION_OPTIMIZATION データベース オプションを設定するには

相関関係の統計情報の操作

DATE_CORRELATION_OPTIMIZATION データベース オプションを ON に設定すると、該当するテーブルのすべてのペアに対して、インデックス付きビューという形式で相関関係の統計情報が自動的に作成されます。SQL Server クエリ オプティマイザで datetime 列のペア間の相関関係を利用できる場合、それらの相関関係の統計情報がクエリ プランで使用されます。また、相関関係の統計情報は、関連のある INSERT、UPDATE、および DELETE ステートメントのロジックにも含められます。相関関係の統計情報の名前は次の形式で表されます。

_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>

<FK_constraint_name>** は、sys.objects カタログ ビュー内の外部キー制約の名前です (datetime のペアはこのカタログ ビューに基づいています)。<constraint_object_id> は、外部キー制約 objectid の 8 桁の 16 進数表記です。

ms177416.note(ja-jp,SQL.90).gifメモ :
相関関係の統計情報の名前が ID の長さの上限を超える場合、SQL Server により、その名前の FK_constraint_ の部分が短縮されます。

SET SHOWPLAN XML を使用してクエリを実行した場合、相関関係の統計情報から派生したフィルタ ノードには次の属性が含められます。

DateCorrelationOptimization="true"

たとえば、相関関係の統計情報から派生した <Predicate> ノードは、次のようになります。

<Predicate DateCorrelationOptimization="true">

この属性は、相関関係の統計情報のみから生成されたフィルタ ノード、または相関関係の統計情報から派生した述語と他の述語を組み合わせることによって生成されたフィルタ ノードに含められます。

一般に、DATE_CORRELATION_OPTIMIZATION データベース オプションが ON に設定されている場合、SQL Server により、相関関係にある datetime 列のすべてのペアに対して相関関係の統計情報が作成されます。次の操作を行うと、SQL Server により、他の相関関係の統計情報も作成されます。

  • datetime の相関関係の最適化要件を満たす CREATE TABLE または ALTER TABLE を使用して、外部キー制約を作成します。
  • datetime 列のクラスタ化インデックスを作成すると、その列が別のテーブルの datetime 列と相関関係のペアになります。
    ms177416.note(ja-jp,SQL.90).gif
    注意 : クラスタ化インデックスが ONLINE = ON オプションを使用して作成された場合、相関関係の統計情報は作成されません。ただし、インデックスの構築がコミットされると、外部キー制約の作成など、別のトランザクションでのイベントの結果として、インデックスに依存する相関関係の統計情報が構築される場合もあります。
  • 別のテーブルの datetime 列と相関関係のペアにするために、列の NULL 許容属性またはデータ型を変更します。

相関関係の統計情報は、SQL Server により不特定のタイミングで削除されることがあるため、この統計情報をアプリケーションから直接参照しないでください。相関関係の統計情報の保守コストがパフォーマンスの低下につながると判断した場合は、相関関係の統計情報を個別に削除できます。相関関係の統計情報の DROP 権限は、既定では、sysadmin、db_owner、および db_ddladmin 固定データベース ロールのメンバと、相関関係の統計情報が定義されているテーブルのペアの所有者に設定されています。これらの権限は、譲渡できません。

相関関係の統計情報は、次の状況で削除されます。

  • DATE_CORRELATION_OPTIMIZATION データベース オプションを OFF に設定すると、SQL Server により作成されたすべての相関関係の統計情報が削除されます。
  • 保存に大きな領域が必要な相関関係の統計情報や、保持していても利点がないと推定されている相関関係の統計情報は削除されます。
  • DROP TABLE または ALTER TABLE を使用して外部キー制約を削除した場合、その外部キー制約に関連付けられている相関関係の統計情報は削除されます。
  • 何らかの操作が原因で、相関関係のペアになっているテーブルの所有者がそれぞれ別のユーザーになった場合、対応する相関関係の統計情報は削除されます。
  • ALTER TABLE...SWITCH ステートメントを実行した場合に、変更元のテーブルか変更先のテーブルのいずれかで相関関係の統計情報が定義されていると、その相関関係の統計情報は削除されます。
  • datetime 列にクラスタ化インデックスを作成した場合、同じテーブルの別の datetime 列について作成された相関関係の統計情報は削除されます。状況に応じて、新しく作成されたクラスタ化インデックスに基づいた新しい相関関係の統計情報が SQL Server によって作成される場合があります。
  • 先頭のインデックス キーが datetime 列のクラスタ化インデックスを削除した場合、同じテーブルに新しい相関関係の統計情報を作成できる別の datetime 列が存在していると、削除したクラスタ化インデックスに関連付けられている相関関係の統計情報は削除されます。
  • ALTER TABLE を実行して、相関関係の統計情報に関連する列のデータ型または NULL 許容属性を変更した場合、それらの統計情報は削除されます。

相関関係の統計情報は、それらを作成または削除する原因となる操作と同じトランザクションで作成または削除されます。このトランザクションは、オンラインでも非同期でもありません。

1 台のサーバーを使用している単純なチューニング環境でデータベース エンジン チューニング アドバイザを使用して実稼働サーバーを直接チューニングする場合、データベース エンジン チューニング アドバイザにより、相関関係の統計情報についてコストと利点が検討されます。ただし、テスト用の稼動サーバーを使用している環境でデータベース エンジン チューニング アドバイザを使用した場合、データベース エンジン チューニング アドバイザでは、相関関係の統計情報が内部システム オブジェクトであるとは見なされません。したがって、相関関係の統計情報は、データベース エンジン チューニング アドバイザでインデックスのチューニングが分析されている間は、データベース エンジン チューニング アドバイザによるクエリの最適化に使用されません。テスト用の稼動サーバーを使用している環境で、データベース エンジン チューニング アドバイザにより作成される相関関係の統計情報を保持するインデックス付きビューに関する推奨設定は、コストのみが考慮され、利点については考慮されていないので、無視することができます。データベース エンジン チューニング アドバイザでは、テスト環境および実稼働環境の両方において、datetime 列をクラスタ化インデックスにするなど、DATE_CORRELATION_OPTIMIZATION データベース オプションが ON であれば有益になり得る特定のインデックスの選択が推奨されない場合があります。

相関関係の統計情報についてのメタデータに対するクエリ

DATE_CORRELATION_OPTIMIZATION データベース オプションの設定を表示するには、sys.databases (Transact-SQL) カタログ ビューの is_date_correlation_on 列を選択します。

特定のビューが相関関係の統計情報に基づいているかどうかを判断するには、sys.views カタログ ビューの is_date_correlation_view 列を選択します。

参照

概念

SET オプション

ヘルプおよび情報

SQL Server 2005 の参考資料の入手