• 發行項

將存取相互關聯日期時間資料行的查詢最佳化

DATE_CORRELATION_OPTIMIZATION 資料庫 SET 選項可提升在兩個資料表 (其 date 或 datetime 資料行相互關聯，而且在查詢述詞中指定日期限制) 之間執行等聯結 (Equi-Join) 之查詢的效能。

因 date 或 datetime 資料行值相互關聯，並受益於啟用 DATE_CORRELATION_OPTIMIZATION 的資料表，通常是一對多關聯性的一部分，而且主要是用於決策支援、報告或資料倉儲等用途。

例如，在 AdventureWorks 範例資料庫中，Purchasing.PurchaseOrderHeader 資料表的 OrderDate 資料行與 Purchasing.PurchaseOrderDetail 資料表的 DueDate 資料行是相互關聯的。PurchaseOrderDetail.DueDate 的日期值通常是 PurchaseOrderHeader.OrderDate 後不久的時間。

當 DATE_CORRELATION_OPTIMIZATION 資料庫選項設為 ON 時，SQL Server 會在資料庫中任何兩個具有 date 或 datetime 資料行，並且已由一個資料行外部索引鍵條件約束連結的資料表之間，維護相互關聯統計資料。依預設，此選項設定為 OFF。

SQL Server 會將這些交互關聯統計資料與查詢述詞中所指定的日期限制一起使用，以推斷可在查詢中加入的額外限制，而不需要變更結果集。查詢最佳化工具會在選擇查詢計畫時使用這些推斷的條件。因為新加入的限制可讓 SQL Server 在處理查詢時讀取更少的資料，所以可產生更快速的查詢計畫。如果兩個資料表上都已定義叢集索引，而且維護相互關聯統計資料時所針對的 date 或 datetime 資料行是叢集索引的第一個或唯一一個索引鍵時，也會改善效能。

例如，假設您執行下列 Transact-SQL 指令碼，以準備 AdventureWorks 資料庫來維護 Purchasing.PurchaseOrderDetail 和 Purchasing.PurchaseOrderHeader 的交互關聯資訊：

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);
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 AdventureWorks
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

現在，假設您執行了下列查詢：

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

這個查詢所傳回的 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/02' AND '2/1/02'
AND d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14;

新加入條件 (如第二個 AND 子句中所指定) 的實際格式要視原始查詢和資料庫中的資料值而定。加入隱含的條件之後，最佳化工具就會使用這項條件來建構執行計畫。在這個範例中，因為 PurchaseOrderDetail.DueDate 上有叢集索引，所以可使用該索引來擷取滿足 d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14 的資料列。如果 Purchasing.PurchaseOrderDetail 中有存在數年的資料，那麼與原始查詢比較起來，這項查詢將可大幅減少執行時間。

使用因啟用 DATE_CORRELATION_OPTIMIZATION 而推斷出來的條件來執行查詢計畫之前，SQL Server 會先根據資料庫目前的內容，確認查詢可以產生正確的回應。

使用 DATE_CORRELATION_OPTIMIZATION 資料庫選項的需求

兩個資料表都必須符合下列所有條件，才能因啟用 DATE_CORRELATION_OPTIMIZATION 資料庫選項而受益：

  • 必須以下列方式設定資料庫 SET 選項。ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL 和 QUOTED IDENTIFIER 必須將 SET 設定為 ON。NUMERIC_ROUNDABORT 必須將 SET 設定為 OFF。

  • 資料表之間必須具有單一資料行外部索引鍵的關聯性。

  • 資料表必須都具有定義為 NOT NULL 的 datetime 資料行。

  • 必須至少有一個 datetime 資料行是叢集索引的索引鍵資料行 (如果索引鍵是複合的，則必須是第一個索引鍵)，或如果是資料分割資料表，則必須是資料分割資料行。

  • 兩個資料行都必須為相同的使用者所擁有。

