sp_QuickieStore: Safe Dynamic SQL

Headboard

---

Thanks for watching!

Video Summary

In this video, I take a deep dive into implementing safe dynamic SQL within my new stored procedure, SP_QuickieStore. This procedure is designed to quickly extract useful data from QueryStore about your worst-performing queries, providing insights that can help optimize performance and improve overall database efficiency. I meticulously review the implementation of dynamic SQL, focusing on best practices such as using `QUOTENAME` for object names to avoid potential issues with special characters or schema resolution across databases. Additionally, I discuss how to safely pass parameters into dynamic SQL blocks while ensuring that no user input is directly concatenated into the query string, which could introduce security risks. The video also covers some of the tricky checks and conditions necessary when working with QueryStore metadata, such as verifying the state of query store features in a database.

Full Transcript

Erik Darling here with Erik Darling Data. I’ll be out on location next week for BeerGut Magazine doing a series of interviews with some of the world’s most famous beer guts. Actually, I’m just going to be on vacation. So, because I’m on vacation, or will be on vacation, I am doing a bunch of low effort code review videos of my new store procedure, SP underscore QuickieStore, designed to hopefully quickly get data out, useful data out of QueryStore about your worst performing queries, I guess. That’s the elevator pitch. I don’t know. Hopefully, hopefully that elevator doesn’t smell too much like farts. Anyway, the first thing according to my list is even though I have misspelled implementing I’m going to talk about implementing safe dynamic SQL in this store procedure. And despite the fact that SSMS has this red rectangle in the search bar, I promise I do actually have things marked review 01 that we’re going to look at. Now, I went through and I marked a whole bunch of stuff with these things because this is a long store procedure. Turns out it takes a lot of code to effectively figure things out in QueryStore.

And so, I didn’t want to spend a long time scrolling. I’ve already got some weird hand tingles from all the mouse wheel scrolling I’ve had to do while working on this thing. So, you know, maybe someday I’ll get a purple heart from Microsoft. Anyway, let’s scroll down to where we start with some dynamic SQL stuff. And you’ll see some things. You’ll see some things that we’re not reviewing yet that we will review in the future. But what I set up is a few different things. Now, obviously to hold dynamic SQL, and I don’t know how long a string is going to be ahead of time. So, I’ve had to make some things max. I have tried to be kind when I know that a string is not going to be max and I don’t have to concatenate that string into a string that is a max and hopefully not deal with any weird length conversion issues where SQL Server is like, Oh, you’re only in nvarkar 4. We don’t need the other 3000 lines of text. So, try to do some nice things there. Anyway, what I do ahead of time, or at least most of the time when I’m thinking is when I know that some of my dynamic SQL is going to be working with objects is I like to set up variables to hold quoted versions of those object names.

All right. So, like I don’t want to have to think about using quote name every single time I reference this thing. I just want to set up, set that up ahead of time and then reuse it. All right. So, I have a couple of those and I have my nvarkar max SQL, which is going to hold a lot of the SQL statements that I use. And I have my where clause, which I’m going to use to dynamically set up a where clause to hopefully find the right data and yada, yada, yada. Where I set some of those dynamic SQL bits, even some other bits that I think are important to use are down here. Now, you’ll see that one, like, even though database ID is under review 01, it’s not technically used in dynamic SQL, but I think it’s, it’s almost always a good idea to avoid any confusion or potential, like, incorrect data to get a database ID to go along with things.

That way, you know, like, let’s say you’re running this on a weird server where there’s a lot of like backups and restores of databases and you might, I don’t know, go to the wrong thing, look at the wrong place. Something we’ll cover in a future video about debugging is it will return parameter values for all these things. So, you can at least make sure that you’re getting to the right database. So, but even, so even though the database ID thing isn’t exactly used in dynamic SQL, I think it is important to have some, some things resolved for other reasons.

But then down here, you can see database name quoted gets turned to quote name there and procedure name quoted gets a little bit more complicated. Because, I use this in a funny way with object name, which requires the string to be passed in there to be set up in a specific way to be able to find objects across databases. Now, remember, this sort of procedure is mostly going to be run from master.

And if I want to resolve an object name or an object ID in a different database, I need to tell SQL Server exactly where that’s going to be. I need to tell SQL Server which database, schema, and object name to go after. All right.

