I love Query Plans
No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah.
It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make a change, well, &^%$ happens sometimes.
It used to make sense
When the plan cache was the only bar in town, that’s where you clinked your glasses.
It was okay that sometimes it was packed and sometimes it was dead and sometimes they were out of what you wanted to order.
With the advent of the Query Store, it was like, well, the plan cache got gentrified.
You didn’t have to worry about restarts anymore, there was some GUI love, and much more in the way of information persisted to views that you used to have to go gnarling through XML to pull out if you wanted to do any searching on your own.
All that XML processing was slow and expensive, too. I had a couple copy/paste incidents with Code From The Wild© on servers I was desperately hoping to find some problems on (in the process I probably created some problems).
Kick it around and talk some more
I’d been planning, and I’ve actually started and decided I hated, several iterations of a stored procedure to go at Query Store. Due to Other Things® coming up and all the usual excuses (not to mention the perilously low adoption rates for versions of SQL that actually have Query Store on them — we just don’t have much 2016 work coming through the door) I kept putting it off.
But then vNext/2017 happened.
And that damn Wait Stats DMV.
And now I’m just a little too excited about getting something written.
But I’d love to hear from you, first.
What questions would YOU like to ask the Query Store?
Best Laid Plans
My vision for the moment is to bring a lot of the same information and warnings out of plans, and probably the same options for sorting (CPU, reads, etc.), but with the opportunity to ask some more interesting questions up front.
This doesn’t mean sp_BlitzCache is going away. As long as there’s a plan cache, and people using versions of SQL prior to 2016, well, there will probably be a need for it. At least until they’re all out of support. Ho ho ho, ha ha ha.
It also gives me a chance to tame some of that absolutely insane dynamic SQL.
So, please, if there’s anything out there you’d like to see, figure out if it’s possible, whatever, leave a comment.
I’ll be here forever, writing XQuery and dynamic SQL, so you don’t have to.
7 Comments. Leave new
Simple things like when I’m monitoring/tuning a stored procedure and it is calling another stored procedure what are that actual values of the arguments passed.
Unfortunately, that’s not possible — QS doesn’t track it, and there’s nothing I could do to see anything other than compiled values.
Thanks!
After finding a scalar function, can it find who put it there and send an electrical shock to their keyboard?
That will be in a future offering called EaaS (Erik as a Service).
You have to pay my bail and bar tab, so, you know… pricey!
At the very least, offer an option to see query times in seconds or minutes, (and probably have less than a second represented as <1 as opposed to .0000013). In Warehouse Land we don't care about the little repetitive queries on our big servers. It's the ones that drag we want to keep an eye on. Maybe port it to BlitzCache as well?
Oh boy, Kyle, my man —
EXEC sp_BlitzCache @DurationFilter = 60, @ExpertMode = 1will let you filter on long running queries (@Duration is in seconds) and show you the average duration of a query.Good luck in Warehouse Land.
Cool, thanks. I generally use a @top = 100, @sortorder = ‘avg duration’, but that gives me another parameter to play with.