SQL Server Performance Office Hours Episode 55

Summary

In this video, I dive into some common questions and issues faced in SQL Server performance tuning, particularly focusing on scalar functions causing lengthy execution times and the challenges of using extended events for monitoring. I also share insights on parameter sniffing and the limitations of the parameter sensitive plan optimization feature. Additionally, I discuss my free open-source SQL Server Monitoring tool, emphasizing its value as a cost-effective alternative to commercial tools. The video wraps up with an overview of upcoming events like Data Saturday Chicago and SQL Day Poland, inviting attendees to connect and collaborate in person. Lastly, I touch on the ongoing development of AI within SQL Server and Azure, sharing some use case scenarios from proof-of-concept projects.

Chapters

*00:00:00* – Introduction and Free SQL Server Performance Monitoring Tool Overview
*00:05:29* – Optimized Locking in SQL Server 2025
*00:14:18* – Conclusion and Future Plans

Full Transcript

Erik Darling here with Darling Data, and it is, oh, quite a Monday, isn’t it? I suppose we’re all going to have to deal with this in our own special way. But anyway, that means it’s time for Office Out. That’s how I’m going to deal with it. Answer some questions, because people seem to keep having them, and I seem to keep having answers, so I don’t know, maybe let’s keep this gravy train rolling. He-he! Alright. Down in the video descriptions. Well, actually, there’s only one of them. I don’t know why I pluralized that, but there’s all sorts of helpful links. If you would like to hire me for consulting, purchase my training, become a supporting with money member of the channel, you can do that. You’ll also find links to continue to ask me office hours questions, so this gravy train will stay on the tracks. And as always, if you enjoy this content in any way, shape, or form, and you feel that it may enrich the lives of those around you, please do like, subscribe, tell a friend, all that good stuff, so that I can continue racking up these ground-breaking, record-setting view and subscriber counts.

I’m hot on the heels of that Amiga Repair channel. I swear, one of these days, so close. I’m going to overtake that thing. I also have, I mean, semi-recently, free SQL Server Monitoring. You ever want to have performance monitoring for your SQL Server, but, you know, all the other paid tools out there kind of suck, and, you know, aren’t worth the money, and, you know, I’ll probably be in jail.

Well, I got a free one. It is free. It is open source. There’s no email sign-up.

I’m not phoning home with any information from you. It’s just a bunch of T-SQL collectors running on a schedule, getting the most important performance metrics that your server has to offer. All sorts of stuff that you would want to see in a monitoring tool, nicely grouped together, colorful charts and graphs, help you tell a nice story about what’s going on.

Weight stats, blocking, deadlocks, top queries, all sorts of, you know, cool internally stuff that fancy-pants consultants like me have figured out how to collect data for over the years in a meaningful way. Nox-style dashboard, so you can see what’s up, what’s down, what’s hot, what’s not. And if you are, you know, in favor of our new robot overlords, there are also a number of built-in MCP tools so that you can ask questions of your performance data.

And just the collected performance data, nothing else. And you can have LLMs kind of tell you a little bit about what’s going on, where and when with your performance. You can just talk to your data naturally, which is, I think, kind of cool.

And I don’t think anyone else is really doing that. So, if you want to get that for free, it’s all right there. On my GitHub repo, code.erikdarling.com, or if you are bold and brave enough to remember that link or just click the link down in the video description, you can find all that stuff.

Oh, well, you know what? Data 2 Nashville already happened, which means Data Saturday Chicago is this weekend. And, well, that certainly snuck up on us, didn’t I?

I feel like it was just yesterday I was saying February is never going to end. So, now I got to make a March picture. I’m going to have a database.

Databases are going to play baseball this March, I think. But I also will have SQL Day Poland coming up May 11th and 13th. And Data Saturday Croatia coming up June 12th and 13th. So, you know, if you are in Chicago, Poland, or Croatia, stop by and see me.

I would love to see you. Maybe we’ll get along. Maybe we’ll be best friends. You never know. I could use more friends. But, yeah, this is February, February. It’s done.

It’s done. I got to fix this one. All right. I’ll put that on my to-do list. February, over. All right? No more of this. This was a terrible winter. I hated it.

All of it. Anyway, we have questions which have been asked. And so we have questions which will be answered here. Let’s see what the first one we got.

Recently encountered a query that used scalar functions, which was taking 50 minutes to complete. Not an unnatural state of things, from my experience.

After hours of aimless clicking. Well, don’t click aimlessly. Click with purpose and meaning. Why would you aimlessly click? I trace the issue back to an extended event.

Is there a specific metric which I could have used to indicate performance issues from extended events? So I think within the query that you were running itself, likely not. You may have noticed either a session reading from an extended event, if someone were actively monitoring it.

Or you may notice other extended event-related weights on the server go up around when you were executing this query. But in general, no. I haven’t found a great way to track this one down.

And I have seen it happen a couple of times. I think early on with SP human events, before I added the skip plans thing. Funny story behind that.

Someone wanted me to watch a load test. And I was like, well, you can use extended events for that and capture some stuff. And so we set it up. And they started running stuff.

And they were like, this is taking like four times as long. And I was like, yeah, that’s extended events. So I don’t know. I wish I had a good way to say, lesson learned.

