Filtering To Specific Queries Using sp_QuickieStore
Video Summary
In this video, I delve into the advanced customization options available in my free open-source tool, SP Quickie Store, which enhances query store results for SQL Server tuning and troubleshooting. I walk you through various parameters that allow you to filter down to specific queries and execution plans based on criteria such as execution type (successful, failed, timed out), procedure schema, plan IDs, and query text. By demonstrating these features, I show how SP Quickie Store can significantly streamline the process of identifying and analyzing problematic queries, especially in scenarios where traditional methods fall short.
Full Transcript
Oh boy. Hot on the heels of that last video. I think it’s maybe two minutes later. We’re going to look at how you can further customize some of the results that you get back from a query store using my free open source. I mean, it’s free to everyone but me. I charge myself a very, very high consulting rate to use it. How you can use that to filter down to specific queries, plans, stuff like that. Now, again, we’re going to start off with the help parameter because the help parameter will give you information about what all the parameters can do.
If I add parameters, change parameters, anything like that, this will get updated so you can stay on top of exactly what’s going on and what you might care about. But getting down a little bit further in here, this is, there’s a whole section of parameters that help, they can help you filter your results down to a specific set of things that you might care about. So if we look at the full list here, I’m going to walk you through these and then we’ll, I’ll show you some examples of them.
We have execution type, which will tell you, which allows you to filter on queries that are either, you know, successful, failed for some reason, either, you know, different reasons why queries might fail. You can find ones that ran or you can find ones that like timed out, errored out, stuff like that. If you want to look for a specific store procedure in query store, you can use the procedure schema and procedure name parameters.
Procedure schema will default to DBO. So if you have procedures in different schemas, you will need to provide that schema in order to successfully find them. If you want to include a list of plan IDs or query IDs or just a single one, well, I’ll split any comma delimited value in here out to a list and pass along and pass that into a table for searching.
Likewise, you can also choose to ignore some plan and query IDs. By default, SP quickie store will filter things out like creating, altering indexes, altering tables, stats updates, whether they’re, you know, manual or whether they happen as part of a query so that you don’t get some of that noise in there that might happen from those. Like you don’t need to know that creating or rebuilding an index took an hour because you can’t tune that.
If it’s useful for you to find, let me know. I can, you know, change the default on that. But otherwise, it’s like, what are you going to do with that with that information?
Like what? Also, there’s a section in here. So what would happen to me quite a bit in my consulting efforts is I would find queries from other sources where query plans were not necessarily part of the the data collection. If you ever look at the blocked process report or the XML deadlock report, what you’ll see is that SQL handles for the queries involved will be logged in there along with the query text, but not the query plan, which is understandable because that’s it will be a concerted effort to include a query plan and all that.
And staging XML inside of XML sounds a bit, this is a bit nightmarish, even to me, a person who spends a lot of time in XML. So, yeah, I get that. I totally get that.
But a lot of the times when you’re trying to figure out a blocking or deadlocking scenario, the query plan becomes really, really important because the shape of the query plan, you know, lookups, you know, missing indexes, things like that can really play into if like the reason why there was significant blocking or deadlocking. There is also a query text search parameter. So if you know, like if you’re looking for like some entity framework query or something where, you know, maybe something that was generated by dynamic SQL, something like that, you can search for the specific text.
On its own, SP Quickie Store will, if you don’t have wildcard searches on either end of your query, it’ll add them in for you so that we, you know, search the text for everything that you might find. But if you have weird spacing inside of the query, like if it’s like select, like carriage return, like big tabs and stuff like that, other weird white space issues, you might need to add your own parentheses inside of the string to search for stuff. So let’s just demo some things real quick here.
Let’s run this. And we’ll find that there’s a store procedure in here called vote sniffing. Let’s let’s I’ll type in a demo.
Screw it. Rules be damned. So let’s look for it. So since that is in the DBO schema, we don’t need to worry about supplying the procedure schema. If this were in a different schema, we would need to tell it.
But since this is sniffing, I think I spelled that right. Since this is just in the DBO schema, we can run this and we can search for a specific procedure name. Now, one thing that I talked about in the first video was how the all plan IDs parameter can be really useful.
And that is because if we use this, let’s just say that this was not a store procedure. Let’s say that this was a regular, a regular old, you know, query dynamic SQL, you know, entity framework, something like that query where we had no idea how else to identify. But we wanted to look for specific plans.
What we could do, stick that in there and we could get back all the plan IDs that we cared about from this list. Right. Pretty neat.
At least I think so. If you wanted to ignore any of this stuff, do any of that, we totally could. You can do the same thing with query IDs. Right. So if we come back up here, we grab query ID 312. We can say include query IDs.
And we can look for 312 and we can just focus in on anything that query ID 312 might have given us. Other things that can be useful. You know, if you want, like, let’s say that you had a bunch of query timeouts over the weekend.
Right. Let’s say or at night or sometime, some other time when you weren’t looking at things. Excuse me.
We could look at the execution type. And we could see that valid inputs are regular, aborted, and exception. I don’t know if I have any aborted or accepted queries in here.
But let’s take a look. And let’s see. Do we have anything aborted?
I have no idea. It’s possible. It’s entirely possible. No, we do. Look at that. I have two aborted queries in here. I don’t think I have any queries that would have failed.
But, you know, that’s because my queries never fail. I only quit on them. It’s never the query’s fault. It’s just me running out of patience. So anyway, this is different ways you can narrow down.
You can narrow your search down to what you care about from the results that you see here. I’m going to soapbox a little bit and say this is one of the big reasons why I wrote this store procedure. Because right now, the query store GUI doesn’t give you a way to search through any of these things or filter down to any of these things.
You can track certain query IDs, but you have to find that query ID and right-click it and track it and all this other stuff. You can’t, like, manipulate what query store brings back in a meaningful way to search through things for any of this stuff. And this is all important stuff when you’re doing query tuning work, when you’re doing consulting work, where you’re walking into a server that you may have never seen before, where you’re walking into a server where you might have to be able to put together pieces of a situation really quickly.
You know, again, coming back to, like, block process or XML deadlock report, like, you have to find those queries, you have to find those plans, you have to get in there and do stuff really quickly. So this is all sort of invaluable stuff for me in my day-to-day consulting work, and that’s why I put it in here. You know, if the query store GUI were written by someone who, or designed by someone who cares about humanity, we would be able to search and filter to these things a bit more easily.
But, alas, we do not have those options currently. I would love to film a video where I say, SPQuickieStore is no longer necessary because the query store GUI is completely up to snuff and offers you a commensurate experience. But for me, I still need all this stuff when I’m working on things in my day-to-day life.
So I made it easy for me. I made it easy for you. You know, again, please love me.
So anyway, that’s about the end of this one. You know, the usual rigmarole. Like, subscribe, join my email list, hire me to do work for you. No, whatever.
Or don’t. Maybe just go, maybe buy me a coffee someday so I can pour whiskey in it and have a good morning. Anyway, thanks for watching. Hope you learned something.
Hope you enjoyed yourselves. And I will see you in the next video where we will talk about something else entirely with SPQuickieStore. All right. Cool.
Feel good about this one. I only coughed once. It’s almost a new record for me. All right. Goodbye. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.