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 share some personal updates and reflections on my health and work life, while also diving into technical discussions about SQL Server. I start by talking about a recent bout of vertigo that has been affecting me, which led to the Epley maneuver for relief. This segues into a discussion on SQL Server performance tuning, specifically focusing on how version upgrades can sometimes lead to worse performance due to changes in cardinality estimators. I also address the topic of “optimized for sequential key” and its mysterious nature, as well as some interesting queries from viewers about database encryption and bulk inserts. Throughout the video, I offer advice on finding jobs that align better with one’s passions and how to handle imposter syndrome by writing things down and referencing facts more confidently. The technical portion includes practical tips on troubleshooting poor-performing queries and understanding encryption mechanisms in SQL Server environments like Azure. Overall, it’s a mix of personal anecdotes and professional insights aimed at helping DBAs and developers navigate their roles more effectively.

Full Transcript

I’m live, and I have my contacts in this week, so no glasses for you nice people. You get to see my face completely unfettered by shoddy Ray-Bans. So, lucky you. Lucky, lucky you. All 7,000 of you that have decided to join me today. On this glorious occasion of this splendid defeat. Ever think of LASIK? Yeah, I always think of LASIK. I mean, I always, uh, I don’t know what it is. Yeah, I would love to get LASIK. Another thing, yes, I would absolutely get LASIK. I would absolutely. get LASIK. I probably should get LASIK. I mean, I don’t drive, so I don’t have to worry about driving at night. I barely leave the house after dark. I don’t think walking at night would be an issue either. Mostly I’m afraid of like they, so there’s two things, I guess, because I have, I have a stigmatism. And so that automatically makes LASIK like three times as expensive or something. And, uh, the other thing is that I am afraid of, of them messing up and just like shooting a laser through my eyes. So. Sometimes, sometimes I, I really wanna, but then I’m like, eh, you know.

I’d rather be able to see poorly than have, have holes in my face where my eyes used to be. Laser holes in my face. There’s other stuff that I would, I would like to get surgically fixed first. Like, I just wish someone would take a drill and like make my ear canals better. My ears are awful.

I, uh, I woke up in the middle of the night on Sunday spinning, which is not that unusual of an occurrence, but it was like particularly bad. And, uh, I, I, I realized that I had vertigo, not just alcohol induced spins. I had, I had like legit vertigo. I had like, I hadn’t felt that way. I guess the last time I had felt even remotely like that was when, uh, Brent took people on the Alaskan cruise. And the first night at sea, the boat was, uh, the first night at sea, the boat was at all sorts of bizarre angles, flitting and floating around.

And, uh, I was, I was seasick for like a month after that. Uh, so when this, when this happened, it was like significantly, it was like acute vertigo. It was not intractable. It was just acute. And, uh, so I had to do this thing called, uh, the hell is it called again? Hang on. Hang on. Hang on. Hang on. I’m going to go look this up. Uh, vertigo maneuvers. What’s it called?

The Epley maneuver. E-P-L-E-Y. Epley maneuver. And I had to like lay back with my head hanging off the bed and like turn it and turn and turn my head and flip around. It was, it worked. Gosh darn it. It worked. Worked wonderfully. Uh, the acuteness of the vertigo has subsided to just slightly mild nausea. So that’s nice.

Uh, I’m just, I’m just cramming Dramamine and Advil in my head now. So whatever. It’s fun. It’s fun. Today’s the first day that I have not felt significantly dizzy since Monday. Hopefully I’m on the, hopefully I’m on the mend. I haven’t been going to the gym because, uh, I, I’ve been unsteady on my feet.

That seems like a pretty bad time to, you know, squatter, deadlift or overhead press, any of those things. Anyway. That was, that’s my, that’s been my fun week so far.

If I’ve seemed, uh, cranky or short in any, in any venue this week, probably because I’ve just been, uh, sick and on Dramamine for the entire week. Dude, how you’re getting better. Thanks, Rowdy.

I’m going to assume you meant hope. At least, you know, partially assume you meant hope. I don’t know. Hope is a useless venture.

Yeah, yeah, yeah. All right. Someone, it’s been five minutes of me talking about being dizzy. Someone, someone asked me a SQL Server question before, before I have no attendance whatsoever. Not, not, not doing my dancing monkey routine and making people, making, hopefully making people’s lives easier.

