SQL Server Performance Office Hours Episode 47

SQL Server Performance Office Hours Episode 47

---

I have a query that is timing out after 30 seconds and is mostly waiting on pagelatch_sh. It’s a clustered index seek for a small number of rows on a narrow table. The query doesn’t show up as part of a blocking chain. Any suggestions for next step in finding the problem? I don’t think it is last page insert contention because there are few inserts to this table

Did you ever use performance monitor for troubleshooing and, if so, what metrics did you use ?

I never want to use DBCC SHRINKFILE on a data file again. Do I lose anything by totally abandoning it?

Could you please send me the link of the SQL books

On a client call recently, you were pretty specific about avoiding usage of the OBJECT_ID() function. I noticed you used it in a demo recently, fine enough (it’s just a demo). Any other such functions we should avoid using in production?

To ask your questions, head over here.

Video Summary

In this video, I dive into answering five fascinating questions from my YouTube community, covering topics like performance troubleshooting and SQL Server best practices. I share insights on how to approach a query timing out due to page latch SH waits, discuss the limitations of using Performance Monitor for SQL Server diagnostics, and explain why avoiding certain functions like OBJECT_ID in production code can prevent concurrency issues. Additionally, I provide an update on my consulting and training offerings for 2026, including new pricing options and subscription models that cater to a broader range of needs. Whether you’re looking to improve your SQL skills or just want to support the channel, there’s something here for everyone. Stay tuned as we kick off January with more exciting content focused on AI in SQL Server!

Full Transcript

Erik Darling here with Darling Data. Back from my big five days off of pumping out YouTube content. And somewhere between rusted and rested. Somewhere in there. But ready to go. Ready to get back into the swing of things. And of course, by the time you see this, it’ll be Monday, which means it is time for office hours, which means it is time for me to answer five. Terribly interesting questions from you, my beloved watchers out there. So we’re gonna get into that. Before we do, down in the video description, there are all sorts of helpful links. If it is 2026 and you have a brand new budget and you are just looking for ways to spend it, I have rehauled and updated my consulting and training pages. The consulting page has some new offerings on it. Slightly lower priced entry points for people who maybe need some help, but don’t need giant buckets of hours. And I’ve also redone the training stuff. So if you want, you can, you can, now the everything bundle is everything, everything. It includes the normal everything performance bundle, learn T-SQL and performance engineering.

And there are new ways to purchase that as well. There are yearly and monthly subscriptions if you are more keen on that sort of arrangement for purchasing training. So all that is overhauled and brand new for 2026. If you would like to, if the content that you see here just moves you in such a way that you would like to give me four bucks a month, buy me half a New York coffee, you can still become a channel member. That has not been, that has not been overhauled yet. Who knows what 2026 will bring. And of course, that is also where you go to ask me office hours questions.

So if you have a burning SQL Server question that you want to see answered live here, well, semi live here, I’m alive, I promise. Here on YouTube, you can do that. And of course, if you, if you think that I would be a valuable presence in someone else’s life, please do like subscribe and tell a friend so that I continue to reach the people I am intended to reach. I will be leaving the house. Well, a couple months from now, apparently data to Nashville, March 6th and 7th tickets are flying out the door for that. So hurry up and get your seats while you can. I have not been informed of the winged status of the data data Saturday Chicago pre con, but I assume that it is that is also quite winged. That will be March 13th and 14th and newly added to this list.

And why I had to remove the little flying database image over here because it was getting in the way of things is a SQL day Poland in Rocklaw, which I hope I am saying sort of right. And that will be May 11th to 13th in Poland. So you’ve got that going for us.

And welcome to, of course, the first video of 2026. We are all hopefully happy and not hungover. I count six databases and 12 arms and 12 legs.

So we are in good shape for 2026. Everyone’s got all their appendages, eyeballs, other other necessities, apparently. Plenty of well, they’ve all got drinks, too.

Looks like this is a good party. So is there, it looks like there’s a little pickle person in this one or something. I’m not sure what that is.

I had not noticed that before. Hmm. I wonder, I wonder what’s going on there. Anyway. Can’t, can’t win them all. Let’s answer some questions here.

Do, do, do, do, do. Ba, ba, ba, ba. Let’s see. The first one. What do we have here? This is all the big one, huh? I have a query that is timing out after 30 seconds and is mostly waiting on page latch SH.

Okay. Let me stop you right there. What does mostly mean? Does mostly mean 29 seconds?

Does mostly mean 29 milliseconds? What is mostly? It’s a clustered index seat for a small number of rows on a narrow table.

The query doesn’t show up as part of a blocking chain. Well, I don’t know. I don’t know how you’re judging that, but okay. Any suggestions for next step in finding the problem?

I don’t think it’s… Why would it be… Is it a select query or an insert query? Why can’t… Oh, I beg you, please give me the information that I need.

Please. I beg. So, look, the next step in this is always one of two things. You either get the actual execution plan, or you monitor the server for this query, and you see what is happening in near or around when this query is executing.

Those are the only two next steps. Or rather, those are the two most logical next steps. Because unless you are able to figure out what’s going on here, either via executing the query and seeing where things are actually getting stuck in the actual execution plan, or if…

