Category: SQL Server

ICYMI

---

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

Thanks for watching!

Video Summary

In this video, I share my experiences from the recent Bits conference and reflect on the fun and challenges of being a speaker there. I discuss how I managed to keep my mustache despite my wife’s initial approval, hinting at potential adventures or hijinks that might come with it. Additionally, I delve into some technical topics like foreign keys in production, deadlock issues related to cascading actions, and troubleshooting SPN stuff for SQL Server. I also provide insights on SSIS package design and the behavior of spools in query plans, hoping to help fellow database professionals navigate these complex areas more effectively.

Full Transcript

I’m alive. I’m live. Oh, I’m so nervous. It’s been like 10 seconds and no one’s here. Let me quit things that make noise. So I’m back from bits, and bits was quite enjoyable. I like bits a lot. I hope that I get to go back next year. Regardless of where it is, who knows where it will be? I know that right now they kind of rotate between a few different places. So it’s like Telford, London, Manchester. Last year was London, this year was Manchester, so I don’t know if it’ll be back in Telford. Though I hear there’s not much ado. in Telford, England. So who knows? Maybe they’ll find somewhere else to do it where there’s more going on. I ran out of stickers while I was there. I had to get new stickers. I ordered much more this time. Many more stickers. I actually have to send some out to people. And I have fun secret swag coming that will hopefully be here in time for a… I got a pre-con in Madness.

in. Yeah. I went to Madsson, Wisconsin at the beginning of the month. Next month. And like the sixth I think. So I’m going to be off to Madsson, Wisconsin to talk more about SQL Server. I’m gonna see my friend Joe Obish who lives out there. Be a good time for everyone. I hope. I still haven’t been able to shave off the mustache. My wife decided that she likes it. Yeah. Yeah. Keeping it on for now. Wife might, I was gonna shave it off. I was like, yeah, keep it look good. I was like, I don’t think so, but you said so. You know, what the hell? What’s the worst thing that happens? I have a mustache. Where I live, it’s not the weirdest thing in the world for someone to have hand and neck tattoos and a mustache. Stick with it. See how things go. Who knows? Maybe this mustache will get me into some trouble, get some hijinks, have some adventure in my life. The mustache adventures.

Food crumbs. More like leftovers, yes. You’re talking to a guy who has had food on his glasses on several occasions, so food and a mustache wouldn’t be too outlandish. Wild stuff out there. All right. What was I going to say? I’ve been working on some fun stuff this week.

I found some fun deadlock things, and the deadlocks were all related to cascading foreign keys, and many of the cascading actions, cascading deletes more specifically, many of the cascading actions didn’t have supporting indexes for the foreign keys, so there’s like these giant clustered index scans hidden away in foreign key cascading actions, which is quite interesting. And I’ve been writing some weird queries against the Sentry 1 repository database, so like some of the stuff that’s not available in the GUI yet is like hidden away in the table, so I’ve been writing stuff against that. I want to blog about it, but I’m not sure where the blog post is going to end up. I don’t know.

It depends on what Aaron Bertrand thinks about my query. Let’s see where that goes. Fun stuff there. Getting emails from people about stuff and things. Someone ask a question, because I’m just sitting here babbling to myself for the last five minutes. If no one has questions, I’m just going to go put my head down, because I have a terrible sinus infection in case you can’t tell. My face feels awful. Worse than it looks, I guarantee you.

Any recommendations for automated ERDs other than database diagrams and SSMS? Well, SSMS is going, got rid of those. They’ve been deprecated. As soon as you move to SSMS, I think it’s version 18. They’re gone. I think Toad makes a pretty good one.

Richie used to have a lot of good recommendations for these. If you’re on Twitter and you want to bother Richie about it, then I would say, hey, Richie, what’s good for database diagrams? He used to use those a lot for stuff. Me, I just model everything after the Stack Overflow database.

Yeah, and that’s why they’re getting rid of them, I guess, because hardly anyone uses them. And everyone I know at some point in their life has opened up Management Studio, went to click on something and accidentally clicked on database diagrams. You get that pop up, like, there aren’t any diagrams. Do you want to make something? You’re like, no, I didn’t want that at all.

