sp_QuickieStore Passing Lists

Posted on by Erik Darling

Checking It Twice


Thanks for watching!

Video Summary

In this video, I delve into the implementation of passing lists of IDs to my new store procedure, SP_to_the_underscore_quickie_store. I explain how you can include or ignore specific query plans and query IDs by passing in strings of IDs up to 4000 characters long. While I opted for a more manual approach using XML and dynamic SQL rather than table value parameters, this method allows for greater flexibility and reusability across different scenarios where you might want to include or exclude certain queries based on their IDs.

Full Transcript

Erik Darling back once again with another hopefully spectacular video about my new store procedure, SP to the underscore to the quickie store. And in this one I want to talk a little bit about how I implemented passing in lists of things to the store procedure. Now, I do not give you the ability to pass in the list of things to the store procedure. I do not give you the ability to pass in a list of databases because I often look at, look, querying one query store, one database worth of query store sucks enough. I only give you, pass in 10 databases and sit there for an hour. We pull data back. It sucks. It’s not the way I want you to spend your time. You can do better than that. At least I think you can anyway. There’s no universal queries. I guess the universal queries should be the plain cache, but we all know how unreliable that is. At least it’s not a good way to pass in. At least it’s pretty fast to query. Get some stuff back pretty quick from it for what it’s worth. But what I wanted you to be able to do is pass in a list of plan or query IDs to include or ignore. Now, you’ll notice that all of these parameters are in VARCAR 4000. I figured that was a reasonable limit on the length of a string for you to pass in things to include or ignore. If you truly need to include or ignore, a string of a string of IDs longer than that, let me know. It wouldn’t be too much of a big deal to make those maxes. I guess. Someone will ask. Great. Anyway, so you have these four things that you can use to include or ignore query plans here.

If we scroll down a little bit to where I actually process this stuff, the thing that I use to… Why is this being weird? There we go. There it is. For some reason, SMS doesn’t always keep good track of where things are and should be. A lot of weird skipping around in there. But what I use to split the strings… Now, of course, in the real world, you have many better options for how you get a list of strings into a stored procedure or whatever.

If it were easier, I would have used a table value parameter. I think table value parameters are great. I know that they have some of the downsides of table variables, but I’m still pretty keen on them as opposed to what people usually do, which is pass in the list of strings and then use a string splitter function in the middle of a where clause to split the list out and performance sucks and everyone’s sad. So in order to partially avoid that, what I do is I use a… See, I think I couldn’t even create a helper function to do it. I have to do everything in place here.

So I use this piece of code. I’m going to be honest with you. I did not come up with it. I sort of forget where it came from. I know that I used it in SP human events to pass out list of weight stats. It may have been in one of the blitz procs at some point that I was working on. I don’t know. If you know where this thing came from, let me know and I’ll happily give credit somewhere in the liner notes. I just forget.

But this uses XML and some cross-applying of nodes in order to split a list of IDs out. Now, I am using Dynamic SQL for this because I want to be able to reuse this piece of code regardless of what people choose to include or ignore. Remember, there are four different possibilities for things that people might want to include or ignore.

There’s plan IDs to include, query IDs to include, plan IDs to ignore, query IDs to ignore. I want a reusable piece of code for all that. I don’t want to have to, you know, I don’t want to have a separate piece of whatever nonsense this is doing extra work. Right. So what I’m going to do is when I need to, when people have something that they want to include or ignore, I’m going to use that piece of Dynamic SQL to do it each time.

Right. So you can actually include plan IDs and query IDs and ignore plan IDs and query IDs all at the same time. And I’m going to show you how that works. So if include plan IDs isn’t null. So the main table that I use to drive a lot of the queries here is query store runtime stats.

That has the plan ID, that has a plan ID column. It does not have a query ID column in it. So for plan IDs, it’s actually really easy. All I have to do is split the string that I pass in of include plan IDs.

