fpucheta
commented
3 years ago Great post Kendra! I was particularly looking for the "Find queries using the index in Query Store", but running this one on a highly utilized with ~2 month of Query Store information took ~1h to run. I then copied Query Store tables to a different database and just once I created a full-text index on query_store_plan.query_plan column, it run pretty fast. What do you think or what's your experience with creating indexes (Full-text or non-clustered) directly on QS tables?
Comment history migrated from wordpressKevin Kline February 10, 2017 8:24 am Great post, Kendra! I really enjoyed this. =^D
-Kev
Loading...
Reply Kendra Little February 10, 2017 11:07 am Thank you!
Loading... Reply Doug Lane February 22, 2017 2:16 pm I was thinking about how I was going to find all the queries using new indexes I deployed. I thought I might have to write a script and maybe blog about it, but there your post was when I googled the idea. You saved me the effort on both counts. Thanks, Kendra!
Loading... Reply Kendra Little February 23, 2017 1:30 pm ZOMG, I HELPED DOUG! 🙂 Yay!
Loading... Reply ramin May 2, 2017 9:23 pm very useful for me.thanks kendra
Loading... Reply Binoy June 20, 2017 11:46 am This is outstanding, thanks Kendra for great work
Loading...
Reply Jeffrey Smith September 22, 2017 6:55 pm
UPPER? Why?
Loading... Reply Kendra Little September 23, 2017 10:09 am For case sensitive instances. The word INDEX in the index hint could be typed using a variety of cases.
Loading... Reply Adrian Sugden August 23, 2018 1:24 am Just what I need to try and rationalise the 19 NCI I’ve found on a 200m row table.
Loading... Reply Guillermo G January 18, 2019 8:02 am Thanks for sharing! Great post.
Loading... Reply Dave May 17, 2019 8:29 am In each query plan returned, the index isn’t necessarily but appears in the OptimizerStats property as ‘%UsageStatistics=”[IndexName]”%’. Struggling to get around this. Any ideas?
Loading... Reply Kendra Little May 17, 2019 10:52 am Ahhhh — that makes sense. This script was written before OptimizerStats got added to the plan.
Should be able to do the filter with XQuery but I think you’re right about it being tricky. Maybe Erik Darling will write/blog this if he hasn’t already, I’ll tweet to him 😀
Loading... Reply Kendra Little May 23, 2019 8:46 am Dave! Here is some help from the community that should help get you where you need to go. Take your pick.
From Erik Darling: https://gist.github.com/erikdarlingdata/fad7e3f99bfa58fe28eebb72d89ff6d9
From Jonathan Kehayias: https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/
Loading... Reply Damian Pawski April 21, 2020 7:58 am awesome, thank you
Loading... Reply LW July 23, 2020 3:45 pm awesome content. also, Ive never seen the ‘AT TIME ZONE’ trick so I learned something else that i wasnt expecting here! Thanks! 🙂