Live SQL Server Q&A! | Darling Data

ICYMI

Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.

Video Summary

In this video, I delve into some fundamental SQL Server and database design principles. Starting off, I discuss the importance of avoiding wide tables—tables with more than 100 columns—and how they can lead to indexing challenges and maintenance headaches. I also share a few patterns to look out for in your schema that might indicate poorly designed tables, such as prefixing column names or using numeric suffixes for similar data. Additionally, I explore the concept of Michael J. Swartz’s 10% rule, emphasizing that while SQL Server supports up to 1,024 columns per table, it’s wise to keep your tables narrow and well-structured.

Moving on, I cover memory grants in queries, explaining how they are necessary for operations like sorting and hashing but not always required. I provide practical examples of scenarios where a query can process terabytes of data without needing any memory grant, highlighting the importance of understanding these nuances for optimizing performance. Throughout the video, I offer actionable advice on improving your database design, referencing Lewis Davidson’s book “Relational Database Design for SQL Server” as an excellent resource to guide developers in creating more efficient and maintainable databases.

Full Transcript

So, let’s see. Science and technology is the, I don’t know, channel, stream that this thing takes place in. I don’t know if that’s really accurate. I don’t know if I’d call it science or technology. Maybe, maybe neither. It’s not terribly scientific. And aside from the fact that I use a computer to broadcast, I have absolutely no technology in here. I did get a new phone. Got a Pixel 3 XL. Pretty psyched about that. It’s a picture of my kid picking her nose on there. She’s a good time. So, just to kill a little time until questions come in, I see people in here, which is very exciting. Thank you for joining me. I’ve been writing some blog posts about my first six months of consulting independently. And, I don’t know, it was a real tear-jerker. There were some emotional moments in there. I’m kidding.

I replaced feelings with whiskey many decades ago. But, anyway, I’ve been, shut up, motorcycle. Back to your biker gang. I’ve been getting allergy shots for, I want to say, like, three years. At least three years. I’m on, like, the monthly maintenance shots now where, like, I just, like, they don’t even make me safe for a half hour anymore. I get my shot and I hang out for, like, 10, 15 and just say goodbye. Whiskey. Whiskey. Tisigr asks, whiskey or bourbon? I would never drink bourbon. Bourbon is just hyped up maple syrup. I would never drink bourbon. I’m a scotch guy.

And, like, very specifically, I like Iowa scotches. I like the stuff that tastes like burning band-aids. That’s my jam. But, yeah, we’ve been getting allergy shots for three years now, monthly maintenance. And the last, I don’t know what changed in the, I don’t know what changed in the world. I don’t know what new life form showed up on planet Earth. But it’s, like, I’ve never had a shot in my life. For, like, a long time, they were great.

I, like, I went from having, like, severe, like, eyes running, nose constantly bubbling, gross stuff, allergies, like, unable to function allergies, to, like, I would take maybe, like, antihistamine, like, once a week or so or, like, once every couple weeks. It was great. But, man, the last week has been absolutely positively brutal. Like, I’ve been taking stuff every day. Like, every morning, I wake up at, like, 3, 4 in the morning with, like, my face in awful condition.

I don’t know what the hell is different this year. But, man, it is bad. Bad. Ugh. It’s terrible.

Anyway, I don’t know. I don’t know. Someone ask a question. There are at least six of you here.

I don’t know how many more are going to come in. There are some of you. Someone has to have a SQL Server question. You can’t just come here to hear me complain about things or blab on and on about blog posts and whatnot.

Someone has to have a question about SQL Server. Please, God, someone have a question. Come on, there’s, like, 10 of you. Laura says, do you have any thoughts on replacing temp tables with in-memory OLTP?

Yeah, don’t. Hecaton is like herpes. It doesn’t have the nerve to kill you.

It just hangs around being awful for the rest of your life and flaring up at inappropriate times. So, I just have not found anything compelling about Hecaton. It seems like every time…

And this is not just me. This is some very smart people that I’m friends with. Every time they think that they have a specific problem with latches that Hecaton might solve, it is an absolute dead end.

Absolute dead end. In SQL Server 2019, TempDB is going to use… Well, you can use TempDB as, like, an Hecaton-y thing anyway.