Is they interact with the incredible piece, the incredible feat of engineering known as Microsoft SQL Server. That has, somehow has worse partitioning than Postgres. Far worse partitioning than Oracle.

Far worse partitioning than probably DB2. I say that without ever having used DB2. I could be completely wrong. I’m going to put an asterisk on that one.

Farrest asks a question near and dear to my heart. How often do you see version upgrades leading to worse performance? Man.

That’s, that happens a lot. That new cardinality estimator is just… I would like to, I would, I would, I would like an intimate look at the workloads that it helps.

I mean, granted, the legacy cardinality estimator certainly messed a lot of stuff up. And the certain, legacy cardinality estimator was certainly never completely perfect. That’s why they worked on the new one.

But I would really like to know exactly what kind of workload the new one is, like, super beneficial for. Because when, when, when I look at, like, the workloads that, that tank when they use it, like, this isn’t that bad. You’re not doing anything weird.

You’re not, like, like messing things up in any way that other people aren’t messing things up. Like, what makes this cardinality estimator so much worse for you? And this is after flailing at stuff like stats updates and, like, like changing indexes and, like, moving chunks of code around.

And it’s like, this is still not good. Something is a muck here. So pretty often, yeah.

And it’s like, it’s not like I want to spook people away from using the new cardinality estimator. It’s just, you have to really, really test that thing out. And you have to really pay close attention to what, which, to which queries, how they behave under the different cardinality estimator. I’ve seen somewhere it’s just, like, nothing changed, really.

It’s like, it’s disappointing. Coming for fireworks of some kind. This is, like, something to go, like, miraculously bad or something to go, like, miraculously right. It’s going to happen.

This is, like, the same thing. Like, there’s, like, a hundred row difference in the estimate. I’m like, oh, it’s not exciting. Then again, very few people want excitement out of SQL Server.

Very few people are like, excite me. Everyone’s just like, please just stay, just be quiet. Just don’t do anything awful. It’s like having a baby in a restaurant.

Please just be quiet and don’t cry and don’t, like, pull the tablecloth off the table or, like, knock any glasses over. Just please just, like, sit and be cute and quiet. Peter, my goodness.

Did you ever get more or juicier details on optimized for sequential key? Saw one pretty sparse follow-up on the announcement. Nothing since.

No, I haven’t gotten anything new on that. You know, there was a blog post kind of going through how it worked, which, you know, I guess for something that’s patented, you know, something that’s, like, secret sauce. It gave an okay amount of detail.

I mean, it’s probably, you know, not going to be enough unless you can, you know, look at source code and debuggers and all sorts of other fun stuff. But, you know, no, no, I haven’t gotten anything. Then again, very few people willingly give information to me.

Very few people are, like, let me volunteer you this information. I don’t know. I try to get on the cool kid lists where people are, like, let me tell you how this works.

But, you know, cool kid lists won’t happen. Chloe. How are we doing?

What was I going to say? Yeah, I went for a walk. I went for a walk this morning. Pleasant walk.

Hi. Lee says, I’m troubleshooting a poor-performing query. When I ran spwhoisactive with getlocks equals one, I see the following database.encryption scan. Did someone turn on?

Did someone try to turn it on? Did someone try to use it in some way? I’m curious about that. It sounds like someone is trying to turn on TDE or someone is trying to use, like, always encrypted or something. So that’s cute.

That’s curious. I don’t know. Let’s see. Hmm. Yeah.

It looks like someone was trying to turn on TDE of some kind. I’m going to stick you, stick the first thing I found into chat because it sounds promising. Yeah.

Unfortunately, there’s very little else that that could mean. It’s most likely related to either, like, TDE or column-level encryption or cards outside trying to beat my foul language. Yeah.

It’s about it. There’s not a lot else that causes that. Azure. Ooh. The plot thickens. I don’t know.

Does Azure use, like, some sort of encryption in the background that we don’t know about? I wonder if it was for, like, maybe it’s for, like, a system database or something. I bet, you know, it would make a certain amount of sense if Azure, like, encrypted your local system databases.

I’m going to get my thinking juice in. Column-level encryption is present with that. It sounds like a good place to start troubleshooting.

