Does sp_BlitzIndex uses Query Store data?

All we need is an easy explanation of the problem, so here it is.

SQL Server 2019

  1. When running sp_BlitzIndex does the result generated is using Query Store data?
    Trying to understand if Query Store affects sp_BlitzIndex report.

  2. sp_BlitzIndex has multiple suggestions in its report,
    I’m trying to understand what steps should follow sp_BlitzIndex to verify the need for such an index.
    Should it follow both or one of these two? sp_BlitzCache/sp_BlitzQueryStore
    An example would be the usage of one of the rows –
    603 uses; Impact: 100.0%; Avg query cost: 74.4459
    On paper that looks great, before I copy&paste the TSql of the index, How can I understand which queries exactly would have used the suggested index? (so-called 603 uses).

Kind a lost myself during the index tuning as it feels like every command just redirects to another, not sure where I should stop and act haha.

Ty

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

backwards

If anything, you should use sp_BlitzIndex to further validate missing index requests you find while looking for queries to tune to find overall potential uses for the index. The real value of sp_BlitzIndex for me is finding indexes that I can clean up. The missing index section may serve to take the temperature of a database to see if anyone is paying attention to things, but…

The only way to know what real value the request has is to run the query asking for it, get the actual execution plan, and see if the missing index request aligns with a bottleneck there.

I wrote about this a bit here:

Your workflow might be better off as:

  • Use other scripts to find queries to tune
  • Make note of any missing index requests
  • See if the request is "high value" in sp_BlitzIndex
  • Test the query with and without the index suggestion

Method 2

No – the source code for sp_blitzindex does not reference query store DMVs.
Feel free to CTRL/CMD+F through the code here – no mention of query_store dmvs.

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzIndex.sql

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply