11

tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?


I'm aware there are many questions about "dropping vs disabling indexes"...but none that I could find that specifically cover statistics.

I know that the statistics are not altered or changed (at least that's what I gathered from the MS documentation). But my question is whether the statistics are still used.


For background, I'm working on a large index tuning project. It involves adding/removing indexes across hundreds of identical databases but with varying workload patterns. Collectively there's over 2M indexes.

My first step is to drop any "unused" indexes. However, rather than dropping them, I'm considering just disabling them instead so that the definition is retained. This would allow me to record in a table the instance, database, object id and index name/id of any index that is disabled. If performance begins to degrade after, the index can be re-enabled (rebuilt).

However, if the statistics for the disabled index are still used to generate plans...then disabling them will not have the same performance impact as dropping them. If this is the case, then disabling the index is not a "true" performance impact test and I risk introducing performance problems if the disabled indexes are eventually dropped.

1 Answer 1

20

Yes, the optimiser still uses the statistics even when the index is disabled.

For the demo, I'm using AdventureWorks2019 OLTP and a query example from the documentation

I'll run this query with Actual Execution plan

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;

If I open the execution plan XML, I can see the stats being used (I've deleted a few attributes for brevity).

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

I'll create the recommended stats per the example and rerun the query

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

At the top, we can see the statistic's name in the showplan XML. enter image description here

I'll drop the stat and create a filtered, non-clustered index with the same definition (which also created the stats)

DROP STATISTICS Production.Product.BikeWeights

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

We can check the index and the stats with this DMO query:


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'

enter image description here

Note that the sys.stats DMV doesn't have an is_disabled column

Rerunning the query shows us that the stat named changed from BikeWeights to IX_Product_BikeWeights.

enter image description here

Now I'll disable the index and rerun both the DMO and the test queries.

ALTER INDEX IX_Product_BikeWeights ON Production.Product DISABLE

enter image description here

enter image description here

The statistics are still being used even when the index is disabled.

For the final run, let's drop the index (which drops the stats as well)

DROP INDEX IX_Product_BikeWeights ON Production.Product

enter image description here

The statistics are also missing.

2
  • 1
    I also checked whether the stats would be auto updated after the index was disabled and the answer to that was "yes" - though of course it has to go to the table to get the data and sort it as the index is out of date Commented Jan 31, 2023 at 23:07
  • @MartinSmith now that I did not expect. I always thought that stats basically went completely untcouched and became stale once the underlying index was disabled. The more I dig into this topic the more nuance I find. Commented Feb 2, 2023 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.