SQL Server Performance Office Hours Episode 1

SQL Server Performance Office Hours Episode 1

---

Do you like me? Y/n

Does Erik prefer nested loops or fruit loops?

If Erik was an operator in an execution plan plus, what operator would he be?

Sometimes my sql server quits responding so I reboot it but then it takes forever to come back online. Is there an easier way to corrupt my data? PS I’m running on prem with azure disks so latency is a little high but usually performance is good. Also hi Erik, this was a test to see how many words I could fit into a form.

Can you fix my server performance for free? Here’s my execution plan <?\stmtsimple(@2$?@38,!@/&!;,$-“2(,&~¥’c/&1@,’]+_•

Can I edit my response?

Are you free later

What is the best Champaign to buy someone when you find out they don’t have to use Microsoft Fabric?

Hi Erik. Im having huge problems with a table that stores text files in a varchar(max) column. Can you suggest an alternative to that? Thanks

Where’s the beef?

What do you wish that more DBAs have read? Specific books, blogs, etc.

To ask your questions, head over here.

Video Summary

In this video, I dive into some interesting and varied questions submitted by viewers for our inaugural Office Hours episode. We tackle topics ranging from the nature of serial nested loops in execution plans to more complex issues like performance tuning and data storage strategies. Whether you’re curious about SQL Server’s behavior or looking for practical advice on improving your database management skills, there’s something here for everyone. I also share details on upcoming events like SQL Saturday New York City 2025 and offer insights into how you can support the channel through membership or other means. Join us as we explore these questions together and continue to grow our community of data enthusiasts!

Full Transcript

Erik Darling here with Darling Data. And we’re going to try something a little bit different today. I believe that I have fielded enough high-quality questions to do our first Office Hours episode. So we’re going to try this out. We’re going to see how it goes. So I hope you’re all prepared. It’s been a little while since I’ve done one of these. But here we go. If you like this channel’s content, if you like watching Intel, time out looking for drivers, which is always a good time, you can become a member of the channel. You can join 50 some odd other people who have said, Erik Darling, thanks for all you do. Here’s four bucks a month. And I appreciate every last one of you, as individually and as a group. If you don’t have the four bucks a month, I mean, you know, Christmas did just pass and a lot of people are still struggling with credit card debt. There are all sorts of free things you can do that help me grow this channel, like liking and commenting and subscribing. If you want your question to appear on a future episode of Office Hours, you can go to that link right there, which is also in the video description. It’s amazing how this works. And you can you can put your high quality question about SQL Server performance in the question bar and I will answer it right here. If you need help with your SQL Server, if maybe the answer you got was not quite enough to get you over the proverbial finish line, I am available to do all of these things with SQL Server health checks, performance analysis, hands on query and index tuning, dealing with your SQL Server performance emergencies and training your developers so that you can avoid SQL Server performance emergencies in the future. If you would like to get people trained, if you would like to get yourself trained, you can get all of my performance tuning content for about 150 USD. Again, this whole fully assembled link for you is available in the video description. For upcoming events, SQL Saturday New York City 2025 is still taking place on March 10th, May 10th. Good Lord, I can’t even read today. It’s gonna be interesting reading questions, isn’t it? May 10th at the Times Square building in May 10th.

So you should go to Manhattan. So you should go there and come learn some more stuff about about about data. It’s not just SQL Server, it’s data in general. So with that out of the way, let’s, let’s, let’s go answer some of these questions. Now, these are the questions that I have fielded so far from you, the public. And we’re gonna we’re gonna work our way through these. So the first question we have here is do you like me? Yes or no? Well, I mean, the obvious answer is no, I don’t like you. I love you. Why? Why would why would we stop it there? Why would we stop this? Why would we be such a low ambition? Why would we stop it there? Why would we stop it there? Why would we stop it there? So why would we stop it? Why would we stop it? And I would try to get it, and I would like to answer it.

serial nested loop on its own. So as long as the loops are parallel, I’m happy with either one of them. They are equal in my eyes. If Eric was an operator in an execution plan, what operator would he be? Well, I think that’s obvious. It would be delete. Let’s see here. Sometimes my SQL Server quits responding, so I reboot it, but then it takes forever to come back online. Well, accelerated database recovery would be a pretty good option for you, assuming that you’re on SQL Server 2019 or up.

Most of the reboot time of SQL Server comes from all that pesky transaction log stuff, and accelerated database recovery helps you avoid all that. Then there’s another question here at the end. Well, I mean, I guess that first one was more of a statement. Is there an easier way to corrupt my data?

Well, you know, you can always use no lock hints. You can always use dbcc write page. There’s many easy ways to corrupt data. All depends on how far you’re willing to go. Here we have a really good question. Can you fix my SQL Server performance for free? Here’s my execution plan. Well, believe your execution plan has become corrupted. I’m not sure what is going on there. It looks like it started off strong with a parameter called two. So this looks, this looks to me like perhaps an issue with simple parameterization, or perhaps this is an ORM generated query, because this is, this is not the type of parameter that I would expect a human being to name right there. And then we have, oh dear, we go up to parameter 38. That’s a, that’s a big one. 38 parameters. Perhaps you’re dealing with long in clauses. I don’t know. Something is amok there. Yeah. All right. Let’s, let’s, let’s move on a little bit. Let’s see. Uh, can I edit my response? I guess, I guess we never, I guess we never got around to testing that. Uh, yeah, the, the, the actual answer is yes, you can edit your response. If you, if you type in your question and then you’re like, wait, did I make a typo? You can edit your response. So that is a big resounding yes there. Are you free later? Well, this is not the type of question that someone would ask if they were confident in their knot work. I would suggest if you’re wondering if I’m going to be free later, you should learn how to tie better knots or buy stronger rope or do something else that would help you be more confident in whatever scenario you’re envisioning. What the, someone who really maybe perhaps should have edited their response. What is the best champagne to buy someone when you find out they don’t have to use Microsoft fabric? Well, assuming you meant the champagne that you drink and not the, the champagne that is, uh, I think, I think that’s how the lake is spelled. I was never quite that good at geography. Uh, but I assume that if you mean the, the, the, the drinkable potable sparkling wine, um, I, I, I enjoy Krug quite a bit. Uh, I enjoy Ruinart quite a bit. Uh, they make a very good Blanc de Blanc. Uh, so either one of those would be sufficient, but I just, I just want to point out that no, no one has to use Microsoft fabric. In fact, I would, I would do my best to dissuade anyone from using Microsoft’s beta version of Databricks. It’s, it’s, it’s a real joke. Um, it’s, it’s probably got about 16 months left until it’s something else. So, you know, don’t get too attached. Whatever, whatever bottle of champagne you buy will probably last longer and have a longer shelf life than Microsoft fabric. All right. So, uh, hi Eric. Hello. Hi. Uh, I’m having huge problems with a table that stores text files in a Varkar max column. Can you suggest an alternative to that? Well, um, you know, it’s, it’s, it’s a fairly well, well trod path, uh, that you should not be storing, uh, uh, blob files or blob data in your, in your database. It’s going to end up, it’s going to end up being rather painful for you. Uh, the smart thing to do there is of course to, to store a pointer to the file system in a reasonably sized, it could be a Varkar 500 some odd thing. Uh, usually that’s usually about enough to store a file path depending on how, how deep your directories go. Uh, and you could just store a pointer to that file on disk and, and just access that file on disk rather than try to store that in your database. Uh, file stream is a real lousy feature. I wouldn’t, I wouldn’t, I wouldn’t expect anything out of that if that’s what you’re using. Uh, if you’re just storing the, the, the data is, uh, well, I mean, you’re, you says you’re storing it as a Varkar max and not a, not a binary. So it’s not even anything that you could convert. If you’re just storing the, the text of the text files in there, well, God help you. Um, uh, I think if, if, if you’re, if, if you’re storing the text of the text files in the column, which is a different scenario, uh, what you would probably want to do is, uh, put that into a different table and maintain the primary key from both tables and just do lookups to, uh, the, the, the max column in this sort of lookup table for that. Um, that, that’s, that’s usually the best solution there. Uh, there’s also an SP table option option to store, uh, all blob data, uh, off row, uh, which can sometimes help with some things. I’ve messed around with that a bit in the stack overflow database and the posts table with the, the, the, um, the body column, cause that can be very long. That’s also an, that’s an Embarkar max field. Uh, and it, it certainly has some interesting stuff that it does, uh, to performance there. But, uh, yeah, uh, in general, um, depending on what your exact scenario was, is not a little bit light on detail here, but if you’re storing like blob data, like, like, like a file is a file in there, like file stream or something, uh, I would rather store a pointer to the file on the, on the, on disk somewhere. Uh, if you’re storing the contents of text files in a column, uh, I would prefer to separate that blob data out to a lookup table with maintain the primary key between the two. And then, uh, just make sure that whenever you, only if you ever need that stuff, uh, to, to, to join to that table, if you don’t, so that way it just gets in the way of less things.

All right, moving on here. Uh, where’s the beef? Hopefully in, hopefully in the fridge. Uh, or if it’s not in the fridge, then hopefully it’s, it’s in the process of being cooked. Uh, I, I, I would recommend a, a cast iron skillet for that. If you’re cooking beef, it’s probably the best, the night, the best way for you to treat your meat. It’s with a cast iron skillet. It’s well seasoned. Uh, if you, if you need, if you need fuller cooking instructions, I’m happy to provide them for you. All right. And our final question today, uh, what do you wish that more DBAs have read?

Specific books or blogs, et cetera. Well, that’s an interesting question, uh, because I don’t think that the problem is that, uh, there’s a lack of things that DBAs have read. I think there’s a lack of things that DBAs have comprehended. Uh, the problem isn’t often with like putting your eyes on words.

The problem is often actually comprehending what those words mean and how those words translate to action in the database. Um, I, I suppose it would be helpful if DBAs read some books on economics, they could understand that really nothing is free. That’s an important concept to learn in databases.

Everything has a trade-off. Um, I suppose, I suppose Milton Friedman would be useful in that regard. I don’t know. He seemed, he seemed, he seemed keen on that concept. Um, but as far as like specific SQL Server stuff, I mean, selfishly, of course, my blog over at erikdarling.com, uh, you know, from a performance point of view, uh, I think that, um, you know, if you’re, if you want to start like way back foundational material, uh, Craig Friedman, uh, F-R-E-E-D-M-A-N, like Friedman, Craig spelled the normal Craig way, uh, wrote a lot of stuff that is still very applicable about how a SQL Server works today. Uh, of course, Paul White, um, still, still writes to this day. Craig hasn’t written in quite a while, but Paul writes quite a bit. Um, uh, Paul, Paul is one where you, you do need to engage reading comprehension fully, though. That’s, that’s, that’s good there. Um, and is, I think another thing that’s very important to read is actually the documentation. Um, you know, the, the, the, the doc, the documentation for SQL Server is certainly flawed and it is certainly incomplete in some ways, but, uh, I do think that, um, many of the docs articles are at least reasonable attempts to, uh, teach you exactly how, well, teach you how something was designed, perhaps not how it works, uh, perhaps not what it was intended to work with, but at least to tell you how it was designed and, you know, give you all the, you know, the syntax for things. And there’s a, there’s a reasonable amount of stuff in there. Uh, as far as books goes, um, I would actually, uh, go back to, um, Craig Friedman there. Um, uh, so Kaylin Delaney, uh, used to write a lot of books about SQL Server. And back when she was writing these books about SQL Server, uh, a lot of people from Microsoft would contribute. Craig Friedman being one of them. Connor Cunningham was, uh, gotten involved with one or two of them, but there were a lot of smart people who were involved. I think Kevin Farley was in, uh, at least one of them. There’s like a whole list of like smart people who would contribute to her books on SQL Server. Uh, and you know, people who worked for Microsoft and had certain insights, uh, into how the engine worked that would be beyond what a normal person could reasonably surmise just from using it. So a lot of those books are still very valuable for foundational material. Granted, a lot of the specifics on Slava Ox was in on one of them.

Uh, but there was a lot of, uh, there’s a lot of good foundational materials there that sort of gives you a good idea about how SQL Server works as a database, how it functions, um, how a lot of, you know, the, like, you know, the, uh, like monitoring, uh, query plans, query execution, storage engine stuff. Granted some of the, like the details of that has changed over the years because of something, a lot of stuff about SQL Server has changed over the years, but it’s a very good way to get, um, a good sort of just deep dive into how databases work. Um, if we want to go beyond books and blogs and you just want like good general database knowledge, um, I think I mentioned this recently in another video, but, um, the Carnegie Mellon University database group, really smart guy named Andy Pablo pub, like puts all his lecture, his class lectures and notes and slides and like class projects. Like they’re all available on YouTube for free. You can sit there, you can watch them. It’s about an hour of class. Um, he has, you know, like, you know, he has spring fall semesters, there’s advanced, there’s intro to databases. There’s one going on right now about query optimization.

And if you just kind of want to learn a little bit about like, uh, like, like real date, like database internal stuff, not necessarily specific to SQL Server, but good database internal stuff, that’s, that’s a, that’s a very, very good resource. I can’t think of anyone who, uh, who has material, the caliber that Andy does, um, with like the, like the up to dateness of it. Cause it’s all, it’s all like current, he’s like still teaching. So there’s a lot of great stuff there. So, uh, that’s, I mean, I realize that’s somewhat of a roundabout answer. Um, and many of the things in there, you probably would have, uh, come across on your own already. Um, I think another good resource, uh, would be the database administer, administrators stack exchange site.

Um, a lot of questions that people have about SQL Server or databases in general have already been answered there. Um, and you know, like it’s not always, uh, always the easiest to find the exact thing, like granted, like site search is not, not a hundred percent there, but if you type in like some basic, uh, keywords and you, or, you know, you, you’re looking like for like kind of like a, like a subject or, you know, there’s like just something like some specific thing you’re after, you can probably find it there.

So always check the, like, you know, like the database administrator stack exchange site has a lot of great resources on there as far as Q and A goes, you know, where, you know, and it’s not just like, there’s like crappy fly by night answers either. There’s a lot of good detailed responses in there.

So that should, that there’s another good place to look when you are, um, when you’re trying to figure out, uh, or figure out a database problem, or you have a question about, uh, how to do something in a database or how something in a database works. It’s another very good resource. Anyway, uh, that brings us about to the end of this, this here Q and A. So, uh, I’m gonna, I’m gonna get going, uh, wait for some more things to filter in here. Again, if you would, if you would like to add anything to the Q and A, if you would like to be part of the Q and A, if you would like me to answer your question, uh, you can go to this link. The, the, the, the, the, the link is fully fleshed out down in the video description.

So you can click on that and you can, you can, you can submit your question and I will, I will put it up on the screen and answer it. That’s, it’s about how it works. So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video and, uh, hopefully I will be reading your questions on a future office hours.

There we go. All right. 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.