It sounds to me like something that might give you an encryption scan. That would be the next direction that I pointed my troubleshooting efforts in. Further than that, I don’t know because I do nothing with security.

It’s wonderful. It’s wonderful to not ever touch security. I never have to worry about securing anything.

All I have to worry about is making something faster. It’s like, yes. It’s great. I get to sit around with all my fun tricks, learn some new tricks. Justin Patterson says, bulk insert on a table with a date time column, default get date.

All dates are the same. Yeah. So when it’s in a transaction like that, when you do bulk insert, you’ll get all the same values for stuff like that. So even if you had other default values for like Rand, I think new ID might be.

Actually, I don’t know. I was going to say new ID might be the only thing that’s immune to that, but I’ve never actually tried it. So I don’t want to promise you anything.

That makes sense to me if you’re bulk inserting. SQL Server is probably like, yeah, get date. All right. The reason that I want to say that that is the way it goes is because in the Stack Overflow example database, the votes table, there’s a creation date column that is a date time.

And there is only ever like a single, there’s like, it’s all like chunks of a single date value that ends up in there. There is like no, never any like value for the timestamp. It’s all like full somethings.

Just like dates with no time attached to them. And I think that’s because there’s a bulk insert process that goes there. That’s all I know. That’s all I know, I’m sticking to it.

I’ve had other, there are other columns that have non-deterministic functions like that that will not give you different values on insert. You have to like feed them something to get different values. He says, I want to practice performance tuning and keep getting roped into nonsense, i.e. stuff that bores me.

Why would I ignore that message? That’s a great message. Get a job where you get, stop getting roped into stuff that annoys you. That’s the best way to do it.

Look for jobs where they’re like, we need people to performance tune this database and say, I want to focus on performance tuning. The reason that I want to leave this job is because I do not get to do enough performance tuning, which is my passion with SQL Server. And so I end up getting roped into figuring out why someone is trying to turn on TDE and why that person has privileges to turn on TDE in this database that I’m trying to performance tune.

You have a junior now. Congratulations. Mazel tov.

Hope juniors a healthy baby. And you can make them get roped into the stuff that bores you so that they can leave as soon as possible. So one thing you have to be careful of with those juniors, if they get bored, they’re going to not lie.

They might exaggerate their experience in their time with your company when they start looking for new jobs. And it’s, oh, you know, I’ve been, boy, I’ve been working hard at this DBA thing for a long time. Imposter syndrome is now 100%.

I don’t believe in imposter syndrome. Just don’t. I think what a lot of people confuse for imposter syndrome is a lack of confidence in their knowledge about certain things. And while I think that’s certainly an understandable thing to have, and that’s certainly probably a good way to stay on your toes about things.

There are times when it hits me, and I used to think that it was imposter syndrome when someone would, like, say something that was wrong. And I would know that it’s wrong. But, like, hearing someone else say it would make me question whether or not I knew my stuff.

And I used to think, man, I feel that must be imposter syndrome. Maybe, I don’t know. Maybe it feels different to other people.

But that’s always what it struck me as. And over time, I sort of got more confidence in the stuff that I know. And I started writing things down more so that I could be more confident in it. And that seemed to help.

Writing things down is helpful. Being able to reference facts and correctness is helpful. So don’t feel like an imposter. You know, we’re all continuing to learn, except people who work with TDE, because they have very little to learn.

And then they’re done. And then they can go back to, like, playing chess in a park or whatever. Go back to ranting at pigeons.

And, you know, so, like, whenever that happens to me now, and, you know, it’s probably easier for me to do this as a consultant. So whenever someone says something that I know to be demonstrably false about SQL Server or databases or computers in general, what I always do is say, all right, can you, like, can you show me where you read that?

Like, can you show me, like, can you show me some source for your information? Or if they were, like, well, I tested it. I’m, like, I’d love to see it.

Can you show me your test? Like, can you show me what you did that led to that conclusion? And very often, those people will go silent. The whole show me your work thing leads to a lot of people just quitting and giving up.

Like, I don’t have it anymore. It’s a long time ago. I have to go check my email or something like that. I have to dig through my scripts folder.

Okay. I’ll wait on that. We’ll be over here waiting for you. Pantelis says, I found this.

