使用统计信息提高查询性能

筛选统计信息可以提高以下从定义完善的数据子集选择数据的查询的查询性能。筛选统计信息使用筛选器谓词来选择统计信息中包括的数据子集。与全表统计信息相比，设计完美的筛选统计信息可以改进查询执行计划。有关筛选谓词的详细信息，请参阅 CREATE STATISTICS (Transact-SQL)。有关何时创建筛选统计信息的详细信息，请参阅本主题中的确定何时创建统计信息部分。有关案例研究，请参阅 SQLCAT 网站上的博客文章 Using Filtered Statistics with Partitioned Tables（将筛选统计信息用于分区表）。

在自动创建统计信息选项 AUTO_CREATE_STATISTICS 为 ON 时，查询优化器将根据需要在查询谓词中的单独列上创建统计信息，以便改进查询计划的基数估计。这些单列统计信息在现有统计信息对象中尚未具有直方图的列上创建。

可以使用下面的查询来确定查询优化器是否为查询谓词列创建了统计信息。它将查询目录视图 sys.stats 和 sys.stats_columns，以便为具有单列统计信息的所有列返回数据库对象名、列名和统计信息名称。查询优化器通过使用 AUTO_CREATE_STATISTICS 选项在单列上创建统计信息时，统计信息名称以 _WA 开头。

USE AdventureWorks;
GO
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

AUTO_CREATE_STATISTICS 选项不确定是否为索引创建了统计信息。此选项也不生成筛选统计信息。它严格应用于全表的单列统计信息。

在自动更新统计信息选项 AUTO_UPDATE_STATISTICS 为 ON 时，查询优化器将确定统计信息何时可能过期，然后在查询使用这些统计信息时更新它们。统计信息将在插入、更新、删除或合并操作更改表或索引视图中的数据分布后过期。查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较，确定统计信息何时可能过期。该阈值基于表中或索引视图中的行数。

查询优化器在编译查询和执行缓存查询计划前，检查是否存在过期的统计信息。在编译某一查询前，查询优化器使用查询谓词中的列、表和索引视图确定哪些统计信息可能过期。在执行缓存查询计划前，数据库引擎 确认该查询计划引用最新的统计信息。

AUTO_UPDATE_STATISTICS 选项适用于为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。此选项也适用于筛选统计信息。

自动创建统计信息选项 AUTO_CREATE_STATISTICS 和自动更新统计信息选项 AUTO_UPDATE_STATISTICS 默认为 ON，我们建议使用这一默认值来用于大多数用户数据库。您可以使用以下 SELECT 语句来为所有用户数据库查看这些选项的当前值：

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

以下示例为 AdventureWorks2008R2 数据库将 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 设置为 ON：

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

有关如何设置这些统计信息选项的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

在 AUTO_UPDATE_STATISTICS 为 ON 时，您可以覆盖数据库范围的统计信息更新行为，并且根据您的应用程序的要求为单独的表、索引或列将自动统计信息更新设为关闭。在 AUTO_UPDATE_STATISTICS 为 ON 时，您可以通过以下方式为表、索引或列禁用和重新启用自动统计信息更新：

• 使用 sp_autostats 系统存储过程。这可以禁用或重新启用表或索引的统计信息更新。

• 对于 UPDATE STATISTICS 语句指定 NORECOMPUTE 选项。若要重新启用统计信息更新，请重新运行 UPDATE STATISTICS，但不使用 NORECOMPUTE 选项。

• 对于 CREATE STATISTICS 语句指定 NORECOMPUTE 选项。若要重新启用统计信息更新，请使用 DROP STATISTICS 删除统计信息，然后运行 CREATE STATISTICS 但不使用 NORECOMPUTE 选项。

• 对 CREATE INDEX 语句指定 STATISTICS_NORECOMPUTE 选项。若要重新启用统计信息更新，您可以运行 ALTER INDEX 且 STATISTICS_NORECOMPUTE = OFF。

