SQL Server Performance Office Hours Episode 38

Questions:

3. What are your thoughts on using AI (non-Microsoft) for query optimization and index maintenance? I’ve had 50-70% success tuning queries/indexes with 2-3 LLMs by providing schema/index/stat context first. Considering AI automation: identify poor-performing queries, add env context + tuning rules, use MCP tooling to test in read-only dev env; 3 models propose optimizations, best performer wins and goes to DBA for review. Thinks this is worth pursuing now, since AI will get better over time too? Not worried it will replace my job, performance tuning is only about 5% of my work and changes, generally, only make it to prod if prod reports performance issues. Even when I took the worst offending query and reduced runtime by 12X (no exaggeration) using indexed temp tables and a CTE to do aggregates then join on the temp tables instead of repeated joins on the same tables over and over. The change was *I think* was “too good to be true” and “might have a logic flaw that we can’t properly test for in dev” and thus hasn’t made it to prod… sorry for the rant.
Lets say there is a table with Key column of number but it has trailing zeros (may be obe or two zero), i had that column as varchar, but business would never put zero when searching for that column. All out queries had like clause with ‘%%’, that killed the perofrmance. How would you approach the problem ?
I’ve never seen anyone use a plan guide, not even in a blog post or quick video. Any idea why? When do you use them?
We have a UDF as a default expression for a column on a table. Does SQL Server still evaluate the UDF in default expression even when I explicitly insert values into that column?
Hey Erik. It took me many months, but I’ve finished watching all of your videos and turned your lessons into a drinking game. What do you think? I’m playing it right now https://dbfiddle.uk/CQbdBNH5

Video Summary

In this video, I dive into a lively session of Office Hours where we tackle five community-submitted questions on various SQL Server topics. From exploring the use of AI in non-Microsoft environments for query optimization and index maintenance to discussing the pitfalls of storing numbers as strings with trailing zeros, we cover a wide range of scenarios. Additionally, I delve into the lesser-known topic of plan guides and address some humorous yet insightful queries about UDFs used as default expressions and even the personal experience of someone who has binge-watched all my videos—turning them into a drinking game! It’s been an engaging session, and I hope you found it both entertaining and informative.

Full Transcript

Erik Darling here with Darling Data. And today’s video is, of course, everyone’s favorite. My favorite, your favorite. Of course, that is everyone, isn’t it? Office Hours, where I answer five community submitted questions to my fancy little Google spreadsheet. And hopefully everyone is happy with the answers. If you look down in the video description, there are all sorts of helpful, useful links for you and me. You can hire me for consulting by my training, become a paid member of the channel. Of course, you can ask me Office Hours questions for free. They are not insert coin here. But we’ll see, we’ll see how this, see how this next year’s economy is. And if, of course, if you like this material, you, I would be ever so pleased, ever so chuffed. If you would like, subscribe and tell a friend or two or 10. I think I’ve suggested in the past, just taking people’s laptops and hitting subscribe in their YouTube accounts. Past data community summit coming up in Seattle, November 17th to 21st. Kendra Little and I doing two bang up days of T-SQL pre-cons, arguably the best two days of T-SQL pre-cons that have ever taken place in the world. I think I’ve mentioned before, I do have a couple other announcements coming up soon, hopefully sooner than later, so that you can see what you’re doing.

The fine folks of the world can start purchasing some tickets. Be nice there. Anyway, let’s go answer these gosh darn questions, shall we? Oh, we have a third. So if you watched last week’s episode of Office Hours, we might remember our friend with the high volume fintech OLTP something or other on SQL Server 2017 Enterprise Edition. Third question has arisen from our friend. Hopefully we can give our fintech friend enough free advice here. What are your thoughts on using AI non-Microsoft? Well, geez, I’m convinced. For query optimization and index maintenance.

We’re having AI rebuild our indexes? I don’t know. What is that? What are you on about? I’ve had 50% to 70% success tuning queries. Okay, well, I have 100% success. How’s that? With two to three LLMs by providing schema index that…

I’m just going to say something here. You work in fintech and you’re sending schema index and stat context to different LLMs. That’s interesting. Okay.

I hope they’re local. Considering AI automation. Identify poor performance queries. Add environment context and tuning rules.

