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.

7 thoughts on “Find Indexes Used In Query Store

  • Chris Wood

    Can you elaborate on why using sys.dm_db_index_usage_stats isn’t completely accurate? I have been using it since I read Tim Ford and Louis Davidson’s book a number of years ago.

    • ScaryDBA

      Reboots, taking the database offline, resets the data. There used to be a bug in 2012 & 2014 where rebuilds reset the data. 2012 & 2014 upgrades reset the data. Deletes & stuff that don’t actually affect the data are counted as affecting the data. Rollbacks don’t decrement. Finally, for unique indexes, the optimizer can use them in decision making, but because the index is not used in the plan, no updates occur.

      I’m not saying don’t use that data, just know that the data has flaws.

  • Chris Wood

    I know about reboots as I use the fact they happen once a month. I also understand that if the rebuild indexes or just update stats that will show up. I look at the overall usage per month and compare month to month to see how a table/index gets used or not.
    I wasn’t sure if there was something else I didn’t know about. Of course the counters can be reset and does DBCC DROPCLEANBUFFERS would void it all too.
    Thanks for your update and I know what you say is real info.
    Think I will have to look at your query against QS.
    Chris

    • Just know that the QS query isn’t complete either. There are any number of reasons why a query might not be in the query store. However, it’s another tool in the arsenal.

  • parsing query plans in the SQL Server engine is problematic for several reasons, among them efficiency. Also a problem is that xml structures don’t mesh well with the 8K page strategy of SQL Server memory management. I am not sure if XML operations in SQL Server results in direct OS VAS allocations (the old xp_xmldocument certainly did), but if it does, this is not good for long-term robustness (the ability of SQL Server to run forever without restart, the way applications do).
    This is why I have a C# program parse execution plans for index usage. I started with the plan cache, but I can also do query store. See http://qdpma.com/ExecStatsZip.html or http://www.qdpma.com

    • That’s a great way to get it done. I do agree. XML queries in general have an impact on the server outsize to what they deliver. I’m not sure about any memory leak. As I stated above. Not a perfect solution.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.