I would pry this whole lot to see if that happens. But, whereas, I guess the question is, why? Why?

What are you trying to fix? What is a problem that we’re trying to solve here? What do you think Hecaton is going to make better in your stored procedure that you want to use in memory for?

That’s the big question. What’s going to get better about it? What’s going to get better? I don’t know.

Do, do. Do, do. Do, do. Try to have better indexing of… What on earth? You can index TempTables now.

What’s missing from your current TempTable indexing that Hecaton is going to provide a safe and secure solution for? That’s what I’m curious about.

Too slow. Well, so here’s the thing. What’s too slow? Creating the index or populating the Temp…

or populating the table? Josh says Hecaton indexes are extra confusing. Yeah, they are. So who knows how many hash buckets you should set up? Creating the index.

So… Creating the index is too slow. Okay. Fair enough. See, this is one of those things where it’s like, we’re going to end up going down a real rabbit hole.

Because I’m going to ask you what kind of index. And what kind of data types you’re indexing. And how many rows it is. And a lot of other stuff.

And this is… I hope you’re prepared. Because this can go on for a long, long time. I know.

Then you’d have to actually look at code. Bad news is you’d actually have to look at code to implement Hecaton. and get it set up and running there. If you don’t…

If creating an index is slow now on a Temp table, I don’t think it’s going to be any faster in Hecaton. It’s not free.

Nothing is free. Nothing is free. Yeah, no. Well, you know, my…

So Hecaton is very specifically designed to deal with locking and lashing issues. The in-memory portion, I think, is…

The misgiving around it is that it’ll make any workload faster. And that’s not really true. And the use cases where I’ve seen Hecaton be successful is with really large-scale, fast data ingestion into tables where data is not going to live for very long.

So the example that I always give, because it’s the example that I’ve seen work best, was with online gambling, where data had to come in very fast. And we cared about that data for a very short amount of time, where we wanted very fast updates and being able to get to that data to be snappy and not get blocked up and locked.

And then after like an hour or so, or whenever the betting thing is over, they get pushed out to regular on this table. So that’s the only time.

It’s the only time I’ve ever seen Hecaton be successful. For every other weird niche thing that someone’s like, oh, I bet if we just did this in memory, it’d be faster.

It has never worked out. Never worked out. I don’t know. It’s like, people hear about these features and they, I don’t know, the pamphlets that Microsoft comes up with for these things are amazing because they make it seem like they’re going to fix every single problem that you’re having.

They just sound like this, like golden acres retirement home for your data. And geez, never, never seems to go.

Yeah. So like with the Lars, I guess to put it, put it as succinctly as possible. you can try it, but be very careful with the database that you try it in because once you have created an in-memory file group in a database, one cannot drop an in-memory file group.

One must drop and recreate the database. So if you’re going to try it somewhere, create a new database and enable things there. Don’t do it with a database you care about.

So that’s about all I got. Let’s see. TZH X says, do you have a rule of thumb for how wide a table is too wide? Currently have a bunch of core tables in the system with a hundred plus columns each.

Yeah. You’re about there. So I think one way to, one way to phrase this really well is the maximum number of columns you can have in a table is a thousand and 24.

And a really smart friend of mine, Canadian fella, says it has, has sort of a, what do you call is it? Michael’s Michael J. Swartz 10% rule with, if you’re using 10% of a maximum of a limit, right?

Cause these are limits. They are not goals. They’re not things that you are trying to attain. They are things that they are, they are like, like the, the capacity at which SQL Server will stop functioning. So you’re, you’re right about there.

And the thing that really sucks about a hundred column tables is they’re really, really difficult to index. Well, unless you don’t care about read speeds from them, unless you’re just dumping data in there and not really doing anything with it, then it becomes really hard to index that.

Cause people are, people are going to you know, they’re going to want to search on, on weird sets of columns. And they’re going to want to select weird sets of columns and potentially order by weird sets of columns.

And all that adds up to is a lot of a heartache and pain and trying to index those things. So when I start seeing tables like that, that there are three patterns that I look out for very specifically.

