优化访问相关 datetime 列的查询

  • 项目

SQL Server 2005 引入了 DATE_CORRELATION_OPTIMIZATION 数据库 SET 选项。此选项提高了在两个表（这两个表的 datetime 列相关）之间进行同等联接而且在查询谓词中指定了日期限制的查询的性能。

datetime 列中的值相关并能从启用 DATE_CORRELATION_OPTIMIZATION 获益的表通常属于一对多关系，主要用于决策支持、报告或数据仓库。

例如，在 AdventureWorks 示例数据库中，Purchasing.PurchaseOrderHeader 表的 OrderDate 列和 Purchasing.PurchaseOrderDetail 表的 DueDate 列是相关的。PurchaseOrderDetail.DueDate 的日期值与 PurchaseOrderHeader.OrderDate 的日期值基本一致。

DATE_CORRELATION_OPTIMIZATION 数据库选项设置为 ON 时，SQL Server 将维护数据库中任意两个包含 datetime 列并由一列外键约束链接的表之间的相关统计数据。默认情况下，此选项设置为 OFF。

SQL Server 使用这些相关统计数据和查询谓词中指定的日期限制来推断可以将其他限制添加到查询中而不会更改结果集。查询优化器在选择查询计划时使用这些推断条件。这可能会使查询计划执行地更快，因为添加的限制使 SQL Server 在处理查询时读取更少的数据。如果两个表都定义了聚集索引，并且为其维护相关统计数据的表的 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)

-- 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

第二个 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 数据库选项

使用相关统计数据

对于所有符合标准的成对匹配表，在将 DATE_CORRELATION_OPTIMIZATION 数据库选项设置为 ON 后，将自动创建索引视图形式的相关统计数据。当 SQL Server 查询优化器能够利用成对 datetime 列之间的相关性时，它会在其查询计划中使用这些相关统计数据。相关统计数据也包含在影响它们的 INSERT、UPDATE 和 DELETE 语句的逻辑中。相关统计数据的名称采用以下格式：

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

<FK_constraint_name>**是 datetime 匹配基于的 sys.objects 目录视图的外键约束的名称。<constraint_object_id> 是外键约束的 objectid 的 8 位十六进制表示形式。

ms177416.note(zh-cn,SQL.90).gif注意：
如果相关统计数据名称超过了标识符长度的限制，则 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 列相关匹配。
    ms177416.note(zh-cn,SQL.90).gif
    注意： 使用 ONLINE = 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 (Transact-SQL) 目录视图的 is_date_correlation_on 列。

若要确定视图是否基于相关统计数据，请选择 sys.views 目录视图的 is_date_correlation_view 列。

请参阅

概念

SET 选项

帮助和信息

获取 SQL Server 2005 帮助