Always check extended events first. But it’s just so rarely a thing. I’m glad you figured it out. That’s good for you.

But no, I don’t have a great way of, you know, predetermining if there is a harmful extended event running aside from, you know. Well, I mean, like most client environments I walk into, like they have like some script from 2003 that looks at weight stats that they run once in a while. You know, the fact that like there are store procedures that do all this stuff a little bit more surgically is big news.

So most extended events are not up and running that you, you know, at least ones that you wouldn’t expect to see. For you, apparently you have someone to watch out for. And that someone might be you.

No, you never said who created that extended event. You just said you traced it back to an extended event. So I don’t know. You or someone you know is the murderer. We don’t know who.

I see that PSPO, that is the parameter sensitive plan optimization, works for update in SQL Server 2025. Have you had any good results with it? I’ve not really had all that many great results with the parameter sensitive plan optimization generally.

No, Microsoft, you know, did not think through bucketing things well, I don’t believe. And my gripes with the parameter sensitive plan optimization remain in place even through SQL Server 2025. There are certainly times when it works.

I can be perfectly OK. But yeah, there’s a there’s a there’s a lot to be desired with that one. You know, like the the most common and least common top bottom buckets and then everything else in the middle bucket.

Kind of like this, this thing in the middle can can span a lot of different row counts and you can end up with sort of ineffective plan variants for that. So I don’t know. Another another could have been, but probably ain’t going to be.

Hey, I always tell you, we got fabric. Lucky people. All right. Your new monitoring tool is sick.

Call the doctor. However, I am also working on a small niche commercial SQL Server monitoring tool similar to the big hitters, centralized repo, but much cheaper as a passion project. So I don’t think it’s my tool that’s cooking you.

DBA dash is free and already does that. Like, you know, I don’t know what you are a bit vague for what what is small and niche about your monitoring tool. I don’t know what specifically you you what you have niched yourself in.

But I think, you know, you like depending on like what your niche is, who that market is and how underserved they are. It might be sort of tough to get like software as a service off the ground is like, you know, I mean, you said it’s a passion project. So maybe the money doesn’t matter, right?

Like maybe if you can just get it out to some people and they’re happy with it, that’s good enough for you. But since you’re asking people to pay for it, I assume that you expect to make money from it. And that that might be a tough proposition in this day and age.

Unless you are already a fairly large, well-established enterprise vendor, you may have a hard time getting people to pay for something. You know, like like I mean, like obviously the thing that I’m doing is totally free, right? Like download it, take it, use it.

But, you know, it’s an open source project. And I always say, you know, hey, if you like this, you can contribute to it. If you find this useful, you can contribute to it. It’s been a very, very small portion of anyone caring enough to contribute to it. Thank you very much to anyone who has.

You’re beautiful, wonderful people, but you are the vast minority on that. So I don’t know who you’re going after with this tool. I don’t know what your I don’t know what your hook is, but good luck. I think it’s going to be a tough time for that sort of thing.

Let’s see. Could you share a few concrete use case scenarios where you have implemented AI within SQL Server 2025 in a production environment? Is this an interview or any in energy or banking sector?

Awful specific. So not SQL Server 2025, but Azure SQL, where the vector stuff has been around a little bit longer. I have helped people with some proof of concept stuff to for them to figure out how they could fit SQL Server’s vector search stuff in to various things that they are interested in trying, whether it whether that is, you know, like sort of like a chat body thing or sort of a developer assistance thing, you know, sort of getting into, you know, like, finding related issues, things like that.

I’ve certainly helped people with that sort of stuff, but I don’t know at this point how far those projects made it along. You know, people were fairly happy with the proof of concept, but, you know, the vector stuff in SQL Server is kind of dead in the water until vector indexes become writable. I don’t know when that’s going to be.

I don’t know what Microsoft is busy with, aside from Fabric, because, you know, hey, we got that. Remember that one? Remember that joke? So, I mean, like, a lot of people, I just think, aren’t going to be able to use it well until Microsoft actually finishes the features around it.

Vector indexing being one of them, getting a lot of that stuff out of the preview program and into GA. You know, until that happens, I think a lot of people are just going to say, that’s nice. Okay.

Cool. I’ll be over here waiting. You take your time. So, you know, I wish that it were further along. I wish that it were a little bit more well-developed and a little bit more, I don’t know, production-ready in a lot of ways.

But here we are. Here we are. That is not where we are today. We are here today, but that is not where Vector Search and SQL Server 2025 or Azure is today.

We are standing by. Been impressed by optimized locking yet? No.

Seen it? Looks cool? Been impressed? Nah. It’s all right. Anyway, thank you for watching. Hope you enjoyed yourselves.

Hope you learned something. And I will see you in tomorrow’s video where I will undoubtedly talk more about my free SQL Server performance monitoring tool a little bit more. Because I would desperately love to spread the word of that thing further and hope that more people get out there, try it, you know, in a perfect world, more people would get involved with the project, perhaps even sponsor the project.

But, you know, reporting issues, contributing code or ideas, asking questions, all that stuff helps me move things forward in a way that makes things better for everyone. So, if you’re into those things, stop by code.erikdarling.com. All right.

See you tomorrow.

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.