one is column names that have prefixes. So like customer name, customer number, customer address, customer, things like that. Cause those should all be in a table called customer. The other thing that I look out for is, uh, columns that end in numbers.

So like phone one, phone two, phone three, email one, email two, email three. I’m also, I also pay attention to those. Cause those should probably be in their own sort of like entity, attribute value type table, like a, like a long narrow table where, you know, you link up, you know, like, you know, different, different, uh, entities to different attributes and different values than EAV table.

It’s amazing how that works out. The third thing that I look out for when I see very wide tables is, uh, clusters of missing index requests.

So like, like a lot of the times when I’ve seen crazy tables like that, um, what’s, what’s jumped out to me is that, um, missing index requests will be very specific around, which columns are in them.

So the, so like you’ll have a very similar set of search columns and a very similar set of select columns. And oftentimes you can find ways to break, to normalize that table, to break that table apart into separate tables based on what people are searching on within that table.

So you might find like, you know, setting, let’s say, like say you find 12 missing indexes and out of those 12, like four of them are selecting columns one, two, and three.

And the where it causes on columns four or five and six. And then, and the, another one, there’s like four requests and now there’s some, you know, sort of tanglement between columns eight, nine and 10 and 11, 12 and 13.

So like, you can usually find patterns within the missing index requests and within the column names, they can give you some pretty good direction and breaking. I keep saying breaking. I mean, normalizing.

It don’t mean you’re breaking the table. You’re not hurting the table. You’re not breaking your SQL Server. You’re normalizing your data, which is a wonderful thing to do. If you want more advice, if you want a lot of really good advice on doing that, Lewis Davidson has a book called relational SQL Server database design or something like that.

Let me go, let me go over to Amazon and grab a link for it. Cause it’s, I’m probably, I’m probably getting the title or something wrong.

I wish Davidson equal server design. It’s a book that I have in my bookshelf too. It’s not like I just tell people to buy these things and then screw off and let you do all the hard work.

It’s a book that I actually own and I’ve actually read. So let me stick that link in there. There you go. Wonderful.

We have a link. That’s beautiful. That’s a good thing. And let me actually, while I’m doing that, Michael, I should spell Michael, J sport 10% rule. There we go.

Swartz 10% rule. So I would send this stuff over to your developer. I would, I would get a copy of that book for your developers. And I would, I mean, unless you’re the developer, in which case I’m sorry for making money, but you know, could sell, send them, send them, show them that book, show them that link.

Say maybe, Hey, pal, we need to fix this. We need to do better. We need to do better and be better at our SQL Server, relational database design and implementation.

It’s, it’s crazy. So like, I’m like around the five year mark of consulting. I mean, obviously not independent, like, like just sort of generally like general consulting.

And I’m going to say something, uh, that is, is very, okay. I think it’s going to be annoying to a lot of people.

And as your problems are not special, your problems are very fundamental. the problems that your database is having is because you did something weird and wrong. You embraced wide tables.

You did not embrace clustered indexes. You embrace scale, our functions. You, uh, you know, did not pick a decent indexing strategy. There are so many just basic, like you, you chose data types really poorly.

Everything is a, is a long string for some reason. Everything is, uh, mistyped across tables. This, it’s very often, just very fundamental, easy fix things that like you, like should have been done from the get go, but just weren’t.

And, and it screws everything up. And it, it breaks my heart to come in and see like the same problems over and over again, where it’s just like you, like if someone had just made a couple better decisions at the outset, you could have avoided years of problems.

Years of problems. Let’s see. Kapil asks, can a query have a long running query on terabytes of database? Zero granted query memory.

I see some on large queries and don’t get why. Yes. Uh, so query memory grants. So like every query gets some memory. Because every operator.

Requires some memory to like figure out what state it’s in and what it’s doing and what it’s up to. So that every query gets some memory memory grants are very specific to a couple operations. One of them is sorts, right?

So if you sort data, you’re going to require a memory grant to do that. Uh, if you hash data, whether it’s a hash match aggregate or a hash join, you’re going to require memory to do that. And if your query requires, or if your query goes parallel, you’ll require a little extra memory to manage the exchange, the, the parallel exchanges, the buffers and the parallel exchanges.