I wouldn’t like the opposite of that. I would like no diagrams. So I had something kind of funny happen the other day where I went to the archery range and I was shooting for the first time in, like, months. And I did not move my arm out of the way in time.

And you can’t really tell all that well because of the tattoo and everything, but there’s this big bruise going down my arm and it’s swollen as hell because a bowstring caught it. A lot of fun there. A lot of fun there. Let’s see. Josh asks, how would you explain CX consumer weights to someone that is so new? Well, CX consumer is like the leader of a gang, right? So you have, let’s say that you have a dot for query running and you have four worker threads and they go out there and do gang stuff, go out there, do some crime, right? Doing awful things out there to good law abiding citizens. So you have people, you have these four gang members out there doing awful things. And that one coordinator thread is the gang leader and he’s waiting for them to come back and hear their stories. That’s CX consumer. CX consumer is a gang leader and he’s waiting on all the other gang members to finish doing things. They come back from their nefarious deeds and activities. So he sends them out and they go out and do things. And then, well, those four, four people are out there doing stuff. CX consumer is racking up saying, come on, man, hurry up, hurry up, bring my money. Smacking people around.

Zane says he used it once and it didn’t work so well. I agree that that has never worked so well. So let’s all agree not to use that ever again because that, that doesn’t sound like a lot of fun. Uh, let’s see. Uh, I made foreign keys in production unknowingly with the diagrams once.

Wow. That’s, uh, that’s interesting. Did, cause I’m assuming that that didn’t like, like it just created foreign keys and it didn’t actually like, like index them or do anything helpful to support those foreign keys. Yeah, no, right. No, probably not. Yeah. That’s a good time. That’s a real good time. Uh, yes, you’re welcome, Josh. Um, I, I have no other insight into that. Um, I, I did blog a while back. Some guy, you’ve probably never heard of a site, um, about, uh, about when, cause a lot of people said that CX consumer weights were not, uh, or something that you can ignore. And I disagree completely because, uh, if you have a parallel query that is waiting on a lot of CX consumer, it can be, you’ll have to excuse the crime scene back there. Uh, it can be, um, what do you call it? Uh, bad, but it can be a sign of, uh, really, really terribly skewed parallelism. Uh, Julie asks, what’s the best way to troubleshoot SSPI SPN errors? Uh, I skip right past troubleshooting and go right to the active directory people. And when I talk to the active, active directory people, I say, Hey, active directly people, make sure that my SQL Server service accounts, uh, have the ability to delegate SPNs. I want them to specifically have that privilege. Uh, so that I don’t have to troubleshoot that error. I just know that my, my AD service accounts have that privilege available to them. And I don’t have to think about it. There are like all sorts of, you know, uh, commands you can write to set like, uh, DOS commands and PowerShell commands to like set SPNs manually. Uh, that’s for the birds. I say active directory person, please give my, uh, account the ability to delegate SPNs on its own. So I don’t have to worry about that later because I’ll be damned if I’m gonna remember all those commands. Um, if anyone wants to be super helpful, uh, the late great Robert Davis had a blog post about, uh, troubleshooting SPN stuff. And if you feel like Googling that and putting the link in chat for me, that’d be great. Cause when I start doing that, I get all messed up. I don’t have anyone to help me with it. I need, I need, I need all you to be helpers.

Hugo says at least you had foreign keys better than most databases. I don’t know. I, I almost disagree because most of the time I’m not getting any great benefit from foreign keys. Like it’s nice to know the relationships, but I’m not seeing like, uh, what do you call it there? I like join elimination. Cause how often are you like querying two tables and only selecting columns from one? And then, you know, you load data in or like you have to delete data or like you have to update data or anything like that and go start checking all those foreign keys for the birds and the birds. Julian was Robert Davis, D A V I S. Uh, you’ll, his website is SQL soldier dot something calm probably. But over there, there was a, a good, a good writeup on a SPN type stuff. But like I said, you’re much better off going to the, uh, the AD people and just requesting the right permissions for your, your account, your service accounts. Let’s see. Hugo says more often than you’d think through views. I views. What’s up next? You have a lot of tricks up your sleeves. Hugo’s got tricks.