And then, ah, there we go. Oh, oh, we had to split up the message. I see now. In Azure, all newly created SQL databases are encrypted by default. And the database encryption key is protected by a built-in server certificate.

Certificate maintenance and rotation are managed by the service and require no input from the user. Jeez. Like, no input from the user is just like, I would really like this maintenance to not happen while I’m trying to performance doing the database.

It seems like a strange thing to not get user input on. I would want input on that. It seems like if someone was going to be doing that sort of thing on a database that I was working on, I would be like, hey, I have input on when I would like this to happen.

If you see SB who is active running, stop doing what you’re doing. Something bad is happening. It could be, like, I still think it’s going to be like a system database maybe.

Or the column level encryption thing that you said you’re using. Who knows? All sorts of fun stuff going on there. All sorts of fun stuff going on up in the cloud.

All sorts of fun stuff. Does anyone else have a SQL Server question? Come on.

There are a billion of you out there. One billion. You’re not spamming chat. There’s no spam, please. Ask all the questions you want.

There is nothing else in here. He said I have a stinky update statement which has a function being used. For example, update my table set my field equals write my field 1.6.

My table has around 10 million records in it. I know the best thing here would be to remove the function entirely, but I can’t do that. Is there any way I can prove this?

Only way I have been able to improve this is to do something like set my field equals write. Yeah.

So the function, built-in functions. Let me be very clear about this. Built-in functions like write, left, substring, date, add, date, time. And that situation don’t have the same kind of overhead that, like a user-defined function of this scalar or multi-statement like the at sign table variable returning type would.

Those functions don’t really trouble queries too much unless you put them in a where clause. Now, granted, like how you’re doing it in the where clause might be helpful in that even though you have to scan all 10 million rows, you probably rule some of those 10 million rows out.

So what I would do is rather than update your table with that, I would add a computed column. Not persisted, just a computed column. So you could add, you know, it’s an alter table, my table.

Add my field computed as case when my field 2 is not equal to write my field 1, 6, then write my field 1, 6. And that would be instant, as long as you don’t persist it, that would be instantaneous.

And then you could index that column. You could do all sorts of fun stuff to that column. You wouldn’t have to go and update the entire table.

You would just have this virtual column sitting on your table that has that on there. No, that would, no, no. Those, the built-in functions are CLR.

They’re CLR. Let’s just call them CLR-ish. They’re behind the scenes. They are C Sharp. They take advantage of .NET stuff, and they don’t, they don’t, they don’t force anything to run serially. So, no.

You don’t have that problem there. The only thing that forces, I mean, there are a few things that force serialization. Right now, the most common is scalar user-defined functions. Modifying table variables in 99% of circumstances.

Global aggregates. Certain system views. The recursive part of a recursive CTE. Gosh, a whole bunch of stuff.

Top, when it’s outside of a cross-supply. Justin says, how do you pronounce S-A-R-G ability? How do I pronounce that word?

Sargability. Search argument. Sargability. If anyone says Sargability, ask them if they pronounce argument, argument. Or if they, I guess that’s it.

And then if they want to argue with you about it, you can say, well, that’s not how you pronounce argue. Ijit. That’s my advice.

You have heard S-Argability? The hell said that? You have my permission to throw eggs at whoever pronounces it that way. How do you pronounce G-I-F?

That depends on where I am in the world. In France, it is les GIFs. In certain parts of rural America, it is GIF.

In other parts, depending on local colloquialisms, it could be GIF or GIF or GIF. It kind of depends on where you are. Local factors apply.

Okay. Local factors apply to that. There’s no, I mean, there might be. There might be an intended way to pronounce that.

But, you know, all sorts of things happen when words are culturally embraced by people who speak differently, speak different languages. So, really, you know, hard Gs and soft Gs don’t exist in every single tongue. Some, I’ve heard that there are some languages out there that don’t have the letter G in them at all.

They’ve skipped over it entirely. So, how do you pronounce that? How do you pronounce that word if you don’t, if you haven’t invented the letter G? It’s a tough one.

It’s a tough one. You know, most importantly, there is an I in GIF, and I decide how I’m going to say that one. Greek have no letter G.

Isn’t that ironic? So, they’re just reeks. That’s weird, because I know several Greek people.