So there are, there are three things that base, uh, in a nutshell require memory. There are some less, uh, frequent things like optimized nested loops join, which also will ask for a memory grant. And there’s, using like doing like inserts, the columnstore will also ask for a memory grant, but, uh, yeah, it’s, it’s entirely possible to have a query run across terabytes of data and not have to sort or hash anything.

It’s entirely possible for that to happen. And for a query, not to ask for a memory grant to do any of that work.

So fun stuff, huh? Like, like it’s, it might not be a great query. It might be a very slow, terrible query because at that point, I’m picturing a query with like, like lots of like little nested loops joins and, or maybe like in like index supported merge joins where, uh, memory just isn’t required to do any of that stuff.

So yeah, it’s totally possible. Uh, I find it a little suspicious that, that it’s happening on terabytes of data. Cause usually when you get terabytes of data involved, SQL Server is, uh, is pretty keen on doing some hash joins there, but who knows, who knows if you have a query plan that you can share and that you have a question about, uh, that would be a good question for DBA dot stack exchange.

Dot com. That’d be a wonderful place to ask a longer version of that, where perhaps you could provide some more detail and people could give you much more detailed answers. But I think in a nutshell, that should, that should get you going in the right direction.

Let’s see here. TZH asks, I’ve got a handful of nonclustered indexes on the clustered index key with separate columns included to cover some repeat queries. It just seemed dirty.

Yeah. I mean, you’re between a rock and a hard place, right? It’s either you, you, you got these wide tables. And if you don’t index them, uh, people are going to complain and it’s hard to index them. And if you over index them, people are going to complain.

And, uh, it all sucks. It all sucks. Uh, James says, how do I sign up to get the blogs you write sent to my email?

I added both my personal and work email to the newsletter, but never seemed to get any emails. I have checked junk mail and filters. Um, um, I don’t know what would, what would be going wrong there.

I, uh, that email list seems to work because, um, uh, what do you call it? I get, like, I see the email comes to me and I get bounce backs from everyone.

Who’s like, I get everyone’s out of office reply. Cause I’m an idiot and I don’t know how to change that in MailChimp. And when I think about Googling it, I find something better to do.

So I, but it’s, it’s kind of nice to see it’s working. It’s like, it’s like my way of knowing that I’m not just like alone in my office for eternity. Uh, but yeah, uh, I’m not sure if you shoot, if you send me an email, like if you use the contact form on my site to send me an email, um, I can, I can, I can look at your email address and look in MailChimp and kind of see what’s happening there.

It’s entirely possible that like, for some reason, I don’t know, maybe you spelled your email address wrong or, uh, or, or maybe you’re like, whatever your, your email host is just, just hates stuff from MailChimp so badly that it doesn’t even make it to spam, but just gets like immediately quarantined and junked.

It’s also entirely possible that I’ve been blacklisted again, because I don’t know. It’s happened before. I got an SSL certificate and I thought things were cool and people are still like, yeah, we don’t trust you.

I’m like, I got a certificate. I paid GoDaddy like 125 bucks for this damn certificate. How can I be untrustworthy? How, how is, how tell me how that works? Very annoying.

I don’t know. Anyway, Forrest, my furry friend. Oh, by the way, I meant to say if, if you sent me your address last week to get stickers mailed out to you, stickers were put in the mail, uh, this week.

So you should be getting them eventually. I don’t know when. Be honest with you. I wish I could, I wish I could predict and control the mail. Unfortunately, that is where my powers run out.

Anyway, Forrest says, or asks, do you ever find that knowing about memory management internals is helpful? It’s helpful when I create very specific problems that deal with memory management.

I would say that for the general public who have a pretty well-defined set of SQL Server issues, memory management is almost never, um, the issue.

The issue is that SQL Server is managing like 12 gigs of memory when it needs like 128 gigs of memory. Uh, so, you know, it’s, it’s good stuff to know.

It’s good. Like, like SQL jeopardy stuff to know. There’s a lot of good SQL jeopardy stuff to know, but you got to keep stuff, that stuff like back here, the stuff you got to keep up here is way different. Um, so yeah, it’s, it’s good to know.

It’s nice to know about, but, uh, whether it’s ever, whether I’ve ever like walked into a customer site and been like, uh, ha, I see you’ve got stolen pages. Let’s solve that.