在 AUTO_UPDATE_STATISTICS 为 OFF 时，不能为单独的表、索引或列将自动更新设为打开。重新启用自动统计信息更新将还原 AUTO_UPDATE_STATISTICS 选项指定的行为。如果 AUTO_UPDATE_STATISTICS 选项为 OFF，统计信息更新将不会发生。

统计信息更新可以是同步（默认设置）或异步的。对于同步统计信息更新，查询将始终用最新的统计信息编译和执行；在统计信息过期时，查询优化器将在编译和执行查询前等待更新的统计信息。对于异步统计信息更新，查询将用现有的统计信息编译，即使现有统计信息已过期。如果在查询编译时统计信息过期，查询优化器可以选择非最优查询计划。在异步更新完成后编译的查询将从使用更新的统计信息中受益。

数据库范围的异步统计信息更新选项 AUTO_UPDATE_STATISTICS_ASYNC 将确定查询优化器是使用同步统计信息更新还是异步统计信息更新。默认情况下，异步统计信息更新选项被关闭，并且查询优化器以同步方式更新统计信息。AUTO_UPDATE_STATISTICS_ASYNC 选项适用于为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。

您可以使用以下命令查看所有数据库的自动更新异步选项：

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

在以下情况下应考虑使用同步统计信息：

• 您执行会更改数据分布的操作，例如截断表或对很大百分比的行执行大容量更新。如果您在完成该操作后未更新统计信息，则使用同步统计信息将确保对更改的数据执行查询前统计信息是最新的。

在以下情况下，考虑使用异步统计信息来实现可预测性更高的查询响应时间：

• 您的应用程序频繁执行相同的查询、类似的查询或类似的缓存查询计划。与同步统计信息更新相比，使用异步统计信息更新时您的查询响应时间可能具有更高的可预测性，因为查询优化器可以执行传入的查询而不必等待最新的统计信息。这避免延迟某些查询，而不延迟其他查询。有关查找类似查询的详细信息，请参阅使用查询和查询计划哈希值查找和优化类似查询。

• 您的应用程序遇到了客户端请求超时，这些超时是由于一个或多个查询正在等待更新后的统计信息所导致的。在某些情况下，等待同步统计信息可能会导致应用程序因过长超时而失败。

数据库引擎优化顾问是一种工具，用于分析一个或多个数据库中工作负荷对性能的影响。它会提供改进性能的建议（例如建议创建索引），并且可能建议使用 CREATE STATISTICS 来创建查询优化统计信息。您应该按照它的建议执行。有关数据库引擎优化顾问的详细信息，请参阅优化物理数据库设计。

在某一查询谓词包含具有跨列关系和相关性的多列时，针对多列的统计信息可能会改进查询计划。针对多列的统计信息包含称作密度的跨列相关统计信息，这些统计信息不可用于单列统计信息。在查询结果依赖于多列之间的数据关系时，密度可以改进基数估计。

如果多个列已处于同一索引中，则多列统计信息对象已存在并且不必手动创建它。如果这些列尚未处于同一索引中，则可以通过对多个列创建索引或通过使用 CREATE STATISTICS 语句，创建多列统计信息。与统计信息对象相比，它要求更多的系统资源来维护索引。如果应用程序不要求多列索引，您可以通过创建统计信息对象但不创建索引，有效地利用系统资源。

在创建多列统计信息时，统计信息对象定义中列的顺序将影响生成基数估计的密度的效率。统计信息对象在统计信息对象定义中存储键列的每个前缀的密度。有关密度的详细信息，请参阅 DBCC SHOW_STATISTICS (Transact-SQL)。

为了创建用于基数估计的密度，查询谓词中的列必须匹配统计信息对象定义中列的前缀之一。例如，以下内容在列 LastName、MiddleName 和 FirstName 上创建多列统计信息对象。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO

在此示例中，统计信息对象 LastFirst 具有以下列前缀的密度：(LastName)、(LastName, MiddleName) 和 (LastName, MiddleName, FirstName)。密度不可用于 (LastName, FirstName)。如果查询使用 LastName 和 FirstName，并且没有使用 MiddleName，则密度不可用于基数估计。