And so, that can rather quickly get out of hand. So, I have to quote the database name. And something that’s always bugged me about building dynamic SQL is when I want to put together object names, whether it’s, you know, database, schema, object, or, you know, schema object, is I always have to put my own damn dots in. I wish that there was something I could do that would just add the dots in for me.

I’m sick, sick of damn dots. If I had a dollar for every time I typed tick dot tick, I don’t know. I don’t know.

I would probably not have to go write for Bear Gut Magazine, moonlight for Bear Gut Magazine to make side money. But, so I have to get the database name and then I have to go get the procedure schema. If I don’t have that, then I substitute that with DBO.

And then I have to get the procedure name in there. So, that’s the kind of stuff that I do there. Moving on down a little bit, here are the parameters that I pass into dynamic SQL. Now, there are two ways to give things to dynamic SQL to look at.

There is stuff like object names, which you can use quote name to, like, pretty safely quote out. And then there’s the passing parameters in. I don’t want to pass any parameters directly into my dynamic SQL if I can avoid it.

Over in SP human events, or, sorry, SP underscore human events, I do have to do some of that stuff where I pass strings directly in. Because the way that extended events get set up, they don’t allow for parameterization of things. So, I have to take some extra steps to clean strings and make sure things are sanitary there.

But, here, I don’t really have to worry about that too much. I’m just passing in things that can easily just be parameterized, right? So, I’m not going to concatenate any values directly into my string from user input.

All of my user input is going to be passed and parameterized to those code blocks. All right. So, let’s move on down a little bit to the next section where dynamic SQL starts getting used.

And here, you can see a wonderful example of how I use the already quoted database name to get to the database that I care about to see if query store options contain valid settings or contain any settings at all. This is actually kind of a tricky check to write. Initially, I was looking just at sys.databases to see if query store was turned on.

But, it turns out you can have terribly unreliable metadata in sys.databases. I ran into situations where – so, I’ve been testing this thing out with clients and, you know, all that other good stuff for a while now. And, I ran into situations where sys.databases said query store was turned on.

But, the query store settings said, no, we’re not turned on. And, I ran into situations where sys.databases is like query store isn’t turned on here. But, I looked at the query store settings.

And, lo and behold, query store was enabled at the database level. So, there’s all sorts of things that can get weird there. So, what I have to make sure of if I’m going to tell people that query store is really turned on is that it’s got an actual state of zero, right? Sorry, the thing that I have to look for if query store is not turned on is having a query store state of zero.

And, I saw this once in SQL Server 2016 in RDS. I don’t know if this is a normal thing, but I’ve realized that I had to check against this too. The other thing that I check for is to see if there are absolutely no rows in here, right?

Because, this only handles if we find something, right? I need to figure out if absolutely no rows exist in that table, then we’re also going to return a zero here, right? And, then if we go down a little bit further, we’ll see some of how the SP execute SQL stuff works with parameterization.

So, we pass in our SQL. We pass in the parameters that SQL Server expects, which in this case is query store exists up here. In other cases, this could, you know, be replaced with, you know, whatever where clause values you want to stick in.

And, then we tell SQL, we tell the dynamic SQL block that we expect this thing to get passed as an output parameter, right? And, then if query store exists equals zero, then we will throw this error message. Fun stuff.

Now, there is a good, another thing that I want to actually look at. I think I’ve neglected to note. No, not in the selection, dummy.

In the review stuff. But, you can use a parameterized set of parameters with dynamic SQL. So, in this case, if you remember, way up here, way up here, I have a parameter, a declared variable called parameters.

And, then I have these parameters that I define in this block here. And, then if we go down way further to here, you can see that I actually use parameterized set of parameters to pass parameters into my dynamic SQL. So, that when I built up my where clause and done other stuff, then, like all the stuff in here where I tell SQL Server, where I want to find, where I don’t want to find.

It’s a little bit easier to read. Right up somewhere. About, oh, God, where is it?

There we go. In here where my where clause is going to be, you know, stuff like, you know, looking for start dates and end dates and execution counts and things like that. But, anyway, that is how I implemented, hopefully, 100% safe dynamic SQL in SP Quickie Store.

I hope you enjoyed watching this. Maybe you found it somewhat informative. I’m going to get this rendering and start recording the next video, which will be about how I implemented error handling.

So, fun stuff. I will see you, I guess, in the next video then. That makes sense, right?

More videos for everybody. Free! 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.