優化訪問相關 datetime 列的查詢

DATE_CORRELATION_OPTIMIZATION 資料庫 SET 選項提高了在兩個表（這兩個表的 date 或 datetime 列是相關的）之間執行同等聯接且在查詢謂詞中指定了日期限制的查詢的效能。

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 列是聚集索引的第一個鍵或唯一鍵時，也會提高效能。

例如，假設準備用 AdventureWorks 資料庫並通過執行以下 Transact-SQL 指令碼來維護 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 必須設定為 ON。NUMERIC_ROUNDABORT 必須設定為 OFF。

在兩表之間必須存在單列外來鍵關係。

兩表必須都包含定義為 NOT NULL 的 datetime 列。

必須至少有一個 datetime 列是聚集索引的鍵列（如果索引鍵是複合鍵，則必須是第一個鍵），或者如果它是已分割槽表，則必須為分割槽依據列。

兩表必須為同一使用者所有。

將 DATE_CORRELATION_OPTIMIZATION 資料庫選項設定為 ON 時，請考慮以下幾點：

SQL Server 維護統計資料形式的相關資訊。這些統計資料由 SQL Server 在對相應表執行 INSERT、UPDATE 和 DELETE 操作時更新，更新統計資訊會影響這些操作的效能。不應在更新頻繁的資料庫環境中啟用 DATE_CORRELATION_OPTIMIZATION。

如果為其維護相關統計資料的任一 datetime 列不是聚集索引的第一個鍵或唯一鍵，請考慮對其建立聚集索引。這樣做通常會提高相關統計資料涵蓋的各種查詢的效能。如果主鍵列已經建立了聚集索引，則可以修改表，使聚集索引和主鍵使用不同的列集。

在下列情況中，啟用 DATE_CORRELATION_OPTIMIZATION 不會帶來任何好處：

沒有符合先前所述的用於維護相關統計資料的標準的成對錶。

雖然有符合用於維護相關統計資料的標準的成對錶，但聯接這些表的查詢在其謂詞中沒有指定日期限制。

設定 DATE_CORRELATION_OPTIMIZATION 資料庫選項

ALTER DATABASE (Transact-SQL)

 使用相關統計資料
對於所有符合標準的成對匹配表，在將 DATE_CORRELATION_OPTIMIZATION 資料庫選項設定為 ON 後，將自動建立索引檢視形式的相關統計資料。當 SQL Server 查詢優化器能夠利用成對 datetime 列之間的相關性時，它會在其查詢計劃中使用這些相關統計資料。相關統計資料也包含在影響它們的 INSERT、UPDATE 和 DELETE 語句的邏輯中。相關統計資料的名稱採用以下格式：

 
_MPStats_Sys___ 是 datetime 匹配基於的 sys.objects 目錄檢視的外來鍵約束的名稱。 是外來鍵約束的 objectid 的 8 位十六進位制表示形式。

注意：
如果相關統計資料名稱超過了識別符號長度的限制，則 SQL Server 將縮短此名稱的 FK_constraint_ 部分。
 

使用 SET SHOWPLAN XML 執行查詢時，相關統計資料衍生的所有篩選器節點將包含以下屬性：

 
DateCorrelationOptimization="true"例如，受相關統計資料影響的 節點形式如下：

 
此屬性包含在任意完全從相關統計資料生成或通過將相關統計資料影響的謂詞和其他一些謂詞合併生成的篩選器節點中。

通常，DATE_CORRELATION_OPTIMIZATION 資料庫選項設定為 ON 後，SQL Server 將為所有符合標準的成對 datetime 列建立相關統計資料。SQL Server 還將在您執行以下操作時建立其他相關統計資料：

通過 CREATE TABLE 或 ALTER TABLE 建立滿足 datetime 相關優化要求的外來鍵約束。

對 datetime 列建立聚集索引，而該列可以與另一個表的 datetime 列相關匹配。
注意：
使用 NLINE = ON 選項建立聚集索引時不建立相關統計資料。但是，提交了索引生成之後，取決於索引的相關性統計資料可以作為其他事務（例如建立外來鍵約束）中事件的結果生成。
 

更改列的為空性或資料型別以使其可以與另一個表的 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 來更改相關統計資料涉及的列的資料型別或為空性時，這些統計資料將被刪除。

建立或刪除相關統計資料的事務與引起建立或刪除相關統計資料的事務相同。此事務既不是聯機事務也不是非同步事務。

在一個簡單的基於單臺伺服器的優化方案中使用資料庫引擎優化顧問直接優化生產伺服器時，資料庫引擎優化顧問將考慮相關統計資料的成本和收益。但是，在測試生產伺服器方案中使用資料庫引擎優化顧問時，資料庫引擎優化顧問將認為相關統計資料不是內部系統物件。因此，資料庫引擎優化顧問在其索引優化分析期間不在查詢優化中使用相關統計資料。在測試生產方案中，您可能想要忽略資料庫引擎優化顧問提出的、有關包含相關統計資料的索引檢視的全部建議，因為資料庫引擎優化顧問只考慮了成本而沒有考慮收益。在兩種方案中，資料庫引擎優化顧問可能都不建議選擇某些索引（例如啟用 DATE_CORRELATION_OPTIMIZATION 時會有收益的 datetime 列的聚集索引）。

 查詢有關相關統計資料的後設資料
若要檢視 DATE_CORRELATION_OPTIMIZATION 資料庫選項的設定，請選擇 sys.databases 目錄檢視的 is_date_correlation_on 列。

若要確定檢視是否基於相關統計資料，請選擇 sys.views 目錄檢視的 is_date_correlation_view 列。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-520794/，如需轉載，請註明出處，否則將追究法律責任。