Find Indexes Used In Query Store

One of the most frequent questions you’ll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead.

I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one.

What if we queried the information in Query Store?

Indexes Used in Query Store

Now Query Store itself doesn’t store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and, Query Store has the query plans.

AH HA!!

Let’s try this:

SELECT qsqt.query_sql_text,
       qsq.object_id,
       CAST(qsp.query_plan AS XML)
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE qsp.query_plan LIKE '%PK_Address_AddressID%';

And that works.

The key to this is to remember that the plans stored in Query Store are stored as text. So querying them is simply a matter of taking advantage of that. The LIKE operator quickly disposed of everything.

Boring.

Also, if you run the query twice, guess what? Yeah, the fact that we are querying using that string means that query now shows up in the list. So, if we really want to focus on identifying where an index was used in Query Store, we need XQuery.

XQuery To See Indexes Used in Query Store

To do this, we’re going to modify our query pretty radically:

WITH XMLNAMESPACES
(
    DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
, QueryPlans
AS (SELECT qsq.object_id,
           CAST(qsp.query_plan AS XML) AS query_plan,
           qsqt.query_sql_text
    FROM sys.query_store_query AS qsq
        JOIN sys.query_store_plan AS qsp
            ON qsp.query_id = qsq.query_id
        JOIN sys.query_store_query_text AS qsqt
            ON qsqt.query_text_id = qsq.query_text_id),
  QueryPlanObjects
AS (SELECT qp.object_id,
           qp.query_plan,
           qp.query_sql_text
    FROM QueryPlans AS qp
    WHERE qp.query_plan.exist(N'//RelOp//*[@Index="[PK_Address_AddressID]"]') = 1)
SELECT *
FROM QueryPlanObjects AS qpo;

Now I’ll only ever see plans that have an @Index property with the named index attribute. The XPath that I did limits us to the RelOp elements and then looks for any of the elements under RelOp that have an attribute value of the index we’re looking for.

Conclusion

This is not a perfect solution by any means. It’s only going to show indexes within Query Store. However, it’s going to list every plan within Query Store that includes a specific index, so you can see where, and more importantly, how, an index is being used.

For more on the Query Store, please look through my blog.

---

If you’re looking for how to use Query Store and generally improve your skills with query tuning, then I want to recommend you to my free, online course, that’s being hosted on the Redgate Youtube channel. It covers Query Store, Extended Events, execution plans and more. Look at the Community Circle play list for this free course and a whole bunch of others from Steve Jones, Kendra Little and Kathi Kellenberger.