I mean, because that… Which could totally not do anything for you. You could totally not see what’s interesting there. You could hit F5, it could finish instantly, and you’re done. Then you need to monitor things. You could use extended events or profiler or something else to watch live action on that table and figure out what is happening when that query is timing out.

I find it highly unlikely that page latch sh is your problem there. So that’s what you can do. I cannot tell you why with the set of information you’ve given me.

Next up. Did you ever use performance monitor for troubleshooting? And if so, what metrics did you use?

So no, not really. I tried a lot because, you know, like when I first got into like SQL performance stuff, there were a lot of articles and, you know, things about using Perfmon and exporting it to Excel and blah, blah, blah.

I just… I could never… I could never like wrap my… I could never get it to like give me the answers that I needed. So I couldn’t really tell you which metrics to use.

I can tell you that there is a nice fellow in the community who hasn’t blogged in quite a while, but used to have a really neat sort of… Not a traditional like query and index tuning thing, but like performance monitoring and like, you know, a lot of like hardware stuff.

The blog was SQL Sasquatch. And it’s a guy named Lonnie N… A bunch of underscores.

And he talks about Perfmon quite a bit in his blog posts and would list out the counters that he would use for various investigations. Yeah, like I’ve used the like sys Perfmon counters view in SQL Server to like look at some stuff, but it’s…

I’ve never like… I’ve never found a ton of answers there. Like every once in a while there will be something interesting in there, but mostly it’s just like… Yeah, look, you’re doing…

Actively doing things. Good for you. Like… So no, it’s never really been my thing. And then, you know, like with Perfmon too, you know, a lot of SQL Server monitoring tools sort of came around and gave you like a better picture of what was happening on SQL Server than you would get from those insane charts and graphs that Performance Monitor would spit out with all the different sort of like value resolutions and lines and stuff.

Like one thing that always tripped me up with that was like different lines had like different… Like sort of like…

I forget what the exact word is, but like resolutions to them. So like one of them would be like 1.000 something and it would always be up at the top and the other ones would be like 0.0001 something and always be like down at the bottom.

And you’re like, how do I make… How do I make sense of what’s happening here? There’s all sorts of like complicated stuff that just always made it a little bit too hard to use for me. Okay.

Okay. I never want to use dbcc shrink file on a data file again. Okay. Do I lose anything by totally abandoning it? On a client call recently, you were pretty specific about avoiding usage of the object ID function.

I noticed you use it in a demo recently. Fine enough. It’s just a demo. Any other such functions we should avoid using in production? Well, so like when I tell people to not use those functions, the reason why is because they do…

A lot of them… I don’t have the full list of ones, but a lot of them do not obey the same locking semantics that you might find by just accessing various system views to get that…

To resolve certain object names and stuff instead. Aaron Bertrand has a post in his Breaking Bad Habits series over on sqlblog.org, where he talks about, you know, like queries that use these, that get blocked where hitting sys.object, sys.scheme, sys.table, stuff like that, don’t get blocked.

So the main reason why I tell people not to use that in sort of like production code is because if the… Like you might… Your queries that run those might get blocked in places that queries that just use the underlying system tables and views wouldn’t get blocked.

So they can contribute to sort of like concurrency issues, blocking chains. I don’t know about deadlocks necessarily, but they could certainly get blocked in places where you would not find blocking from hitting the underlying sys views that would allow you to resolve those things in different ways.

You know, of course, the object ID functions are… You know, they’re quite simple for… You know, the type of procedures that I typically write for troubleshooting stuff.

But even I’ve tried to get away from using those in a lot of places there. And I just, you know, try to use the underlying system views instead so that I don’t get blocked up trying to resolve various object names to see where they are.

I’m not perfect at it, of course. You know, there’s lots of old code that I would have to go back and revisit and rewrite queries to sort of change the way that they are figuring out if various objects exist or not.

But, you know, one thing at a time. There’s only so many hours in the day and usually that stuff gets fixed as I hit a problem with something.

And if I’m not hitting a problem with something like pretty regularly, then I’m probably not going to jump on changing the code because, you know, it’s a lot of effort for not much.

Anyway, could you please send me the link of the SQL books? So I don’t know exactly what you mean here.

There is, of course, erikdarling.com slash books, which has a list of SQL books that I recommend. I think you might mean that one.

And then I guess that has to be it. So sure, I’ll put that link down in the video description with the questions and stuff. It’ll be in there.

So anyway, that was an interesting start to office hours of 2026. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video. We’re going to spend January… I’m going to be doing little snippets of my new course, Get AI Ready with Erik, all about, you know, handling vector stuff with SQL Server 2025 and Azure and all where Microsoft has sprinkled AI into the database.

And by sprinkling in AI, I mostly mean there’s a vector data type and there’s a whole lot of stuff that you have to think about when you start using it. And hopefully, I’ve thought about most of it for you and I can just tell you what to do.

So that’s going to be what we’re going to focus on for the majority of this month. But I also have some bit obscene podcasts lined up with Joe and Sean and maybe some special guest appearances.

So it’ll be a busy January because we got to stay busy. And I will see you over in tomorrow’s video where we will talk about some vector stuff.

All right. Thank you for watching. Goodbye.

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.