Time to read: ~ 4 minutes
Words: 899
Welcome to T-SQL Tuesday, the monthly blogging party where we receive a topic to post.
This month we have Erik Darling ( blog ) asking us to post about “code that makes [us] feel a way“.
A while back, I was tasked to performance tune some code that brought me through all five stages of grief. It’s best to take you through the events from the developer’s viewpoint. There’s less cursing that way.
Denial
Hey, what’s up? You were asked to troubleshoot some code. Not a big deal; these requests come in from time to time.
You have code that keeps coming up as a high consumer of CPU on our systems? OK?
It had gotten to the stage where it kept appearing in sp_WhoIsActive? What’s that? And should it not be “whom is active”? Well, agree to disagree.
Let’s see the code so. Wow, that’s a small scroll bar! Yeah, that’s one of ours.
No, it is. I swear it is.
I’m not surprised you can’t find it in stored procedures; we dynamically create it. Here’s the procedure. Yeah, all 900 lines! Pretty cool, huh?
What do you mean, why? We had to! We had to react to the different parameters that get passed in.
Anger
Alright, alright! Calm down. I misspoke.
Yeah, that’s it, breathe. There aren’t different parameters. It’s a parameter. Singular.
No, we still need to generate the query dynamically. Cause the XML could be different. Huh? Yeah, the parameter is XML; it could be anything in there. So we pass the XML in as a nvarchar(max) string.
You spilt some of your coffee there when you banged the desk.
Then we use sp_xml_preparedocument at the start, but then we have to use sp_xml_removedocument at the end.
You’ve never heard of those before? I thought you were a DBA?
We use the “prepare” to get the XML into a state that’s easier to consume, and we use the “remove” so we don’t get memory leaks!
Your face is getting a bit red, did you know that?
It’s SQL Server; it uses enough memory it can share some!
Did we read the docs? No, why? It can use one-eighth of the total memory available for SQL Server. Ah, but that’s “can”, not “will”.
Yes, yes, cursors upon cursors upon cursors. Why? We don’t know how many values are in each XML node in the params, so we have to split them out.
We then join them into a comma-delimited string, which is then used in IN clauses… woah! Is that the sound of your teeth grinding? You know that’s not good for your health. What do you mean neither am I?
Anyway, then we parse all of that down to a massive, what do you call it, swiss-army knife, lego-block, dynamic query built based on what’s passed in. You don’t call it that? I thought you did. What do you call it so? Wow, that’s not a word I had heard of before.
It’s not too bad, though! We pass in everything as literal values, so it’s faster that way. We read up on that parameter sniffing issue you said can happen. That isn’t going to catch us out here!
Modern problems require modern solutions, as they say. What’s that? It’s not a modern problem? I’m a what? Are you allowed to call me that?
Bargaining
You want me to re-write it? You’ll help? It’ll take a lot of time. It’s not really a priority.
Plus, it’s not even worth it. How much CPU? Is that a “illion” with an “m” or “illion” with a “b”? Per run? Is that a lot? Well, I really wouldn’t know, now would I?
Yeah, good point; you’ll also have to check how much memory it uses. Hey! Probably one-eight of the total memory available for SQL Server. Alright, it wasn’t that bad a joke; everyone’s a critic!
You’ll give me credit? But it’s fine in Dev; there’s no point. I suppose we could remove some of the WHERE OR IS NULL branches since we know if they’re empty when we create the query.
Nah, that would make the procedure a bit too big. Anything over 1000 lines is too much. Well, 985 is still less than 1000!
Depression
Why are you crying? No, we care, we do, we really do. No, not all the code is like this; this is old. Yes, we stopped using that XML parameter pattern. Yes, and the cursors. Well, most of them, at least.
Your tears are mixing with the coffee you spilt, you know that?
Here, there’s really no need for the fetal position in the middle of the office. You have yet to see the code for the busier databases.
You know what, why don’t you go home and come back in tomorrow, and I’ll go over that way, OK?
Acceptance
Hey! There’s our favourite DBA. You seem a lot cheerier.
Oh, you don’t have to perf-tune that query anymore? That’s great; see, it wasn’t so bad. What’s that? The latest release failed? It ran fine in Dev.
You’re permitted to set up a QA environment to test performance before code gets released? What kind of code? Any stored procedure?
Ah, that’s OK. No, really, it’s fine. We started using ORMs and embedding SQL into the app a while ago. This won’t affect us.
You’ve started crying again.