Use MCP. Holy Moses. Okay. Performance tuning is only 5% of your work. Boy, oh boy.

Look, if it’s working for you, go ahead. I don’t know. Performance tuning is 5% of your work. If you need an LLM to tell you to use index temp tables and a CTE, you’re probably better off doing that.

All right. I need to move on before my head explodes. Okay. Let’s say there is a table. Yes, let’s say there’s a table. I would love to say there’s a table.

With a key column that’s a number, but it has trailing zeros. I don’t know why a number with trailing zeros is strange. Many numbers have trailing zeros.

Numbers with leading zeros are a different matter. I had that column as Varkar. Oh, great way to store numbers.

Varkar. Good job. But business would never put zero when searching for that column. All our queries had claws with double wildcard that killed performance. How would you approach that problem?

Well, I wouldn’t. That is just a fundamentally unsound design. You have allowed business users to trample over the database entirely, and they’ve won now. You can’t take that away from them.

I’m saying you because you asked the question. But whoever designed this screwed it up from the beginning. Even if you put a columnstore index on that, the double wildcard, assuming this is going to be a Varkar at least 10 or 11, right?

Because most people, when they store numbers as strings, at least account for the maximum width of whatever data type the number is supposed to start with. So if it’s an integer, then it would be like, what, 10, 11 or something, right? If it’s a big, the 9 quintillion number, it’s like 15 digits.

So, like, you know, it would be like a Varkar, like 10, 11 or 15 or whatever. So, Varkar 10, sorry, because it’s the same number of digits as a phone number. It’s a 214, three other digits, four other digits.

So, you’ve just ruined the whole thing from the get-go. You might be able to do some very complicated stuff with trigram searches, but I don’t know if you want to implement all that just to help these people out. But the smarter thing to do would have been to give them more restrained search protocols so that you did not end up here in the beginning or from the beginning.

There you go. Anyway. I’ve never seen anyone use a plan guide, not even in a blog post or quick video.

Any idea why? When do you use them? I’ve seen many people use plan guides in blog posts and videos. So I have no idea why you’re not able to find them.

Perhaps you do not search well, whoever you are. When do you use them? Well, you use them when you need to guide a query to a different query plan that it is not finding naturally. The name is somewhat self-explanatory in that regard.

I’m not going to pretend that they are very easy or ergonomic to use. There are many times when even yours truly has struggled to get the query to pick up on the plan guide. But with enough trial and error, you can usually get it working.

All right. Let’s see here. We have a UDF as a default expression for a column on a table. Does SQL Server still evaluate the UDF in the default expression even when I explicitly insert values into that column?

Oh, I see. It’s a default. Okay.

It shouldn’t know, but there’s no telling what strange things might happen when you start doing awful things like using UDFs as default expressions. I’d actually never even considered that level of terrible.

So good job you. You might make it into a future demo with that level of bad. Holy Moses. All right.

My Lord. Hey, Eric. Hey, Eric. It took me many months, but I finished watching all of your videos. You should go outside.

You should go enjoy life. There is a world out there. I don’t know why you’d want to watch all of them. Oh, well, that’s why. You’ve turned my lessons into a drinking game. Well, I changed my mind.

I wholeheartedly approve of you. What do you think? I’m playing it right now. Well, what do I think? I fully endorse drinking games. I think that they are one of the last vestiges of proper modern society and that you should continue to watch my videos and drink.

I’m not going to that link, but I don’t want to know because I have a feeling if I go there, I’m going to know how much you drank and I’m going to know how much drinking it took you to get through my videos and that might make me a little sad.

So I’m not going to go there, but I like that you have the kind of go-getter attitude that you create drinking games for me. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in next week’s office hours, but also in tomorrow’s video, whatever that may be.

I’m sure it’ll be a humdinger. Actually, we’re going to go through my Rogols presentation that I recently finished giving at the Pass on Tour events. So we’ll have that to look forward to, won’t we? Rogols.

Yay. Fun. It would be awesome as if I talked about Rogols and no one compared me to other people who have talked about Rogols.

That would be fantastic. It’d be nice if I could talk about something without someone just pointing out that, hey, someone else has talked about that. Wow. Cool. All right.

Rogol and away all this week. Thanks 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.