sp_QuickieStore: Now Handling The Biggest XML
Thanks for watching!
Video Summary
In this video, I share an exciting update to SP Quickie Store, a tool I’ve been working on to help SQL Server users manage and analyze their Query Store data. I explain the problem of large query plans that can’t be displayed as XML in SQL Server Management Studio due to exceeding 128 nested nodes deep. To address this issue, I introduce SP Quickie Store’s new feature: it automatically converts these large query plans into a readable format by using the `TRY_CAST` function and saving them as SQL plan files. This allows users to easily view even the most complex execution plans without manual intervention. If you’re interested in trying out this solution or want access to my other useful stored procedures, head over to my GitHub repo at code.erikdarling.com. Don’t forget to subscribe to my channel for more SQL Server tips and tricks!
Full Transcript
Erik Darling here with Darling Data with an exciting, thrilling, industry-leading announcement. I was voted by EarGut Magazine to be the SQL Server Consultant most likely to lead the industry in announcements, but there’s really no indication about whether those announcements would be good or bad, anything. Good news is, no layoffs so far. New social media in turn is working out nicely. Apparently I have videos on TikTok now, so they’re just like chopped up versions of this, so if you prefer me in smaller bits and bytes and pieces, you should follow me on TikTok, I guess, or whatever you do on TikTok. I don’t know if it’s following or not. I don’t actually open the app, but, you know. It’s there. I’ll put a link to it in the notes for this video. But anyway, SP Quickie Store. I’ve been working on a really cool update for this the last, I guess, evening. Depending on how you count your evenings, it is evening. And what it does is, so, I should explain the problem a little bit first.
And then I’ll show you the solution. So, in Query Store, Query Plans are not stored as XML. And the plan cache, you know, you don’t have to worry about, well, I mean, you do kind of have to worry about this stuff. That’s why plans are stored in like seven different places. But for this, rather, for Query Store, and specifically, Query Plans are not stored as XML. They’re stored as like this weird, like, compressed image text thing. And it’s up to you to turn Query Plans into XML so you can click on them and look at the pretty pictures.
Unfortunately, some Query Plans are so gosh darn big, so enormous, so more than 128 nested nodes deep that you cannot display them as XML and SQL Server Management Studio. You try to do it, and you get an error. So, if you just said like, convert XML this blob, and it was too big, you’d get an error saying this XML is greater than 128 nested nodes deep. You can’t do that, dummy. And so, that sucks. But SQL Server has a function that has, I mean, goes back pretty far into the mists of time called try cast. Try convert is a different animal. Try cast is like, wherever.
So, with try cast, you can try to cast something. Well, you can, and specifically, you can try to cast this as XML. And if it doesn’t work, it’s just null. Problem is, you still might want to see those Query Plans. You still might want to say, like, well, okay, so what? You can’t, like, XML clickify it as SMS. I still want to see the thing. Now, to be clear, you can still open plans that are greater than 128 nested nodes deep as graphical query plans.
You just can’t do it, like, click on it, open it, because, I don’t know, Microsoft hates XML or something, I guess. Just didn’t want to deal with it. I don’t know what the deal is. I wasn’t there when XML was implemented in SQL Server, so I couldn’t tell you any inside baseball on that.
Anyway, so this is what Quickie Store does now, all right? So, I’m going to hit some approximation of F5. It might have been Ctrl and E. It might have been F5. You can’t see my fingers, and I’m not telling you.
So, we have two queries up here and two query plans. Now, if we zoom in a little bit, zoom in a little bit, there we go. Thank you, Zoomit. Very responsive application.
There’s two different things here, all right? There’s this one up top, which is, let me see, this is a huge query plan. And there’s this one on the bottom, which is just a regular old query plan.
So, if you click on this one, we get nice pretty pictures. Great. Stunning. Miraculous achievement. Thank you, SSMS.
By the way, I’m running SQL Server Management Studio 20, right? Big shout-out to Aaron Stellato for getting us V20. You know, it’s cool.
But this first query plan, a little different, right? If you click on this, we do not get a nice pretty picture. We get this insane blob of text. What do we do with this insane blob of text?
Well, there are some directions up at the top of the insane blob of text that tell you what to do. It says, remove the headers and footers and save it as a SQL plan file. Cool. So, here’s what we’re going to do.
We’re going to just erase that. Oh! Look at that. Pretty colors now. Beautiful colors. Yes. And then we’re going to just grab the scroll bar and head all the way down to the bottom.
And we have one other thing to get rid of. It’s the sort of the XML containment unit. And it’s this last question mark greater than thing.
So, we delete that. And we just make that a line. And then, our job gets pretty easy. We’re going to say, hey, we’re going to save as.
And we’re on the desktop already, so that works out well. And then we’re going to say, all files. And there’s an annoying text file joining us for the video. Thank you for making an appearance.
Annoying.txt. And we’re just going to, rather than mess around a whole bunch and make you watch me probably make typos and other stuff, I’m just going to delete XML. And I’m going to say, SQL plan.
And we lost our color. We lost our glorious color. Very sad. But what we can do now is close this out. And we can go to file and then recent.
And then hope that nothing weird is in there. No, it’s just stuff I’ve been working on. That’s fine. Okay. I get nervous.
It’s like, if I opened up a browser and it was like, would you like to restore your tabs? They’d be like, oh, maybe not. No. I mean, Darling Data does not have an HR department, but we do try to maintain the strictest standards of family-friendly SQL Server content.
So, you know, if we ever get an HR department, we can’t get any retroactive violations. But if we just click on this now, because that’s in our recent files menu, it’ll open up and we have a beautiful graphical execution plan. All right.
And this is, of course, a massive execution plan. It just keeps going and going and going. It took a little bit of work to get a query plan to test this out on. If we say zoom to fit, it doesn’t disappear.
It’s just this really thin line across the middle. So that just keeps going. There’s the end of it. I’m not going to zoom in because it’s just a weird disgrace.
Someday I’ll figure out how to make one shaped like the little Charlie Brown stripe and put that somewhere. Maybe. I don’t know.
I saw someone playing Doom in Task Manager the other day. Not like they made it say Doom. Like they were actually playing the game Doom. It was like the CPUs are like drawing the images. It was wild.
But I mean, I don’t know if they’re actually playing. They might have just like had some program do a thing. Probably. I don’t know. I’m not that good at computers. I’m only good at databases. So you deal with what you got.
So anyway, very excited about this. If you want to download SP Quickie Store, you can go to my GitHub repo at code.erikdarling.com. There will also be a link to code.erikdarling.com in the show notes.
And so you can go get that and all my other store procedures. Because I have a lot of good ones that help you with SQL Server and help you find problems. Maybe even fix problems.
And if you can’t fix the problems yourselves, you can always hire me to do it. I am a consultant after all. I do get paid to fix these things. Strange world we live in.
So anyway, what else? Let’s see. We talked about TikTok, Quickie Store. I think that was it.
Yeah, that’s all we talked about. Cool. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you download SP Quickie Store and use it to analyze your SQL Server’s query store. And if you like this video, I do like thumbs ups from my beautiful watchers.
And if you like this type of SQL Server content, pretty please subscribe to my channel so that I can brag about how many subscribers I have to absolutely no one. I just like seeing numbers go up. So aside from like blood pressure and cholesterol and stuff, those are the bad ones.
Whatever. Whatever. Anyway, thank you for watching and I will see you in another video probably sooner than later because I have to record videos so that my social media in turn can stay busy. So thank you for watching.
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.
“Zoom to fit” fixes all my problems.
This isn’t Tinder, sir.