Well, I guess, I guess, I mean, they’re named like Giorgio or something. Or George, George, George something, Georgious, Giorgio, Giorgio, or Georgie, something like that. I don’t know.

I’ve given up on learning new languages. I’m just no good at it. I can’t even learn JavaScript. Talk about how tough life is.

But in English, G is not associated with a single sound. No, G is associated with many sounds, including sounds that aren’t like, you know, G alone, like ing. Like, there’s no G sound, like ing, ing, ing.

Like, talking, texting, walking. There’s a G in there. Josh has announced his presence, thank goodness.

I was wondering how I was going to develop an ulcer without you. Now that you’re here, you better ask a good question. Which you won’t, because you’re lazy.

You didn’t come prepared. Yes, today’s episode is brought to you by the letter G. Like, G, I wish I was sitting somewhere drinking.

Gosh darn it. I want to go drink. Golly and gosh. Glass of wine sure would be great.

Grandma’s gin, Ginny Gums, Gristle Grimly. Gee, I’m still at work. It’s 1230 here.

Oh, it’s like 530 there, I think. Unless you have a weird daylight savings time thing going on. It’s 530 there, I think. I think UK is five hours ahead.

France is six. Wilson says, I saw this on Reddit today. You have any tools you use? What?

Tools. I got a whole box of tools behind those nagels that someday I’m going to use to hang up those nagels. But I have to buy a piece of furniture for over here first. My office underwent a half-assed redesign before I went on vacation for a month.

I got a new standing desk. And I threw up my old desk, which was like an L shape. And it had a bunch of drawers and storage in it.

So now I have my old standing desk riser put up to maximum height. And I’m using that as ghetto shelving until I decide on what kind of piece of furniture I’m going to use over here to put my stuff on. And then when I get that, I can make more permanent choices about where Ren and Stimpy and the nagels will go.

And that’s how dull my life is. I don’t know. It’s awful.

Josh asks, if a query’s duration and CPU usage increases according to Query Store, there’s your first problem. Never trust a query store. But nothing else does.

With the same plan, same logical reads, dop, et cetera, what could that mean? Well, if it’s the same reads, that rules a lot of things out like index fragmentation. And if duration and CPU are both going up, then that rules out external forces like blocking, in which case duration would increase and CPU would stay the same or maybe even go down.

Let’s see. What could cause that? Maybe.

Well, it’s the same plan. Well, actually, that could make sense. What if, I mean, you could be, what if you’re just dealing with more data? Or, I guess what I might be interested in is, if it’s the same plan, are you hitting parameter sniffing?

Remember that one? Remember parameter sniffing? That thing that you learned about in high school?

That was glue sniffing. Yeah. All right. Well, I mean, it sounds like it could be parameter sniffing. It could be. I don’t really know what else to make of that.

If it’s the same plan, and reads aren’t, oh, well, reads aren’t going up, so parameter sniffing would probably rule that out. Yeah. It’s a VM.

Oh, boy. Okay. So, if you’re on SQL Server 2017, if you’re on SQL Server 2017 and using Query Store, you might want to join off to that aggregate wait stats view, like Query Store wait stats, to see if there are different waits involved for different iterations of this query. That’s what I’d point to.

That’s what I’d point to. But you’re probably on 2016, and I’m going to be disappointed in you. Oh, good idea. I finally had one of those.

I’m so happy when I have a good idea. Happens once a year. Once a year. My good idea for last year was, well, it’s still this year. Crap.

I guess I have two good ideas this year. Actually, three. One is, I didn’t get a real job. Two is, I had temporary tattoos made for swag. And three is, check the wait stats.

It’s funny, because I hate wait stats. Josh wonders aloud if SOS scheduler yield, like CPU contention or an oversubscribed VM, would be included in CPU time. Maybe.

Maybe. I would also look at what that query is doing, because, well, it might need to read like a fairly set amount of data.

I don’t know. Is it? I’m always curious when, like, a query is, like, really CPU intensive without being really read intensive. Like, what are you up to in there?

Like, all your data is in memory if you’re not doing, like, a lot of reads. Grumbling monster behind me. All your data is in memory.

If you’re just, like, purely pushing CPU, that’s one thing. I wonder if this query is parallel or serial, if the plan is parallel or serial. And I would wonder further if there are other issues involved.