I hung out with Hugo at SQL bits. Hugo’s always fun to hang out with. Hugo is sitting in my session right behind it. So it’s just like no pressure there. I am dressed up in a, in a wife beater and, and, and, and, and, and light wash jeans running around like a, like a, like a fool and staring at these two. And they’re like the front row, like terrified. Like, please don’t let me say anything. Don’t let me say anything. Don’t. Uh, Zane says most of my work has been OLAP. And then FKs have not seemed necessary or beneficial. Yeah. Uh, data warehouse foreign keys. I’m, I’m, I’m usually, I’m usually pretty steadfastly against those. You might be able to convince me in a few, few scenarios, but, uh, for the most part, you know, uh, if I see foreign keys in a data warehouse, I get nervous because if you, especially if it’s, if, especially if it’s like a clear out and reload data warehouse, where you’re like, not just like a trickle in, it’s got a slowly trickling, changing thing. If you have to, if you’re just like every day, you’re like wiping it out and reloading lots and lots of data in foreign keys, but your butt hard, not, not good there. If you like data integrity should be done in the OLTP side, you shouldn’t be doing data integrity in the data warehouse. That should be handled when you put data in, like in tiny little chunks.

Let’s see. Uh, what is that? Uh, uh, let’s see. Uh, that’s a long name there. Uh, I am working on an SSIS package that identifies non-matching records in a, in a progress DB, uh, and updates records in Azure SQL DB. Would you create a new SQL DB for the progress table and then use a lookup task or is there a more elegant solution? Uh, I’m going to be very honest with you. I do not use SSIS a whole lot. Um, if you have, if you, if this is a new project and you have the luxury, I would, I would try it every way that you think might be, might be rational for you to go with and, uh, see which one works the best. Um, you know, lookup, lookup tables are certainly good for some, man, there is someone out there sawing away. I hope it’s not like a mass murder. I hope it’s not uh, I don’t have a terribly good answer for that just because of my, my lack of experience with SSIS.

Uh, I’m not really sure what the workflow you have currently looks like and why you think the, the lookup table would be better. If you want to stick some more details, maybe some nice person in chat who uses SSIS a lot would chime in and save my skin from, from, uh, more babbling about things that I don’t know. Oh my goodness. Let’s see. I had an email question or rather a, a, a Twitter message question this week about spools. And, uh, I’m waiting for the person to send me the query plan for it. But the question was, why do spools spools so much data? So like, that was, that was like the, the, the bottom line of the question. And the reason is that, uh, a spool doesn’t just, uh, so a lazy spool doesn’t just execute once. An eager spool will execute once, grab a whole bunch of rows, and then allow the parent operator to just kind of grab whatever it wants from that spool. Uh, lazy spools execute lots of times. Usually you can tell by the rebinds and rewinds and executions. Well, actually executions will be the total of rebinds and rewinds for a lazy spool. But, uh, for a rewind, that means you, uh, you use data in the spool.

And for a rebind, that means you went down to the child operators of the spool, ran those and got a new set of data and brought that in to the spool. And the spools usually happen on the inner side of nested loops because it’s a very repetitive, right? It’s a loop. It’s the only, you only join that loops, hashes and merge joins. They go get data, go get data, jam that data together. Nested loops are like, I got some data, go look, get some data, go look, get some data, go look. So it gets very repetitive.