將 DATE_CORRELATION_OPTIMIZATION 資料庫設為 ON 時，請考慮下列事項：

  • SQL Server 會以統計資料的形式來維護相互關聯資訊。這些統計資料會在適用的資料表上執行 INSERT、UPDATE 和 DELETE 作業的期間被 SQL Server 更新，而這可能會影響這些作業的效能。您不應該在需要使用大量更新的資料庫環境中啟用 DATE_CORRELATION_OPTIMIZATION。

  • 如果維護交互關聯統計資料時，有任何一個 datetime 資料行不是叢集索引的第一個或唯一一個索引鍵，請考慮在該資料行上建立叢集索引。這樣做通常可以提高交互關聯統計資料所涵蓋查詢類型的效能。如果主索引鍵資料行上已有叢集索引，您可以修改資料表，讓叢集索引和主索引鍵使用不同的資料行集。

  • 在下列情況中，啟用 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 位數十六進位表示法。

[!附註]

如果交互關聯統計資料的名稱超過識別碼的長度限制，那麼 SQL Server 會縮短名稱中的 FK_constraint_ 部份。

使用 SET SHOWPLAN XML 執行查詢時，任何從交互關聯統計資料衍生的篩選節點都會包含下列屬性：

DateCorrelationOptimization="true"

例如，受交互關聯統計資料影響的 <Predicate> 節點看起來會像：

<Predicate DateCorrelationOptimization="true">

只要是完全從交互關聯統計資料產生的篩選節點，都會包含這個屬性，但也可以藉由將受交互關聯統計資料影響的述詞和其他述詞結合的方式，來包含這個屬性。

通常，當 DATE_CORRELATION_OPTIMIZATION 資料庫選項設定為 ON 時，SQL Server 會為所有適合的 datetime 資料行配對建立交互關聯統計資料。SQL Server 會在您執行下列動作時建立額外的交互關聯統計資料：

  • 透過 CREATE TABLE 或 ALTER TABLE，建立滿足 datetime 交互關聯最佳化需求的外部索引鍵條件約束。

  • 在 datetime 資料行上建立叢集索引，且該資料行適合與其他資料表的 datetime 資料行進行交互關聯比對。

    [!附註]

    使用 ONLINE = ON 選項以建立叢集索引時，不會建立相互關聯統計資料。不過，在認可索引建立後，與索引相依的相互關聯統計資料可能會因為另一個交易中的某個事件而建立，例如，外部索引鍵條件約束的建立。

  • 變更資料行的資料類型或 Null 屬性，使該資料行適合與其他資料表的 datetime 資料行進行交互關聯比對。

您不應該直接在應用程式中參考交互關聯統計資料，因為 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 屬性時，這些統計資料將會被卸除。

交互關聯統計資料會隨著使它們被建立或卸除的同一個交易來建立或卸除。這個交易既不是線上狀態，也不是非同步狀態。

當您在簡單的單一伺服器微調狀況下使用 Database Engine Tuning Advisor 直接微調生產伺服器時，它會考慮使用交互關聯統計資料的成本與效益。不過，當您在測試生產伺服器狀況下使用 Database Engine Tuning Advisor 時，它並不會將交互關聯統計資料視為內部系統物件。因此，Database Engine Tuning Advisor 在執行索引微調分析時，不會在查詢最佳化中使用交互關聯統計資料。在測試生產狀況下，您可能想要忽略 Database Engine Tuning Advisor 對於保留交互關聯統計資料的索引檢視所做的任何建議，這是因為雖然它知道它們的成本，卻不知道它們的效益。在這兩種狀況下，Database Engine Tuning Advisor 可能都不會建議您選取 datetime 資料行上的特定索引 (例如叢集索引)，即使在啟用 DATE_CORRELATION_OPTIMIZATION 的情況下，選取這個索引反而會比較有益，也是如此。

查詢關於交互關聯統計資料的中繼資料

若要檢視 DATE_CORRELATION_OPTIMIZATION 資料庫選項的設定，請選取 sys.databases 目錄檢視的 is_date_correlation_on 資料行。

若要判斷檢視是否以交互關聯統計資料為根據，請選取 sys.views 目錄檢視的 is_date_correlation_view 資料行。

請參閱

概念

SET 選項

其他資源

使用 Database Engine Tuning Advisor