主页 / dba / 问题 / 322790
Accepted
Chad Baldwin
Asked: 2023-01-31 12:24:16 +0800 CST

如果禁用非聚集索引，是否仍使用统计信息？

  • 772

tl; dr - 如标题所述 - 如果我禁用表上的非聚集索引，是否仍使用该索引的链接统计信息？

我知道有很多关于“删除与禁用索引”的问题......但我找不到专门涵盖统计信息的问题。

我知道统计数据没有改变或改变（至少这是我从 MS 文档中收集到的）。但我的问题是统计数据是否仍在使用。

作为背景，我正在从事一个大型索引调优项目。它涉及在数百个具有不同工作负载模式的相同数据库中添加/删除索引。总共有超过 200 万个索引。

我的第一步是删除所有“未使用”的索引。但是，我没有放弃它们，而是考虑禁用它们以保留定义。这将允许我在表中记录任何禁用的索引的实例、数据库、对象 ID 和索引名称/ID。如果之后性能开始下降，可以重新启用（重建）索引。

但是，如果已禁用索引的统计信息仍用于生成计划...那么禁用它们不会产生与删除它们相同的性能影响。如果是这种情况，那么禁用索引就不是“真正的”性能影响测试，如果禁用的索引最终被删除，我就有引入性能问题的风险。

sql-server

1 个回答

  1. Best Answer

    是的，即使索引被禁用，优化器仍然使用统计信息。

    对于演示，我使用AdventureWorks2019 OLTP和文档中的查询示例

    我将使用实际执行计划运行此查询

    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;

    如果我打开执行计划 XML，我可以看到正在使用的统计信息（为简洁起见，我删除了一些属性）。

    <OptimizerStatsUsage>
  <StatisticsInfo Table="[Product]" Statistics="[AK_Product_rowguid]" />
  <StatisticsInfo Table="[Product]" Statistics="[AK_Product_ProductNumber]" />
  <StatisticsInfo Table="[Product]" Statistics="[AK_Product_Name]" />
  <StatisticsInfo Table="[Product]" Statistics="[_WA_Sys_00000013_1CBC4616]" />
  <StatisticsInfo Table="[Product]" Statistics="[_WA_Sys_0000000E_1CBC4616]" />
  <StatisticsInfo Table="[ProductSubcategory]" Statistics="[PK_ProductSubcategory_ProductSubcategoryID]" />
</OptimizerStatsUsage>

    我将根据示例创建推荐的统计信息并重新运行查询

    CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);

    在顶部，我们可以在显示计划 XML 中看到统计信息的名称。 在此处输入图像描述

    我将删除统计数据并创建一个具有相同定义的过滤的非聚集索引（它也创建了统计数据）

    DROP STATISTICS Production.Product.BikeWeights

CREATE INDEX IX_Product_BikeWeights ON Production.Product
(Weight) WHERE ProductSubcategoryID IN (1, 2, 3)

    我们可以使用此 DMO 查询检查索引和统计信息：

    
SELECT 
    i.name
    , i.index_id
    , i.is_disabled
    , s.stats_id
    , s.has_filter
    , s.user_created
    , s.auto_created
FROM sys.indexes AS i
JOIN sys.stats AS s
    ON s.object_id = i.object_id
    AND s.stats_id = i.index_id
WHERE 
    i.object_id = OBJECT_ID('Production.Product')
    AND i.name = 'IX_Product_BikeWeights'

    在此处输入图像描述

    请注意，sys.statsDMV 没有is_disabled列

    重新运行查询向我们显示名为的统计信息从BikeWeights更改为IX_Product_BikeWeights。

    在此处输入图像描述

    现在我将禁用索引并重新运行 DMO 和测试查询。

    ALTER INDEX IX_Product_BikeWeights ON Production.Product DISABLE

    在此处输入图像描述

    在此处输入图像描述

    即使索引被禁用，统计信息仍在使用。

    对于最后一次运行，让我们删除索引（也删除统计信息）

    DROP INDEX IX_Product_BikeWeights ON Production.Product

    在此处输入图像描述

    统计数据也丢失了。

    • 17

相关问题