I don’t know. We’ll have to wait for this. This sounds like a good question for dba.stackexchange.com, a site you may or may not have heard of, for data professionals.

A parallel plan. Let’s see. It’s from an SE question.

You thief! You liar! Come here with bootleg questions. I had to shoot you. Shoot you like the scoundrel you are. It’s a parallel plan.

Well, if it’s someone else’s question, I’m going to assume you don’t have access to run the query and see if there is perhaps some issues with the parallelisms of the query. Like, perhaps some spillage or some skewedness across different things. Because that would certainly explain why CPU and duration would go up without a meaningful increase in reads.

I would want to know. So here’s what I would, here’s where I would dig in. Right?

Reads are the same. Yeah, reads are the same. Same plan. Same dot. Duration and CPU are going up.

Tell me if that query is doing any writes. Tell me if that query is spilling or spooling or doing something else. Tell me if that, like, you know, there’s no way to see it now.

But now it’s like all these, all these things that, like, queries that when selects cause rights, that’s, that’s in my head now. And I’m thinking, like, well, I don’t know, like, like, are you updating stats behind the scenes sometimes? Are you, are you doing some other labor intensive tasks that wouldn’t show up traditionally with a query?

Right? Wouldn’t, wouldn’t, wouldn’t impact query runtime. Wouldn’t, like, show up as, like, a query weight.

It would just be, we did, we had more CPU associated with us behind the scenes. But, you know, we don’t have a good way of finding that out. So I would want to know if that query is performing writes of any kind.

That’s, that would be my next thing. I.O. completion weights would be something to look into. Because that’s usually our sleep task.

So I.O. completion would be, like, sort spills. Sleep, sleep task would be, like, hash spills. There’s no way to tell behind the scenes if it’s updating stats or something when the query runs. But, you know, depending on what goes on there, that could be, could be a drag.

Be quite a drag. Ha ha ha ha ha ha ha. So, yeah. That’s what I would, that’s, that would be, that would be my, the next place that I went to look at. My troubleshooting repertoire.

Like, what’s going on behind here? Oh, man. That was exhausting. It is a select into.

Okay. Well. We’ve, we’ve, we’ve certainly started to piece together slowly but surely. What, what is, what might be going on with this query?

So now we have a select into. Are we selecting into a user database or into a temp table? Do we have additional contention in this user, user database or in this temp table for some reason? Do we have anything that might be blocking writes to this table?

I know it’s a select into. But if you have snapshots occurring, like snapshot backups occurring, they’re quiescing activity. Well, because no, it’s, no, they’ll be blocking.

Never mind. We’re back to the CPU and duration going up and nothing else. Arr. Just trying to crowdsource my answer so I can get more rep than you. Ah, just go ahead.

I’ll, if I could bounty all my rep away and live like a stack exchange hermit, I would. But I do not have the patience to give my rep away like 500 points at a time. One day at a time.

I don’t think you, like you can’t have multiple bounties open at once. Forrest brings up an interesting point. If there’s only a small amount of space available in the database, does SQL Server have to spend more time writing disjoint extents?

How about this one? Does SQL, if SQL Server has to grow when, that has to grow the database and you do the select into? That’s certainly something that I don’t know that, like, I don’t know if that would count towards query CPU and duration.

Like, definitely query duration. I’m not sure if that would count towards query CPU. But if we had to grow the database and, and growths were slow in the database, like, let’s say, you know, a database, database was good.

Like, instant file initialization wasn’t turned on or, so instant file wasn’t turned on, you’re expanding the data file behind the scenes. That might be something. Another thing is, if the log file has to grow, that’s, I mean, instant file initialization never helps that.

So, if behind the scenes, we’re having to grow the transaction log, that could, that could certainly add. And, like, again, like, the part that I’m not sure on is if that would count towards query runtime. I’ve never cared enough to figure that out.

Maybe, maybe I do now. Or maybe that would make a good blog post for someone with a burning question on their mind. I don’t know that it’s quite burning for me yet.

What is burning is how hungry I am. That I’ve been talking for 40 minutes. 10 minutes longer than my usual webcast length. And I’m going to go eat now.

So, thank you for hanging out. I hope you had a time good with your face parts. Bye. Bye.

Bye.