And when the optimizer says, I think we could make this repetitive task less repetitive by reusing spool data, then it creates a spool of some kind, either, usually either a table spool or an index spool. And it, uh, and it populates it with stuff, usually data. And then it goes and uses that data. So for a table spool specifically, a lazy table spool, uh, you’ll, you’ll get a value from the other side of nested loops, say, go get me, go spool this data for me. Usually there’s a sort in there at some point too, if your data doesn’t, if your data isn’t in, isn’t in index order, like, uh, of the way you’re going to go look for it, uh, the optimizer will usually inject a sort into the plan to make sure that, uh, when you go, when you go spool data in that data is reused, uh, as often as possible, right? Cause if you have like numbers one through 10 and you have 10 of each, it makes a lot of sense to order those from one to 10, like one, one, one, one, one, two, two, two, two, like on so on. So you get the one, you go look for the one, and then you can reuse the one nine more times. Then you get the two, go get the data for the two, and you can reuse the data for two, nine more times. Uh, so that’s why spools typically show a lot of, a lot more rows coming out of them than going in or something like that. Let’s see. Zane says, you should get both as data flows, then use a merge, and then do conditional splitting. That’s likely your best SSIS flow.

Hit up a Q&A on dba.se, and I’ll help. Zane is always helpful. Zane is one of the most helpful human beings around. Uh, and it’s, it’s, it’s, that’s, that is, he’s right. That is a good question for, uh, dba.stackexchange.com, where you can go and add a lot more detail and, uh, put a lot more, like, you know, give us, give us some, uh, you know, what do you call it there? Uh, screenshots. Everyone loves screenshots. Peter says, my rule for SSIS is to get data from A to B and leave logic outside the packages and solution. Uh, yeah, so I, I do tend to agree there. Um, most specifically because, um, when, uh, I want people to use SSIS, it’s usually in place of linked server queries.

And people will always do this awful thing where they’re like, I’ve got a linked server. I’m just going to write a query. I’m going to write this fancy query, and I’m going to send like a big join condition where clause, something like that out across the linked server query. And it just never tends to end terribly well. There’s also this nasty downside of linked server inserts where I believe they’re row by row. So I usually just like to grab as much stuff, uh, like they’re row by row. If you go from like one out, uh, when you’re bringing a bunch of data in, you can just dump stuff into a table, the query it locally and you’re in much better shape. So, uh, that’s when, that’s what, so when I want people to stop using linked server queries, I usually suggest that they use SSIS instead because it is much, much less sloppy to, you know, go get data, especially if like, you know, have SSIS off on another server, go grab, have it sit up there with its own resources and everything, go grab data, push it around, push it around, push it around. It’s nice. Nice way to do things.

Not that I’ve ever done it, but I hear it’s very nice. SSIS is an ETL tool. So why would you avoid transformation? Oh boy. Religious, getting religious in here. Yeah, I don’t have, I don’t, I don’t know. Don’t ask, don’t ask me that.

Hugo’s on your case now that you better watch out. I’ll be real careful. Hugo, Hugo won’t, Hugo won’t let go. We blogging about you. He will give you the what phone on that. Let’s see. Do we have any email questions coming in? I have a thank you email from SQL bits for attending. Yeah, you’re welcome. SQL bits. That was a great time. I will always go to SQL bits as long as they have me. It’s a, it’s a fun conference, especially because I feel like it’s a bit less stuffy than other conferences. You know, there’s a, they just do such a nice job of making it a fun and, and very friendly environment. And every, every year I see that they, they put these big, big pillows on the ground that people can just hang out in. And every year I see at least like two or three people for the course of the event, just like face down sleeping on pillows. Like, like, like with their luggage next to them or like wearing a backpack. It’s like later. It’s amazing. I love it. Did you go sightseeing anywhere? Uh, so I spent a lot of my time at, uh, at SQL bits with, uh, penal daway. Uh, and that was, that was a lot of fun. We went out to dinner a bunch. Uh, I also, also hung out with, uh, Andy Mallon and, uh, Randolph and, uh, our friend Joanna was a very good time. Uh, sightseeing.

No, not really. Uh, I’m not, I’m not much of a sightseer, especially, uh, my, my idea of sightseeing is to, uh, look at menus and, and, and like wine lists. That’s, that’s my sightseeing. There’s not a lot, no, there’s not a lot of sightseeing in Manchester. I don’t think at least where I was. Uh, it was, it was, it was funny being there because, uh, every, it seems like every trip I take, I have some piece of electronic equipment that just stops working immediately.

