CREATE INDEX ix_fraud ON dbo.Votes ( CreationDate );
SELECT *
FROM dbo.Votes AS v
WHERE v.CreationDate >= '20101230';
SELECT *
FROM dbo.Votes AS v
WHERE v.CreationDate >= '20101231';
What a difference a day makes to a query plan!
Curse the head
Hard To Digest
Let’s paramaterize that!
DECLARE @creation_date DATETIME = '20101231';
DECLARE @sql NVARCHAR(MAX) = N''
SET @sql = @sql + N'
SELECT *
FROM dbo.Votes AS v
WHERE v.CreationDate >= @i_creation_date;
'
EXEC sys.sp_executesql @sql,
N'@i_creation_date DATETIME',
@i_creation_date = @creation_date;
This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.
That’s an example of how parameters are sniffed.
Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.
One common “solution” to parameter sniffing is to tack a recompile hint somewhere.
Recently, I saw someone use it like this:
DECLARE @creation_date DATETIME = '20101230';
DECLARE @sql NVARCHAR(MAX) = N''
SET @sql = @sql + N'
SELECT *
FROM dbo.Votes AS v
WHERE v.CreationDate >= @i_creation_date;
'
EXEC sys.sp_executesql @sql,
N'@i_creation_date DATETIME',
@i_creation_date = @creation_date
WITH RECOMPILE;
Which… gives us the same plan. That doesn’t recompile the query that sp_executesql runs.
You can only do that by adding OPTION(RECOMPILE) to the query, like this:
SET @sql = @sql + N'
SELECT *
FROM dbo.Votes AS v
WHERE v.CreationDate >= @i_creation_date
OPTION(RECOMPILE);
'
A Dog Is A Cat
Chalk this one up to “maybe it wasn’t parameter sniffing” in the first place.
I don’t usually advocate for jumping right to recompile, mostly because it wipes the forensic trail from the plan cache.
There are some other potential issues, like plan compilation overhead, and there have been bugs around it in the past.
Thanks for reading!
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.
This isn’t your typical training session with the usual suspects causing performance problems.
We’ll be looking at the horrible things that happen to queries when servers are overloaded, and solving some really tough query problems that no amount of hardware will fix.
If you sign up early, it’s only $150. Prices will go up by $50 soon, and I’d much rather see you spend that on a bottle of water at Fenway.
See you there!
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.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I delve into a lively discussion about various database management topics, diving deep into execution plans and offering insights on how to interpret them effectively. Starting off, I share my experience as a DBA and highlight the importance of questioning every aspect of an execution plan—seeking out seeks or scans, understanding join types, and analyzing aggregation methods. The conversation then shifts to more specific scenarios, such as managing log files in data marts and the practicalities of moving from Azure SQL Database to managed instances. I also address the cost-effectiveness of cloud solutions versus on-premises databases, emphasizing that while performance can be challenging to control in the cloud, it’s crucial to weigh the costs against potential benefits. Throughout the session, I encourage viewers to explore resources like Grant Fritchie’s books and my own for a deeper understanding of execution plans and best practices in database management.
Full Transcript
I’m alive and fully mustached. Full, full on mustache. No one is here to admire my mustache. How very sad. Do-do-do. Do-do-do. Do-do-do. One, two. Oh yeah, people are coming in, hanging out. This is wonderful. Wonderful. Makes me feel so not alone. This time, I’m going to have the chat window open so that I don’t have to look at my phone like a scrub.
Welcome Lee, finally made it to one. Is that Luli or Leli? How do you say your name? You’re going to have to give me instructions so I don’t mess it up. Penel is here. Watch out. I might get some weird questions today if Penel is here.
Lou. All right. Lou it is. Yeah. So, for those who don’t know about my antics coming up at BITS, I’m, for charity, not just like to quell some strange desire with me, for charity, I am dressing up like Freddie Mercury to do my index session. And I have this lovely studded belt. All right. So, I got that. I got my macho man armband. All right. This. All right. Got this going on. I have a pair of straight up dad jeans. Check these out straight up. Now you all know what size jeans I wear, which is maybe embarrassing too, but straight up dad jeans that I’m wearing that I got. And of course, I have the white tank top ready to go. So, I have the whole thing. I even have the sneakers, but I didn’t want to look like a noob. Cause I know that like, uh, Manchester is the home of lots of people who wear and take Adidas sneakers very seriously. So, um, I’ve been, I’ve been wearing my, my white Adidas Sambas, breaking, breaking them in a little bit. I don’t want, I don’t want to look get fresh white Adidas Sambas stepping off into Manchester. I might get beat up. Might be some, some soccer fans there who, uh, who just tear me a new one. So I’m getting that going. The mustache is happening. All right. Going to have that full Freddie mustache. I’m not going to have the armpit hair. I can’t do that, but everything else is good to go. Lee, I’m looking forward to seeing you at SQL bits in Manchester. That’s going to be fun. I’ve, uh, I think I have all my material pretty well wrapped up for that. So I’m excited to excited to deliver it. Finally. I also have stickers live. I mean, not really live there. I think they’re, I think they’re dead stickers. So I’ll have stickers for everyone going to bits too. So everyone will, everyone will get something.
Except I don’t know, maybe, maybe not everyone. Maybe some people won’t. I don’t know how that works. So, uh, I don’t know. Does anyone have like questions about SQL Server? What it’s like to have a mustache? Like, I don’t know.
I’ve had, I’ve had sort of a funny day, actually sort of a funny couple of days. Um, I’ve been, uh, let’s see. Julie says, if someone can’t make the SQL bits, how can we get a sticker? Uh, well, Julie, I think, I think since I recognize your name enough, if you want to either, uh, email me your address or, uh, or, or DM me your address on, on Twitter, I will, I will send you a sticker. Dan, if you want one too, let me know. Since you, since you also recommended sticker mule to me and they are awesome stickers. I, I, my, my, my original order was from the company called sticker you. And they sent me the worst stickers that I’ve ever, I’ve ever seen in my life. Like, like, like the logo is illegible and like, like faded looking. And like, I ordered like, like gray, like, like solid gray, but it came back like, I don’t know, like spotted. It looked like TV noise. It was awful. So yeah. Yeah. I might even send you more than once. And some, since some people like to put them on their phones and laptops and foreheads and get tattoos with them. So it’s all sorts of stuff that you can do with that. Uh, now, now I’m, uh, putting the, the final touches on, yeah, you’ll be, but now we’ll get it at SQL bits, but I’ll take the whole stack of them.
And I’ll be, I’ll finally be famous in India. Uh, but yeah, I’ve been, uh, uh, wrap the final touches on, uh, my, my demos for the indexing session. And that’s going to be fun. Um, that’s, uh, that’s gonna, it’s gonna be, it’s gonna be interesting. And, uh, let’s see. Oh, we finally have a SQL question. Lee says, how do you replicate a poorly performing query? I see many badly running queries, but replicating these on a dev environment can be difficult due to not knowing what values are being used for parameters. So, um, you can get from the plan cache.
And this is something that I wrote into SP blitz cache. If you want to use it, uh, you can, from the plan cache, you can get the parameters that code was initially compiled with, but you can’t see what it was last run with, which is, um, uh, sort of downside of dealing with the plan cache. I want to say that Grant Fritchie recently wrote a blog post about how to get that from extended events, but I would have to track it down and find it for you. Um, but that’s, so if you, if you want to just get in that, and that’s a great way to start troubleshooting parameters, nothing, right?
Because, uh, especially if, if, you know, someone is coming to you and saying, hey, when this query, this query is slow, when I run it, then they can perhaps give you the values that it was compiled with that, that was slow for them. And you can compare that to what it, or rather what they ran it with and you compile, uh, compare that to what it’s compiled within the plan cache.
Let’s see. Uh, I have a question. How often does Microsoft release patches for SQL Server 2017? Um, so I don’t know, cause I don’t work for Microsoft. Uh, if anyone from Microsoft is watching and listening and you want to give me a cool job working on SQL Server, I, I won’t complain, but, uh, I, I don’t know. Usually it’s supposed to be every month for the first year and then quarterly every, or every three months or quarterly after the first year, whether that happens and they stick to it, I don’t know. Um, there, uh, there have been some, some downfalls in, in, in the agility that Microsoft has tried to discover in the, in the CU release process.
Yeah. CU 13 now is to do the math. Ah, yeah. Good, good luck on that. You know, uh, they, they, they say they have a new servicing model. So let’s, let’s see if they stick to it. Right. I guess technically, well, no. Yeah. Yeah. You’re right there. I’m sorry. They, they messed up.
Yeah. Uh, it might, it’s funny. These t-shirts always look so much cleaner, like when I’m looking at them and then I get on camera and I just look like a mess. Let’s see. Rowdy has helpfully posted a link. Uh, let’s see. Lisa says, I know you briefly talked about Azure managed instances, but what are your experiences with them? We were seeing some real gutches at work while using them. Um, my experience with them was when, uh, was we got a preview version, uh, to play around with when I was with Brent and, um, I didn’t do a lot. I didn’t like really kick the tires on it. Um, because we didn’t have it up for very long because those things still cost money.
I think it was costing like 1500 bucks a month to keep it up. So we didn’t keep it up very long. Uh, we just, you know, did enough to like, kind of like get some initial stuff back on it. Um, and, uh, you know, yeah, they are expensive. Uh, but I, you know, I actually, over on Twitter the other day, uh, I talked to the, well, I didn’t talk to, I tweeted at and got a tweet response from the PM, uh, guy named Jovan. And he said that they’re working on something for developers that will be less expensive so that you can kind of kick the tires and blog about them and stuff. So that’ll be nice to see when it comes out, but there’s nothing public on that yet. Uh, as far as, uh, tell me, but I would love to hear about the, um, uh, the things that you ran into with managed instances, because, uh, it would be nice to have sort of like, uh, you know, like I have a real world use case and this didn’t work out for me sort of stuff to talk about because, um, right now I got nothing.
Even like, even if I, even if I got like a dev thing to mess with, I would like put stack overflow on it and be like, works for me. Not run into stuff that like, you know, people in the real world might have to do. They are crazy expensive though, but I think, you know, that’s, it’s totally worth it.
Um, you know, uh, as far as being a good mix of on-prem and managed database instances, I think, I think Microsoft hit a good, uh, a good mix of, you know, uh, out of the, uh, on-prem features with the, um, the managed side of the, of the server. See, Darren says, I would too, because I’d like to move from Azure SQL days. Yeah. I think you and everyone else who is on Azure SQL DB wants to manage, wants to move to a managed instance because they, I think, I think comparatively, they are, they are just fresh to death. Let’s see. Uh, Rowdy says a buddy familiar with AWS RDS SQL said that the community really loves it. Have you had any experience with their managed MS SQL?
Uh, what do you mean? Familiar with managed instances? Uh, let’s see. Lisa says we looked at SQL DBs, but the performance just wasn’t there. Yeah. Uh, so, I mean, there are aspects of the cloud where performance is just really tough to lock down, like, you know, uh, storage, networking that, that stuff gets crazy expensive. Like it’s, it’s tough to, you know, and it, and it’s, and it’s hard to like, you know, say, oh, well, the cost here is totally worth it because, you know, um, often it’s not often.
It’s just like, I’m getting soaked on this. Rowdy has more links. Rowdy is amazing. I hope Rowdy is always, always unemployed. So you can always show up and put links in chat for me. Just kidding, Rowdy. I, uh, I have passed your information along to people. Uh, let’s see. Uh, SQL Dev DBA says, uh, we have a 25 gig database, a data mart that has a 39 gig log file in the full recovery model. We take log backs every five minutes. Any thoughts?
I’m not really sure why you’d want to have a data mart in full recovery model. I’m not sure that that sounds like, um, a good, good mix for me. Like, like, like I, like I wouldn’t expect to see like a data warehouse in full recovery model. Um, any advice? Jeez. Uh, so, you know, is it how big, like, I guess, you know, it’s, you gotta have some historical questions about that, right? Like, was it always that big? Was there like a one-time transaction that made it that big? Um, you know, this is like a lot of, a lot of stuff comes to mind when I’m trying to figure out like, well, you know, like, is it worth it to shrink down the log file or am I just going to suck it up because 39 gigs of space just isn’t all that much these days? Like, I can’t imagine sweating 40 gigs unless I only had 20 gigs left, I guess. Let’s see. Uh, Lee says, when you first started as a DBA, what did you find the most useful aspect of executions plan to learn first? Seriously, need to get better at reading them, but it’s entries of it. Um, well, you know, for me, the hardest part was always figuring out like, is this a good execution plan? And, um, you know, I think the best way to start is, uh, like the way that you read the plan right to left and just ask questions to yourself about everything that happens. Uh, ask, like, we start with like, how did we access the index? Was it a seek or a scan? Why was that so? Um, do I not have an index that I could seek into, do, uh, do I have an index that I could seek into, but I didn’t for some reason?
Um, I’m doing this type of join. Why am I doing this type of join? I’m doing this type of aggregation. Why am I doing this type of aggregation? Uh, I think, you know, the best way to learn is truly by questioning plans that you see and like learning about the different operators and why they pop up.
And like over, you know, over the years, it’s funny because the stuff that you care about really does start flowing from right to left. So like, you know, you go from caring about, oh, did I seek or did I scan to, oh, what kind of join did I do to, uh, oh, I did a key look up to like, you know, well, something else downstream. And then you start caring about like the weirder operators are like, what are these spools? Like, what’s going on? Like, why are you spooling data out there? What are you doing to me? And then like, you know, you learn, you learn about, you know, the differences between cash plans and actual plans where like an actual plans, you see all this new information, especially nowadays, Microsoft is filing cool information, uh, actual plans. Um, let’s see, but you know, uh, if you want some reading material on it, uh, Grant Fritchie keeps putting out these books about execution plans that have tons of good information in them. And, you know, even if you only like, even if you don’t read them front to back, even if you just say, all right, look, I need, I want this reference material when I come up to this, when I come across something weird in a query plan, I’m going to go look at the book. It’s totally worth it to have on your shelf for that. Uh, Grant puts a lot of work with them. Grant’s a super knowledgeable guy. I would, uh, I think having, having his book on your shelf, if you’re trying to learn about execution plans is probably a really, really good idea.
Uh, let’s see, uh, SQL dev DBA follows up with log DB will typically be at least some size above the largest table to accommodate reboot. Yeah, it will. So it’s going to be, I mean, I would say at least the size of that object plus 50% ish, but you know, again, 39 gig log files, and isn’t really going to be like my biggest concern. Um, yeah, I mean, unless, unless you find yourself frequently having to restore those, but even, I think even then with instant file initialization turned on the data file will go quick and spacing out the log file will be kind of painful, but, uh, most people aren’t restoring data marts. Most people are just kind of rebuilding them. Let’s see. Uh, let’s see. Mike Whitty says, yep, that’s been our experience. Lee, cool, rowdy.
We do hourly imports from an Oracle database. Uh, once you have the data imported, I mean, uh, it’s a good question. Uh, yeah. So if you’re just dumping data into the database, it might not be, uh, as big a deal as if you’re, you know, doing some aggregations or moving stuff around or, you know, doing some kind of, uh, flat or what do they call it? Like presentation type logic, uh, to the data once it comes in. Good question. Trying to think of some other stuff. Um, Ben Navarez had a good, had a pretty good book, uh, about SQL Server 2014. That’s a, I mean, it’s not like dated, but it’s, you know, it’s not, it’s not as up to date as Grant’s book is now. And of course you could always buy my book from, it’s like, I think it’s free for Kindle users. So if you want to, if you want to just read the Kindle version of my book, you can. There’s, there’s a few things about execution.
And also if you’re, if you’re coming to my, my, my pre-con in, in, in, in jolly old Manchester, there’ll be lots of stuff about execution plans in there. Lots of deep probing, long fingers going into query plans and saying, what’s wrong with you? Why are you doing that to me? Uh, SQL WDB says we’re not manipulating it. We have views that use the data and we massage them with the views in order for power BI to access them. Might have to consider putting the data mark to recover. Yeah, I would. I mean, so putting it, so just to, you know, kind of set some expectations here, putting it into simple recovery model, isn’t going to fix the size of the log file. It might give you a better idea of, um, you know, how big the log file should be, but, uh, it’s not going to like magically shrink the log file for you. What I would do.
So here’s what I would do. Uh, I would, uh, set up a query to, to run and look at free space in the log file and have it run like every, you know, have it, have it run like every minute because you’re taking log backups every five minutes. Uh, I would have it run every minute and just kind of look at how log space is actually used. And it might be that, you know, you, you could shrink your log file down once to like, you know, maybe half the size and just leave it for a while and see if, see if it grows again. Let’s see. Oh, Julie posted a link and I have to okay it.
It’s funny. Like Amazon makes me okay. Uh, Amazon. Jeez. This is an Amazon link, but, um, YouTube makes me okay. Every length that comes in. Uh, let’s see. Lou says, uh, have you tried? Oh, the Azure DevOps studio. No, I haven’t. Uh, and I know I’m probably a bad DBA for not that, but, uh, I’ve been really head down, uh, lately trying to, uh, you know, get the consulting thing rolling and, uh, trying to get, uh, all of my various presentations and whatnot lined up for, uh, for a future use. So I’ve been really trying to kind of have not had a lot of time to experiment with new, new bells and whistles, but I probably should. It’s, it looks neat. I think the main drawback for me of, um, of, uh, Azure DevOps studio, or as they call it on, on Twitter, ADS is that, uh, it does not do well with execution plans right now. And that’s kind of like my bread and butter. So like whenever I want to like do something like in management studio, there’s like a 90 ish percent chance that there’s an execution plan involved. So I don’t know. I don’t know if like, I’m gonna, I don’t know if I’m to hop on that, hop on that yet. Cause I need, I need my execution plans or else, you know, I have no blog posts, but I don’t have execution.
It’s funny how that works. I should probably, probably learn how to blog about other things, right? Put some pancake. I have like, I have like pretty good recipes from getting laid off. And like, no, no, like I, I, I make, make pancakes and French toast and all sorts of other stuff for my kid in the morning. So it’s fun. It’s funny to have like that kind of time on my hands. I’m just like, what, what shape would you like it in today? A unicorn head. Of course, let’s do that. Like paint brushes and little spatulas and like details. It’s fun. I don’t know. Maybe SQL Server isn’t my calling. Maybe, maybe custom pancakes are my calling. Am I on Instagram? No, I’m not on Instagram.
Uh, I, I am baby stepping into social media. Uh, I do not, I do not do terribly well with it. So, uh, um, I’m, I got on Twitter because that seemed like the easiest to manage. And, uh, I could just say things instead of always having to have a picture to go with them. But, uh, maybe, maybe Instagram is next. Maybe I should at least like parking spot my company name.
Now someone’s probably gonna like hold it ransom from me. You don’t have to pay a million dollars to get Erik Darling data on, uh, on Instagram. Right. It says I do vlogs from your neighborhood. Uh, so I was thinking about like, I, like at first I was like, oh, I’ll do them from the gym.
But then like, no one wants to watch that happen. No one wants to like watch me deadlift and yell at things. There’s just no, no audience for that. This is, there’s like a million people who do the exact same thing. Plus I don’t leave the house except to go to the gym. I like walk there and back. And then that’s it. My favorite restaurant closed, or I would say our favorite restaurant, like, uh, like the family’s favorite restaurant closed. Uh, after the first of the year, the, the chef got a new opportunity to do like some cool thing and they closed. And that was kind of a blessing in disguise because it saved us a ton of money, a ton of money to eat there like twice a week.
It’s ridiculous. Let’s see here. Any other questions? I mean, I wish I was on Instagram. I don’t know. I guess the question for you is, are you, are you on Twitter? Are you, are you, are we friends on Twitter? Are we tweet twins? Fweets? What do we call it? I don’t know.
Let’s see. Uh, oh boy. Yeah. All sorts of things. All sorts of things. All right. Let’s see. Uh, talk to us about trivial plans, the good, the bad, and the ugly. So trivial plans are a wonderful sort of optimization where, um, SQL Server will say, I have such an obvious way of doing this one thing that I am not going to think about. Like if I thought about, if I thought for like, like as many CPU cycles as you asked me to, to come up with a better execution plan for this query, I likely wouldn’t. The thing is that sometimes it’s wrong and sometimes trivial plans lie, uh, because trivial plans are tied into this thing called simple parameterization and simple and parameterization in general can, can cause issues with either parameter sniffing, not using filtered indexes, stuff like that. And so, uh, when you get trivial plans, often you will get a simple parameterization alongside it. It’s not guaranteed, but it’s, it’s in there. And, uh, you know, there are some downsides where, you know, sometimes there is a better plan waiting behind that trivial plan fence that, you’re just not finding, uh, trivial plans won’t ask for missing indexes. Trivial plans will never go parallel. You know, there’s just stuff that you don’t get from a trivial plan that you get from full optimization. Not that every query in the world needs full optimization. And it’s really tough to find like when a trivial plan does better with full optimization. So it’s just something that I keep an eye out for. Like if I see that a plan gets trivial optimization, I might throw a one equals select one on there, get a, get full optimization and just see if anything changes. If not, I go about my business tuning the plan at hand. Julie says, is there any way to limit how often a SQL agent job sends a failure notification? Uh, not that I recall. Um, I don’t know if there’s a way to, to sort of like spoof that and like, like damp that down a little bit. Uh, I know that most monitoring products offer a way to sort of, uh, uh, uh, what do you call it? Uh, well, Rowdy posted a link.
Yep. And the link I added. Okay, cool. Uh, oh, look there. Look at that. How can I live with the number of emails sent by SQL Server agent? I’m going to upvote that. Sweet. I’m going to upvote that answer too. That’s a good answer. Thanks Rowdy. Uh, let’s see. Lee says, we have seen cases of parameter sniffing at work. The easy fix has been to add that option recompile. Why is that a bad fix?
Overhead seems minimal compared to the bad plan. If the overhead is truly minimal, then I wouldn’t call it a bad fix. Um, usually when, uh, when I’m dealing with parameter sniffing, I ask myself a few different questions. It’s either, uh, like what’s the difference between the good plan and the bad plan?
Like, so it, with the, with the small plan, am I getting like some little like serial key lookup, like low memory plan. And when a big plan comes through or when a big value comes through, is that just overwhelming it? And then like, what plan do I get for the big plan? Like this, look at what the differences are because sometimes there are ways to, you know, sometimes it’s like, oh, if we have a slightly better index or a slightly different index, we can avoid having to, we can avoid parameter sniffing altogether. Other times it’s like, well, maybe if I just hint to like, say optimize for this value, which is a bigger value, it makes more sense. Um, option recompile.
I just, I dislike it. Not, not necessarily because of like the overhead, because most of the time SQL Server coming up with a query plan is fairly easy. The reason that I dislike option recompile is that we don’t have any, we don’t have sort of any good historical information in the plan cache about, uh, what that query is up to over time. And if there’s ever a problem with the plan that we get with option recompile, we don’t, we don’t have a good way to like sort of track that and figure it out. So it’s not that I’m against option recompile all the time. I just, you know, if you’re going to use it, you need to know like it, you don’t have that kind of good forensic information in the plan cache anymore. Um, you know, so take, take the good plan, take the bad plan, or take the, the plan that’s bad for some value and just try to look at the differences. Uh, you know, this is, this is a good time to, uh, I guess if, if you have, if when you get grants book, try to like, like look at the operators that you’re getting and try to figure out situationally why SQL Server may have chosen those operators for one plan and not for another. So that’s a, it’s a good bit of homework to do is figure out why the optimizer thought, well, like figure out like, like look at like, you know, uh, the plan that it comes up with for the small values and be like, okay, cool. We have that.
And then run it with the big value and say, okay, now it takes us long. Then recompile it and look at it for the, look at the big plan and say, okay, we got a totally different thing. And then it’s just helpful to compare and go back and forth and look at, you know, what, what one does and why SQL Server was like, oh yeah, we, we had to do this differently because we were dealing with a way different set of data. Right. Any more questions, any other things that we can talk about, do yell at each other about yes, no, maybe I don’t know. Rowdy, you might, you might, you might have to, you might have to come up with one for me.
Putting Rowdy on the spot. Mike Walsh is being funny. Mike Walsh does not like my mustache. Oh, you posted on my Twitter. Let’s look at my Twitter then. Let’s see what happened over there. Oh, that’s you. Okay. I didn’t, that’s why I didn’t recognize you because you, you are a picture of a dog over there and you are, I think not a picture of a dog over here. Yes. Not a picture of a dog over here. So yeah, Mike, I know it’s not very punk rock. The goal is not to be punk rock. The goal is to be late stage glam rock. And that’s, that’s my, that’s my goal to be like, like we, we were glam and now we’re getting out of glam and we just kind of have some weird side effects happening.
Let’s see. Uh, Roddy says 3d printed pancakes are just as good as homemade. I’ve never had a 3d printed pancake someday. I want to live in that. I want to live in that future world where 3d printed pancakes are a thing. That sounds awesome to me. I love, I would love to try 3d print, except steak. Steak is where I draw like biological matter is where I would draw the line on 3d printed.
Come to Dallas. Uh, I was supposed to go to Dallas to, uh, do a user group thing, but, um, I don’t know. I think they found someone cooler to do it. So I don’t know. Maybe I’ll do it again, but we’ll see. Who knows? Who knows what the future holds? Let’s see. Oh, a question.
What’s the best way to get more information. Nigel says, what’s the best way? Uh, okay. I’ll do this really quick. Uh, so, uh, Rowdy, no, it wasn’t going to be SQL Saturday in May. It was going to be, uh, like a training day type thing, but I think I want to say they got like Andy Leonard or something for it. I, I, I, I had a much or though, uh, let’s see. Nigel says, uh, what’s the best way to get information from deadlocks? We’re having some deadlock issues. And while we use monitoring tools, they don’t give us enough information. Uh, typical scenario is one update, one select.
The update is only updating one table. The other table is in the deadlock. The only clue is there is RL between the two, but the update does not involve the columns. Um, so, uh, if you use the first responder kit tools, there is a store procedure on there that I wrote called SP Blitzlock that gives you, that breaks down. I think, I think really well, the information that comes out of deadlocks. Um, if you run that, I think that often gives you, uh, better information than monitoring tools do. I think monitoring tools, uh, don’t, don’t go into the depth that they should of telling you, uh, what happened. Uh, so I would start there. Um, if it sounds like if you have a monitoring tool, they might even have a deadlock extended event session set up and you can point SP Blitzlock at the, uh, the deadlock extended event session. And you can get a ton of cool information out of that. Dan says, I think SP Blitzlock is great.
And yes, it is. And I wrote that drunk on a plane, so it has to be great. So they gave you maybe way better info than Redgate. Wow. Well, you know, Redgate has never asked my opinion on deadlocks. Maybe they should.
Let’s see. Dan says, uh, how do you go about determining or recommending the correct amount of RAM? I have a customer with one terabyte of data over multiple databases and 25 gigs of RAM in an OLTP environment. Holy smokes. Uh, wow. So if I, if I’m making a sort of just base out the box, you want to know how much RAM to have? I like to say 50% of your data.
Um, and that’s not because I think that 50% of your data is active. Usually it’s somewhere around like the 20 to 30%, but I want 50%. I want to, I want to, I want to, I want to have RAM equal to 50% of data because caching data isn’t the only thing that SQL Server does with memory.
Obviously query plans are going to ask for memory grants and some of them are going to be pretty big. And it’s going to, it’s going to memory that for memory grants is going to fight with memory for the, for, uh, the buffer pool and for the plan cache. And just to be safe and say, I have this much memory available to me, that’s what I’m going to go with. Uh, if you want more, like more, a more detailed breakdown of what you should have, um, I would say look at weight stats.
If you’re spending a lot of time waiting on memory ish weights. Uh, so, and by that, I mean, if you’re spending a lot of time waiting on disc because you’re, you’re the, your, your active data is not in memory when you need it to be. So you’re looking at like page IO asset page, IO latch sh and ex weights. Uh, that’s a pretty, that’s that especially sword does not responding well. Like you have long average milliseconds per weight on that. Then it’s a pretty good sign that, uh, you might want to have some more memory in there to alleviate, you know, time that you’re spending, spending waiting on disc, or if you’re hitting some of the poison weights around memory, like resource semaphore, resource semaphore query compile, that’s an even bigger argument to get some more memory in there. Cause well, that won’t solve the problem a hundred percent. Oftentimes you add more memory and you end up, uh, just giving, uh, memory grants, a bigger piece of a pie, bigger, bigger pie to ask for a bigger piece of, uh, it is, it can solve some lower level problems with a resource semaphore. So, uh, Darren says, yes, more memory doesn’t cost extra in licensing unless you need to break that magical boundary where you move to enterprise edition. And which case all of a sudden, all your core is magically cost $5,000 more licensing is so funny like that, right?
Like, like, like I’m like, I’m holding, like, let’s pretend that this تي 缶 What those & questions bearing You
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.
One is the loneliest number. Sometimes it’s also the hardest number of rows to get, depending on how you do it.
In this video, I’ll show you how a TOP 1 query can perform much differently from a query where you generate row numbers and look for the first one.
Thanks for watching!
Video Summary
In this video, I delve into an interesting performance discrepancy between two queries that produce the same results but exhibit vastly different execution times. The primary query uses a `CROSS APPLY` with `TOP 1`, which surprisingly took over a minute to return just 101 rows, despite minimal logical reads on the involved tables. By examining the execution plan and statistics, I highlight how an index spool operator was created behind the scenes by SQL Server, significantly impacting performance due to its single-threaded nature even in a parallel query context. To contrast this, I demonstrate a slightly modified version of the query that uses `ROW_NUMBER` instead, achieving a much faster execution time with similar logical reads but vastly reduced CPU and elapsed times. This comparison underscores how simple query rewrites can have substantial performance benefits.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data because Brent is lazy. And I was kind of enjoying my Saturday afternoon and writing some blog posts when I came across what I thought was an interesting difference between two queries that are written slightly differently, give you the same results but quite different performance. So I wanted to talk about that with you and unfortunately to talk about that with you I have to put this drink down to operate the computer. So, but that’s okay. because it’s a quick video and hopefully no one will notice. Now, I have this query here and the whole point of the query is to get the top 100 users by reputation and their most recent badge. And to do that I’m using cross apply with the top one over to the badges table. And if you look down in the corner you can probably see that this query ran for a little over a minute to return those 101 rows. That’s a pretty long time for not a lot of data.
Now, we can figure out why when we start looking at some different aspects of the query. And by different aspects I mean what happened with statistics time and IO and what happened in the query plan. Now, the CPU and elapsed time are almost the same which is a little bit weird because this is a parallel plan. Usually the whole point of a parallel plan is to use multiple threads to cut down on the total elapsed time. So you sacrifice using extra CPU to make the query overall run quicker. And you can also see that we didn’t do a lot of work against users or badges.
The users table we did 44,000 logical reads and the badges table we did about 50,000 logical reads. That’s not a lot of reads. The other thing we’re running into though is that we have this work table.
And this work table does a ton of logical reads. That’s about 24 million. So we have to ask ourselves where that came from.
And if we look over at the execution plan, it’ll become a little bit more obvious. Well, it’s really obvious to me and now it’s going to be obvious to you too. That work table comes from this index spool operator.
SQL Server wanted an index so badly on this data that it created an index behind your back up in tempdb. And it didn’t ask for an index. If you look at this top line here, there is no missing index request.
If we go and we look in the execution plan XML, there will be no missing index request. There is just this query running where SQL Server says, I’m going to create an index for you, you lazy bad DBA. What really stinks about this index spool?
Well, there’s a couple things that stink about it. One is that after this query runs, SQL Server will throw it away. And if this query runs again, or if this query runs a million times, every time this query runs, this spool will get created and thrown away. But what’s particularly nasty about these index spools is that if we go look at the properties, and we look at where all the rows line up across the parallel threads in the query, they all end up on one.
And it doesn’t matter how big this table is. It doesn’t matter, like if you use a different table, if I use seven different tables. Index spools build the index behind them single threaded.
That’s just the way it goes. So all eight million rows end up on one single thread. In this case, it’s thread three.
If I ran it a bunch of different times, they might end all end up on one different thread, but they would all always end up on one thread. That’s no good. We don’t like that.
And that’s basically what made this parallel query run like a serial query. Because this whole unit of work is done serially. And this is really where we spent the majority of the time in the query.
Well, that stinks. And you see this pretty frequently, specifically with cross-apply with a top one. And it’s because the optimizer can’t really unroll that.
And what I mean by unroll that is just turn it into a regular join. It’s going to use like kind of like the literal translation of cross-apply to go get a row and apply it to what’s down here. The optimizer is free to transform that into a regular join, but it doesn’t, especially when a top is involved.
Now, let’s contrast that with a query that’s written slightly differently. It’s still going to return the same results, but we’re going to use row number instead. We’re even still going to use cross-apply.
And we’re going to select the user ID and the name from the badges table. But this time we’re going to generate a row number over the same columns that we generated the top one with. And then we’re going to end up filtering out the results to only where row number equals one.
Now, remember that first query took a minute and three seconds to run. And if we go and execute this, it will be significantly faster. I forget how fast, but long enough for me to take a sip.
Now, that took six seconds. Why did that only take six seconds? Why did we end up with like, you know, you can see that it’s the same amount of reads here, the 44,000 and 49,000.
But for the CPU time and elapsed time, we did way better. That’s about, you know, there was like a tenth of the time. One percent of the time.
I’m not good. I’m not good at math no matter what. It doesn’t matter if it’s Saturday morning or not. If we look over in the execution plan, this plan is also parallel. But we don’t have any spooling operators.
You know, we, in this case, the optimizer was free to take that cross supply. And rather than do a nested loops row by row join, it was free to transform that into a hash join right here. And when we went and generated the row number over all the results of the badges table partitioned by user ID and ordered by the date column, we filtered out all of those rows, all of the rows that we weren’t using with this filter operator pretty early on.
So we still did it. And I’m not saying that this query is perfect and that we couldn’t tune things better and that we couldn’t make things better for this query. But it just goes to show you that sometimes a pretty simple rewrite can have pretty profound effects on a query.
And that, you know, sometimes that cross-apply with top is not always the best form of a query that can be written. Anyway, I’m going to go get back to the rest of this. I hope you enjoyed this.
I hope you learned something. And I will see you, I don’t know, maybe, maybe, maybe I’ll record something later that I won’t remember. I don’t know. We’ll see. Thanks for watching.
Video Summary
In this video, I delve into an interesting performance discrepancy between two queries that produce the same results but exhibit vastly different execution times. The primary query uses a `CROSS APPLY` with `TOP 1`, which surprisingly took over a minute to return just 101 rows, despite minimal logical reads on the involved tables. By examining the execution plan and statistics, I highlight how an index spool operator was created behind the scenes by SQL Server, significantly impacting performance due to its single-threaded nature even in a parallel query context. To contrast this, I demonstrate a slightly modified version of the query that uses `ROW_NUMBER` instead, achieving a much faster execution time with similar logical reads but vastly reduced CPU and elapsed times. This comparison underscores how simple query rewrites can have substantial performance benefits.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data because Brent is lazy. And I was kind of enjoying my Saturday afternoon and writing some blog posts when I came across what I thought was an interesting difference between two queries that are written slightly differently, give you the same results but quite different performance. So I wanted to talk about that with you and unfortunately to talk about that with you I have to put this drink down to operate the computer. So, but that’s okay. because it’s a quick video and hopefully no one will notice. Now, I have this query here and the whole point of the query is to get the top 100 users by reputation and their most recent badge. And to do that I’m using cross apply with the top one over to the badges table. And if you look down in the corner you can probably see that this query ran for a little over a minute to return those 101 rows. That’s a pretty long time for not a lot of data.
Now, we can figure out why when we start looking at some different aspects of the query. And by different aspects I mean what happened with statistics time and IO and what happened in the query plan. Now, the CPU and elapsed time are almost the same which is a little bit weird because this is a parallel plan. Usually the whole point of a parallel plan is to use multiple threads to cut down on the total elapsed time. So you sacrifice using extra CPU to make the query overall run quicker. And you can also see that we didn’t do a lot of work against users or badges.
The users table we did 44,000 logical reads and the badges table we did about 50,000 logical reads. That’s not a lot of reads. The other thing we’re running into though is that we have this work table.
And this work table does a ton of logical reads. That’s about 24 million. So we have to ask ourselves where that came from.
And if we look over at the execution plan, it’ll become a little bit more obvious. Well, it’s really obvious to me and now it’s going to be obvious to you too. That work table comes from this index spool operator.
SQL Server wanted an index so badly on this data that it created an index behind your back up in tempdb. And it didn’t ask for an index. If you look at this top line here, there is no missing index request.
If we go and we look in the execution plan XML, there will be no missing index request. There is just this query running where SQL Server says, I’m going to create an index for you, you lazy bad DBA. What really stinks about this index spool?
Well, there’s a couple things that stink about it. One is that after this query runs, SQL Server will throw it away. And if this query runs again, or if this query runs a million times, every time this query runs, this spool will get created and thrown away. But what’s particularly nasty about these index spools is that if we go look at the properties, and we look at where all the rows line up across the parallel threads in the query, they all end up on one.
And it doesn’t matter how big this table is. It doesn’t matter, like if you use a different table, if I use seven different tables. Index spools build the index behind them single threaded.
That’s just the way it goes. So all eight million rows end up on one single thread. In this case, it’s thread three.
If I ran it a bunch of different times, they might end all end up on one different thread, but they would all always end up on one thread. That’s no good. We don’t like that.
And that’s basically what made this parallel query run like a serial query. Because this whole unit of work is done serially. And this is really where we spent the majority of the time in the query.
Well, that stinks. And you see this pretty frequently, specifically with cross-apply with a top one. And it’s because the optimizer can’t really unroll that.
And what I mean by unroll that is just turn it into a regular join. It’s going to use like kind of like the literal translation of cross-apply to go get a row and apply it to what’s down here. The optimizer is free to transform that into a regular join, but it doesn’t, especially when a top is involved.
Now, let’s contrast that with a query that’s written slightly differently. It’s still going to return the same results, but we’re going to use row number instead. We’re even still going to use cross-apply.
And we’re going to select the user ID and the name from the badges table. But this time we’re going to generate a row number over the same columns that we generated the top one with. And then we’re going to end up filtering out the results to only where row number equals one.
Now, remember that first query took a minute and three seconds to run. And if we go and execute this, it will be significantly faster. I forget how fast, but long enough for me to take a sip.
Now, that took six seconds. Why did that only take six seconds? Why did we end up with like, you know, you can see that it’s the same amount of reads here, the 44,000 and 49,000.
But for the CPU time and elapsed time, we did way better. That’s about, you know, there was like a tenth of the time. One percent of the time.
I’m not good. I’m not good at math no matter what. It doesn’t matter if it’s Saturday morning or not. If we look over in the execution plan, this plan is also parallel. But we don’t have any spooling operators.
You know, we, in this case, the optimizer was free to take that cross supply. And rather than do a nested loops row by row join, it was free to transform that into a hash join right here. And when we went and generated the row number over all the results of the badges table partitioned by user ID and ordered by the date column, we filtered out all of those rows, all of the rows that we weren’t using with this filter operator pretty early on.
So we still did it. And I’m not saying that this query is perfect and that we couldn’t tune things better and that we couldn’t make things better for this query. But it just goes to show you that sometimes a pretty simple rewrite can have pretty profound effects on a query.
And that, you know, sometimes that cross-apply with top is not always the best form of a query that can be written. Anyway, I’m going to go get back to the rest of this. I hope you enjoyed this.
I hope you learned something. And I will see you, I don’t know, maybe, maybe, maybe I’ll record something later that I won’t remember. I don’t know. We’ll see. Thanks for watching.
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.
When you’re reading query plans, you can be faced with an overwhelming amount of information, and some of it is only circumstantially helpful.
Sometimes when I’m explaining query plans to people, I feel like a mechanic (not a Machanic) who just knows where to go when the engine makes a particular rattling noise.
That’s not the worst thing. If you know what to do when you hear the rattle next time, you learned something.
One particular source of what can be a nasty rattle is query plan operators that execute a lot.
Busy Killer Bees
I’m going to talk about my favorite example, because it can cause a lot of confusion, and can hide a lot of the work it’s doing behind what appears to be a friendly little operator.
Something to keep in mind is that I’m looking at the actual plans. If you’re looking at estimated/cached plans, the information you get back may be inaccurate, or may only be accurate for the cached version of the plan. A query plan reused by with parameters that require a different amount of work may have very different numbers.
Nested Loops
Let’s look at a Key Lookup example, because it’s easy to consume.
CREATE INDEX ix_whatever ON dbo.Votes(VoteTypeId);
SELECT v.VoteTypeId, v.BountyAmount
FROM dbo.Votes AS v
WHERE v.VoteTypeId = 8
AND v.BountyAmount = 100;
You’d think with “loops” in the name, you’d see the number of executions of the operator be the number of loops SQL Server thinks it’ll perform.
But alas, we don’t see that.
In a parallel plan, you may see the number of executions equal to the number of threads the query uses for the branch that the Nested Loops join executes in.
For instance, the above query runs at MAXDOP four, and coincidentally uses four threads for the parallel nested loops join. That’s because with parallel nested loops, each thread executes a serial version of the join independently. With stuff like a parallel scan, threads work more cooperatively.
Too Much Speed
If we re-run the same query at MAXDOP 1, the number of executions drops to 1 for the nested loops operator, but remains at 71,048 for the key lookup.
Beat Feet
But here we are at the very point! It’s the child operators of the nested loops join that show how many executions there were, not the nested loops join itself.
Weird, right?
Thanks for reading!
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.
(More unfortunate words were never spoken) I decided to offer a free three hour block of time to whomever named the cadre of under-performing queries we’re going to be looking at as part of my SQLBits precon.
For my @SQLBits precon, I have seven queries with very tricky problems.
They sniff, they spool, they defraud, they steal, they push, and generally run amok.
If you come up with a cunning name for this septet of villainy, you’ll win three free hours of consulting time with me.
If you’re not on Twitter, not following me, or just missed it in the sea of having better things to do, leave a comment with your idea.
I do occasionally like blog comments.
Thanks for reading!
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.
Because you’ve got the cash money to pay for Enterprise Edition, some nice hardware, and also Enterprise Edition on another server or two.
Maybe you have queries that need fresh data going to a sync replica, and queries that can withstand slightly older data going to an async replica.
Every week or every month, you want to be a dutiful data steward and see how your indexes get used. Or not used.
So you run Your Favorite Index Analysis Script® on the primary, and it looks like you’ve got a bunch of unused indexes.
Can you drop them?
Not By A Long Shot
You’ve still gotta look at how indexes are used on any readable copy. Yes, you read that right.
DMV data is not sent back and centralized on the primary. Not for indexes, wait stats, queries, file stats, or anything else you might care about.
If you wanna centralize that, it’s up to you (or your monitoring tool) to do it. That can make getting good feedback about your indexes tough.
Failovers Also Hurt
Once that happens, your DMV data is all murky.
Things have gotten all mixed in together, and there’s no way for you to know who did what and when.
AGs, especially readable ones, mean you need to take more into consideration when you’re tuning.
You also have to be especially conscious about who the primary is, and how long they’ve been the primary.
If you patch regularly (and you should be patching regularly), that data will get wiped out by reboots.
Now what?
If you use SQL Server’s DMVs for index tuning (and really, why wouldn’t you?), you need to take other copies of the data into account.
This isn’t just for AGs, either. You can offload reads to a log shipped secondary or a mirroring partner, too.
Perhaps in the future, these’ll be centralized for us, but for now that’s more work for you to do.
Thanks for reading!
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.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I delve into various topics related to database management and development. Starting off, I address a common issue where an application’s performance is blamed on the SQL Server or network, only for it to turn out that the real culprit was poor app design. We explore how simple file transfers between data centers can reveal latency issues, emphasizing the importance of practical testing over just theoretical analysis. Moving on, I discuss resources and training for advanced T-SQL, recommending the latest book by Itzik Ben-Gan and Adam Mechanic as a solid reference guide. The conversation then shifts to Microsoft’s managed instances, highlighting the lack of freely available resources and the financial barrier that prevents many from experimenting with these services. Throughout the video, I share my thoughts on potential solutions, such as reaching out to Microsoft for a free instance or considering a podcast format, while also acknowledging the time constraints and challenges in pursuing new projects.
Full Transcript
Are we live? We are live. We are live and lonely. It’s a weird Friday to be doing this though. I get it with me. Do do do do do, do do do do. People where are people? People, people, people. What if you threw an office hours and no one showed up? There’s one person here.
All right, off to a good start. If we accumulate one person per minute, this will be real lively, I think. To show my seriousness for my charity event, I have officially instituted the mustache.
It’s growing in. So by the time bits rolls around, it should be about down to here. But I’ll probably have to groom that in some way.
So, welcome, four of you. It’s nice to see you all. Oh, boy.
Where’d that go? I’ve lost chat. Where did chat go? Oh, man. Someone’s going to have to, like, tweet the questions at me or something.
Because now I don’t know how to get chat back. We had chat. Chat, where are you?
Chat. Chat. Oh, someone left. Someone didn’t want to. Oh, someone came back. Someone came back.
Thank you for coming back. So I’m going to have to do something kind of weird, I think. I’m going to have to go on. I’m going to have to go on this from my phone, I think.
So I can look at the chat window again. Because I have no idea where it went. And there’s no button that says bring back the chat. So bear with me for a second here while I fumble with this nonsense. Doot-a-doo.
Doot-a-doo. Yeah, there we go. Doot-a-doo.
Hey. There’s a live thing. All right. I can see me. All right. Yes. Mike says, nice stash. Thank you, Mike. I can see chat on my phone now.
So we’ve reached this bizarre level of recursion that I don’t even know how to deal with. Woo-hoo! All right. Let’s see.
Let me make this a little bit more tolerable for all of you. I’m going to hold my phone here. James says, best SQL Server trainer. Hands down. Thanks. You should tell that to Brent because he’d love to hear that. That would be the first thing I’d want to do.
Let’s see. I had to leave create a channel so I could chat. Weird. Yes, that is weird. I don’t understand why YouTube would make you do that. Right now, YouTube won’t even let me try to get the chat window back up without ending.
My live stream. So YouTube has some damn work to do on this interface because this is ridiculous. My sweater in a cold region.
Yes, I am in a cold region. I am in the Northeast. But thankfully, I am indoors. And thankfully, see that pipe in the corner right there? That thing gets absolutely nuclear, blazing hot in a way that human words can’t describe.
So even if I tried to get cold in here, I would fail miserably because of that pipe. That pipe… I mean, you could barbecue it.
If you wrap meat around that pipe, it would cook. It would cook through. It’s insane. It’s… It’s… I used to live in an apartment in Astoria, Queens. And when I lived there, the whole building was steam heat.
And during the winter, my room would get ridiculously hot because I had a big steam pipe in it. I had the radiator turned off. There was no radiator.
The radiator was like, no, screw it. The one time I turned the radiator on, it was like boiling. And during the winter, what I could do is have my bedroom window open, put a six-pack of beer in the windowsill. The beer would be nice and cold.
And my room would be temperate. And I could just sit there pleasantly drinking beer with the window open. Like sub-zero, like snowstorm, cold, freezing. It didn’t matter.
All winter long. Let’s do a barbecue next Friday. Yes. All right. We’ll do a barbecue next Friday. That sounds good to me. I like barbecues. Especially when I don’t have to go anywhere for them. It’s the best kind of barbecue.
It’s a stay-home barbecue. The stay-b-queue. The stab-by-queue? I don’t know. It’s all weird. It’s all very weird.
Da-da-da. Da-da-da-da-da. I’m looking at stuff. And I’m not ignoring you.
I promise. I promise I’m not ignoring you. I either stare at myself on YouTube or I look at to see if anyone said anything dumb about me on Twitter. I just bounce back and forth between that.
But you nice people who decided to show up today, why are you here? I don’t know. You can want to ask questions, James. You can ask questions.
What else would you like to do? It’s like office hours except lonelier. Let’s see here.
Let’s see. We’ll start with Marcy’s question. What is the best way to triage network performance if you put your app in a data center? What about…
What about… So… Like, do you mean, like, apps communicating with the SQL Server? Like, are you alive? Or do you mean, like, applications pulling data back and forth across the network? Because it’s two kind of different things there.
In that, like… Okay. So if… When I think about networking in a data center, I think about two things. I think about the path that data has to take from the SAN, right?
From the disks across the channels to get to your SQL Server, to get rid into memory, and then to end up in the application. And then I think about, like, you know, the bobs and weevils that the data has to get across to then go to the application. So there’s two different sort of types of networking that are at play there.
And what you got to be clear about is, like, which one you think the problem is. Because a lot of the times when people think that they have a problem with network performance going from the SQL Server to the app, the problem is really, like, that the app server itself is either underpowered, overloaded. I mean, I’ve seen app servers in balanced power mode that reduced, like, app time by, like, 30 or 40%.
And that was a nightmare. So there’s a lot of different… There are a lot of different angles you could approach that from that I would be more… I would be curious to hear more about.
So Darren says, do you do any work with Azure managed instances or MDs? What’s an MDs? I don’t know.
I don’t know what an MD is. So that might tell you how much work I do with Azure. But I like them. I just don’t have a free one to play with. If you have one that you want me to do something to, I’d be happy to work with it. I have nothing against them.
I think they’re lovely things. I think that the managed instance thing in particular is going to do a lot for Azure adoption as it matures as a product. Just like I would be able to do a lot if I ever matured as a person.
But I refuse. Okay. Treehouse SQL, that is. So, Marcy, start with wait stats. If you’re waiting a lot on async network I.O., then it is time to figure out what those apps are doing.
The other part of that picture is how the apps ingest data. So I’m going to tell a pretty funny story about one time. I know I’ve told this in the past, but I was working at a real job.
And we had this in-house bug tracking software. And the developers for it, like, redid the whole user interface. And they added a bunch of, like, pretty things to it.
So that when you went to, like, log a ticket or look at ticket activity, you could feel like you were looking at, like, a work of art. There were, like, animations. And things would, like, slide across the screen.
But when they rolled it out, everyone said, the SQL Server got really slow. And I was like, okay. Let’s look at the SQL Server. And, of course, I’m looking at the SQL Server, and the SQL Server is bored out of its mind.
It’s, like, barely registering wait stats. I’m like, all right. So let’s capture one of the queries that the app is running. And let’s see how long it just takes to hit it in SSMS.
So we do, and it returns immediately. I’m like, all right. So let’s use one of the app servers that still sees the old UX and see if that’s slow. So we do it over there.
And, like, the bare bones HTML ugly as sin, like, clunky rectangle button, like, gray button website, blazing fast there, too. So we had them disable, like, the new, like, CSS crazy animation stuff on the new one. And when we did stuff there, it was blazing fast, too.
So it was actually the way that the app ingested data and the things that the application did to present that data out to people that made things look slow and that made it look like there was a SQL Server problem or a network problem. But it was really just crappy app design. That’s fun, right?
Fun stuff. Let’s see. App is in a new data center. SQL did not move. So app. Well, Atlanta to Vermont.
That’s a pretty good hike. What, I mean, like, what happens if you, let’s start easy and let’s say what happens if you copy a file between, from one data center to another? Like, let’s just say you, like, want to drag and drop a file.
Let’s see how fast that goes. That’s what I would want to know. I would want to, like, you know, you could ping stuff and trace route and all that other, wire shark and whatever else. But, you know, take, like, a one gig file and just see how long it takes to copy from one thing to another.
That’s always kind of a fun way to do things. Let’s see. Let’s see what they say.
Oh, manage data. Oh, manage database. I see now. So Azure SQL DB, you called MD, you tricky, tricky son of a gun. All right. James says, I am planning on going from DBA to SQL developer.
What is the best training I can get for advanced T-SQL? Boy. So when I think about advanced T-SQL, there’s, like, a real wall that you hit.
Right. So, like, you can’t, there’s, like, no more complicated syntax to do something at some point. Right.
You just, like, what, a row number of windowing function. Nothing, like, there’s not a lot that’s really changed with T-SQL lately. And a lot of the problems that have been solved with it that are hard are pretty well written about. So if you think about stuff like bin packing, traversing hierarchies, doing, like, the last non-null, any sort of, like, ordered set stuff, there’s very few people who do that, or rather who write about that and write about it well.
I mean, really, you’re looking at it’s been gone. So what I always say is, buy the most recent book. Oh, it’s down here somewhere.
Buy the most recent book on T-SQL, which right now is this. And it’s backwards. Oh, man. So it’s called T-SQL querying.
It’s by Itzik Ben-Gahn and my friend Adam Mechanic. And I think that if you just have this book as a reference, you can go, you can get through a lot. Because there’s just, you know, good, thorough explanations of, like, how to solve a lot of common problems that do require more advanced T-SQL.
But it’s not like, you know, when you’re writing these queries, you need, like, there’s a more advanced join you can write. All right? It’s like, a join is a join, pretty much.
Right? You just need to figure out, do I need inner, outer, left, right, up, down, cross, cross apply or something. After that, it’s, you know, the syntax doesn’t, like, get infinitely more complex as you do more things. So I would start with a nice book that just gives you reference material on how to do certain stuff.
And then as you approach different problems, maybe that’s when things get more complicated. Let’s see. What else have you got?
Nothing. No other questions, really? All right. I guess I can start drinking then. No, I have to go to the gym. Maybe I won’t bother.
Maybe I’ll just start drinking. Go to the gym tomorrow. I have so many choices. All right. Any other questions? Anything else? Let’s see. Ooh, let’s see. Darren, firing stuff up. Do you know of any resources for managed instances?
No. So it depends on what you want to learn. Where Microsoft has kind of sadly fallen short is that kind of develop is like kind of like learning resources on stuff.
They used to have these virtual labs where you could go in and for free spin up like, like spin up and set up like a failover cluster availability group, log shipping, mirroring, all sorts of cool bells and whistles. They’re doing away with that and replacing it with something. I’m not really sure what it is.
But, you know, what makes it tough for me to like, you know, get on a high horse and say, hey, I really want to talk about this Microsoft product. Is it if I want to do anything with a managed instance, it’s going to cost me like $1,500 a month because there’s no developer managed instance. There’s no like, hey, you know, you might blog about this and get people to adopt it.
Here’s a managed instance that you can kick around in our there’s like like a fifth of a man of a VM in our billion dollar data center here. Go have fun with it. It would cost me like real money to play with a managed instance and to be able to write more stuff about it.
So I think part of the reason why you might not see as much written about those topics as you want is that it like unless someone has a job where their job is paying for like that hardware and they get to like work with or rather work like paying for that service. And they get to work with that service day in and day out because that’s all their business pay the bills. Oh, you’re kind of out of luck.
So if you know anyone at Microsoft who wants to give me a managed instance to to play with and blog about the greatness and awesomeness of sure. Let’s you can you can give them my email. You can give them my YouTube channel.
I would love to have a chat with them here. Let’s see. Yeah, there’s no playground. You know, that’s something that a lot of people don’t think about when they’re moving their infrastructure to the cloud is like. Like, you know, if you have dev and QA and UAT.
Down, like locally, there’s no dev and QA and UAT up in the cloud that all costs the same money. Right. Like, even like even like even if you say you put developer edition on you’re still paying.
Excuse me for a big, big old honking cloud instance. So that’s a lot of fun to think about in that way. Have you thought about a podcast?
Yeah, except. You know. What what terrifies me about a podcast is that a it might be expensive.
Like, I don’t I don’t know what goes into making a podcast. I don’t know how to like. I just haven’t.
Yeah. And in my head, sure, I would like to do it. But then when I look at the amount of time I have in a day and the amount of stuff that I have to do to try to, you know, make make a dime here and there. I’m sort of like, well, I’d love to have a podcast.
But if unless there’s like a button I can push that makes a podcast for me, I don’t know if I’m going to do it just yet. So maybe in time, maybe maybe when I get some more free time, like after SQL bits. And I do my my pre con there and, you know, hopefully survive everything.
Get to it. But for now, I’m just going to I’m just going to do these. I’m also working under this like sort of like blanket of terror that I’ll show up and there will be no one here. Like, I’m very grateful for the 10 of you who chose to show up and ask me questions and talk to me about stuff.
But, man, if I if I ever if I’m ever here and it’s like like 10 past and there’s two people and no one’s asking a question and I’m staring at my screen uncomfortably making doot doots, then, you know, I don’t want to be like no podcast this week. No one showed up. Like, you know, because what’s challenging about the YouTube format is that there’s no like screen share.
So if like there were two people here who weren’t asking questions, I couldn’t switch it over to now I’m going to show you something. It was like I would have to I don’t know, do something totally different. I would have to use like a like a paid platform, like a go to webinar or something like that.
So lots of stuff that I would have to do differently, but not necessarily in a bad way. Just I’ll get a little bit more infrastructure in place and things will go from there. I think I think that’s a nice way of saying all the stuff that I just said, you know, a little bit more infrastructure in place and then then we’re good to go.
See here. Steve asks, I recently ran SP Blitzcache and a top offender has warnings. We couldn’t find a plan for this query.
The proc is a 2200 line nightmare. Any recommendations on next steps, diagnose, resource? So every time I see that, it’s either because the plan has a recompile or like the store procedure, the plan had a recompile hint on it or something.
And like see what’s ever logged some metrics about it, but didn’t keep the plan or the plan recompile for some other reason. And sometimes like if you, when you see that there should be a bunch of reasons listed in there, why there might not be a plan. So what I would do is just grab the query text or like the store procedure name from one of the other columns and see if you can get an estimated plan for it there.
You know, one of the kind of things that I always wish that I could have put into Blitzcache that I didn’t get a chance to was like just some like validation on if a query plan is there. Because, you know, well, it’s good to be able to say, hey, this is a crazy, you know, awful thing that is executing and doing stuff. If there’s no query plan for it, it kind of defeats the point of a Blitzcache.
So I always wanted to do something where like if like a certain amount of the values were null that came back or whatever, then like we would like, like it would change the value of top. So we would bring back some more plans to, you know, to analyze because if you bring, if like you say top people is 10 and five of the lines are we couldn’t find a plan for this query. And you’re looking at like maybe five query, maybe like five plans that, you know, you know, kind of like limited value.
Like they’re probably at the end too. It’s like kind of stinks. Yeah.
Yeah. See, there’s a lot of different reasons why plans might not be in the cache when we go look and they all kind of stink. Like what, so what I would say is if it’s a big 2200 lines for a procedure, like you’re saying most likely. So there’s a limit to the number of nodes that can exist in XML.
It’s like 128. And after 128, you have to run a special command to get the plan XML from another place and you can’t render it in any way. It’s useful.
It’s a lot of fun. So like you can’t like cast it to XML because it’s too big and it throws errors and you can’t do anything good with it. So there should have been a query in there that said, like gave you like a select star from like a query plan text DMV for the SQL handle that, or plan handle that, that didn’t bring back a plan in BlitzCache. And by the way, BlitzCache support now is 99 cents a minute.
So you owe me like three bucks. Damn. I underpriced myself.
I should have said 299 for the first minute. Could have tripled my money. Damn. Let’s see here. What is the best software source control for SQL Server? I mean, look, Redgate kind of runs away with everything right now.
You know, I don’t, I, I, I have issues with some of their products, but I think for stuff like that, Redgate runs away with it. Hands down. I wouldn’t, I wouldn’t do it.
I wouldn’t go with anything else right now. You know, I would, I would probably get it. I would probably use a different monitoring tool, but for source control and DevOps type stuff, I don’t think that I would trust anyone other than Redgate. At this point.
You know, and that’s, and like, and that’s, you know, just based on, you know, like surveying kind of who does stuff, how involved they are with the SQL Server community. You know, the number of people using them, the available training and kind of expertise on it. Like, this just like Redgate just, you know, just stole the ball from everyone else.
It was just kind of like standing there, holding it over their head. Like, so I just, you know, whatever, whatever you need to do, I would, I would go to them first. So far as I know, they are, if you run into something that, you know, if you have some scenario that one of their products just is not addressing, they are pretty, pretty, pretty responsive.
And, you know, getting that kind of stuff put together, especially if you have a lot of money. Or like, or if you have like a company name that like looks good for them to say, hey, we work with like awesome charity group, corp, or something. I don’t know.
Lots of fun stuff. All right. Do, do, do, do, do, do. I don’t need any of this stuff. All right.
Let’s get rid of some stuff on my phone that was blinking and making YouTube questions not show up properly. Next week, I promise it will be seamless because I won’t click the wrong button near the participants window to make everyone disappear. I apologize.
I like sort of accidentally raptured all of you and sent you to a phantom zone or something. But I’m able to join you from my phone. We have a link.
It’s coming to the light, I guess. All right. Do we have any other questions from the magnificent 10 of you who have shown up? Let’s see here.
Darren says, have you ever worked with in-memory OLTP much, exploring some solutions using it? No, and only because I never got a chance to. So it’s a really, it’s a really niche feature as far as what I think it’s a good fit for.
And all of the use cases that I’ve seen for it have been for incredibly high rate data ingestion applications where the data that you care about doesn’t hang around long. So think about like an online gambling type thing where you have a bunch of events and people want to bet on events and do stuff. But as soon as that event is over, like no one’s going to go back and care about it, right?
Like events over, done, payouts. So when you want to ingest all of that like really high value data that needs to happen immediately, in-memory OLTP can be great for that. And then as soon as an event is over, you kind of flush it out to on disk tables where, you know, people can still get to it, but it’s not going to be, you know, like blazing latch-free, lock-free stuff.
And the reason that it’s really good for that is because with the high rate of data ingestion stuff is that that’s where the lock-free thing is really cool because it’s all of those concurrent inserts and updates and stuff that really need to happen quickly. If your problem is like, well, we need this like data warehouse report to be faster, that’s not a good use case for in-memory OLTP. And if your problem is like we have 5,000 transactions a minute and they’re kind of slow, then you don’t need in-memory OLTP.
You need a much more basic level of like tuning and indexing query stuff because the in-memory OLTP stuff, it really shines, I would say, like the 20, 25, 30,000 transactions a second or a minute or some ungodly number where, you know, you just really need to bang stuff in quick and no one can wait. You need all that like, it was like grandma betting 50 cents on Greyhound. Let’s see here.
Who is your pick for the Super Bowl? What teams are in the Super Bowl? You’d have to tell me that first and then I’ll pick. I have no idea right now. I pay almost no attention to any sports.
Sorry. And I’m not anti-sport. I like watching sports in bars and I like watching sports.
I like watching sports highlights because I think anyone who can make millions of dollars doing that kind of stuff because they’re just that good at it is pretty fun to watch. But, yeah, I have two kids and a wife and the amount of sports watching that goes on in my house is limited to the first like week to month when the Mets don’t suck or when there’s like a lot of anticipation that the Mets might do well this year. And then the Mets tank and no one watches baseball anymore.
So that’s about where my cutoff is. Let’s see here. I’m starting to use more clustered columnstore indexes. Yes.
Anything we should be careful of or anything cool we might be missing. So I’m going to go against nearly everything that I’ve ever said about index maintenance when I talk about columnstore and that it is absolutely positively vital that you maintain your columnstore indexes. There’s all sorts of stuff with like gravestones and deltas and compression and dictionaries and like all sorts of things that if you don’t have your columnstore indexes maintained correctly, you can really boot performance on it.
Joe Obish. I keep telling him to blog about it, but he doesn’t. But maybe he finally will.
But at his talk at pass over last year, which might be out on video now, I’m not sure yet. But at his talk at pass last year, he went into a bit how if, you know, you load a bunch of data into your columnstore indexes, but it’s like loaded inefficiently or like data types are weird, then you can end up with like really, really bad query performance because you don’t get like good dictionary compression or like run length encoding and all the other stuff that goes into making query store. Really, really fast.
So like, like, you think about the number like what makes it what makes columnstore really powerful right now is batch mode. So you work on batches of rows at a time rather than like single row at a time like you do with rowstore indexes. And when you have like, misloaded, let’s call them misloaded columnstore indexes, you end up working on far fewer batches because far fewer of them can fit in like an instruction at a time.
But Joe Obish is the man to watch for that. I think, you know, Nico, I’m pretty sure he has some posts on it. If you want to dig through his, his, his like 150 post archive on columnstore, but that’s, that’s the first thing that I would think of.
Um, so aside from maintenance, uh, you know, being very, very mindful of data types and, um, you know, when you, when you see, when you, or when you’re working with columnstore, there are like certain op like plan operators that you just want to avoid. Like constantly like nested loops, merge joins, uh, sorts, um, uh, anything like when operators execute in row mode rather than batch mode for some, for whatever reason, it’s a lot of stuff that can go wonky with those queries. So what, but what I love columnstore and I love what they’ve been doing with it.
There’s still a lot of, you know, manual labor type stuff that you, you have to do when you’re, when your queries are on to make sure that you’re getting the best performance of it. Um, everything is better in bars. Yes.
Everything absolutely is better in bars. Except sleep. Sleeping in bars is not, not better. It’ll get you thrown out pretty quickly. All right.
Any other questions? Anyone else? Fire something fun at me. Let’s see. I don’t know. I, I, I was wondering if I was, uh, no, nothing on Twitter. Okay.
So sometimes I always think that someone might ask a question on Twitter if they can’t show up here on the YouTube channel. Uh, let’s see. Some deranged creep wants to know my age, sex and location. And I’m just going to call, I’m just going to call in the Chris Hansen patrol on you.
None of that. Uh, am I excited about bits? Yes, I do have the costume and I’m, I’m, I’m disappointed that you didn’t notice that the picture that I put on Twitter about the costume because it is fully loaded. It is here.
The only thing that I couldn’t get is, so what’s funny is when I was, when I was talking to Andy Mallon about, about like the, the costume that I was buying, he, he was all excited. He was just like, he, he thought he like, he had like a, like a, like a dead on thing in mind. I was like, look, I can do a lot of it.
And he was like, well, did, did you get a racer back tank top? And I was like, I don’t know what that is. I bought like regular tank tops, which I’m still going to look fat in, but I can’t like imagine it like, like a racer back tank top. And then like, I got all, I got all nervous and I was like, uh, Hey, uh, I’m going to go look on Amazon for a racer back tank top.
And I don’t think that a clothing company has made a racer back tank top for men since Freddie Mercury wore one. Um, and even at that juncture, he might have just worn one that was made for a woman because he was a, he was a pretty slender fella, but not me. And like, I, I tried to look at, uh, like, you know, women’s sizes that, that might, might fit a man of my stature.
And, um, then I guess got depressed. I didn’t want to get into that. Uh, bringing up, hoping I’d bring it up for a preview.
Uh, I’d have to go get it. I don’t have it in my office. It’s the one thing that I managed to talk my wife into not making me keep in the office because everything else that might vaguely belong to me is jammed into my office. It is crazy.
Do you have a pick of him? I can, oh, geez, Louise. Let’s see here. How can I do this? Uh, all right. Let’s see. Uh, where is, how do I get to a browser now? This is very challenging.
You guys are pushing the limits of my, my technical ability here. Freddie Mercury. Live aid.
Come on, baby. Come on. Don’t fail me now. There we go. Images. No, not videos. Images. Come on, phone.
Come on. There we go. All right. So this is a very close approximation of what I will look like at SQL bits. Uh, I can’t do justice to his armpit hair. And, um, I’m, I’m not going to be able to lose 120 pounds, but I have that outfit pretty well nailed.
Except for the racer back tank top. That’s just going to be a regular tank top. I don’t know if I can tuck mine in.
That might, that might get dangerous. I don’t know how that’s going to go. That might not be a great idea. Excuse me. Jeez Louise. It is dry in here. All right.
Let’s see. Get back into the old YouTube. Come on, baby. There we go. Yeah. Ah, practice the pose. Live stream, picture and picture.
Thank you. That is the one thing that I have managed to get right in my time on YouTube is live stream, picture and picture. Uh, when I tried to schedule these, starting this up was the most painful thing I’ve ever done. I had to pay 10 bucks for an encoder or a decoder called like Wirecast.
And every time I went to start it up, it would tell me that my camera didn’t work. And there would be like, like blue screen, like not like computer blue screens, but it would just show me like a blue screen preview. And it was terrible.
So like, but like when I just hit like the go live button, it’s dead simple. When I try to schedule these, it’s miserable. I’m going to have to do something really. Uh, let’s see.
Any other, any other fun questions? You creeps staring at my poor office. Let’s see.
Zach asks. No, no. That was the same person. Practice the pose. No. I mean, it’s just the arm up, right? This isn’t a good camera for that, for me to practice. It’ll be fun.
It’ll be fine. Let’s see. We have, we do have, we have a question or maybe we have a statement. I don’t know. We’ll figure it out in a minute. Sam says, uh, by tuning indexes on a secondary moving reporting to read only secondaries are really popular movement in my workplace.
I’m concerned about how to manage the disparate index needs. Yeah. Uh, I, I wrote a post about this recently that is scheduled.
I think for like two weeks from now. Blogging my butt off. Uh, but it’s, you know, it, it’s, it’s tough to like, depending on how you’re replicating the data, you know, you, you, you usually can’t have a different set of indexes on a secondary. From the, what you have on the primary.
And what makes things even more challenging. And I think this might be the third week in a row that I’ve said this is that, uh, the index DMVs on like readable secondaries don’t migrate their data back to the primary. So you have a really hard time figuring out like what indexes are good for anything.
It’s like looking around like, uh, okay, this one looks good here. This one looks good there. I don’t, you know, it all just goes to heck at some point. Uh, and it would be, it would be nice if there was like something that coagulated all that DMV data, but there is nothing at this point.
You would have, you would have, you are stuck doing that on your own, writing your own scripts to push data around. Yeah. But yeah.
Uh, so like, unless you use some form of like capital R replication, right? By that, I mean like not AGs or log shipping or mirroring where you can apply a script afterwards to make different indexes on a, on a, on a secondary available than what are just on the primary. Uh, yeah.
Kind of out of luck on that. And that’s, and that’s too bad because I know that with availability groups, at least on readable replicas, SQL Server is able to create like temporary statistics. So it would be nice if you could create temporary indexes too.
That would be fun. Aggregate, not quite. No, I like coagulate better. I think coagulate sounds better as a word. Aggregate.
Aggregate just sounds aggressive and angry. Not fun. Coagulate. It’s like, we’re going to go, we’re going to go have fun. We’re doing it together. Co-agulate. A bloody mess.
Why? Lots of things coagulate. Pancake batter. Uh, fat. I don’t know.
What else? What else coagulates? I’m trying to think of something that coagulates in a pleasant way now and I’m starting to see your point. Maybe coagulate isn’t good either. Maybe there’s no way, there’s no good way, no good way to say you bring things together. There’s no good single word for that.
They’re all just ugly. All right. Uh, anything else? Any other fun questions? Observations?
Hmm. Hmm. Hmm. All right, folks. Uh, thank you for joining me. Uh, I appreciate all of you showing up and asking stuff. I will be back next week at the same time doing the same thing.
And hopefully you’ll be able to make it then. I’ll see you next time. Thank you. And, uh, next time I will have the Freddie Mercury costume ready to show you. I think we’re getting, we’re getting close enough to bits now where I can, I can, I can do the reveal.
So I will see you next week. Not dressed up, but I’ll, I’ll have it. I’ll have it. I’ll tease you a little. Maybe I’ll wear some little shorts or something.
Bye.
Video Summary
In this video, I delve into various topics related to database management and development. Starting off, I address a common issue where an application’s performance is blamed on the SQL Server or network, only for it to turn out that the real culprit was poor app design. We explore how simple file transfers between data centers can reveal latency issues, emphasizing the importance of practical testing over just theoretical analysis. Moving on, I discuss resources and training for advanced T-SQL, recommending the latest book by Itzik Ben-Gan and Adam Mechanic as a solid reference guide. The conversation then shifts to Microsoft’s managed instances, highlighting the lack of freely available resources and the financial barrier that prevents many from experimenting with these services. Throughout the video, I share my thoughts on potential solutions, such as reaching out to Microsoft for a free instance or considering a podcast format, while also acknowledging the time constraints and challenges in pursuing new projects.
Full Transcript
Are we live? We are live. We are live and lonely. It’s a weird Friday to be doing this though. I get it with me. Do do do do do, do do do do. People where are people? People, people, people. What if you threw an office hours and no one showed up? There’s one person here.
All right, off to a good start. If we accumulate one person per minute, this will be real lively, I think. To show my seriousness for my charity event, I have officially instituted the mustache.
It’s growing in. So by the time bits rolls around, it should be about down to here. But I’ll probably have to groom that in some way.
So, welcome, four of you. It’s nice to see you all. Oh, boy.
Where’d that go? I’ve lost chat. Where did chat go? Oh, man. Someone’s going to have to, like, tweet the questions at me or something.
Because now I don’t know how to get chat back. We had chat. Chat, where are you?
Chat. Chat. Oh, someone left. Someone didn’t want to. Oh, someone came back. Someone came back.
Thank you for coming back. So I’m going to have to do something kind of weird, I think. I’m going to have to go on. I’m going to have to go on this from my phone, I think.
So I can look at the chat window again. Because I have no idea where it went. And there’s no button that says bring back the chat. So bear with me for a second here while I fumble with this nonsense. Doot-a-doo.
Doot-a-doo. Yeah, there we go. Doot-a-doo.
Hey. There’s a live thing. All right. I can see me. All right. Yes. Mike says, nice stash. Thank you, Mike. I can see chat on my phone now.
So we’ve reached this bizarre level of recursion that I don’t even know how to deal with. Woo-hoo! All right. Let’s see.
Let me make this a little bit more tolerable for all of you. I’m going to hold my phone here. James says, best SQL Server trainer. Hands down. Thanks. You should tell that to Brent because he’d love to hear that. That would be the first thing I’d want to do.
Let’s see. I had to leave create a channel so I could chat. Weird. Yes, that is weird. I don’t understand why YouTube would make you do that. Right now, YouTube won’t even let me try to get the chat window back up without ending.
My live stream. So YouTube has some damn work to do on this interface because this is ridiculous. My sweater in a cold region.
Yes, I am in a cold region. I am in the Northeast. But thankfully, I am indoors. And thankfully, see that pipe in the corner right there? That thing gets absolutely nuclear, blazing hot in a way that human words can’t describe.
So even if I tried to get cold in here, I would fail miserably because of that pipe. That pipe… I mean, you could barbecue it.
If you wrap meat around that pipe, it would cook. It would cook through. It’s insane. It’s… It’s… I used to live in an apartment in Astoria, Queens. And when I lived there, the whole building was steam heat.
And during the winter, my room would get ridiculously hot because I had a big steam pipe in it. I had the radiator turned off. There was no radiator.
The radiator was like, no, screw it. The one time I turned the radiator on, it was like boiling. And during the winter, what I could do is have my bedroom window open, put a six-pack of beer in the windowsill. The beer would be nice and cold.
And my room would be temperate. And I could just sit there pleasantly drinking beer with the window open. Like sub-zero, like snowstorm, cold, freezing. It didn’t matter.
All winter long. Let’s do a barbecue next Friday. Yes. All right. We’ll do a barbecue next Friday. That sounds good to me. I like barbecues. Especially when I don’t have to go anywhere for them. It’s the best kind of barbecue.
It’s a stay-home barbecue. The stay-b-queue. The stab-by-queue? I don’t know. It’s all weird. It’s all very weird.
Da-da-da. Da-da-da-da-da. I’m looking at stuff. And I’m not ignoring you.
I promise. I promise I’m not ignoring you. I either stare at myself on YouTube or I look at to see if anyone said anything dumb about me on Twitter. I just bounce back and forth between that.
But you nice people who decided to show up today, why are you here? I don’t know. You can want to ask questions, James. You can ask questions.
What else would you like to do? It’s like office hours except lonelier. Let’s see here.
Let’s see. We’ll start with Marcy’s question. What is the best way to triage network performance if you put your app in a data center? What about…
What about… So… Like, do you mean, like, apps communicating with the SQL Server? Like, are you alive? Or do you mean, like, applications pulling data back and forth across the network? Because it’s two kind of different things there.
In that, like… Okay. So if… When I think about networking in a data center, I think about two things. I think about the path that data has to take from the SAN, right?
From the disks across the channels to get to your SQL Server, to get rid into memory, and then to end up in the application. And then I think about, like, you know, the bobs and weevils that the data has to get across to then go to the application. So there’s two different sort of types of networking that are at play there.
And what you got to be clear about is, like, which one you think the problem is. Because a lot of the times when people think that they have a problem with network performance going from the SQL Server to the app, the problem is really, like, that the app server itself is either underpowered, overloaded. I mean, I’ve seen app servers in balanced power mode that reduced, like, app time by, like, 30 or 40%.
And that was a nightmare. So there’s a lot of different… There are a lot of different angles you could approach that from that I would be more… I would be curious to hear more about.
So Darren says, do you do any work with Azure managed instances or MDs? What’s an MDs? I don’t know.
I don’t know what an MD is. So that might tell you how much work I do with Azure. But I like them. I just don’t have a free one to play with. If you have one that you want me to do something to, I’d be happy to work with it. I have nothing against them.
I think they’re lovely things. I think that the managed instance thing in particular is going to do a lot for Azure adoption as it matures as a product. Just like I would be able to do a lot if I ever matured as a person.
But I refuse. Okay. Treehouse SQL, that is. So, Marcy, start with wait stats. If you’re waiting a lot on async network I.O., then it is time to figure out what those apps are doing.
The other part of that picture is how the apps ingest data. So I’m going to tell a pretty funny story about one time. I know I’ve told this in the past, but I was working at a real job.
And we had this in-house bug tracking software. And the developers for it, like, redid the whole user interface. And they added a bunch of, like, pretty things to it.
So that when you went to, like, log a ticket or look at ticket activity, you could feel like you were looking at, like, a work of art. There were, like, animations. And things would, like, slide across the screen.
But when they rolled it out, everyone said, the SQL Server got really slow. And I was like, okay. Let’s look at the SQL Server. And, of course, I’m looking at the SQL Server, and the SQL Server is bored out of its mind.
It’s, like, barely registering wait stats. I’m like, all right. So let’s capture one of the queries that the app is running. And let’s see how long it just takes to hit it in SSMS.
So we do, and it returns immediately. I’m like, all right. So let’s use one of the app servers that still sees the old UX and see if that’s slow. So we do it over there.
And, like, the bare bones HTML ugly as sin, like, clunky rectangle button, like, gray button website, blazing fast there, too. So we had them disable, like, the new, like, CSS crazy animation stuff on the new one. And when we did stuff there, it was blazing fast, too.
So it was actually the way that the app ingested data and the things that the application did to present that data out to people that made things look slow and that made it look like there was a SQL Server problem or a network problem. But it was really just crappy app design. That’s fun, right?
Fun stuff. Let’s see. App is in a new data center. SQL did not move. So app. Well, Atlanta to Vermont.
That’s a pretty good hike. What, I mean, like, what happens if you, let’s start easy and let’s say what happens if you copy a file between, from one data center to another? Like, let’s just say you, like, want to drag and drop a file.
Let’s see how fast that goes. That’s what I would want to know. I would want to, like, you know, you could ping stuff and trace route and all that other, wire shark and whatever else. But, you know, take, like, a one gig file and just see how long it takes to copy from one thing to another.
That’s always kind of a fun way to do things. Let’s see. Let’s see what they say.
Oh, manage data. Oh, manage database. I see now. So Azure SQL DB, you called MD, you tricky, tricky son of a gun. All right. James says, I am planning on going from DBA to SQL developer.
What is the best training I can get for advanced T-SQL? Boy. So when I think about advanced T-SQL, there’s, like, a real wall that you hit.
Right. So, like, you can’t, there’s, like, no more complicated syntax to do something at some point. Right.
You just, like, what, a row number of windowing function. Nothing, like, there’s not a lot that’s really changed with T-SQL lately. And a lot of the problems that have been solved with it that are hard are pretty well written about. So if you think about stuff like bin packing, traversing hierarchies, doing, like, the last non-null, any sort of, like, ordered set stuff, there’s very few people who do that, or rather who write about that and write about it well.
I mean, really, you’re looking at it’s been gone. So what I always say is, buy the most recent book. Oh, it’s down here somewhere.
Buy the most recent book on T-SQL, which right now is this. And it’s backwards. Oh, man. So it’s called T-SQL querying.
It’s by Itzik Ben-Gahn and my friend Adam Mechanic. And I think that if you just have this book as a reference, you can go, you can get through a lot. Because there’s just, you know, good, thorough explanations of, like, how to solve a lot of common problems that do require more advanced T-SQL.
But it’s not like, you know, when you’re writing these queries, you need, like, there’s a more advanced join you can write. All right? It’s like, a join is a join, pretty much.
Right? You just need to figure out, do I need inner, outer, left, right, up, down, cross, cross apply or something. After that, it’s, you know, the syntax doesn’t, like, get infinitely more complex as you do more things. So I would start with a nice book that just gives you reference material on how to do certain stuff.
And then as you approach different problems, maybe that’s when things get more complicated. Let’s see. What else have you got?
Nothing. No other questions, really? All right. I guess I can start drinking then. No, I have to go to the gym. Maybe I won’t bother.
Maybe I’ll just start drinking. Go to the gym tomorrow. I have so many choices. All right. Any other questions? Anything else? Let’s see. Ooh, let’s see. Darren, firing stuff up. Do you know of any resources for managed instances?
No. So it depends on what you want to learn. Where Microsoft has kind of sadly fallen short is that kind of develop is like kind of like learning resources on stuff.
They used to have these virtual labs where you could go in and for free spin up like, like spin up and set up like a failover cluster availability group, log shipping, mirroring, all sorts of cool bells and whistles. They’re doing away with that and replacing it with something. I’m not really sure what it is.
But, you know, what makes it tough for me to like, you know, get on a high horse and say, hey, I really want to talk about this Microsoft product. Is it if I want to do anything with a managed instance, it’s going to cost me like $1,500 a month because there’s no developer managed instance. There’s no like, hey, you know, you might blog about this and get people to adopt it.
Here’s a managed instance that you can kick around in our there’s like like a fifth of a man of a VM in our billion dollar data center here. Go have fun with it. It would cost me like real money to play with a managed instance and to be able to write more stuff about it.
So I think part of the reason why you might not see as much written about those topics as you want is that it like unless someone has a job where their job is paying for like that hardware and they get to like work with or rather work like paying for that service. And they get to work with that service day in and day out because that’s all their business pay the bills. Oh, you’re kind of out of luck.
So if you know anyone at Microsoft who wants to give me a managed instance to to play with and blog about the greatness and awesomeness of sure. Let’s you can you can give them my email. You can give them my YouTube channel.
I would love to have a chat with them here. Let’s see. Yeah, there’s no playground. You know, that’s something that a lot of people don’t think about when they’re moving their infrastructure to the cloud is like. Like, you know, if you have dev and QA and UAT.
Down, like locally, there’s no dev and QA and UAT up in the cloud that all costs the same money. Right. Like, even like even like even if you say you put developer edition on you’re still paying.
Excuse me for a big, big old honking cloud instance. So that’s a lot of fun to think about in that way. Have you thought about a podcast?
Yeah, except. You know. What what terrifies me about a podcast is that a it might be expensive.
Like, I don’t I don’t know what goes into making a podcast. I don’t know how to like. I just haven’t.
Yeah. And in my head, sure, I would like to do it. But then when I look at the amount of time I have in a day and the amount of stuff that I have to do to try to, you know, make make a dime here and there. I’m sort of like, well, I’d love to have a podcast.
But if unless there’s like a button I can push that makes a podcast for me, I don’t know if I’m going to do it just yet. So maybe in time, maybe maybe when I get some more free time, like after SQL bits. And I do my my pre con there and, you know, hopefully survive everything.
Get to it. But for now, I’m just going to I’m just going to do these. I’m also working under this like sort of like blanket of terror that I’ll show up and there will be no one here. Like, I’m very grateful for the 10 of you who chose to show up and ask me questions and talk to me about stuff.
But, man, if I if I ever if I’m ever here and it’s like like 10 past and there’s two people and no one’s asking a question and I’m staring at my screen uncomfortably making doot doots, then, you know, I don’t want to be like no podcast this week. No one showed up. Like, you know, because what’s challenging about the YouTube format is that there’s no like screen share.
So if like there were two people here who weren’t asking questions, I couldn’t switch it over to now I’m going to show you something. It was like I would have to I don’t know, do something totally different. I would have to use like a like a paid platform, like a go to webinar or something like that.
So lots of stuff that I would have to do differently, but not necessarily in a bad way. Just I’ll get a little bit more infrastructure in place and things will go from there. I think I think that’s a nice way of saying all the stuff that I just said, you know, a little bit more infrastructure in place and then then we’re good to go.
See here. Steve asks, I recently ran SP Blitzcache and a top offender has warnings. We couldn’t find a plan for this query.
The proc is a 2200 line nightmare. Any recommendations on next steps, diagnose, resource? So every time I see that, it’s either because the plan has a recompile or like the store procedure, the plan had a recompile hint on it or something.
And like see what’s ever logged some metrics about it, but didn’t keep the plan or the plan recompile for some other reason. And sometimes like if you, when you see that there should be a bunch of reasons listed in there, why there might not be a plan. So what I would do is just grab the query text or like the store procedure name from one of the other columns and see if you can get an estimated plan for it there.
You know, one of the kind of things that I always wish that I could have put into Blitzcache that I didn’t get a chance to was like just some like validation on if a query plan is there. Because, you know, well, it’s good to be able to say, hey, this is a crazy, you know, awful thing that is executing and doing stuff. If there’s no query plan for it, it kind of defeats the point of a Blitzcache.
So I always wanted to do something where like if like a certain amount of the values were null that came back or whatever, then like we would like, like it would change the value of top. So we would bring back some more plans to, you know, to analyze because if you bring, if like you say top people is 10 and five of the lines are we couldn’t find a plan for this query. And you’re looking at like maybe five query, maybe like five plans that, you know, you know, kind of like limited value.
Like they’re probably at the end too. It’s like kind of stinks. Yeah.
Yeah. See, there’s a lot of different reasons why plans might not be in the cache when we go look and they all kind of stink. Like what, so what I would say is if it’s a big 2200 lines for a procedure, like you’re saying most likely. So there’s a limit to the number of nodes that can exist in XML.
It’s like 128. And after 128, you have to run a special command to get the plan XML from another place and you can’t render it in any way. It’s useful.
It’s a lot of fun. So like you can’t like cast it to XML because it’s too big and it throws errors and you can’t do anything good with it. So there should have been a query in there that said, like gave you like a select star from like a query plan text DMV for the SQL handle that, or plan handle that, that didn’t bring back a plan in BlitzCache. And by the way, BlitzCache support now is 99 cents a minute.
So you owe me like three bucks. Damn. I underpriced myself.
I should have said 299 for the first minute. Could have tripled my money. Damn. Let’s see here. What is the best software source control for SQL Server? I mean, look, Redgate kind of runs away with everything right now.
You know, I don’t, I, I, I have issues with some of their products, but I think for stuff like that, Redgate runs away with it. Hands down. I wouldn’t, I wouldn’t do it.
I wouldn’t go with anything else right now. You know, I would, I would probably get it. I would probably use a different monitoring tool, but for source control and DevOps type stuff, I don’t think that I would trust anyone other than Redgate. At this point.
You know, and that’s, and like, and that’s, you know, just based on, you know, like surveying kind of who does stuff, how involved they are with the SQL Server community. You know, the number of people using them, the available training and kind of expertise on it. Like, this just like Redgate just, you know, just stole the ball from everyone else.
It was just kind of like standing there, holding it over their head. Like, so I just, you know, whatever, whatever you need to do, I would, I would go to them first. So far as I know, they are, if you run into something that, you know, if you have some scenario that one of their products just is not addressing, they are pretty, pretty, pretty responsive.
And, you know, getting that kind of stuff put together, especially if you have a lot of money. Or like, or if you have like a company name that like looks good for them to say, hey, we work with like awesome charity group, corp, or something. I don’t know.
Lots of fun stuff. All right. Do, do, do, do, do, do. I don’t need any of this stuff. All right.
Let’s get rid of some stuff on my phone that was blinking and making YouTube questions not show up properly. Next week, I promise it will be seamless because I won’t click the wrong button near the participants window to make everyone disappear. I apologize.
I like sort of accidentally raptured all of you and sent you to a phantom zone or something. But I’m able to join you from my phone. We have a link.
It’s coming to the light, I guess. All right. Do we have any other questions from the magnificent 10 of you who have shown up? Let’s see here.
Darren says, have you ever worked with in-memory OLTP much, exploring some solutions using it? No, and only because I never got a chance to. So it’s a really, it’s a really niche feature as far as what I think it’s a good fit for.
And all of the use cases that I’ve seen for it have been for incredibly high rate data ingestion applications where the data that you care about doesn’t hang around long. So think about like an online gambling type thing where you have a bunch of events and people want to bet on events and do stuff. But as soon as that event is over, like no one’s going to go back and care about it, right?
Like events over, done, payouts. So when you want to ingest all of that like really high value data that needs to happen immediately, in-memory OLTP can be great for that. And then as soon as an event is over, you kind of flush it out to on disk tables where, you know, people can still get to it, but it’s not going to be, you know, like blazing latch-free, lock-free stuff.
And the reason that it’s really good for that is because with the high rate of data ingestion stuff is that that’s where the lock-free thing is really cool because it’s all of those concurrent inserts and updates and stuff that really need to happen quickly. If your problem is like, well, we need this like data warehouse report to be faster, that’s not a good use case for in-memory OLTP. And if your problem is like we have 5,000 transactions a minute and they’re kind of slow, then you don’t need in-memory OLTP.
You need a much more basic level of like tuning and indexing query stuff because the in-memory OLTP stuff, it really shines, I would say, like the 20, 25, 30,000 transactions a second or a minute or some ungodly number where, you know, you just really need to bang stuff in quick and no one can wait. You need all that like, it was like grandma betting 50 cents on Greyhound. Let’s see here.
Who is your pick for the Super Bowl? What teams are in the Super Bowl? You’d have to tell me that first and then I’ll pick. I have no idea right now. I pay almost no attention to any sports.
Sorry. And I’m not anti-sport. I like watching sports in bars and I like watching sports.
I like watching sports highlights because I think anyone who can make millions of dollars doing that kind of stuff because they’re just that good at it is pretty fun to watch. But, yeah, I have two kids and a wife and the amount of sports watching that goes on in my house is limited to the first like week to month when the Mets don’t suck or when there’s like a lot of anticipation that the Mets might do well this year. And then the Mets tank and no one watches baseball anymore.
So that’s about where my cutoff is. Let’s see here. I’m starting to use more clustered columnstore indexes. Yes.
Anything we should be careful of or anything cool we might be missing. So I’m going to go against nearly everything that I’ve ever said about index maintenance when I talk about columnstore and that it is absolutely positively vital that you maintain your columnstore indexes. There’s all sorts of stuff with like gravestones and deltas and compression and dictionaries and like all sorts of things that if you don’t have your columnstore indexes maintained correctly, you can really boot performance on it.
Joe Obish. I keep telling him to blog about it, but he doesn’t. But maybe he finally will.
But at his talk at pass over last year, which might be out on video now, I’m not sure yet. But at his talk at pass last year, he went into a bit how if, you know, you load a bunch of data into your columnstore indexes, but it’s like loaded inefficiently or like data types are weird, then you can end up with like really, really bad query performance because you don’t get like good dictionary compression or like run length encoding and all the other stuff that goes into making query store. Really, really fast.
So like, like, you think about the number like what makes it what makes columnstore really powerful right now is batch mode. So you work on batches of rows at a time rather than like single row at a time like you do with rowstore indexes. And when you have like, misloaded, let’s call them misloaded columnstore indexes, you end up working on far fewer batches because far fewer of them can fit in like an instruction at a time.
But Joe Obish is the man to watch for that. I think, you know, Nico, I’m pretty sure he has some posts on it. If you want to dig through his, his, his like 150 post archive on columnstore, but that’s, that’s the first thing that I would think of.
Um, so aside from maintenance, uh, you know, being very, very mindful of data types and, um, you know, when you, when you see, when you, or when you’re working with columnstore, there are like certain op like plan operators that you just want to avoid. Like constantly like nested loops, merge joins, uh, sorts, um, uh, anything like when operators execute in row mode rather than batch mode for some, for whatever reason, it’s a lot of stuff that can go wonky with those queries. So what, but what I love columnstore and I love what they’ve been doing with it.
There’s still a lot of, you know, manual labor type stuff that you, you have to do when you’re, when your queries are on to make sure that you’re getting the best performance of it. Um, everything is better in bars. Yes.
Everything absolutely is better in bars. Except sleep. Sleeping in bars is not, not better. It’ll get you thrown out pretty quickly. All right.
Any other questions? Anyone else? Fire something fun at me. Let’s see. I don’t know. I, I, I was wondering if I was, uh, no, nothing on Twitter. Okay.
So sometimes I always think that someone might ask a question on Twitter if they can’t show up here on the YouTube channel. Uh, let’s see. Some deranged creep wants to know my age, sex and location. And I’m just going to call, I’m just going to call in the Chris Hansen patrol on you.
None of that. Uh, am I excited about bits? Yes, I do have the costume and I’m, I’m, I’m disappointed that you didn’t notice that the picture that I put on Twitter about the costume because it is fully loaded. It is here.
The only thing that I couldn’t get is, so what’s funny is when I was, when I was talking to Andy Mallon about, about like the, the costume that I was buying, he, he was all excited. He was just like, he, he thought he like, he had like a, like a, like a dead on thing in mind. I was like, look, I can do a lot of it.
And he was like, well, did, did you get a racer back tank top? And I was like, I don’t know what that is. I bought like regular tank tops, which I’m still going to look fat in, but I can’t like imagine it like, like a racer back tank top. And then like, I got all, I got all nervous and I was like, uh, Hey, uh, I’m going to go look on Amazon for a racer back tank top.
And I don’t think that a clothing company has made a racer back tank top for men since Freddie Mercury wore one. Um, and even at that juncture, he might have just worn one that was made for a woman because he was a, he was a pretty slender fella, but not me. And like, I, I tried to look at, uh, like, you know, women’s sizes that, that might, might fit a man of my stature.
And, um, then I guess got depressed. I didn’t want to get into that. Uh, bringing up, hoping I’d bring it up for a preview.
Uh, I’d have to go get it. I don’t have it in my office. It’s the one thing that I managed to talk my wife into not making me keep in the office because everything else that might vaguely belong to me is jammed into my office. It is crazy.
Do you have a pick of him? I can, oh, geez, Louise. Let’s see here. How can I do this? Uh, all right. Let’s see. Uh, where is, how do I get to a browser now? This is very challenging.
You guys are pushing the limits of my, my technical ability here. Freddie Mercury. Live aid.
Come on, baby. Come on. Don’t fail me now. There we go. Images. No, not videos. Images. Come on, phone.
Come on. There we go. All right. So this is a very close approximation of what I will look like at SQL bits. Uh, I can’t do justice to his armpit hair. And, um, I’m, I’m not going to be able to lose 120 pounds, but I have that outfit pretty well nailed.
Except for the racer back tank top. That’s just going to be a regular tank top. I don’t know if I can tuck mine in.
That might, that might get dangerous. I don’t know how that’s going to go. That might not be a great idea. Excuse me. Jeez Louise. It is dry in here. All right.
Let’s see. Get back into the old YouTube. Come on, baby. There we go. Yeah. Ah, practice the pose. Live stream, picture and picture.
Thank you. That is the one thing that I have managed to get right in my time on YouTube is live stream, picture and picture. Uh, when I tried to schedule these, starting this up was the most painful thing I’ve ever done. I had to pay 10 bucks for an encoder or a decoder called like Wirecast.
And every time I went to start it up, it would tell me that my camera didn’t work. And there would be like, like blue screen, like not like computer blue screens, but it would just show me like a blue screen preview. And it was terrible.
So like, but like when I just hit like the go live button, it’s dead simple. When I try to schedule these, it’s miserable. I’m going to have to do something really. Uh, let’s see.
Any other, any other fun questions? You creeps staring at my poor office. Let’s see.
Zach asks. No, no. That was the same person. Practice the pose. No. I mean, it’s just the arm up, right? This isn’t a good camera for that, for me to practice. It’ll be fun.
It’ll be fine. Let’s see. We have, we do have, we have a question or maybe we have a statement. I don’t know. We’ll figure it out in a minute. Sam says, uh, by tuning indexes on a secondary moving reporting to read only secondaries are really popular movement in my workplace.
I’m concerned about how to manage the disparate index needs. Yeah. Uh, I, I wrote a post about this recently that is scheduled.
I think for like two weeks from now. Blogging my butt off. Uh, but it’s, you know, it, it’s, it’s tough to like, depending on how you’re replicating the data, you know, you, you, you usually can’t have a different set of indexes on a secondary. From the, what you have on the primary.
And what makes things even more challenging. And I think this might be the third week in a row that I’ve said this is that, uh, the index DMVs on like readable secondaries don’t migrate their data back to the primary. So you have a really hard time figuring out like what indexes are good for anything.
It’s like looking around like, uh, okay, this one looks good here. This one looks good there. I don’t, you know, it all just goes to heck at some point. Uh, and it would be, it would be nice if there was like something that coagulated all that DMV data, but there is nothing at this point.
You would have, you would have, you are stuck doing that on your own, writing your own scripts to push data around. Yeah. But yeah.
Uh, so like, unless you use some form of like capital R replication, right? By that, I mean like not AGs or log shipping or mirroring where you can apply a script afterwards to make different indexes on a, on a, on a secondary available than what are just on the primary. Uh, yeah.
Kind of out of luck on that. And that’s, and that’s too bad because I know that with availability groups, at least on readable replicas, SQL Server is able to create like temporary statistics. So it would be nice if you could create temporary indexes too.
That would be fun. Aggregate, not quite. No, I like coagulate better. I think coagulate sounds better as a word. Aggregate.
Aggregate just sounds aggressive and angry. Not fun. Coagulate. It’s like, we’re going to go, we’re going to go have fun. We’re doing it together. Co-agulate. A bloody mess.
Why? Lots of things coagulate. Pancake batter. Uh, fat. I don’t know.
What else? What else coagulates? I’m trying to think of something that coagulates in a pleasant way now and I’m starting to see your point. Maybe coagulate isn’t good either. Maybe there’s no way, there’s no good way, no good way to say you bring things together. There’s no good single word for that.
They’re all just ugly. All right. Uh, anything else? Any other fun questions? Observations?
Hmm. Hmm. Hmm. All right, folks. Uh, thank you for joining me. Uh, I appreciate all of you showing up and asking stuff. I will be back next week at the same time doing the same thing.
And hopefully you’ll be able to make it then. I’ll see you next time. Thank you. And, uh, next time I will have the Freddie Mercury costume ready to show you. I think we’re getting, we’re getting close enough to bits now where I can, I can, I can do the reveal.
So I will see you next week. Not dressed up, but I’ll, I’ll have it. I’ll have it. I’ll tease you a little. Maybe I’ll wear some little shorts or something.
Bye.
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.
No, not because I’m looking. It’s just that a lot of companies, if they’re hiring their first DBA, or if they need a new one, don’t know how to start when screening candidates.
You can ask all sorts of “easy” questions.
What port does SQL Server use?
What does DMV mean?
What’s the difference between clustered and nonclustered indexes?
But none of those really make people think, and none of them really let you know if the candidate is listening to you.
They have autopilot answers, and you can judge how right or wrong they are pretty simply.
What’s MAXDOP Divided By Cost Threshold?
Tell them you’re setting up a brand new server, and you don’t wanna lose more than 5 minutes of data.
Ask them how they’d set up backups for that server.
If the shortest backup interval is more than five minutes apart, they’re likely not a good fit if:
It’s a senior position
They’ll be the only DBA
You expect them to be autonomous
This question has an autopilot answer, too. Everyone says they’ll set up log backups 15 minutes apart.
That doesn’t make sense when you can’t afford more than 5 minutes of data loss, because you can lose 3x that amount, or worse.
If Logging Is Without You
Aside from their answer, the questions they ask you when you tell them what you want are a good barometer of seniority.
What recovery model are the databases in?
How many databases are on the server?
How large are the databases and log files?
Questions like these let you know that they’ve had to set up some tricky backups in the past. But more than that, they let you know they’re listening to you.
Thanks for reading!
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.
This is a short post to “document” something interesting I noticed about… It’s kind of a mouthful.
See, hash joins will bail out when they spill to disk enough. What they bail to is something akin to Nested Loops (the hashing function stops running and partitioning things).
This usually happens when there are lots of duplicates involved in a join that makes continuing to partition values ineffective.
It’s a pretty atypical situation, and I really had to push (read: hint the crap out of) a query in order to get it to happen.
I also had to join on some pretty dumb columns.
Dupe-A-Dupe
Here’s a regular row store query. Bad idea hints and joins galore.
SELECT *
FROM dbo.Posts AS p
LEFT JOIN dbo.Votes AS v
ON p.PostTypeId = v.VoteTypeId
WHERE ISNULL(v.UserId, v.VoteTypeId) IS NULL
OPTION (
HASH JOIN, -- If I don't force this, the optimizer chooses Sort Merge. Smart!
USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'),
MAX_GRANT_PERCENT = 0.0
);
As it runs, the duplicate-filled columns being forced to hash join with a tiny memory grant cause a bunch of problems.
The value is a constant, hard coded in the product, and its value is five (5). This means that before the hash scan operator resorts to a sort based algorithm for any given subpartition that doesn’t fit into the granted memory from the workspace, five previous attempts to subdivide the original partition into smaller partitions must have happened.
At runtime, whenever a hash iterator must recursively subdivide a partition because the original one doesn’t fit into memory the recursion level counter for that partition is incremented by one. If anyone is subscribed to receive the Hash Warning event class, the first partition that has to recursively execute to such level of depth produces a Hash Warning event (with EventSubClass equals 1 = Bailout) indicating in the Integer Data column what is that level that has been reached. But if any other partition later also reaches any level of recursion that has already been reached by other partition, the event is not produced again.
It’s also worth mentioning that given the way the event reporting code is written, when a bail-out occurs, not only the Hash Warning event class with EventSubClass set to 1 (Bailout) is reported but, immediately after that, another Hash Warning event is reported with EventSubClass set to 0 (Recursion) and Integer Data reporting one level deeper (six).
But It’s Different With Batch Mode
If I get batch mode involved, that changes.
CREATE TABLE #hijinks (i INT NOT NULL, INDEX h CLUSTERED COLUMNSTORE);
SELECT *
FROM dbo.Posts AS p
LEFT JOIN dbo.Votes AS v
ON p.PostTypeId = v.VoteTypeId
LEFT JOIN #hijinks AS h ON 1 = 0
WHERE ISNULL(v.UserId, v.VoteTypeId) IS NULL
OPTION (
HASH JOIN, -- If I don't force this, the optimizer chooses Sort Merge. Smart!
USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'),
MAX_GRANT_PERCENT = 0.0
);
The plan yields several batch mode operations, but now we start bailing out after three recursions.
Creeky
I’m not sure why, and I’ve never seen it mentioned anywhere else.
My only guess is that the threshold is lower because column store and batch mode are a bit more memory hungry than their row store counterparts.
Thanks for reading!
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.
