Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzCache: add @SlowlySearchPlansFor parameter #2000

Closed
BrentOzar opened this Issue Mar 19, 2019 · 0 comments

Comments

Projects
None yet
1 participant
@BrentOzar
Copy link
Member

BrentOzar commented Mar 19, 2019

Is your feature request related to a problem? Please describe.

A few use cases:

  • When people are doing index design on a table, they ask, "How do I know which queries are triggering this missing index?"
  • When people are considering removing an index, they ask, "How can I tell which queries are using this index?"
  • When people are researching a particular anti-pattern, like the usage of a scalar function in ad-hoc statements, they ask, "How can I tell which queries are calling this function?"

Describe the solution you'd like
I'd like to add a @SlowlySearchPlansFor parameter that accepts strings (including % wildcards) and then searches sys.dm_exec_query_plan.query_plan for that string.

In order to reduce load, it should be a part of the WHERE clause on the existing search - so for example if we're searching for the top queries by reads, hopefully we're going to be sorting those plans from worst reads to least, and we're going to come across the string pattern that they're searching for sooner rather than later.

Gotchas:

  • It needs to replace LIKE wildcards [, ], and ^ with a single underscore (because those muck up plan searches for object names like [Users])
  • It needs to leave the % wildcard in place, though, because I can already envision what's gonna happen when I have sp_BlitzIndex build a call stack for missing index searches

Describe alternatives you've considered

  • Dumping the entire plan cache to table, and string searching that
  • Telling them to just set @top to a big number, and manually looking through the query plans for a given anti-pattern or index

Are you ready to build the code for the feature?
Yes, the time has come.

Out of scope: sp_BlitzIndex enhancements when called for a specific table:

  • In existing index list, add sp_BlitzCache call stack to show what queries are using the index
  • In missing index list, add sp_BlitzCache call stack to show what queries wanted that index

@BrentOzar BrentOzar added this to the 2019-03 milestone Mar 19, 2019

@BrentOzar BrentOzar self-assigned this Mar 19, 2019

BrentOzar added a commit that referenced this issue Mar 19, 2019

BrentOzar added a commit that referenced this issue Mar 19, 2019

@BrentOzar BrentOzar closed this Mar 20, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.