在查询优化器为单个列和索引创建统计信息时，它为所有行中的值创建统计信息。在查询从行的某一子集中选择数据时，这一行子集具有唯一的数据分布，筛选统计信息可以改进查询计划。可以通过使用带 WHERE 子句的 CREATE STATISTICS 语句定义筛选器谓词表达式来创建筛选统计信息。

例如，使用 AdventureWorks2008R2 时，Production.Product 表中的每种产品属于 Production.ProductCategory 表中的以下四种类别之一：自行车、部件、服装和附件。上述每种类别在重量方面的数据分布均不同：自行车的重量范围为 13.77 到 30.0，部件的重量范围为 2.12 到 1050.00 且有些部件的重量为 NULL 值，服装的重量全部为 NULL，附件的重量也为 NULL。

使用自行车为例，针对所有自行车重量的筛选统计信息将向查询优化器提供更精确的统计信息，并且与全表统计信息或者针对 Weight 列的不存在的统计信息相比，可以改进查询计划质量。该自行车重量列很适合于筛选统计信息，但如果重量查找的数目相对较少，则不见得适合于筛选索引。筛选索引所提供的在查找方面的性能提升可能抵不上将筛选索引添加到数据库所导致的额外维护和存储成本。

以下语句对自行车的所有子类别创建 BikeWeights 筛选统计信息。筛选的谓词表达式通过使用比较 Production.ProductSubcategoryID IN (1,2,3) 枚举所有自行车子类别，对自行车进行定义。该谓词无法使用“自行车”类别名称，因为它存储于 Production.ProductCategory 表中，并且筛选表达式中的所有列都必须位于相同的表中。

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

查询优化器可使用 BikeWeights 筛选统计信息来改进下面这个查询的查询计划，此查询选择重量超过 25 的所有自行车。

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S 
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

如果错误或其他事件阻止查询优化器创建统计信息，则查询优化器将不使用统计信息创建查询计划。查询优化器将统计信息标记为缺失，并且在下次执行查询时尝试重新生成统计信息。

在使用 SQL Server Management Studio 以图形方式显示查询的执行计划时，缺少的统计信息将予以警告显示（表名称以红色文本显示）。有关详细信息，请参阅显示图形执行计划 (SQL Server Management Studio)。另外，使用 SQL Server Profiler 监视 Missing Column Statistics 事件类可以指明何时缺少统计信息。有关详细信息，请参阅Errors and Warnings 事件类别（数据库引擎）。

如果缺少统计信息，则执行以下步骤：

• 确认 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 为 ON。

• 请确保数据库不是只读的。如果数据库是只读的，则查询优化器无法保存统计信息。

• 通过使用 CREATE STATISTICS 语句创建缺少的统计信息。

如果查询响应时间很长或不可预知，则在执行其他故障排除步骤前，确保查询具有最新的统计信息。有关排除查询运行速度缓慢问题的详细信息，请参阅用于对运行慢的查询进行分析的清单。

与查询优化器执行的统计信息更新相比，升序或降序键列（例如 IDENTITY 或实时时间戳列）上的统计信息可能要求更频繁地更新。插入操作将新值追加到升序或降序键列上。添加的行的数目可能过小，以致无法触发统计信息更新。如果统计信息不是最新的并且查询从最频繁添加的行中选择数据，则当前统计信息将没有这些新值的基数估计。这可能导致不精确的基数估计和查询性能低下。

例如，如果统计信息未更新以包括最近销售订单日期的基数估计，则从最近销售订单日期选择的查询将具有不精确的基数估计。

考虑在执行维护过程（例如截断表或对很大百分比的行执行大容量插入）后更新统计信息。这可以避免在将来查询等待自动统计信息更新时在查询处理中出现延迟。

在执行重新生成、碎片整理或重新组织索引之类的操作后不更新统计信息。这些操作不会更改数据的分布，对统计信息没有影响。但是，请注意在使用 ALTER INDEX REBUILD 或 DBCC DBREINDEX 对表或视图重新生成索引时查询优化器会更新统计信息。这是内部实施这些操作导致的副产品，实际并未完成，因为需要更新统计信息。查询优化器在执行碎片整理或重新组织操作后不更新统计信息，因为这些操作并不删除并重新创建索引。

要改进表达式的基数估计，请遵循以下指导原则：

• 只要可能，应简化其中含常量的表达式。查询优化器在确定基数估计前并不对包含常量的所有函数和表达式进行求值。例如，简化表达式 ABS(-100) to 100。

• 如果表达式使用多个变量，则考虑为表达式创建一个计算列，然后对该计算列创建统计信息或索引。例如，如果您为表达式 Price + Tax 创建计算列，则查询谓词 WHERE PRICE + Tax > 100 可能会具有更好的基数估计。

要改进变量和函数的基数估计，请遵循以下指导原则：

• 如果查询谓词使用局部变量，则考虑重新编写查询以使用参数，而非局部变量。在查询优化器创建查询执行计划时，局部变量的值未知。在查询使用某一参数时，查询优化器将基数估计用于传递到存储过程的第一个实际参数值。

• 考虑使用标准表或临时表来保存多语句表值函数的结果。查询优化器并不为多语句表值函数创建统计信息。使用此方法，查询优化器可对表列创建统计信息并使用它们创建更好的查询计划。有关多语句表值函数的详细信息，请参阅函数类型。

• 考虑使用标准表或临时表来代替表变量。查询优化器不会为表变量创建统计信息。使用此方法，查询优化器可对表列创建统计信息并使用它们创建更好的查询计划。在确定是使用临时表还是表变量时需要进行一些权衡，与临时表相比，在存储过程中使用的表变量会导致更少的存储过程的重新编译。根据应用程序，使用临时表来代替表变量可能不会改进性能。

• 如果某一存储过程包含使用某一传入的参数的查询，则在查询中使用该参数值之前，应避免在该存储过程内更改该参数值。查询的基数估计基于传入的参数值，而非更新的值。为了避免更改参数值，您可以重新编写查询以使用两个存储过程。

  例如，以下存储过程 Sales.GetRecentSales 将在 @date is NULL 时更改参数 @date 的值。

  USE AdventureWorks2008R2;
  GO
  IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END;
  GO
  

  如果对存储过程 Sales.GetRecentSales 的首次调用为 @date 参数传递了 NULL，则查询优化器将使用针对 @date = NULL 的基数估计编译存储过程，即使查询谓词不是使用 @date = NULL 调用的。此基数估计可能与实际查询结果中的行数差别很大。因此，查询优化器可能会选择非最佳查询计划。若要避免此情况发生，您可以按如下所示将存储过程重新编写成两个过程：

  USE AdventureWorks2008R2;
  GO
  IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      EXEC Sales.GetNonNullRecentSales @date;
  END
  GO
  IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
  AS BEGIN
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END;
  GO
  

为了改进局部变量的基数估计，您可以将 OPTIMIZE FOR 或 OPTIMIZE FOR UNKNOWN 查询提示与 RECOMPILE 一起使用。有关详细信息，请参阅查询提示 (Transact-SQL)。

对于某些应用程序，每次执行查询时都重新编译查询可能会占用过多时间。OPTIMIZER FOR 查询提示可对此给予帮助，即使您不使用 RECOMPILE 选项。例如，您可以将 OPTIMIZER FOR 选项添加到存储过程 Sales.GetRecentSales，以便指定一个特定的日期。以下示例将 OPTIMIZE FOR 选项添加到 Sales.GetRecentSales 过程：

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

对于某些应用程序，查询计划指南可能不适用，因为您无法更改查询，或者使用 RECOMPILE 查询提示可能导致过多的重新编译。您可以使用计划指南来指定 USE PLAN 之类的其他提示，以便在向应用程序供应商调查应用程序变化的同时，控制查询的行为。有关计划指南的详细信息，请参阅使用计划指南在部署的应用程序中优化查询。