Last year it was, uh, my laptop power supply. I plugged it in at, at the first day of our pre-con, I plugged it in to the thing and, uh, plugged it into my laptop and it started leaking. Like it just, it wouldn’t charge. And like this weird fluid, there was like, like, like warm Vaseline just started leaking, like just like grease started cooking out, like cooking oil. So I leaking out of the, out of like the weird rectangle thing that’s in the middle of every, uh, every, every laptop power supply. So that was, that was, that was the year before last. And this year, uh, I had my travel power adapter.

I plugged it in and it made a pop noise and that was the end of it. Uh, nothing would charge in there. So I had to throw that out and it took me a long time to find a new one. I don’t, I walked around for like 45 minutes. I went to like every Sainsbury’s and like electronic store that was around the hotel. And finally I walked into like a random pharmacy, you know, it’s like, do you sell power supplies? I was like, yeah, look at this one. I was like, perfect. I’ll take that. And that wouldn’t work the entire time. It was great.

Uh, let’s see. Didn’t you tweet a picture of a literal, a literal sewage? Yes, that was a literal sewage canal. Uh, well, it was, I mean, when the canal was full of water, it looks much nicer. But the first night I was there, uh, I was, I was, I was walking around at night. I was walking back to the hotel and, uh, I noticed that the canal was drained and it just looked like there was just like, like, like a, like a, like a, like garbage alley the whole way through. Uh, so I don’t know. Max says, did Freddie end up making an appearance?

Why, why didn’t you watch my, my video yet, Max? It’s available on the SQL bits website. You can see for yourself. You can see me dressed up as Freddie with your own eyes. Let’s see. A lot of talk about SSIS over in chat. I’ve seen it correctly version controlled and TFS, but it only lasted until I let one of my colleagues get his mitts on it. I’ve had tons of success doing source controlled SSIS packages. I’ve even built a few semi applications with SSIS. Wow. Holy smokes. You people do a lot with SSIS. I’m glad someone out there does because it’s not going to be me. I’m too old to learn these new tricks.

Far too old. I got to stick, I got to stick to boring stuff like the optimizer. TFS is great for source controlling SSIS and SSRS. That’s the first positive thing I’ve heard about TFS. Usually when people talk about TFS, there’s a long, long string of curse words either before or after, or like even, even in between the T and the F and the S. Usually, usually the F and the S get replaced with some of the things that are not so nice.

Not so family friendly. But yeah, it’s funny to hear that. TFS is good for something. Finally. Thank you, TFS. What a wild ride. Let’s see. Any questions coming in on Twitter? No, not really. All right. Yeah, Manchester was a, was a really good time. I went to, there’s a, there’s a whiskey bar near the hotel called Britain Britain’s Protection, which was very, very good. They had 300 whiskeys. And I think we drank all of them at one point or another. It was a lot of, it’s a lot of that. And then a few nice restaurants. I had, I had a burger in, in Manchester that was not disappointing. Usually, usually in England, the burgers are not like Americans, like up to American standards for burgers, but this one was damn good. That was it. I was, I quite enjoyed that. It was at a, it was at a place called Almost Famous. It’s very good. I would, I would have that burger again.

Someone keeps calling me. I’m not going to talk to them. No, thank you. I like that T-Mobile now tells me when there’s a, when there’s a scam likely. Martin says, Red’s does good burgers in Manchester. Yeah. I heard that Red’s is actually a really, really good barbecue spot. But I just didn’t get a chance to go there. There was a, there was a lot of, I was hanging out with Penal most of the time and he’s a vegetarian. So bringing him to a barbecue place would have been kind of rude. Like here, have like, have some sweet potato.

I got you some broccoli. But, uh, we went to, uh, I don’t know, went to a French place. We went to a Thai place. Everything was generally pretty good. Yeah, it’s right. I mean, if you’re getting a call, it’s probably, probably is a scam. Most likely is most likely is the only people who call me when it’s not a scammer to like, tell me someone is dead or got arrested or something. So I’m like, I will like never pick up my phone. It’s like never good news. It’s always like someone, someone needs money for something. Goodbye. There’s some good mock meat barbecue places in London. Uh, yeah, but we were in Manchester. So, you know, the fake meat was what only what was locally available, unfortunately.