Let’s crack that caper. And it’s been like some weird problem. It’s never been a weird problem. It’s always been like, well, you, you, Oh, you put like 500 gigs of, of memory inside SQL Server, but you forgot to raise max server memory from 64 gigs.

When you made the change or like, you’ve got a SQL Server, you are like, you’ve got a VM host with like eight SQL servers on it. And they’re all just fighting over memory constantly.

Or like, just like, like, like stuff has never been like, like, Oh, SQL services bad managing memory. It’s always been like, Oh no, there’s a people problem.

We have a big people problem here. So there’s that. Kapil says, perfect. I got it. Yes, indeed. It’s one heck of a crappy query. Too many inner joins with no sorting in a serial plan.

Woo. We, so wow. Serial plan. So we have a serial plan with no sorting over terabytes. Is there a scalar valued function involved here or a table variable involved here? Because there, there is something amok when we have a query that looks like that over that much data.

And, and there’s like no memory grant or sorting or anything that something stinks about that. Something is stinky in there.

Something is very, very stinky in there. Kapil falls. Are you planning to release some training videos? I learned a lot from, you’re available at Brent’s query tuning classes.

Yes, I am. So I’m in, I’m in a weird, weird position. And I wrote about my weird position a little bit in, in my, in my six months of consulting on my own blog posts, which will be out.

I don’t know, but I think on the anniversary of, on the anniversary of me getting laid off. So it’ll be like June 3rd, which is exactly six months from January 3rd, which is when I got beheaded.

Or when I, when I, when I had a fall from grace and became unfamous or whatever, whatever you want to call it. But yeah, so it’s, it’s been a tough mix of, you know, trying to write good training material.

In other words, like from scratch. So it’s like, I, I do like, well, I’m not, I will never ever be accused of being accused of being a perfectionist. I, I do like to provide high quality or as high quality as I’m capable of material with, with the training.

So I do try to like, you know, have, have nice pictures and have things be laid out clearly and look nice. And, and the other thing is that I don’t want to just repeat myself. So I wouldn’t want to just like completely rehash training videos that I had recorded that are up on Brent’s site.

If I’m going to, if I’m going to do this thing, it’s got to be me. So it’s, it’s tough because now I have to sort of find new ways to say things or new ways to present things.

Uh, so it is, it is writing all the material from scratch and, and it’s, it’s been going a bit more slowly than, than I’d like just because, you know, I mean, it’s a great problem to have because I, I have enough like consulting work coming in where it takes serious chunk, serious bites out of my, my time during the week where I can’t sit and dedicate it to, to writing the training, but it’s getting written.

Um, you know, I’m going to, I’m going to take, uh, some of the material from the, the, the server tuning pre-con that I’ve been doing. And, uh, I’m going to, that’s going to be like worked into some of the more advanced material.

Uh, a lot of the more advanced, uh, query tuning stuff is written right now. My, uh, my outline is, uh, like, like beginner stuff, uh, which I’m going to call starting SQL.

And that’s going to cover, um, that’s going to like do like, uh, sort of like a, like a jump, jump right into like, this is what a query does.

This is how, why it’s fast, why it’s slow. This is indexes, uh, you know, then going deeper into what is an index, what’s it, what’s a weight, you know, what’s a query plan. So like get, like getting like from like, like, like beginner stuff, but like, you know, not like, I think you’re a dummy beginner stuff.

Like I’m going to like teach you the really important, stuff about those things. Uh, then after that, I’ll probably do like a little bit of internals, not like, not like a book of boring, like, this is a database page.

This is a slaughter a type internals, like, like the stuff that I’ve found useful over the years. Um, then we’ll do like hardware stuff and then indexes more like more advanced index stuff. And then more advanced execution plan stuff.

And then query tuning. Um, I’m also hoping to have a very special guest, maybe do some, some stuff on columnstore. But I can’t say who or when, but that would be nice if that happened too.

So yeah, that’s my, that’s my plan. And, uh, and, and Josh to, to answer your email.

