SQL Server Performance Office Hours Episode 42

Questions:

My developers overuse table-valued parameters in application code. Are there any server/database settings I can change to make the queries using them run faster? I know about in-memory tempdb and memory-optimized table variables, but I don’t have latch contention issues so I doubt that they would help.
why aren’t developers good at database performance yet?
Why can’t you make an ErikAI to answer these questions?
Do you have any training content specifies performance tuning efforts and limitations for SQL Server Standard Edition? I get excited to see posts from you and the community around Batch Mode in Rowstore only to get let down by licensing limitations yet again.
Are you doing a Black Friday sale? (This was a blatant lie.)

Video Summary

In this video, I delve into answering five community-submitted questions during an office hours session, providing insights and advice on various database-related topics. We cover issues like table-valued parameters in application code, performance tuning efforts for SQL Server Standard Edition, and the limitations of licensing. I also touch on upcoming pre-conference events at Data Tune Nashville and Data Saturday Chicago, encouraging viewers to purchase tickets early. Throughout the session, I emphasize the importance of regular practice in database performance tuning and share my thoughts on why developers might not excel in this area due to time constraints and project deadlines.

Full Transcript

Erik Darling here with Darling Data. It is Monday in YouTube. Actually, it is Monday in regular land, too. Which Monday it is, you will never know. It’s between me and my Monday. Anyway, we are doing office hours in which I answer five community submitted questions and hope that I can give good answers at a reasonable rate. They’re free. Shut up. If you want to ask your own question, you can head down to the video description where these helpful yellow fingies are pointing. And there’s a link there where you can go and submit your questions to me. If you would like to participate in some sort of monetary exchange, there are ways to do that as well. You can hire me for consulting, buy my training, sponsor this channel, all sorts of other stuff. And of course, if you enjoy this content, please do like, subscribe, and tell a friend. Because that’s how the good word spreads, my friends. Anyway, coming up. Of course, by the time you see this video, Past Data Summit will actually be occurring. It’ll be actually Monday. I’ll be teaching a pre-con. Isn’t that wild?

So I should probably start promoting other stuff that has recently been acceptified. I will be doing a pre-con at Data Tune in Nashville. That event is taking place March 6th and 7th. And I will also be doing a pre-con the very next weekend. Data Saturday, Chicago, March 13th and 14th.

So buy those tickets now. So I can gloat about people buying those tickets now or something. I don’t know. Well, it would just be nice of you to do. Just buy the tickets. It’s great. Anyway, let’s get on with this whole database party here.

I need to go to the Excel file that has the questions in it. And let’s make sure there are, let’s see, one, two, three, four, five questions. And let’s go answer these.

Did it, did it. What do we got here? My developers. Oh, your developers. Oh, all right. See the way it is? My developers.

Let me tell you this. If they’re your developers, tell them to stop using table-valued parameters if they’re such a problem. All right. Not to spoil the question too much here. But my developers overuse table-valued parameters in application code.

Are there any server or database settings I can change to make the queries using them run faster? I know about in-memory TempDB and memory-optimized table variables. Oh, boy.

But I don’t have latch contention issues, so I doubt that they would help. Boy, are you, boy, are you right there. The problem with this question, though, is you haven’t told me what’s slow about them. What’s, what’s going wrong?

Some details would be nice. There are not really any server or database level settings that, you know, wouldn’t already be turned on if you’re in a position to have them that wouldn’t be helping you out. So I don’t really know what to tell you here.

Tell me what’s slow about them. Better yet, click on, click on that consulting link. And then we can do a fully detailed investigation into these table-valued parameters and figure something out for you.

I don’t know. This level of vagueness just can’t be, can’t be overcome. Why aren’t developers good at database performance yet?

Well, because they don’t practice it. They develop features. They need to get something working quickly because they’re under, you know, whatever, you know, time constraints and deadlines they have to deal with. And they have to make something work.

It’s the same reason why developers aren’t typically good at database security or security in general. It’s why, like, every time you read, oh, we found a database on the internet with no password. Surprise!

You know why? Because someone just had to get something working. Security gets in the way of stuff working, right? Like doing performance tuning. Oh, gets in the way of just making the thing work, right? It’s like, oh, I figured out how to do it.

I don’t know if it’s very fast, but it works on my machine. You don’t pry. Unless you are investing time and effort into learning and practicing a skill, you are never going to get better at it. That is true of any mental or physical endeavor in life.

If you are not regularly engaging in that endeavor, you stand no chance at progressing in that endeavor. Let’s see. Why can’t you make an Eric AI to answer these questions?

Have you seen the cost of training a model? Right? I mean, like an MCP server ain’t going to cut it. There’s not a lot of funding rounds here at Darling Data to get into an AI training situation.

So that’s way out of the question. And I don’t know. I always assume part of the charm was me showing up with my alacrity and effulgence.

And effervescence and answering these questions. From wrong, you just want AI to do it. You want a chatbot.

Well, I don’t know. What else? It’s funny because like LLMs are basically an MLM, right? Because like, I mean, just like the industry in general at this point is an MLM.

But like the people making money off LLMs are like selling prompts. Like you’re selling me a question to ask them? Like you’re just like, oh my God.

Anyone who does that is an absolute scavenger. All right. But that’s why. All right. Because Eric AI would be too expensive to train. Do you have any training content specifies performance tuning efforts and limitations for SQL Server Standard Edition?

I get excited to see posts from you in the community around batch mode and rowstore only to get let down by licensing limitations yet again. No, I don’t. Because 90 something percent of the time performance tuning with Standard Edition is exactly the same as performance tuning with Enterprise Edition.

You have some additional restrictions and limitations around hardware and what is automatically available to you. And even some restrictions on, you know, things like batch mode where it is limited to a DOP of 2 in Standard Edition. But most Standard Edition performance tuning is no different from Enterprise Edition performance tuning.

There is just not a whole lot that you have to do differently. You know, you work within your restrictions and limitations and you make the best of it. You know, a lot of the training that I have would work just fine on Standard Edition.

There’s nothing specific about it. There’s nothing specific enough about it, especially since Microsoft gave up on some of the earlier restrictions that existed with it. Some of the programmability stuff with 2016 SP1 that became available in Standard Edition.

There’s really not a whole lot too, too different there. Microsoft was even kind enough to give UDF inlining to Standard Edition. So, you know, there are some, of course, some additional considerations.

But, again, my rates are reasonable. Final question of the day. Oh, boy.

Are you doing a Black Friday sale? No. My training is already priced so that normal people in the world can afford it. I already bake a lot of coupons and discounts into stuff.

You know, I don’t want you to need, you know, a payday loan or a stipend from work in order to buy my training. I want lots of people to be able to buy it and benefit from it. So I don’t price it at a point where, like, a Black Friday sale would really get a lot more butts through the door.

So, you know, right now, no, I can’t see a reason to discount things further. You know, I do have a family to feed and all that. You know, I have actual children.

You know, actual life. There are people, like, you’ll never see on camera who I have to take care of. So, no, I can’t see a Black Friday sale being a very provocative. I can’t see too many provocative incentives in me knocking stuff down further.

So it already sells fine at the very low and reasonable prices that are set for it. So, no. No.

I’m not embarrassed by my prices. So, anyway. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video, which, if I have things right, will be a very special revival video of sorts. So stay tuned for that.

Anyway, 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.