If you disable a non-clustered index, are the statistics still used?

Question

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.

Answer 1

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.

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'

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.

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

ALTER INDEX IX_Product_BikeWeights ON Production.Product DISABLE

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

The statistics are also missing.