And then I can tack on my where clause. I’m going to stick that into a temp table. And then I can tack on to my where clause that I just want to use this. Right. I just want to include plan IDs that are in this table.

The same thing with ignore plan IDs. If I want to ignore plan IDs, I just pass the list of plan IDs to ignore. And then we insert into ignore plan IDs. And I say, hey, if you want to ignore plan IDs, you want to find things that don’t exist in that table.

For query IDs, it gets a little bit more complicated. Because with query IDs, they’re not in the query store runtime stats table. So I have to figure out a way to get plan IDs associated with query IDs.

And that’s where things are a little bit weird. So if I have include query IDs, what I do is I still do the exact same thing, where I split that list and put the query IDs into a temp table.

But then I have to do this sort of separate query, where I go and get plan IDs from sys.query store plan that are included in that list. So I go and search that table out to find valid plan IDs for the query IDs that you care about.

And then I do almost the exact same thing, where I tack onto the where clause. So I actually reuse include plan IDs here. So I don’t have a separate, like, weird temp table structure.

I just reuse the include plan IDs or the ignore plan ID temp tables in here. So if there are query IDs that I want to ignore, then I get their matching plan IDs. I put those into the include plan IDs table, and we use that to drive the where clause.

And then if there are query IDs that I want to ignore, I do the same thing. I find those and stick those into a temp table via the plan ID. And I just find where, say, where exists blah, blah, blah, find these.

And then I go and tack that onto the where clause down here. And that’s all well and good. Cool and great.

So that, I think that’s it as far as explaining things to you. But here’s what it looks like when you actually run and look for some query IDs. So we’re going to run this, and we’re going to say, go back to the beginning of the year and look for these query IDs.

And I’m going to have debug turned on, even though that’s jumping ahead a little bit. We’re going to talk more about the debugging stuff in the next video. But what I wanted to show you here was sort of a little bit of what happens, where the include plan IDs table gets populated based on the query IDs that get passed in.

And then up here, we can see the query IDs that I had listed to include show up here. We can see some of those query IDs had multiple execution plans. That’s fun, interesting, exciting stuff.

Looking at the query text, you might figure out why. Maybe they’re very close by, slightly different in some ways. I don’t know. We’ll have to do some more digging to figure that part out. But this is how you can use SP Quickie Store to pass in a list of IDs.

And then, you know, if like, let’s say, you know, you get very interested in one of these things, you can just take this copy. You can just copy and paste this out.

And you could, you know, just rerun this to say include plan IDs. And you could put that list in there. And you could run this.

And you would get just those plan IDs back, right? So that’s how you can do that. And that’s the entire reason why you are able to pass list sim is because I was reviewing output and I was writing documentation.

And I looked at the plan IDs and I said, ah, crap. It would be really nice if I could just copy and paste that into something and run it and get everything back. Because the original implementation was that you could look for a single query ID or a single plan ID.

And I felt that that felt a bit, that felt a bit flat as I was writing the documentation. There was no pizzazz, that there was no zing with that. And I didn’t like it.

Everything must have pizzazz and zing with me. In case you couldn’t tell. I live in a world of pizzazz and zing. Wonderful. Anyway.

That’s how I implemented passing in strings to sp underscore quickie store. I hope you enjoyed yourselves and liked it and all that usual stuff. I will see you in the next video, which is going to be about, in case the obvious foreshadowing earlier did not cool you off, going to be about how I implemented debugging in this thing.

Because there are some, I think, I think some neat things about it that if you are the type of person who writes store procedures that either do this sort of server analysis or even, you know, for your own store procedures that you want to, you know, test things in, might be useful.

So, yeah. And we’re going to talk about debugging stuff for both dynamic SQL and table contents and stuff like that. So that’s what we’ll do in the next video. For now, I’m going to go, I don’t know.

I don’t know. I’m just going to wait patiently for this thing to render and upload to YouTube and then more stuff. I don’t know.

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.

Related Posts