SQL Server Performance Office Hours Episode 44

SQL Server Performance Office Hours Episode 44

---

Questions:

• When does make sense to use tables in user DBs rather than tables in tempdb to hold intermediate results in large stored procedures? Assume no hardware difference on user db drive vs tempdb drive.
• We are currently suffering from parameter sniffing issues in our stored procedures (SP) due to the heavy use of IF statements for validation, permissioning and case handling. We are aware that we could use dynamic sql to improve the problem as it would only create and cache execution plans when it’s triggered, as opposed to regular sql statements where plans will get generated even if it’s not executed for the first time the SP is called. Does this mean that we will have to change every sql statement to dynamic sql (bloating the whole SP)? Alternatively, we thought of transferring our SP code to the app level and trigger each sql statement / blocks of SQL statements with Dapper, however let the backend app handle the IF statements. Would this help with parameter sniffing – given that it wouldn’t reuse plans like SP if it isn’t triggered? We are not really familiar we Dapper either, and we would like to know what you think about this (disadvantages and trade-offs). We are also open to other suggestions to tackle this challenge. Thanks in advance 😀
• Azure SQL Db have Automatic tuning (identifying expensive queries, forcing the last good execution plan, adding indexes, removing indexes). Does it work?
• Erik this is reErik this is really P*ss*ng me off and I can’t find the answer (and yes I’ve tried AI). How do you stop SSMS (22) from putting square brackets [] round all object names? Arrgghhh please help me before I bite my own head off with frustration. (from one of your loveliest channel members) xxx
• is it okay to truncate table tables? i read aboit dropping them being bad.

To ask your questions, head over here.

Video Summary

In this video, I dive into a series of community-submitted questions during an office hours session. Erik Darling from Darling Data tackled topics ranging from when it makes sense to use tables in UserDBs versus TempDB for intermediate results in stored procedures, to dealing with parameter sniffing issues and the pros and cons of using dynamic SQL or moving logic to the application level. I also discussed Azure SQL DB’s automatic tuning features and why they fall short of expectations. Additionally, I addressed the frustration of SSMS22’s habit of adding square brackets around object names and offered potential solutions. Lastly, we explored whether it is acceptable to truncate temp tables within stored procedures for better tempDB management. It was a packed session with plenty of valuable insights!

Full Transcript

Erik Darling here with Darling Data and it is a fine Monday, which means we need to have an office hours. Hey, you didn’t see that coming, did you? Which I answer five community submitted questions of varying degrees of quality. And I don’t know. I don’t know. Maybe I should figure out a way to let people vote on these things. I don’t know. Anyway. Down in the video description, you will see all sorts of helpful links. You can hire me for consulting, buy my training, become a supporting member of the channel. All of these things do require money. But you can do free stuff as well. Like, ask me these office hours questions. Maybe I should start charging a dollar for those. Because then people might put a little bit more into them. Who knows, though? And of course, if you like this content, you can always like, subscribe, tell a friend, tell a multiple friends if you have multiple friends. Your mother and I would be very proud of you if you had multiple friends. If you would like to catch me out in the world, I have a couple confirmations for starting off the 2026 conference season. I should have some more of these coming up. There’ll be some pass-on tour dates, probably, and, well, some other stuff, too. We’ll see how life goes.

But Datatune Nashville. I actually announced this a little bit early. My fault. They just officially announced pre-cons today. Tickets go on sale today. So if you go to the Datatune Conf website, you should be able to figure out how to buy a ticket to come see me. I’ll be doing Advanced T-SQL at Datatune. And then Data Saturday Chicago. More Advanced T-SQL. Someday I’ll learn my lesson, right? But tickets are on sale for the pre-cons there. So please do go ahead and buy those and come see me and hang out as the weather starts turning more baseball-y and can all get back to wearing normal clothing, I hope. But with that out of the way, oh, you know what? It’s December. I actually have a new image for this. I just haven’t changed it yet.