No, I don’t, I don’t have your address anymore. That was in my old email account. I don’t have access to any longer. Uh, so if you, if you, if you want to send me your, your actual address, that would be actually, I didn’t, I didn’t read enough of your email.

What the hell? What is this? I don’t even know what this is. Tastings. I’m not, I’m not going to you. You’re annoying me.

Email is terrible. email is the worst thing in the world. Darren says, he’s always found my classes, very informative and entertaining. Thank you, Darren.

I appreciate it. Uh, I’m glad someone does because most of the time, uh, when, whenever I’m talking to the camera, I’ll leave my office and my wife is staring at me like there’s something terribly wrong with me. I’m glad someone out there is, is, is entertained and informed.

The things that I say in here, otherwise, otherwise I don’t, I don’t know what I do. I don’t know. If, if you, if you, if you were one or the other, I would be, I would be ecstatic.

If you were informed or entertained by me, I would be thrilled. But the fact that you’re both, wow. I don’t, I don’t even know what to say to that. Enjoy, enjoy the stickers that I say to that.

Uh, yeah, that’s the thing. I don’t, I don’t carry over well to a lot of crowds. I have, I have a very specific set of people. I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I’ll tell you that that’s probably the hardest part about consulting is, is not like the consulting poker face or like, like the business stuff or the, like the, like the landing clients part.

It’s, it’s like the, the, how can I say this to like, how can I say this to people who I’ve never met in a way where like, I’m not gonna, I’m not gonna make everyone too angry.

And I think like, like that’s probably the hardest thing is, you know, like I, I need to say things in a way that they keep me interested in it. Entertain me.

But then at the same time, like I, I know someone’s not going to be happy with it. Like someone, someone’s going to be unhappy with it. Someone is going to be mad at me or, or, or think that I’m inappropriate or something.

So, ah, whatever. It’s a good time. You know what? Uh, if, if, if I had to pick a different way to make a living, I’m not sure what it would be.

Actually, I can’t, you know what I would do? I would open up a laundromat with a bar in it and I would only have it open from like, I don’t know, like 10 AM to like 8 PM, maybe 9 PM.

Cause I don’t want to like, I don’t want to have like an all night laundromat bar thing. I like, like, like, like, that’s like, like, like just washing machines full of pee would vomit would be the thing there. So I would open up a laundromat with a bar and I have very limited hours and I would just sit there and watch people do laundry.

That’s what I would do. Maybe I’d make a friendly conversation. I’d be the bartender, I guess.

I have a very limited drink menu. Cause I’m not very good at mixing things. Beer. The world doesn’t have enough shot in beer bars.

Everything’s a, everything’s a mixology bar. Everything’s going to have muddled, whatever in it. Shaved ice and rinds of things shot in a beer. Never did anyone wrong.

Anyway, uh, we’re at a, we’re at about a half hour here. And, um, uh, uh, uh, oh boy, this is a question. Hang on.

I just went through one of Joe’s blogs regarding soft Numa starting CWL server 2016. Per your experience, have you turned that feature off due to performance issues as raised by Joe in that blog? You should leave a comment on, on Joe’s blog post and ask him about it.

Cause Joe is by far a much, a much bigger expert on that Numa stuff than I am. So leave a comment with your question and, uh, mate, mate, and Joe will get back to you because he’s contractually obligated to is my guest.

No, I’m kidding. He’s not, he might ignore you, but if he does, I’m sorry. I’m sorry. I can’t control Joe. I was at first.

I was like, you know, if I’m going to do this, I should like, you know, say, okay, I’ll schedule your post to go out. And then I really just give Joe, let Joe off his leash, let Joe off his chain and do his Joe thing. Be ridiculous.

Trying to contain that man. Anyway, uh, that’s about a half hour. Uh, I’m going to, uh, I’m going to get going and, uh, get back to work or whatever you want to call it. Uh, thanks for joining me.

Uh, and I will see you most likely next week, unless something terrible happens. I’m kidding. Nothing. Unless I win the lottery, which would be wonderful. But if I do, if I win the lottery, I’m going to do this, uh, really drunk and tell you all what I actually think about you.

Goodbye. Have a great, great long weekend. if you are memorializing anything. Goodbye. Bye.

Bye. Bye. Bye. Thank you.