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

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

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

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

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

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

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

USE AdventureWorks2008R2;
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);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

USE AdventureWorks2008R2;
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);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

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

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

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

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

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

2 番目の AND 句で指定した追加の条件式の厳密な形式は、元のクエリとデータベース内のデータ値によって異なります。推定条件を追加すると、クエリ オプティマイザーでは、その条件を使用して実行プランが生成されます。この例では、d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' 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 データベース オプションを設定するには

• ALTER DATABASE (Transact-SQL)

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

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

MPStats_Sys<constraint_object_id><GUID><FK_constraint_name>

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

注
相関関係の統計情報の名前が 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 を使用して、FOREIGN KEY 制約を作成した場合。

• datetime 列のクラスター化インデックスを作成して、その列が別のテーブルの datetime 列と相関関係のペアになる場合。

  注
  クラスター化インデックスが ONLINE = ON オプションを使用して作成された場合、相関関係の統計情報は作成されません。ただし、インデックスの構築がコミットされると、FOREIGN KEY 制約の作成など、別のトランザクションでのイベントの結果として、インデックスに依存する相関関係の統計情報が作成される場合もあります。

• 別のテーブルの datetime 列と相関関係のペアにするために、列の NULL 許容属性またはデータ型を変更した場合。

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

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

• DATE_CORRELATION_OPTIMIZATION データベース オプションを OFF に設定すると、SQL Server により作成されたすべての相関関係の統計情報が削除されます。

• 保存に大きな領域が必要な相関関係の統計情報や、保持していても利点がないと推定されている相関関係の統計情報は削除されます。

• DROP TABLE または ALTER TABLE を使用して FOREIGN KEY 制約を削除した場合、その FOREIGN KEY 制約に関連付けられている相関関係の統計情報は削除されます。

• 何かの操作が原因で、相関関係のペアになっているテーブルの所有者がそれぞれ別のユーザーになった場合、対応する相関関係の統計情報は削除されます。

• ALTER TABLE...SWITCH ステートメントを実行した場合に、変更元のテーブルか変更先のテーブルのいずれかで相関関係の統計情報が定義されていると、その相関関係の統計情報は削除されます。

• datetime 列にクラスター化インデックスを作成した場合、同じテーブルの別の datetime 列について作成された相関関係の統計情報は削除されます。状況に応じて、新しく作成されたクラスター化インデックスに基づいた新しい相関関係の統計情報が SQL Server によって作成される場合があります。

• 先頭のインデックス キーが datetime 列のクラスター化インデックスを削除した場合、同じテーブルに新しい相関関係の統計情報を作成できる別の datetime 列が存在していると、削除したクラスター化インデックスに関連付けられている相関関係の統計情報は削除されます。

• ALTER TABLE を実行して、相関関係の統計情報に関連する列のデータ型または NULL 許容属性を変更した場合、それらの統計情報は削除されます。

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

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

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

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

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

関連項目

概念

SET オプション

その他の技術情報

データベース エンジン チューニング アドバイザの使用