But so I’m going to switch over to the Christmas one for the next video, I promise. Typical oversight on my part. Not updating the images there. But I have a brand new one for the month of December. So we’re going to get all Christmassy up in here. But anyway, we’ve got, again, some questions here. And ZoomIt is being a pain in my entire body.

And let’s start way up at the top. Let’s make sure we have one, two, three, four, five. We have five questions. I counted correctly. We are off to a great start, my friends. When does it make sense to use tables in UserDBs rather than tables in TempDB to hold intermediate results in large store procedures?

Assume no hardware difference on UserDB drive versus TempDB drive. I would say almost never. Unless the data that you’re loading is something that you, is like something maybe for like a staging table.

And if the process fails, you don’t want to like restage it. You know, like you might want to preserve the data to say what the hell happened. Then it makes total sense to use a physical table in a user database rather than a temporary table.

The rest of the time, I’m going to get pretty much a hard no from me on that. I can’t think of a lot of exceptions to that rule off the top of my head. But like honestly, unless you need to preserve that data for some, you know, forensic analysis of, again, like what went wrong here type thing, I can’t see a good reason for that.

And ZoomIt is still being a pain in all of my body. Let’s see. Oh, well, it’s letting me draw more things.

It’s just not letting me get unzoom. That’s fun. There we go. Hey, thanks, buddy. All right. This is a long question.

It’s really just like, hey, maybe you need a consultant. We are currently suffering from parameter sniffing issues in our store procedures due to heavy use of if statements for validation, permissioning, and case handling. We are aware that we could use dynamic SQL to improve the problem as it would only create in cache execution plans when it’s triggered, as opposed to regular SQL statements where plans will get generated even if it’s not executed the first time the SP is called.

Does that mean that we will have to change every SQL statement to dynamic SQL, bloating the whole SP? Alternatively, we thought of transferring our SP code to the app level and trigger each SQL statement blocks of SQL statements with Dapper. However, let the backend app handle the if statements.

Okay. Would this help the parameter sniffing given that it would reuse plans like SP if it isn’t? Well, we are not really familiar with Dapper either.

And we’d like to know what you think about this disadvantage in trade. So, um, uh, it sounds like the, the problem you’re hitting is not, uh, I mean, not like sort of the classic parameter sensitivity issue where, um, you know, uh, the cache plan for one set of parameter values is not a very, uh, efficient plan for a, a different runtime set of parameter values. Um, what, what you’re dealing with more is the null sniffing thing.

Um, where I think, you know, in, in general, um, like, like you, like you, you kind of went all over the place with like, like the, like bloating the plan cache and stuff. Uh, but, um, like you, usually what people get concerned about in these situations is, um, that like, let’s say you have a store procedure and I actually have a similar video, not exactly this, but a similar video coming up, um, I think probably tomorrow or the day after. But, um, the, the problem that you run into is that when SQL Server, like you have multiple parameters for a store procedure and maybe one set of parameters is not supplied values for and SQL Server sniffs a null for them, then you run into issues.

So, uh, in general, uh, you really only want to do the dynamic SQL where, uh, the, the sniffing occurs, right? So, um, uh, I’m not terribly familiar with Dapper either. I have, I have run into like many people who didn’t feel like writing dynamic SQL and just handled all the, like, what am I going to run in the app code?

That’s totally fine. That’s totally valid. I don’t have a problem with you doing that. If you want, if you want to learn Dapper and figure out how to, you know, uh, run that stuff, go ahead and do it.

Uh, but you know, the typical, um, you know, the, the typical sort of caveats apply to that, you know, make sure that your, uh, parameters are strongly typed in Dapper. And make sure that your code is parameterized, right? So those two things are going to be the big important thing.

Otherwise you will have a completely different set of terrible problems. Uh, but, um, in general, when you run into this issue, the only code that needs to be put into the dynamic SQL is the code that, um, that would, that would potentially sniff a null parameter value. And then on execution, use a terrible plan based on that null parameter value.