Have I talked to you about your extended warranty? Uh, man, I need, I need one of those on myself. I need an extended warranty on myself. I got life insurance, but I need an extended warranty. Anyway, uh, Martin says, so real meat. Yeah. Real meat. It was, it was all mostly real as far as I could tell. Uh, I would commute four hours to be vegetarian as long as there was a significant amount of alcohol during that commute. You would have to, you would have to really, really get me lit to make that four hour trip worth it. If you’re like, like, like, like if you want me to go four hours for peas, man, you, you would, you would have to, you would have to throw down for that.

Uh, isn’t that called health and extended warranty and yourself, isn’t that called health insurance? Uh, yeah, I guess so. That’s what costs me a lot of money every month. Now you used to cost a whole lot less when I had a real job or, you know, when I had like a, a slightly more real job, it was my other, my, my last fake job had good insurance. Now that I’m paying for it on my own, I have expensive with my, with myself, myself fake job. I have, I have, it’s expected to be inexpensive.

So yeah. So Zane would have to get on the meat train to, for four hours to be a vegetarian. You, you, you, you sort that out yourself. Uh, I think Northern trains are pretty heavily look it up as a rule. Uh, I didn’t get on a train at all there. I took a cab to and from the airport.

I took a plane out and the rest of the time I walked around, I don’t even think I took an Uber or anything like, uh, within the city. I walked everywhere. I was, I was, I was up and down Dean’s gate so much that people probably thought I was a prostitute showing my legs off on the street, all drunk. There was one night I took a detour off Dean’s gate and I walked through like, like shady Jack the Ripper style alleys. I walked by a casino and I walked by a parking garage and there was like a gang of kids drinking beer in the parking garage. And I was like, man, you, you are truly delinquents. If you’re drinking beer in a parking garage like that, you are truly, truly delinquent. You can always decide to migrate to the civilized world. Uh, civilized world wouldn’t have me Hugo. I wish, I wish, I wish they would sometimes sort of, if they would, I would move to, uh, is what my, my, my question, like whenever, whenever I am like, man, what do I want to do for work? My first stop is I look at like French job boards for any vineyards that need a DBA. Unfortunately, most vineyards are not too heavily reliant on SQL Server.

So if I, if I could find a vineyard that needed a DBA, you can, you can bet I’d be out of here. Like pay me whatever you want. Y’all don’t need money. Go hang out. You can, you could, you could almost pay my salary and mine at that point. I would be a okay. Uh, so it’s like, like vineyards and Scottish distilleries. If you could, if like, like, like, like a Freud or Lagavulin or something needed a DBA, I would be out. Like goodbye. Peter says, sounds like you should work with Chris.

Yeah. I would love to, except I don’t know anything about PowerShell, but maybe if she needed a DBA, then, then maybe, but, uh, she could take the PowerShell. I’ll take the SQL Server. It could, could trade that task off. Hugo says, I’d beat you to it. Yeah, you probably would. You’re much closer.

You have a much easier walk, much easier walk than me. Me, I’d be very slow at that. Very, very slow. So, but anyway, I don’t know. Those are, those would be my, my, my, like what I would, what I would leave America for is if LaFroig, Lagavulin, anyone in Chateauneuf-du-Pape, uh, if you’re listening and you need someone to work on SQL Server for you, call me. I’m mostly available. Free 99 for you.

Lovely, lovely people. Lovely, lovely people out there. All right. It’s been a half hour. Uh, I need to go blow my nose and take some like Afrin or something. And, uh, it was lovely talking to you. Hopefully we’ll all be here next week and we can do the same thing. Thanks for, thanks for coming. Thanks for watching. And, uh, I will see you next time. 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.

ICYMI

---

Well, I missed it too. Darn that silly work.

Catch you next time!

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.