ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I delve into a real-world scenario where I faced some unexpected challenges with my web hosting provider, GoDaddy, and the Office 365 email service. I share my experience of dealing with emails that consistently end up in junk folders despite repeated attempts to flag them as not junk, leading me to consider switching to G Suite. The discussion then shifts to a technical question from the community: how to evaluate whether a table with numerous indexes should be optimized or if the existing indexes are sufficient for the workload pattern. I walk through using SQL Server’s SP_BlitzIndex and SP_BlitzCache stored procedures to analyze index usage, focusing on read and write operations, and emphasize the importance of understanding the actual workload before making any changes.
Full Transcript
SQL Server, T-SQL, SSMS, query plans, and data. It’s been a bad week for me and Microsoft. Me and Microsoft have had some problems this week.
When I signed up for web hosting, I went through GoDaddy, largely because I had an account. There was no other real reason for it there. I had an account there. I had bought domains through there in the past.
And so I just went with it. I went crazy. I was like, I’m going to use you, GoDaddy. And I got my stuff through GoDaddy. And they provide you with email via Office 365. The problem I’ve been having with Office since I started using it is that the contact form on my website generates an email.
And the email is from the person who sends it. And the subject line is SQL Server Consulting Inquiry. Okay, pretty easy.
And for the last now nine months and three days, every email that comes in as a SQL Server Consulting Inquiry goes into junk email. I dutifully say not junk and I report it to Microsoft as not junk. I say this is not junk email.
This is how Erik Darling makes his money. This is how Erik Darling affords new Nagel prints when he wants them. There’s another one behind there. But every single one goes to junk despite me reporting it over and over again. And so I’m this close to moving to G Suite.
The only thing I have to figure out is how much of a pain in the butt it’s going to be to move to G Suite. And then if I can keep the rest of the Office products that I need because, you know, I just don’t want the email. But having like, like Word and PowerPoint and Excel and all that, all the other stuff that I use pretty frequently would be nice to have.
Ren and Stimpy is not new though. Ren and Stimpy has been in my life. Let’s see, it’s 2019 for almost five years.
Ren and Stimpy has been in my life. I bought Ren and Stimpy and Dune at the same time. Dune over there. Oh, yeah, that’s what that’s there. But this this past weekend I bought I bought some Nagels. They were they weren’t they weren’t expensive.
They’re not like real Nagels. They’re they’re just Nagel posters. They were like 100 bucks. So I was just happy to get some Nagels. I just have to put them up. It’s the other problem I have is finding time to put things up. Eventually, I’ll figure that out.
I also need to find time to shave. Art all seems to be from a distinct era. Ren and Stimpy was the 90s. Dune was the 80s. Nagel was the 80s. I love I love I love some 80s. I guess I guess is your point.
You’re right there if you’re if you’re trying to call me a fan of the 80s. I do not dispute that. Cannot dispute that. All right, so.
No one’s asking any questions, so I actually have a mailbag question. This week someone was kind enough whose name I won’t say on air out of respect for the living and the dead. And also out of respect for the fact that they probably don’t want that.
So I’ll just say we’ll just call them. See, I’ll check my notes. We will call them butt stuff. So my friend butt stuff has asked.
I’m looking at a table with 10 indexes. 10. 10 indexes. The DBA said he wasn’t worried about the cost of inserts, but I’m concerned. There seems to be overlap on the indexes.
An extra column here. Combineable includes there. The developers said they worked very hard on these indexes and aren’t changing any of them. How how do I go about proving or disproving that I should have these I should change make the index changes that I want. Hmm.
Hmm. Well, it’s a great great set of questions. And the first thing that I would the first thing I want to bring up is, so if you want, if you want to, if there’s a hill that you’re willing to die on, you have to be really, really sure that like there’s there’s a problem there in the first place. So.
I while back, I was in a similar situation where I was working with a client who had migrated their application from access. And when they did that, somehow, I don’t know what the process was exactly. I’m unclear on that because I’ve never migrated anything from access to SQL Server.
I have thankfully blissfully and any other kind where I have I’ve avoided that situation in my life. But when they did that, what they told me what happened is that SQL Server created all the tables. Or not all but many of the tables is the tables had clustered indexes that were like all of the columns in the table.
So you would have a column table with five or six columns in it. And SQL Server created a clustered index on all five or six. And I was like.
It’s the worst thing I’ve ever seen. Access sucks, man. So I I I I I I I I I I’m going to show these people how bad this is. And so I set up a test to do a bunch of single column or single row inserts into a similar table with a wide clustered index. And it turns out they didn’t make a difference for single row inserts.
It started to make a difference when I got up around like a million rows or 2 million rows or, you know, like larger inserts, but they weren’t doing inserts like that. So it was totally wasn’t applicable. In other words, I I got all riled up for nothing.
I was all I was all I I was like it was like index design crazy at that point in my life, I guess. And I was sitting there, man, I got to show these people what’s up. You know, really, really bury this one. And I was wrong internally.
I was wrong. So the first thing to do is understand your workload pattern. If you’re doing a bunch of single row inserts, probably not going to be the biggest deal in the world. Where it might start making a bigger deal is if you’re doing larger updates or deletes. So there are two stored procedures that are going to help you with this with this problem.
The first one is going to be SP Blitz index, which you can go to first, which I I made a code contribution to last last week weekend. Same with Blitz cache actually did did a few things on Blitz cache, but those are the two you’re going to use. You go to first responder kit dot org.
Oh, one word. Hold up a sign here. Let me let me let me let me make a sign for you. So that everyone understands where they’re going to go. And so you’re going to use SP Blitz index and first you’re going to run it against the database that you care about. And then you’re going to zoom in on the table that you care about.
So you can use the database name parameter. You can it’s really hard trying to write and talk at the same time independently. That’s interesting.
So you’re going to first run it against a database that you care about. Right, you’re going to look at the overall problems in the database that table might not be that big a deal. 10 indexes isn’t that crazy to me. And the second thing you’re going to look at second thing you’re going to do with SP Blitz index is use it to zoom in on a specific table. So if you feed it a database name and a table name, then you can zoom in and see if there’s any weird stuff with the with the table itself.
But what I would take a look at. When I would take a look at an SP Blitz index when you run it against the entire database one I would use I would use mode for so first I hope that’s not backwards for all of you first responder kit.org. So head over there and when you run it against Blitz index the entire database see if there are warnings for the table that you care about.
See if there are like big warnings like aggressive locking warnings are like like missing index warnings and like, you know, the duplicate and borderline duplicate index warnings. See if the stuff like that first. That’s the first thing that I would poke at.
And then and then after you run SP Blitz index and you look at that what I want you to do is run SP Blitz cache. What SP Blitz cache is the way you want to run SP Blitz cache is you want to run it. You want to run it.
Since you are your primary concern is with rights. Right with writing to that table you want to run it with a sort order rights or average rights AVG rights or the word average all spilled out. So it all depends on on how many fingers you have I think is how you end up spelling that I I don’t I have all of my fingers, but I don’t type with that many fingers. I think I type like this mostly or sometimes like this.
I very rarely get those involved. But yeah, so run it by rights first and see if there are any queries that are doing a lot of rights against this table that we care about. The second thing I want you to do is run it by reads or average reads.
So again AVG or average depending on how many fingers we’re going to get involved. And then I want you to look and see if there are any queries that are hitting that table and for doing a lot of reads. What this is going to tell you is two things one from Blitz index.
We’re going to see if we have any aggressive locking warnings. If we see aggressive locking warnings and I’m going to say, okay, there are queries that touch this table. That lock it up pretty good. Those are hopefully if your plan cash is like stable and solid and it’s not being cleared out constantly.
If it is, you’re going to have to run this. We’re going to have to run Blitz cash more regularly to maybe find these things, but you can run Blitz cash. You can see what’s doing a lot of rights. Hopefully the queries that we care about, they’re doing a lot of rights are going to be the ones that are hitting that table.
And then we can figure out, okay, maybe there’s a way that we can not do so many rights against that table and not lock it up so much. Ways to do that would be like batching, batching transactions. We’ll only hitting like, you know, 500, 1,000 rows at a time or something.
Right. If you are doing like big, big old whomping inserts into that table. And then the other thing we can look at is by reads. So, so if like, we’re not going to probably unless you’re doing big inserts. You’re not going to see anything that interesting.
Like there, what might pop up is updates because you could have to search a whole lot of a table to update very little of the table. So where that’s going to tie in is you might have some missing index requests against that table. And you might also have some, some update queries that are some delete queries.
If you, I mean, no, no, no, no, no one ever deletes anything. There’s no deletions in the database. We self delete maybe sometime once in a while. No one ever deletes anything, but yeah, that’s where you’re going to start to see if maybe there’s a query that’s looking for a lot of data and not finding it easily. It might be modifying data too.
It might be an update or a delete with the where clause. It might just be scanning a whole bunch of the table. So what I would, what I would do in that case is look first for like pathological symptoms that you have a case to prove, right? I wouldn’t go write my own tests, my own demonstrations.
I wouldn’t go writing my own sort of like test harness to try and prove or disprove when it’s bad. Cause it just might not be lined up with the reality of how that table gets used. I would want to like you, I would want to go into the plan cache.
I would want to go look at the metrics that SQL servers keeping about the indexes on that table. And I really want to start there and build a case with what’s, with what’s actually happening on the server rather than like, I think that might be bad. Let me, let me just go pick on these people for about, about these indexes that they worked so hard on.
I’m sorry. I didn’t mean to say that. This is a family friendly podcast or YouTube video, whatever. Anyway, let’s get back to the story. So yeah, you might be able to find some underlying pathologies with the borderline, borderline duplicate indexes and blitz index. And you might be able to find, you might be able to corroborate some of those pathologies with the information from blitz cache.
But, you know, make sure that, you know, you’re, you’re finding stuff that’s actually happening on the server, not just stuff that’s happening in your head when you look at that table. Because a lot of the times when, you know, as, as people who, who, who stare at SQL servers all day, we get very used to anti patterns or perceived anti patterns and chasing those things down. And quite often those anti patterns, those perceived anti patterns aren’t the, aren’t the biggest problem on a server.
And sometimes they’re not even a problem at all. Not every, I mean, anti patterns are like, you know, never a good thing to, you know, really like, like always chase down to the very end. You might find some and you might say, okay, well, this is, you know, something that generally you’d want to avoid, but it just might not be the problem that you’re having on that server. So I would, I would, I would, you know, I would take my time.
I would, I would not surface with more talk about changing things on this table until you have some evidence that there is a something bad going on. And B that, you know, it’s something you can correct by changing those indexes and C that, you know, it’s your index changes that don’t make the difference. Because it might not be an index consolidation thing.
You might need another index or two on that table. You, I know you have 10 and you really want to get rid of some and you want to combine some and I’m with that. I’m totally with that. But the problem might not be the indexes you have. The problem might be the indexes you don’t have. So anyway, that’s what I think about that.
I didn’t think of edit. I didn’t, I didn’t rehearse any of that. I just, I just did that off the top of my head. So you’re welcome. That is exactly the thought process I would have. And it would take me 15 minutes to get to that point with myself as well. I’m an incredibly unoptimized human being.
Forrest says, what do you think about EF queries? Well, Forrest, I think the same thing about EF queries that I think about presidents. As little as possible. Unless there’s something I have to think about them.
I don’t want to think about them. I don’t want to be faced with them. I don’t want to, I don’t want, I don’t want them in my head. I don’t want them burning away the precious few brain cells that I have left in here because I, it’s, it wouldn’t be good. Wouldn’t be good.
It’s scientifically proven that if you think about entity framework queries, or if you squat below parallel, that brain cells will shoot out of your ears, shoot directly out. And then it will explode. That’s exactly what it’ll do.
All right. So, do we have another question? Someone else want to talk? Someone else have a question about SQL Server? Or, I don’t know, anything else? Anyway, my friend butt stuff.
I hope that, I hope that answered your question. Forrest, who I will apparently be seeing in about a month for SQL Saturday. Crazy, right? Forrest is going to be in New York for SQL Saturday.
Joe Obish is going to be in New York for SQL Saturday. A lot of other very talented speakers are going to be in New York for SQL Saturday. Mr. Robish is having a pre-con for SQL Saturday. Very excited. His first one. Get him out of the gate with that.
Set him up good and proper as a valued pre-con presenter. Hopefully, anyway. As long as he doesn’t like turn into a fainting goat on stage. We’ll see. We’ll see about that. So this is where things get funny.
Outlook will let through stuff from Ninja Forms. Which I don’t, I got a newsletter from Ninja Forms. Which I’ve never asked for. I’ve never signed up for. I’ve never used.
But there we go. You know, there I go getting a newsletter from them. That doesn’t go into spam. What goes into spam? Consulting inquiries. Things that I need to get paid. Things that I need to pay rent for this room. Forrest asks, how often do you run across clients who would benefit from batch mode or columnstore?
Benefit from columnstore? I would say three or four times a year. I will get a client that is simply absorbing an insane amount of data and then aggregating it elsewhere.
I run across that three or four times a year. People who would benefit from batch mode generally. It’s not that they would benefit necessarily from batch mode processing.
Though some undoubtedly would. What a lot of people would benefit from is the stuff that’s currently hidden behind batch mode processing. So like the memory grant stuff. The adaptive join stuff.
You know, that stuff. The stuff that can sort of maybe kind of eventually help with parameter sniffing. So pretty often, pretty often it will see that, you know, I think everyone on earth with a SQL Server or the database in general probably is in some way dealing with. What do you call it?
What’s that word? What’s that word? Parameter sniffing. There we go. Slow in the application. Fast in SSMS. Someone has a deal with it. So yeah, you know, some of that stuff would help. Peter says, I tried to convince some NYC buddies to go to your columnstore thingy, but they all said columnstore is one of the things that works awesome. So no need to go.
Wow. You know, if they’re that good with columnstore, then they should be teaching the pre-con. So if columnstore is working fine for you, that’s great. I think if it’s the Joe’s pre-con is for people who want to really, really learn like like in depth about columnstore and batch mode processing.
I don’t necessarily know that there’s a good counter to that. If they’re not interested in learning more and they think their columnstore workload is running fine. That’s great.
I think what they would find is ways to make it better. I think they would find ways to improve upon what they’re already doing. Unless they’re unless their name is Nico. And then they maybe not maybe they might not learn anything if the name is Nico. You wrote a lot of their column stars.
Well, there you go. What problems could they possibly run into with you with you having been at the helm? I can’t can’t imagine. Can’t imagine why they’ve got to be so flawless. Why would they? Why would they ever need to learn anything further about columnstore? God bless.
God bless. Let’s see here. All right. Checking for some questions here. No questions there. No, that’s a thing. That’s another thing.
All right. Cool. Anyone else? Anyone else have a question? While I’m here staring at you blindly mindlessly getting bored getting lonely getting sad. Anyway.
All right. I’m going to get going then. Thank you for joining me. Thanks for hanging out. And I will see you next week. For a whole nother episode of this, whatever you want to call it. Marios.
Thanks for jumping up and standing over myrice. AsẸ. As I trulyolan can optimize your thanks to my heart and your song. Themetros are all about thesinoss launches remains your subescaytes.
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.