So I think, I think that’s, that’s my final thought on that. Um, but no, you don’t have to make everything inside the store procedure dynamic SQL. Um, you could like certainly group batches together in dynamic SQL, but you know, uh, that gets a little, it gets a little funny looking to me.

Uh, Azure SQL DB have automatic tuning, uh, identifying expensive queries, forcing the last good execution plan, adding indexes, removing indexes. Does it work? Let me ask you a question.

If something like this worked, do you not think that you would see a lot of people just on the roofs, on the balconies, singing its praises, right? Tearing up their SQL Server training, taking down their SQL Server. Like we don’t have to worry about it anymore.

Azure SQL DB have automatic tuning, identifying expensive queries, forcing last good plan, adding indexes. Indexes, removing indexes. We would just not have to be concerned for one moment.

Would we? The answer is it sucks. I’ve seen people use it. The index tuning garbage, the forcing a last good plan.

Well, it forces the last better plan, but that might not be a good plan. And sometimes it doesn’t even force anything. And other times it like the fail, the forcing fails.

And then you end up with a whole different set of terrible things. So, no, if something like this truly worked, you would hear about it. This would be a major breakthrough in database management.

But it doesn’t work. It tries to work, but it doesn’t work. So, no.

No, it doesn’t. No, it doesn’t. Eric, this is Rerick. Hi, Rerick.

Nice to meet you. This is really pissed. Well, I guess passing me off. Well, that wouldn’t be any better.

I don’t know. Anyway, and I can’t find the answer. And yes, I’ve tried AI. Well, I’m glad that you used AI and then you finally, you know, deigned yourself to ask a human being. How do you stop SSMS22 from putting square brackets around all object names?

Arg. Please help me before I bite my own head off with frustration. From one of your loveliest channel members, XXX. Well, all right, lovely channel member.

I have good news and I have bad news for you. The good news is that your head will probably taste lovely. The bad news is, did I say bad news already?

Anyway, the bad news is I don’t think that option is in SSMS22. I went through all the, like, script your stuff out settings. And I didn’t see anything about not putting square brackets in there.

The one thing that I can maybe recommend is Redgate SQL Prompt, which has a lovely keyboard shortcut that says remove square brackets. And it’ll just strip them all out. Otherwise, it’s like, you know, the whole find replace thing on the square brackets.

But that’s not really fun to do every time, is it? I don’t know. The good news is that SSMS22 is under very active development.

And one could very reasonably make a feature request to say, hey, I miss being able to remove square brackets from the scripting options. Maybe, maybe, maybe you could add that in for me.

There might be a good reason why they remove that. I’m not sure what it would be. But, you know, it doesn’t hurt to ask the people who build the product. What’s up with that?

Is it okay to truncate table tables? I read avoid dropping them bad, being bad. I’m going to assume you meant tempting.

Sorry. I’m going to assume you meant tempting. And, yeah, truncating temp tables is okay.

I’ve not run into a problem with people doing that. Dropping them in a high concurrency environment can certainly add some tempDB contention into the mix because you’re messing with how SQL Server caches and reuses temp table stuff.

So it’s explicitly dropping them. Yeah, at the end of the store procedure, like just drop table, pound sign, whatever.

That can all certainly not be great. But truncating them is okay. I actually have one customer who creates some very, very large temp tables along the way in their procedures.

And, like, midway in the store procedure, they might be done with specific temp tables, and they will truncate those to just clear up some tempDB space so they don’t have to wait for the full end of the store procedure because the store procedures, despite a lot of tuning, they do, like, very, very big calculations and aggregations.

So they are time consuming. And it does help manage tempDB space for them to truncate the larger temp tables when they’re finished using them within the store procedure.

So that isn’t… To me, that’s an okay thing to do. Table tables. All right.

That’s good for me here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about some… Oh, I don’t know.

Some other grim database reality, I suppose. What else? What else is there? No joy in Mudville, huh? All right. Cool.

Thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.