Tag: create index in sql server

Free SQL Server Query Plan Analysis In Your Browser

Posted on by Erik Darling

Free SQL Server Query Plan Analysis In Your Browser

Chapters

  • *00:00:00* – Introduction
  • *00:00:29* – Free Tools Overview
  • *00:01:13* – Supporting Memberships
  • *00:02:16* – Pre-SQL Server Performance Monitoring
  • *00:04:25* – Browser-Based Query Plan Analysis
  • *00:08:45* – Sharing and Exporting Options

Full Transcript

Erik Darling here with Darling Data, and continuing in my builder phase of life, you know, at least until the robots get too expensive. I’ve decided, I mean, if you’ve been, you know, dealing with me in any way, shape, or form over the past couple months, you’ll know that I’m working on two free open source tools. One of them is a full SQL Server performance monitor, and the other is sort of a portable query plan analyzer. I’ve taken it a step further, because some people seem allergic to downloading and trying things, and I’ve put the query plan, the free query plan analysis, right in your browser. Ta-da! I mean, this isn’t it. I’ll get to it in a minute. First, you have to listen to me talk about other things. Like, maybe you’re like, wow, Erik, all this free stuff, you must kind of know what you’re doing with the database. Maybe we should hire you for consulting, or maybe we should learn from you. There are links where you can do that down in the video description. It doesn’t hurt to click on them. No executable required. You can also, for as little, for as few, for as tiny little breadcrumb of $4 a month, become a supporting member of this channel.
Be real groovy, groovy-goolies. You can ask me office hours questions for free. I mean, at some point, I might have to start charging a quarter for those, but we’ll see how the economy picks up. And of course, if you, something else you can do for free, if you enjoy this content, and you enjoy the things that I do in my life, you can like, subscribe, and tell a friend, so that you get notified when I do other things, and your friends get notified when I do things, and then everyone gets happier together, I think. Speaking of which, pre-SQL Server performance monitoring. Gratis, or gratis, or whatever, however you say it. I don’t know. I can’t do voices.
You know, I tried a Christopher Walken like 15 years ago, and I just learned my lesson. Yeah, again, no voices. Totally free. Totally open source. No email, no phone home, nothing like that. It’s just all the stuff that a monitoring tool should monitor. A modern monitoring tool should monitor in SQL Server. You know, all the important stuff when you need to troubleshoot a performance issue. And of course, if you want to stay really, really modern, I’ve got built-in MCP servers, where you can just point them at your performance data collected over time, and space, and any other measurement you want to throw in there.
And you can have the robots go through your performance data, find problems, surface things, tell you about stuff, and you don’t have to lift a finger, aside from to say, you know, go look at this thing, right? Send them off to war, right? Anyway. Anyway. Again, happy surprise pre-con day. I will be in Jacksonville, Florida. Maybe it’s warm there, finally, because New York is still not warm. It is. What day is it? April something, and it is still cold here. I’m angry about that. So I’m happy that I’m going to Florida, where I’ll probably wear shorts. Not in front of a crowd, but I might wear shorts independently on my own. Anyway.
It’s a vibe, you know? Other places I’ll be, where it will hopefully also be warm by the time I get there. Chicago, Illinois. Dubious. May 7th and 8th. Not sure about warmth. SQL day Poland. May 11th and 13th. I don’t know. Poland sort of has a reputation for being cold.
I’ve only ever seen it in war movies, where I think it was supposed to look bad, so I’m going to just try to be optimistic about Poland being warm by then. Data Saturday, Croatia. June 12th and 13th. I’ve got to imagine that anything in that area of the world by June, I might be sweating by then. And then back to shivering at past Data Summit. New Community Summit. Date in Seattle. November 11th through 13th. 11th. 9th through 11th.
There are so many other 11th on there, I get confused. Anyway. Let’s talk about free query plan analysis right in your browser. Now this is not an advertisement for Microsoft Edge. Because I hate Bing and this browser is honestly, and it’s fine if you’re into that sort of thing. But if you go to plans.erikdarling.com, that is a brand spanking new subdomain under my website, under the Darling Data umbrella of websites, which is really just erikdarling.com, you will find this lovely interface.
And under that interface, you can either paste in plan.xml or you can upload a plan file. Now, I know what you’re thinking to yourself. There’s already a paste the plan. Yes, there is. But there’s a slight difference here. This one actually analyzes your query plans, and it does not, by default, save your query plans. All of the analysis is done in your browser.
It does not leave your browser. It does not go out into the world. There’s no plan file saved anywhere. You can do that, but you don’t have to do that. So, if you go to choose file, I’m just going to choose a query plan that I have saved immediately here.
You get back information about your query that looks a bit like you would get from the Performance Studio application that I’m building. Up here, when we look at this stuff, we can see some runtime stats about the query, right? We can see, well, oh gosh, golly and gosh, there was a missing index and no parameters were passed in.
We have this lovely graph down here of the wait stats, right? So, we can see, like, sort of graphed out what our query waited on. We don’t have to go digging through plan XML anymore. And then, down in this section below, we will have all the warnings generated by our query plan things that you should probably pay attention to, right?
If you look through this stuff, that’s all critical warning, yada, yada. And, of course, we get back our, well, there’s also the query text in there, but then we get back a lovely graphical representation of our query plan, right? Just like in Performance Studio, right? Stuff over here, look at all this good stuff.
And then, down at the bottom, we have the full text analysis, just like you have in Performance Studio, where I break down everything in a way that a human can hopefully understand. So, if you ever want an opinion on a query plan, and you are not allowed to maybe share it publicly on a site like StackExchange or StackOverflow, or maybe you’re not allowed to paste the plan somewhere where that plan is going to get saved off somewhere, then that’s one thing you can do.
There are also a couple other things in here that are neat. There are two buttons up at the top. One of them is to export HTML.
So, if you want to save off, like, all the HTML from here, you can do that. See, we open that up, and it looks just like it did. Well, I mean, this part’s a little bit different, because I don’t want to, like, export images to you.
But this part, so we have, like, the operator tree from here, and we have all the full text and everything else down there. So, if you want to share your plan, you can hit this button that says share. And when you hit this button that says share, you will be able to choose how long that plan stays saved on my little server for.
It is a secure server. I have done my best to make it unhackable. I’m not saying it’s unhackable.
I’m not challenging anyone in the world. I’m just saying I have taken reasonable precautions against anyone breaking in there. And it will say, you can do this, and you can have it expire after anywhere between one day and one year. And if you hit continue, it will say, are you sure?
You’re okay with this, right? Like, if you click twice, it’s not an accident. All right?
I’m just saying, like, once, oops, twice, that’s on you. But even if you’re like, I got confused, there’s a button up here where you can immediately delete it, right? If you say, ah, I was confused and drunk.
I was off my keister that day. I didn’t mean to put that there. You can, if you’re like, I just messed up, you can immediately delete it, and it goes away, right? And that URL, just the file is gone.
But then if you want to, but if you actually want to share it, then you will get this URL that you can share this plan with whoever you want, with all the analysis baked in, so that you don’t get, I don’t know, you don’t have to, like, explain anything. All the explanation is here. And you can say, how do I fix it?
What do I do? What did I do wrong? How did I end up in this place? How did I end up so doomed and damned with this query plan? And someone else can read it and look at it and say, here’s what I do. You can, you can even ask me, but that, that, that costs, if you want to do that, that’s this consulting link over here.
And that’s where you can, you can, you can get help with your SQL Server from, from me, right? Or with your query plans from me. That’s, that, that, that part is not free, unfortunately.
I cannot, cannot dedicate that much time. But this stuff I’m happy to do and to help people out, right? All right. Anyway, free query plan analysis, right in your browser, doesn’t leave your browser.
Sharing is optional, storing it is optional, and you get to choose how long you want to store it for, right? So you can even, you can even like share it with someone. And as soon as they get it, you can immediately delete the link.
Or if you, you’re uncomfortable with that, you can just export the HTML yourself and just share that with someone. So it doesn’t live on the internet, right? I don’t see anything from this, right? There’s nothing here that I get out of this.
All right. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you will, you will, you will start getting some free query plan analysis right in your browser. All right.
Thank you 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.

Introducing sp_QuickieCache: 80/20 Plan Cache Analysis to Find Your Worst SQL Server Queries

Posted on by Erik Darling

Introducing sp_QuickieCache: 80/20 Plan Cache Analysis to Find Your Worst SQL Server Queries

Chapters

  • *00:00:00* – Introduction
  • *00:00:30* – Recent Video About 80-20 Analysis
  • *00:01:00* – New Store Procedure for QueryStore
  • *00:01:30* – Useful Links in Description
  • *00:02:06* – Free Resources and Services
  • *00:02:34* – SQL Server Performance Monitoring Tool
  • *00:03:00* – Built-In MCP Tools
  • *00:03:11* – Query Data Protection
  • *00:03:28* – QueryStore Benefits
  • *00:03:40* – Surprise Pre-Conference
  • *00:04:00* – Upcoming Conferences
  • *00:04:58* – SPQuickieCache Store Procedure
  • *00:05:03* – SPQuickieCache Overview
  • *00:05:20* – High-Impact Parameter Analysis
  • *00:05:31* – Query Scoring and Tuning
  • *00:06:45* – Detailed Query Information
  • *00:07:17* – Plan Cache Insights
  • *00:08:09* – SPBlitzCash Database Checks
  • *00:08:26* – Find Single Use Plans
  • *00:09:05* – Find Duplicate Plans
  • *00:09:37* – Query Store Alternatives
  • *00:10:17* – Thank You and Future Videos

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about, let’s see, did it happen right? It happened, right? I had to take, if you pay attention to the goings on of Erik Darling and his Darling Data life, there was a video recently about adding sort of 80-20 query analysis into SPQuickieStore, which was very, which continues to be a very, useful thing for me. Uh, it’s in the latest release of the, my, my SQL Server, uh, performance troubleshooting scripts, uh, doesn’t have a snappy name, no first responder kit, but, you know, someday, someday I’ll figure that out. Um, but, I was working with someone recently who, uh, was, was philosophically opposed to QueryStore. Much, you know, there are various reasons why, some, some, some, you know, uh, some right, some wrong, but, you know, I just refused to turn it on, and we needed a way to look at QueryStore. And I wanted to use a similar way, so, uh, I ported the find high impact section of code over to a new store procedure, and I also added a couple bells and whistles to it. So, uh, we will, we will look at that, and we will also, uh, actually, no, we’re, we’re only going to do that. That’s, that’s all we’re doing today. We don’t have, we can possibly cram another iota of interesting things into this.
Uh, down in the video description, you will find all sorts of helpful links for, uh, our lives, to make our lives intertwine wonderfully financially together. Uh, you can hire me for consulting, you can buy my training, and for as few as $4 a month, you can, you can, you can feed a starving consultant, um, uh, some tiny fraction of a New York cappuccino or espresso. Uh, for free, though, you can ask me office hours questions. Maybe you can ask me how to draw other things in SQL Server Management Studio, and we can try that. Uh, and of course, if, if your enjoyment and appreciation of me, uh, is not, is not measurable in money, uh, you can always like, subscribe, and of course, uh, tell a friend about, uh, whatever it is you find useful here.
Or, I don’t know. Maybe you just like Adidas t-shirts. Right? Could go either way. Uh, for free! Also, you can download my SQL Server performance monitoring tool. Um, no, no, no weird telemetry or email sign-up required. I don’t, I won’t spam you with crap. Uh, it’s just all the stuff that I care about monitoring, uh, performance-wise in a SQL Server, uh, collected and, and made into beautiful charts and graphs with all sorts of very user-friendly ways of, uh, looking at that data and getting root cause analysis of your SQL Server performance issues. Uh, there is, uh, also a built-in set of MCP tools that you can have your robot friends talk to about your performance data and only your performance data.
And it’s a magnificent thing in the world, right? Because you, you don’t have to let them loose into your, into your production SQL Server to, like, start running all sorts of crazy queries. They just touch the performance data that’s already been collected, and that’s much easier for them to understand, too, right? Because we, we retain all the important details. We don’t lose them, right? When various things go away and age out of SQL Server, we collect it all over time. It’s beautiful. It’s a wonderful thing, and it’s free.
I have a surprise pre-con. Um, I don’t know, I guess someone else’s airline tickets got canceled or something. I don’t know. Maybe they, sick, maybe their parakeet died. I don’t know. Uh, but I will be at Day of Data Jacksonville, Florida, May 1st and 2nd. Boy, howdy. Look at that. Um, and I will be doing my advanced T-SQL pre-con shenanigans there, so, uh, if you want to come see me in Florida, it’s been a long time since I’ve been to Florida.
Apparently, I’ll be there. Alright. Uh, other places in the world I’ll be. Golly and gosh, look at all these wonderful people who decided to let me, like, be in public, in front of people, and, uh, not stuff a sock in my mouth yet. Uh, I’ll be at Pass on Tour, Chicago, uh, May 7th and 8th. I will be at SQL Day, Poland, May 11th and 13th. Uh, I will be at Data Saturday, Croatia, June 12th and 13th. And then I will be at, in Pass Summit, Seattle, uh, November 9th and 11th. Ah, man, Pass Summit, Seattle. There we go. I am, I am not even drinking today yet.
That’s the funny part. Alright. Cool. With that out of the way, let’s look at this new store procedure. Uh, I think I have to go to Management Studio. Yeah, I remember what that looks like. Alright. Cool. So, uh, this is, this is it. SPQuickieCache. Pay no attention to the terrible red squiggly underlines.
Um, and this is what you get back. So, if, if you have been using, um, uh, SPQuickieStore and you’re on a relatively new version of it, you will find that there is a high-impact parameter, which gives you a similar set of stuff. Uh, the whole idea here is to find queries that hurt you across a variety of metrics and sort of score them and, uh, present them to you in an order in which you should tune them.
Right? Because that is a, a wise and wisdomful thing to do. So, uh, this is sort of what you get back. Um, let’s zoom in on the results here. Up at the top, we tell you how many plan cache entries and all this other stuff we captured. And, um, you know, it was good stuff, right? Like, good information up here. Uh, and then down in this section, this is where your 80-20 queries live. Um, I have taken it upon myself to do some neat things in here, like tie statements back to the procedure that they live in. Uh, I think if we scroll down a little bit further, there’s another one, but I might be wrong because I might’ve run some stuff between now and then. Yeah, it’s not in there, but, um, you know, like for like this line, you get like just create procedure. And there are some things that we don’t get back at the procedure level that we get back at the query level. So, uh, there’s all sorts of neat stuff in there, right?
Like, uh, let’s see up here, we have create procedure, any word, right? And this is like, misses the query hash, but down here we see like the queries from that store procedure, right? So that, that’s, that’s, that’s how it works. But then over to the right, we have some other things too. Uh, we have this impact score and we have, uh, the areas in which queries returned, uh, high signals, right? Uh, so, um, this one up here up at the very top spilled a lot, right? That was how many times it spilled. And if we look over here, uh, we’ll even see, uh, the total spills and the stuff like that. So, and, you know, max spills and all these other things. So we get some, some high level information back about like how much damage this thing is actually doing. So we can, we can make smart choices. Um, down here, uh, there is some information about the plan cash, right? So, um, like, like severe plan duplication and single use plan bloating, plan cash stability, right? And single use plans and all these other neat things at the database level. Um, I also added some of these database, well, I was working on this.
I added some of these database level checks as well to SP blitz cash. So if you are an SP blitz cash fan or aficionado or whatever, um, you’ll, uh, I think at some point when Brent does another release, you will see, uh, the database level breakdown of, um, of, uh, duplicate plans and single use plans. So if there’s like a single database on your server, that’s responsible for all this, it used to just be at the server level, right? And just be like, there’s a lot of plans for this query, but now it’s like for the database, like, look how bad this is. Now, just like all my other store procedures, uh, there is a help parameter where you can see how everything is set up and gets used and that it’s MIT licensed and that, you know, we, um, you know, give this stuff away for free. I give this stuff away for free. Uh, but the important part here is that it lists out all the parameters. And if you look in here, there are a couple of neat little doodads, like find single use plans and find duplicate plans because a lot of the times, Oh, we don’t need you. Uh, you’re, you’re a quickie store. You, how’d you sneak in there? Um, so a lot of the times when, you know, you see alerts like, Oh, there are a lot of single use plans or, Oh, there are a lot of duplicate plans. Uh, you’re like, well, where are they? How do I, you’re going to make me go find those? How do I, how do I do it? I’m hope lost and hope hopeless and helpless. How can I possibly be a more self-sufficient person in the world? Don’t worry. You can be, you don’t have to be, you can be codependent on me. So if we run this query, uh, or rather run SP quickie cash with find single use plans, I will return to you single use execution plans, and I will give you a command to get rid of them. Right? So that’s, that’s cool there. And then there’s also find duplicate plans, and this will give you the top 10, uh, most duplicated plans in your cache. Right now, it appears this TPC database is really, uh, just a nightmare mess of, of things, right? Look at this 1600 plans and 1600 executions, right? And the, the story doesn’t look good there. Right. But, um, you know, this will help you maybe find queries that need parameterizing, um, or maybe it will help you turn on force parameterization for an entire database to avoid the problem. But either way, uh, you get some useful information back there. Uh, this store procedure, just like all my other store procedures are available in my GitHub repo. That’s at code.erikdarling.com. Uh, again, type that out for you. So you can be even more codependent on me. You can be codependent on my code code.erikdarling.com. You can get this here. Uh, you can start using it. You don’t have to turn query store on for it. Um, uh, so that’s, that’s cool too. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in tomorrow’s video where we will do, I don’t know, something stuff. We’ll figure it out.
I do all this stuff at the last minute anyway. All right. Thank you 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.

Can you teach us how to draw a hotdog using SQL Server’s geometry?

Posted on by Erik Darling

Can you teach us how to draw a hotdog using SQL Server’s geometry?

Chapters

https://gist.github.com/erikdarlingdata/20df5b8604d3673f474de798120a891f

Full Transcript

Can you teach us how to draw a hotdog using SQL Server’s geometry? Gee, that would be swell. Well, you know, who am I to deny a reasonable request? So here we go. Here’s SQL Server Management Studio. Here’s a bunch of geometry. Here’s a hotdog. I didn’t pick the colors. I don’t get to pick the colors. These are just the colors that SQL Server Management Studio chose. when it drew the object. It’s a bit of an 80s hotdog, which I appreciate. If I were walking in a mall food court in our glorious decade of the 80s, when everything was better, and I saw a neon sign or some other signage that had a hotdog that looked like this on it, you can bet I would have spent probably at that time about 28 cents on a hotdog. All right. Thank you 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.

SQL Server Performance Office Hours Episode 60

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 60


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video, Monday has thrust itself upon us, and it is time to answer not one, not two, five office hours questions submitted by you, the greater community. Before we get into that, down in the video description, there are all sorts of wonderfully curated, helpful links for you to interact with me in ways that we will both enjoy immensely. You can hire me for consulting, you can buy my training, you can become a supporting, paid member of the channel for as few as $4 a month. And if you don’t find that you get $4 a month worth of joy or information or entertainment out of this channel, you can do some other stuff. Like, I don’t know, you can ask me an office hours question like we’re doing today, and you can, I don’t know, maybe that is worth $4 a month to you.
You can also like, subscribe, and tell a friend. If your enjoyment of this channel is somewhere between one of whatever the smallest unit of your local currency is and $3.99 of your local currency, you can just do something like that. You can say, well, you can say, well, you know, it’s worth like $0.99 whatever’s to me, like cents of whatever your variety you have and say, oh, I’ll send this link to someone who might like it. You can also, for free, download, you can buy it for free, my free SQL Server monitoring tool. Totally free, totally open source, no email, no phone home, no nothing weird.
It just runs a bunch of stuff and gathers a bunch of metrics that I would look at if I were a performance monitor, which now I am, and it puts them in pretty charts and graphs. And you can click through those pretty charts and graphs, and you can get to like the root cause of performance problems on your SQL Server. And if you prefer to have your robot friends do the heavy lifting, then there are optional built-in MCP servers you can use in order to have them do the work for you and give you some analysis on your performance monitoring data.
Not just them going out and running crazy DMV queries on your production server. I have a new surprise. Hey, look at that. A new surprise pre-con. Day of Data Jacksonville had one of their presenters drop out, and so I am taking their place.
Silver Metal Eric, coming your way. I’ll be doing a pre-con on advanced T-SQL performance tuning. And so you can come see me there if you are in the greater Jacksonville area. So that’s May 1st and 2nd. And then I’m home. Oh God, my kids are going to grow so much.
I’ll be in Chicago May 7th and 8th for Pass on Tour. I will be in Poland May 11th through 13th for SQL Day Poland. Crazy. Then I’ll be home for a little bit and I will be at Data Saturday, Croatia June 12th and 13th.
And then at least as far as I know, finally at Pass Data Summit, Seattle. It’s a community summit. Comumity. November 9th through 11th.
So all that going on. But for now, let’s answer some champion baseball office hours questions here. All right. Zoom it. Are we going to do it? Oh, we did it. There we go. Where can I download your awesome database AI art?
You can’t. That’s for me and me alone. You can take a screenshot of it if you want, but I’ll be in it sort of. But I don’t know. Maybe maybe that makes it more memorable.
All right. Old darling data hunting your screenshots. But, you know, I don’t know. I’d make a calendar, except they’d all be a year late. And I know how you people are with money.
Anyway, come on. Zoom it. And, you know, free tools. What can you say about them? Jeez Louise. On a hundreds of million row table, why does update statistics sometimes run multi-threaded and sometimes single-threaded, even though the cost is way above the cost threshold for parallelism?
Same reason that happens for a query. The optimizer says, this one’s going to go parallel. This one’s not.
Cost threshold be damned. You might induce it to, you know, what do you call it? Use a parallel plan by maybe choosing a higher sampling rate. That might work sometimes, depending on how the optimizer feels that day.
Unfortunately, there just aren’t as many tricks for this as there are with, like, you know, inducing a query to go parallel. Right? There are things you can do there, but this, you know, kind of stuck a little bit. All right.
This is a long… I had to start charging by the sentence here. When I use the legacy cardinality estimator for a query, the estimates only change a tiny bit. But the query goes parallel instead of serial.
Same stats, parameters, etc. The only significant cost-related difference is that the estimated number of rows without a row goal for the most IO heavy node of the plan goes down. Does any of that match your experience with the legacy cardinality estimator or trying to encourage parallelism?
I’ve got to be honest with you, it’s a very specific situation. I’m not sure that I have encountered your very specific situation. I’m not sure what other local factors apply to your very specific local situation.
Does that match my experiences? No, not exactly. Not in either case. But if it’s working for you and that’s what you want to happen, I’m very happy for you.
Like, have you ever tasted my mom’s cooking? Can you realistically tune your way out of a terrible data model or is that just delaying the inevitable? Yes, you can tune your way out of a terrible data model.
It is just annoying. You know, but it really does depend on in what direction your data model is terrible. You know, like, you know, the most common one that I see is like the overly wide table.
It’s like 200 columns and you’re like, oh, we’re going to need some wider indexes if we want to deal with this. But, you know, usually it comes down to indexing when you when you need to do that. You know, if you ever suggest changing a data model to people, they just start crying because of the amount of things in the application that would have to change and get tested in the whole works.
You know, it is usually incumbent upon us, the data query performance tuning community to deal with the malfeasance of whomever designed these terrible data models and find some way to accommodate them with our our talents and skills. You know, you can, but it sucks. When do you decide a query is too complex and needs to be broken into multiple steps?
Well, usually about the time that it starts not performing well and usually about the time that it starts not performing well because SQL Server does not fasten the nuts and bolts together in the right places. However, there are different ways of approaching this, of course, you know, temp tables are a very nice materialized mechanism for, you know, material like like sticking a portion of some query into an object that SQL Server can then derive statistics on and you can even index it. And so, you know, like my decision is basically, is this query too slow?
Yes. Is it too slow because SQL Server is not figuring something out correctly because it’s complex? Yes.
Then it is time to do it. Usually the appearance of more than one CTE, especially when those CTE start getting joined together is a good sign. Sometimes it is, you know, if there’s a bunch of derived joins and, you know, stuff like that going on with it.
Other times it might just be, you know, some arbitrary number of sub queries in the select list or in the where clause or even in a join clause. Right. But the decision always starts with, is this query performing acceptably?
And if the query is not performing acceptably, then it could be due to complexity, but it could be some underlying pathological issue as well. Right. So sometimes, sometimes it is a break, breaking the query up is the best possible option.
Other times there are, there are other things, other steps that you’re going to have to take regardless of whatever small steps you, you, whatever, whatever multiple, however many multiple steps you choose to break it up in. And I think, you know, at least the nice thing about breaking a complex query up into multiple steps at some point is that it gives you very specific information and feedback about which portions of the query require your attention. Right. Because there might, there might be some portions that, you know, run very quickly on their own.
There might be some portions that run very slowly on their own. But sometimes it really does come down to like putting those two portions or however many portions together is what causes an issue. Right. So for me, it’s really just, you know, it starts with is performance acceptable?
If not, what steps do I need to take to make it perform acceptably? Right. So that’s, that’s pretty much it for me there. All right. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I’m going to answer one very special office hours question on its own. All right. Thank you 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.

Learn T-SQL With Erik: Partitioned Views

Posted on by Erik Darling

Learn T-SQL With Erik: Partitioned Views

Chapters

Full Transcript

Erik Darling here with Darling Data. And we’re going to finish off this Friday by talking about partitioned views. And look, there are a lot of things I could say about partitioned views that are great and grand and that have come in handy for me over the years in ways that I’m like, wow, thank you partitioned views. Thank you for not being normal table partitioning. Thank you for existing. But mostly in this video, I just want to kind of go over a couple of things that, you know, are not fun about partitioned views. I want to show you some things that are fun, but some things that are not fun are things like, you know, like the setup of them is like a bit arduous. And if you want your partitioned views to be writable, good luck. It’s a hard road. And I’m going to talk about some of the things that disallow a partitioned view from being writable as well. So that’s our lesson from today. Sorry for pausing on the big reveal there. Apparently, I just forgot to click the mouse. Anyway, down in the video description, if in case you were unaware, down in the video description, you will find all sorts of helpful links.
You can hire me for consulting, you can purchase my training, and you can become a supporting member of the channel. If you don’t feel like giving me any money, well, I don’t like it, but what can I do from here? I can’t exactly punch through a screen here. You can always ask me office hours questions for free. That is a free activity you can partake in. If you would rather hear me answer a question than some robot, I suppose that’s as good a way to do it as any.
Of course, if you enjoy the high-quality, flawless, unmatched content that I produce on this SQL Server channel, the most important SQL Server content that you will ever see in your life, please do like, subscribe, and tell a friend. And continuing in the tradition of you not feeling like giving me money, if you would like to stay on that path, if you’d like to stay that course, you can download my free SQL Server performance monitor. It is wonderfully free. I make the same amount of money whether you use it or not.
So, like, okay, do or don’t. It’s up to you. But it is a replacement for paid commercial SQL Server monitoring tools in many cases, in many ways. And, you know, it just looks at all the stuff that I would look at if I were, you know, coming in to look at your SQL Server as a consultant.
Logs at all the tables, gives you pretty charts and graphs, tells you all sorts of things about your data, allows to get you some root cause troubleshooting stuff done. And if you feel like having the robots do the work for you, it’s got MCP tools built in so that your robots have read-only, well-defined access to the monitoring data.
And they can usually make better sense of that than if you were to just let them loose on a server running DMV queries there and about. Anyway, if you do not prefer the robots, if you do not want to have an MCP analysis of your life, you can come see me out in the world. I will be going places, doing things, talking about SQL Server as long as it’s still alive.
There’s something weird on my shirt that I can’t get rid of. I don’t know what it is. It might just be the light. This might just be an old shirt. It’s hard to tell. Anyway, I will be in Chicago May 7th and 8th for Pass on Tour, the east of the west, I guess that is.
I will also be at SQL Day Poland May 11th through 13th. That’s looking up to be a great conference. I can’t wait to see the Dwarves of Vroslav. I keep hoping I say that right.
And then I will be home for a little bit and then over at Data Saturday, Croatia, June 12th and 13th. Lovely time of year for Croatia, I hear. Distinct lack of dwarf statues around the city of Zagreb, but that’s okay because I’m sure I’ll find other things to be amused by in such a lovely place.
And then, of course, November 9th through 11th, I will be at Pass Data Summit in Seattle, Washington. So these are all things that cost money, so come see me so I can make some money back. Anyway, for now, it is March. No, wait, it is April 1st, right?
Yeah, it is April 1st. Well, today I’m recording this on April 1st. I’m publishing this far down the line because, crap. Ah, the magic is ruined for you, isn’t it?
Anyway, let’s talk about partition views. So this is the first thing, is the arduousness of setting them up, right? So, like, I’m just doing this yearly for the votes table in Stack Overflow 2013. So I’ve got, like, 2008, 9, 10, 11, 12, like, this is my preamble stuff.
But then, like, you’ve got to create a table for each one of these things. And one thing that’s very, very important to do on table create is make sure that whatever your partitioning element is, SQL Server has a valid way of eliminating certain, like, partitioned table elements from your query plans.
It will not take the hint otherwise. So here I’m saying, look, SQL Server creation date is going to be between these two dates, or betwixt, I guess. Why don’t we have a betwixt keyword that just says, that acts like greater than, equal to, and less than?
That would be nice. That would be a good extension. Connor Cunningham, if you’re out there listening, we’re going to have between, which is going to stay the same, and we’re going to have betwixt, which is going to do that, all right?
Write it down. All right, so you’ve got to do that for each table, right? You’ve got to give SQL Server a way to know what data is going to be in what table so that, you know, like, if you want to get partition elimination, you can do that, all right?
And then you have to create a view, and you have to list your columns. Now, it is kind of cool where, like, if you do this, and you have, like, different columns and different tables, you can make that work with just, like, placeholders.
So that’s fine, too. Just be careful with null, because I learned from Kendra Little that SQL Server, if you put, like, some column equals null into a view, or if you, like, select null into a temp table, SQL Server defaults to calling it an integer.
So that might, you might find that disagreeable. So make sure that you are strict with your data types if you’re expecting a different one, other than integer for your placeholder columns there. Anyway, after you create your view, then you have to get your data into these various tables.
That’s all quite boring stuff. And if you’re wondering why I created my tables as heaps, it’s because I’m going to start with these tables as unique, having unique clustered indexes on them.
And I’m doing that for a reason that we will expand upon in a moment. So if you want your partition views to be writable, there are some prerequisites.
You need to have a unique clustered primary key. This is why I’m starting with just a unique clustered index, because it must be a unique primary key.
It must be a clustered primary key. That includes the partitioning column. You need non-overlapping check constraints on a partitioning column, which means there are no gaps, no overlaps.
You need identical schema across all member tables, same column names, types, and nullability. You need, they all need to be in the same database. You cannot do that across, across databases.
If you want your partition view to be writable, that would just be insane. And you must use the union all syntax, not union.
There are some limitations as well. Like you can’t have an identity column in any member table. You must strip these tables of their identity-ness. You can’t have a computed column as a partitioning column.
And you can’t use any timestamp columns. That makes the view non-updatable. So screw you timestamp columns anyway. Constraints?
Well, you can’t have default. Well, yeah. So you can’t have default constraints. You can’t have cascading foreign keys. And you cannot put triggers on member tables. You can only put instead of triggers on the view.
You can’t use the default keyword or an insert or update. So that’s fun. You must provide all columns and insert statements.
You can’t do bulk operations like bulk insert or BCP. And you can’t have any self-joins with the view or member tables when you are, if you want this view to be writable. So like if you were trying to do like, oh, I need to put data in here that doesn’t exist.
And you like write, ah, insert, blah, blah. Where not exist, select, blah, blah. That SQL Server will say no. There are all sorts of limitations around data types like XML and other lobs.
User-defined types, CLR types, not supported. Not that anyone uses, though. That’s insane. And some other like just weird random trivia stuff. No full-text indexes, no indexed views.
Check constraints must be trusted and enabled. And all member tables must have the same number of columns. So if like some of the flexibility that you get with indexed views is reduced quite significantly if you want that partitioned, sorry, partitioned views is lost a bit if you want that partitioned view to be writable, not indexed views.
Though those have many restrictions as well that I find unfortunate. But if we look at execution plans, and I realize this might not be connected to anything, but that’s okay.
We’ll get there. Ah, see, I knew something weird would happen. There we go. Anyway, it ran. It worked. All right, let’s give that a second run just to make sure. All right, so we’ve hit.
You can see that we are touching our partition view here. And this partition view is all those tables. But our query plan only shows one table being hit, right? Because we’re only looking at 2010 and 2011.
And because I went and created all those check constraints across all of my, all the tables in the indexed view, SQL Server knows precisely where to go. Right?
And if we do the same thing with a multi-year query, then we will see that we hit more than one table, but we did not touch all of the tables. We just hit the necessary ones, which were 2010, 11, and 12. So SQL Server’s optimizer can be very smart with how it directs queries to index views, even if those queries are parameterized, right?
So if we look, this is a store procedure, which accepts a start date and end date, and you can probably guess where they end up. This thing basically just runs this query, right?
We run that, and we’ll see in the execution plan that some of these lines actually have things, actually have data flowing through them, like 2010, 11, and 12, but the rest of them do not.
So this is sort of dynamic partition elimination, right? So if I change, well, you know what, if I change this to 2012.01.01 and 2013.01.01, and we run this, the execution plan, you’ll see that SQL Server is able to dynamically just get us to the 2012 table.
So the optimizer is pretty good about this. Where that can get confusing is if you look in like a cached plan, so like either in the plan cache or query store, or even if you just get like an estimated plan for this, like you’re gonna, like, you notice what we see here.
We see three thick arrows, right? And that’s because when we cached in, or we compiled and cached a plan for this, it was for 2010 through 2013, right?
So we see three thick arrows here, right? So like cached and estimated plans for these can look pretty confusing because you’re gonna, like, they might look like they’re hitting all of the tables and doing all of the work when they are really not.
So the one point that I wanted to make a little bit with this is if we try to update this, because remember, I only created unique clustered indexes on my member tables, right? So if I try to run this update, it’s gonna fail, right?
And it’s gonna say it’s failing because a primary key was not found on, well, it says just 2008, but really none of the tables have primary keys. So if I create primary keys across all of those tables, right, which doesn’t take too long, you know, they’re all pretty, fairly small tables, and we already have an index on the columns that we’re primary keying, right?
So this is all pretty quick, but, and this is why I try to tell people, trying to make these things writable is often not worth it, right?
It is not worth the effort. Just like, it’s much, much, you’re much, much better off spending the time and coding efforts to just hit the right table somehow, like use dynamic SQL or do something else, right?
Like an instead of trigger that does things because like, you know, like we run this update, man, it’s not fun, right?
So yeah, making these things writable, again, not worth the mental effort and then modifications against them, man, they’re not a lot of fun either, right?
Like if we look at the query plan for this, man, we heave-hoed a lot of work into this one, right? There’s a lot of spooling going on, right?
This thing is a big, wide mess. Even though we, like, it’s like, okay, all right, well, we’re hitting the clustered index on just two tables, 2011, 2012. Man, oh man, we do a lot of work, a lot of work here, right?
It is not worth your time and effort to make the partition view writable. Please just spend your time either writing an instead of trigger or writing, you know, some sort of dynamic SQL that touches the right table based on user input, hopefully sanitized user input, of course, but I don’t know.
You can’t have everything unless you hire me. My rates are reasonable. Anyway, this is just a snippet of some of the class material from Learn T-SQL with Eric.
You can, of course, purchase the whole course via a link down in the video description. There is even a coupon code attached to that.
So if you have enjoyed anything that we’ve talked about today, you can learn much, much more from the entire corpus of class material. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I’ll see you in tomorrow’s… Oh, no, I’ll see you in Monday’s video for Office Hours. I lied to you. I apologize. All right, it’s a bad way to end things.
I should say I love you. I’m sorry or something like that, right? All right, goodbye.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Learn T-SQL With Erik: Partitioning and Column Store

Posted on by Erik Darling

Learn T-SQL With Erik: Partitioning and Column Store

Chapters

  • *00:00:00* – Introduction to Partitioned Columnstore Tables
  • *00:05:02* – Querying Data and Segment Elimination
  • *00:10:37* – Vote Type ID and Common Data
  • *00:13:02* – Conclusion and Future Topic

Full Transcript

Erik Darling here with Darling Data, going through some more of the Learn T-SQL with Erik snippets, things that I feel are important for people to know about, regardless of if they purchase the course or not. But, of course, because there is a course for sale, then I would, of course, appreciate it if you purchase the whole course so that you can learn all the things that I think are important for folks to learn. So, today we’re going to talk about partitioning in columnstore because there are important differences between partitioned columnstore tables and partitioned rowstore tables. One of the sort of superpowers that columnstore has is the ability to use metadata about which row groups have which data in them, and it can skip entire segments that do not contain relevant data.
That’s a wonderful thing. That is something that rowstore indexes do not really have. All right. So, la-dee-da. But today we’re going to kind of look at how those two things, how these two things pair together and how, you know, much like with, you know, normal partitioning, the way that you access data does sort of rely on the partitioning key in order to make this as efficient as possible.
Before we do all that, down in the video description, you will find the most important links in your entire life. You can hire me for consulting. You can purchase my training materials, including the Learn T-SQL with Erik course, which has a little coupon code attached to it at the moment. You can become a supporting member of the channel. And then totally for free, you can ask me office hours questions and you can like, subscribe and tell a friend all about how wonderful all of this content is.
So they can, they can, they can learn as much about SQL Server as you, right? Wouldn’t that be just great? Another incredibly important link down in the video description is where you can get my free SQL Server monitoring tool from. Uh, it’s totally free, totally open source. There is no obligation or requirement on your part to, uh, talk to me or, uh, you know, pay me anything for it.
Uh, it’s just something that I wanted to do for the SQL Server community because I don’t feel that the big monitoring tool companies are doing their jobs well and haven’t been for a long time. And this is my way of writing that situation. Uh, it is a bunch of T-SQL collectors that would grab the same information that I would if I were, uh, doing a performance, uh, analysis or tuning engagement.
Um, it displays all that information in beautiful charts and graphs and gives you all sorts of abilities to click through those charts and graphs in order to find the root causes of your performance problems. If you’re too busy for all that, you can have the robots do it. There are optional MCP servers where you can, uh, let the robot of your choice talk to your performance data that I’ve been collecting.
And it can give you all sorts of nice summaries and talk through things and, you know, just give you general advice on what’s going on or at least information about what’s going on on your server. Uh, so that you are better prepared for the work you have to do. Uh, anyway, if you prefer the, uh, the old human to human thing, uh, you know, I, I still like human contact once in a while.
That’s why I’m going to all these things. Holy crap. That’s a lot of travel. Uh, I am just in order. I will be in Chicago, Illinois, uh, May 7th and 8th for pass on tour East, which is sort of weird cause it’s in the Midwest, but you know, what, what can you do there? Uh, then I will be traveling from Chicago to SQL day in Poland, May 11th through 13th.
Uh, then I’m home for about a month and I will be back out, uh, in the world going to data Saturday, Croatia, June 12th and 13th. And then, um, at least until someone makes me an offer, I can’t refuse, I will be, uh, doing nothing until November where I will be in, uh, Seattle for, uh, pass summit West, November 9th through 11th. I’m not quite sure what to call that one right now, but I’ll work that out.
Anyway, the meantime, let’s, let’s get beery and cheery and let’s talk about partitioned columnstore tables. So, uh, what I’m going to show you first is this query, right? And this query is going to hit a table that I have pre-created, uh, that is the votes table from the Stack Overflow 2013 database.
Uh, it is a clustered columnstore table and it is partitioned by the creation date column by year, right? So we are not partitioned by bounty amount, but the bounty amount column is still interesting. Because if we select some data from this table and we look at the messages tab, we will see that we read 33 segments and skipped 71 segments.
But why? Well, if you’re interested, uh, a part of the course materials is a view that I’ve created that gives me details on what’s going on with my, uh, columnstore stuff. And if we look at that, we will see that we have partitions two through seven and partitions two through five are eligible, right?
Where bounty amount equals zero exists. That is two plus eight plus 11 plus 12, which I’m pretty sure is 33. And then we have, uh, some ineligible parties, segments and partitions, uh, where we have, of course, six with sort of overlap between and partition five there.
But there are, uh, six ineligible segments in, uh, partition five and then 28 and 37, which I am fairly sure adds up to 71, but, uh, I haven’t got 71 figures to sort that out with. So I will let you do that math on your own. So the message really is with all this.
And if you just feel like this being a TLDR for the whole video, that’s cool with me, but just like with, uh, any other index, um, but particularly with partition columnstore, uh, because the creation date column is our partitioning key. If we want to really team up, um, you know, the, like, like segment elimination plus partition elimination, we need to access data via the partition column. So if we run this query, we will see this is, um, uh, looking for bounty amount equals zero.
And the bounding is 2010. Well, just the year 2010, right? Greater than equal to 2010.01 less than, uh, 2011, less than 2011.01.
And we look at the messages tab, we read 11 segments and skip zero, right? So all 11 segments that we, we could have read, we read, right? And that the segments in here are going to be ones that like, like, like I said, in another video, like the batch mode plans don’t show us which, how many partitions we touch.
You know, kind of leaves a little bit of guesswork to the, to the curious mind. But, um, this is how many, uh, like, because this is partition by year, we’re just looking at the year. We’re just looking at the partition that has data for the year 2010 in it.
And there are 11 segments within that year, right? So we didn’t skip any of them, but we were able to skip like all the segments and partitions that aren’t relevant. If I run this query where I’m looking at 2010.12.02 through 2010.12.31, right?
So this is just a portion of, uh, the year. And we look at the messages tab, then we’ll see that we only read two segments and we skipped nine of them, right? So we were able to like, even within one partition, we were able to skip segments that didn’t have relevant data to us.
So that’s, that’s pretty cool there. Um, I forget what this query is supposed to show. I should have left some notes for me for myself, maybe.
But, uh, anyway, uh, we can see that only some of these, um, have the bounty amount equals zero in them, right? So the min data ID in these is zero. The min data in these two is not zero.
So, um, there are two partitions where that have no zero bounties in them, which is a little weird, but not the end of the world. Anyway, uh, if querying the data in other ways kind of gives us different, you know, um, sort of fingerprints, uh, for segment and partition elimination. Uh, none of these are, I don’t think any, there are any examples of partition elimination for the following queries cause we’re not hitting creation date anymore.
But if we look at, come back to this query, we’ll see that there are, you know, all like the max data ID and, uh, partitions three through seven is 550, right? So if we run this and look at it, we’re going to hit like just about everything, but we are able to skip, like, because we’re like, we’re going to read all the partitions, but we’re able to skip a lot of segments based on the metadata that SQL Server has about, um, like what’s where. And, uh, the same thing, if we have a really high post number, right?
Like SQL Server is going to be able to figure out like, Oh, like I, I don’t need to read a lot of this because like, like this post ID has to be like, like, like it’s a very high post ID relative to the, like, like max post ID in the post table. So that, that post ID doesn’t, hasn’t been voted on until like recently. So we’re able to skip over a lot of segments in order to get to just the segments where that post ID might exist.
But if we, um, if we query by a very low post number, right, this is a very high post number. This is a very low post number, like post ID one, three, eight people have been voting on post ID one, three, eight, like basically since, you know, I don’t know, like forever, right? That’s one three is a very low post number.
So we can’t eliminate anything with that one. We have to read all hundred and four segments. We don’t skip any of them. Uh, same kind of deal with vote type ID 16, right? Uh, well, I mean, maybe not totally, but we’re able to skip eight or sorry.
We read 83 segments and we skip 21 vote type ID 16 is of course approved edit suggestion. Uh, so I guess that’s kind of okay. Um, another thing that will not help you in your journey to, um, and you know, it, it, it, it, it, it, it giveth and it taketh away.
So like, you know, people are most interested in data that is, you know, unfortunately sort of common, right? Um, like at least in the stack overflow world, vote type IDs two and three, those are up votes and down votes. Those are the two most important types of votes that, you know, um, that we can get.
So like, let’s, you know, these two, right? Up mod and down mod, right? Oh, that, not that first one. So like, these are going to be common. These are going to be like scattered throughout all of the partitions and all of the segments because, you know, they’re the most common thing.
We could pair this maybe with, you know, like some year boundaries in order to get partition elimination. But if we just need everything, you know, we still have to read through all the segments where this data might exist, but it’s okay. Like generally, I think, because I mean, you know, reading through a 53, actually this, this table is twice as big because I have to put double the amount of data in the clustered columnstore table in order for it to, um, really make, uh, like any sort of performance blip happen.
Um, so this table is actually very, very, uh, about a hundred and something million rows. Um, you know, like we, we do read a lot of them and we do a lot of that, but like the whole thing takes 18 milliseconds reading from that. And, you know, the whole query finishes very quickly anyway.
So columnstore great for reads, right? Very, very, very much speed up read queries. Uh, you can certainly pair it with, uh, partitioning in order to get partition elimination plus segment elimination, which is, uh, can be very useful for, uh, very large tables, but most tables probably won’t need it.
Um, most tables that, you know, I would say, uh, under the like a hundred million or so roll mark, aren’t going to see vast benefits from adding, from, from commingling partitioning and columnstore. That being said, if you anticipate this, these tables growing to very large sizes, you may want to start that journey early. You may not see the benefits immediately, but once that table gets, if that table that’s like 200 million rows, doesn’t see a lot of like, you know, uh, action from that.
You might try, you might start seeing it around like 500 million or a billion rows where all of a sudden you’re able to cleave out not only, uh, segments, but also you’re able to get partition elimination. And whittle down and figure and just read smaller numbers of segments, even within a partition. Anyway, that’s about it for me here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will buy the course, which is helpfully linked for you down in the video description. And I will see you in tomorrow’s video where we are going to talk about partitioned views. So we have that brilliant thing to look forward to.
Anyway, thank you 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.

Learn T-SQL With Erik: Partitioning != Performance

Posted on by Erik Darling

Learn T-SQL With Erik: Partitioning != Performance


Fair warning, the audio got pretty garbled on this one. If you don’t like space alien voices, hit mute and/or skip ahead to the transcript.

Chapters

Full Transcript

Erik Darling here with Darling Data. And today’s video, we’re going to talk about how in SQL Server, at least with rowstore indexes, let me make sure that I lay out all the caveats up front before some actually person shows up and like, before they even watch the video, because Lord knows there’s a lot of that on the internet. Partitioning with rowstore indexes is not a query performance feature. It is, of course, a data management feature. Before we get into that, though, I did want to thank the nice folks at Mother Duck for hooking me up with a nice rubber duck thing here. It says data person over there, backwards hat and glasses, kind of like Run DMC Duck. And on the bottom it says, Mother Duck, you’re the one, you make data so much fun. I thought I was making data fun, but apparently they make data more fun. So thank you, Mother Duck. Down in the video description. You will see all sorts of helpful links. If you feel like spending some money with me, on me, together, you and I. We can go shopping. You can hire me for consulting, buy my training, become a supporting member of the channel, ask me office hours questions. And of course, if you would like to, you know, I don’t know, hang out and make this channel more socially acceptable, well, you can like, subscribe and tell a friend.
Get notified when I publish these works of art. The material that we’re talking through today is, of course, part of my Learn T-SQL with Erik course. This is just a small snippet bit of the full course material. And there’s a link with the coupon code down the bottom where if you feel like purchasing the entire thing, then you can do that at a discount. Another thing that you can get absolutely for free is my new SQL Server performance monitoring tool. All right. It is a free open source, no email signup, no weird telemetry telling me about your SQL servers. It’s just a bunch of T-SQL collectors going in, collecting all the stuff that I would look at during my consulting engagements. And it’s all there to help you get a handle on SQL Server performance.
It is basically all the stuff that I answer questions about when I’m working with people. So I thought it was a pretty good thing to just let loose into the world. You know, there’s only one of me. I can’t scale beyond this one of me. I’ve had a very hard time teaching people to do the correct things with SQL Server. Despite all the years of blogging and videoing and training and everything else, it seems like people are just scared of SQL Server. I don’t get it.
But if you feel comfortable with doing robot stuff, there are optional opt-in MCP tools that you can use. It’s got a server built right in there so you can have your very favorite robot talk to your performance data and answer questions about it. How good those answers are, I can’t tell you. The summaries are at least pretty good, but at least some of the advice is maybe not quite all there yet.
The robots haven’t gotten the message on some things. Anyway, if you like human-generated advice and all that other stuff, you can catch me out and about in the world, traveling all over the place, trying to bring enlightenment from one human to another.
Sort of in order here, I’ll be at Passa with my right hand removed from being my body. Pass on tour in Chicago. That will be May 7th and 8th. That is, I don’t know, I guess about five weeks from now.
At least as of this recording. SQL day in Poland, May 11th through 13th. That is a three-day adventure. That is not a two-day adventure. Then I’ll be home for about a month and heading off to Data Saturday, Croatia, which is going to be a grand old time.
That’s June 12th and 13th. And then, at least as far as I know, that’s all I have going on with my life for a little bit. And then in Seattle, Washington for the Big Pass Summit, November 9th through 11th.
So, if you, again, want to come get a human hug, this is where you can get human hugs from. At least from me. I mean, you might have other sources of human hugs that you prefer.
You may just want to learn about SQL and not human hug me. I understand either way. It’s fine with me. But for now, man, there is so much baseball to go that I am just a very happy person. If you are the type of person who likes buying stocks, playing the market, buy some Coors Light stocks because it’s going to be a busy summer for me.
Anyway, let’s talk about partitioning. So, I’ve got a table called Votes Partitioned. It’s already partitioned because who would want to sit there and watch me partition a table?
It’s not an enjoyable experience, right? But it is partitioned by, let me make that a little bit bigger. Thank you, Aaron Stilato, for project, for product managing this wonderful live result set scrolling, zooming into SSMS.
Of course, you could always scroll results. Now we can zoom on results. But this is my Votes table.
It is partitioned by the creation date column. And it is partitioned by year, right? And you can see that I have followed the partitioning bible. I’ve got an empty row group on either side.
And, well, everything is pretty okay there. Now, it’s sort of annoying. Now, this has nothing to do with partitioning performance in general. So, it’s annoying that batch mode doesn’t show the details of which partitions were accessed in the show plan XML.
So, for a lot of these demos, I’m going to be disallowing batch mode so that I can show you kind of what’s happening with them. So, if we run this and we look at this query, or rather, we look at this query plan, we say, look, we executed our execution plan. We did a great job.
We have obeyed many, many rules of partitioning. And we will see that we only had to access one partition to find our data, which is wonderful, right? Because we just looked for the year, the data from the year 2013, right?
And since our data is partitioned by this, we can find that data easily. The thing is, there is absolutely no difference between seeking into a partition like this and there is seeking into a B-tree index that would happen to lead with creation date. Because our clustered primary key on this table leads with creation date.
So, we can do that sort of thing. It’s almost like just having an index. It’s like, now we have all sorts of other stuff now kicking, going in, and getting a problem, and causing problems for us. So, some things that we normally need to not pose tremendous issues for C++ server, things that cause issues for partitioning.
For example, our partitioning problem is in the daytime. And it seems that Antiglator’s optimizer hasn’t been able to handle this as a study for lately. But, uh, when, uh, when, uh, when, uh, when, uh, when, uh, when we, uh, when we declare a local variable in the day, we think that we need an option to compile that we’ve done here.
That will allow the primary writing optimization to happen. Uh, um, we don’t necessarily get the C++ data results. Unless that battery maybe gets not quite as snappy as the other area. And now we have this whole kind of a strange area where we are.
And we can see the access to all of the partitioning, even though C++ server is able to handle that in a, in a, in a similar way. But, uh, the thing that we know more in that sub-clarity reform is also sub-clarity, uh, partitioning in the nation. So, wrapping a column, like, uh, wrapping a column in the, in the year function, just as bad partitioning as it is with, uh, as it is with the normal index.
Um, and, of course, if you try to VCC and run on any kind of, uh, even, even something that is sort of transparent from the optimizer is convert for eight. Right? Because it’s, like, like, again, C++ server is a smart number of this stuff.
But, um, if you try to convert from eight, eight, uh, here, we, we do not get the results that we would, we would want to get, you know. So this query is also not quite as snappy. And if you look over here, here, you will see, uh, well, this actually doesn’t, it doesn’t show anyway.
I’m not sure what happened here. We don’t even get the actual number of partitions that we, that we, we used on there. So, that’s a fun one.
Apparently, we eliminated it and did not. So, so, the other problem you can run into with, uh, tables that you have partitioned is around line indexes. So, uh, it, what, what, what we’re showing here, you’re getting an end on creation date.
Right? So, creation date dates, the partitioning column. So, those bills don’t justify, like, getting in a creation date, these, these, these query plans, and it’s sort of, or, if you, you know, I, first of all, this used to look at the query plans like this, that, like, basically just says they need to top one, like, from, um, the, from the table, and, like, it’s fine, right?
So, it’s very easy to do. But, if we, uh, run on that query read, we say, hey, I want to get no type by date. And I have an unhonustered index.
I have a line to the partitioning scheme and everything else. Because I want to be able to swap data. And, oh, I do not find my indexes. And I cannot swap data.
And that would lose the data. And it manages the partitioning. Well, this, this doesn’t, doesn’t wait until so soon. Right? See, the results of my list does not have the yielded ability to do things in the same way. Notice, when I’m joking, we have that possibility.
Now, we have a plan with a two-screen magnet. There’s a paracarons. Yadda, yadda, yadda, yadda. And you look over here. Remember, we’re going to see, we, just, just, just to find the new type ID. We looked at all of the politicians.
Right? Right? So, non-aligned indexes can compose real, real politicians. We looked at all of the politicians. Just to sort of contrast that in some of the same thing. If I want to get, like, a min, min, type ID in the votes table.
I already have it. I have the same, basically, like, that index up, you know, I’m going to hover it over right over there. There.
But we need to type ID. So, see, this should be explained to my min, min, type ID very, very reasonably. So, instead, it looks at all of the partitions and scans things. Right? The optimizer is just very, very interesting. The same thing with that.
If we look at this, this unpartitioned table. And we say, getting an min, type ID, I have. I have the same, basically, like, of course, the table of the partitions and the index can’t be aligned.
It’s just, like, I have another index that we can use a type ID. We can get a query branch, which you want. We’ll say, say, again, get the top one, so I can find . Partitioning doesn’t mess that up.
So, we look at some differences here, right? I’m going to run all these queries together. So, the first one is using a non-partitioned table, right?
I’m saying . The second one is using a non-aligned index on the partitioned table, and the third one is using the aligned index on the partitioned table.
Three very different performance quiz proposals. The first thing, we have the plan shapes that we want. We just, in order to find my name from a query from a table, it has that index, right?
The index presents that data in that order. Even if it was in the sense in order, it would just say, okay, go to the other third and work. These are the plan shapes we want.
But using that aligned index, we have to scan all that, but the 3.5 steps in a query, instead of that 0, the second query we do using the non-aligned index. On the partitioned table, right?
So, that’s also something to think about. There are other types of queries that can pose similar performance outputs. So, for example, if I say, you can give, like, you can give, like, you can give, like, you can give, like, you can give, like, you can give, like, you can give, like, you can give this list of the query we’re in shape that you would expect.
So, I say, you can give the top five, I know this index is equal to type ID, just to make sure that, like, it stays consistent. And, and, and, a lot about type ID, this decision is very quickly, we have a short story in here, here, like, this is all zero analysis, right?
Nothing in here is taking time. If I choose the non-aligned index on the, pardon, that is still fast, and that is still, just about the same data we got before, and maybe not exactly the same, but it is, it is close enough here.
Here. This line index, if I tried to run here, it would take a lot. If we come over here and look, you’ll see this query, when it ran, took a full minute, exactly, to the second, one minute, right?
We scan that index, that 35 seconds, that can be sorted, maybe this build, that in about six seconds into the mix, and then between, uh, let’s see, uh, 41, what’s, that’s 52, so, uh, this is about six seconds, the loop joint itself, 41 seconds, going to be, look out, 52 million, right?
Uh, that’s not great. That’s not a good strategy. Right? Uh, all sorts of queries, in the central server, can get very, very strange, once you lose partitioning.
So, so, the next time someone sees the partitioning of the formal feature, laugh at them. Say, it’s a date of the internet feature, and if you’re looking at the closest, it’s, I mean, it’s not what you can find, right?
Because, they’re asking for it. This is, it’s not new information. Uh, you can work with robots a bit, but, like, like, if you grab the, uh, partition numbers from, like, all these tables, and dump them into a table, like a table variable, you find here, here, and if you say, give me these things, and then, and you rewrite my query, right?
So, we just put all the partition into that table, and we have to rewrite our query in kind of a strange way, right? Let me say, type ID, uh, from, the cross-apply button here, right?
In here, we are saying, okay, well, we need to take the partition numbers that we have just put into that to them table, and we need to record them, we need to use those weird, weird dollar sign partition functions, and we need to feed them the creation date column, and we want the table to partition it by, and we say, hey, match the partition number, and that turns out, alright, right?
It’s like, almost the square here, and shape, shape, and line. It’s a little bit more complicated, but it does, does, does best define it. But there’s a lot of times when you just, you’re not going to rewrite my query, as much as possible.
If you’re using ORM, good, good luck with that, right? Dummiesies. If you’re dealing with a vendor application, where you can’t rewrite the code, maybe you’re as free as you want, good luck with that, right?
There’s like, things that you can do, but, they’re not always straightforward, right? They’re not always easy as things in the world, well. And, if you, but if you’re allowed to rewrite the query, you’re going to take advantage of some of this stuff, you can work around it, since the social distancing is partitioning, and get performance links you would get, if you didn’t bother to partition that table in the first place.
So, again, partitioning, and these are sort of, with rows or indexes, it’s not really a strong component feature. If you need to swap data, and all that other stuff, I, I get using it.
But, you also have to, in order to do that, you have to have your indexes aligned, to the partitioning, and if you don’t do that, and you will not really need to do the switch choices. Which is, probably, you might not want to do that in the first place.
But, when it comes to this, like, like, like, normal, running and hitting stuff, partitioning is not a solution, that you should be exploring for that.
Like, just this normal indexing the table, you know, much, much more abundantly, much, much more efficiently, without having to redo the entire table, and worry about all this stuff that comes along with partitioning, and then getting a very, very client, when you at least expect it.
Anyway, thank you for watching, I hope you enjoyed yourselves, I hope you learned something, and again, this is, this is a snippet from my larger environment, he sees, there’s a link down that video description, with a coupon for the cash, if you want to purchase the whole course, and learn the full breadth of the material, you can do that.
Anyway, thank you for watching, I hope you enjoyed yourselves, I hope you learned something, and I will see you in tomorrow’s video, where we will, inspect, partitioning with columnstore indexes, where there can be some performance benefit.
Alright, thank you 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.

Learn T-SQL With Erik: Columnstore Modifications

Posted on by Erik Darling

Learn T-SQL With Erik: Columnstore Modifications

Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video we have something to talk about that not a lot of people talk about when they talk about columnstore, and that is the detrimental side effects of modifications on columnstore indexes. There are, you know, of course, when people talk about them, we talk about the wonderful performance advantages they give you for read queries. You know, if you are dealing with a columnstore index, your data is highly compressed, so you are, you remove sort of an I.O. bound potential from your query. And the use of batch mode, of course, not, which is not specific to columnstore indexes, of course, but, and, of course, you can read from rowstore indexes using batch mode as well, but it is not as efficient as when the base object is columnstore. In batch mode, of course, due to its SIMD magic, sending multiple rows to CPUs for processing can remove the I.O. bound portion. But, there are things about modification queries that can hurt your columnstore indexes, and that’s what we’re going to go and talk about today.
Anyway, down in the old video description down yonder, you’ll see all sorts of helpful links. You can hire me for consulting, buy my training, become a supporting member of the channel if you’re feeling generous across those things. But, for free, you can always ask me questions for my Office Hours episodes, or you can do me a real social solid and like, subscribe, and or tell a friend. I should probably not make any of those optional. I should probably force you to do those like boot camp calisthenics.
But, anyway, down also in the old video description is a link, and that link will allow you to download and use my absolutely free SQL Server monitoring tool. It is an open source project that I released, I think, back in February, and we’re up to version, at least of this recording, 2.5. It’s come a long way. It’s seen a lot of neat changes, a lot of great contributions, and is sort of getting its way out into the wider world where, you know, issues that I wouldn’t find locally get found and resolved pretty quickly.
So, it is reaching a nice, stable, mature point at this point, and I am really happy with the way things are looking. But, if you are in a shop that has a bunch of SQL Servers that you want to monitor, and you can’t get anyone to spend money on a monitoring tool, this is a wonderful way to get great performance insights into your SQL Servers without having to fork over $1,000 a server a year or something to some just absolute buffoon who has never actually troubleshot SQL Server performance before in their life.
So, there are some pretty big advantages to using my free monitoring tool, right? Should be, at least, I think, obvious and apparent based on the source. And if you are on the cutting edge with the robots, there are optional opt-in MCP servers you can use.
You can have your favorite robot talk directly to your monitoring data and only your monitoring data for what I think is… I mean, it may not always give the smartest advice about it. It may not give you, like, the best resolution to things.
But it is very good at digging through the monitoring data and summarizing issues and giving you, at least, a nice write-up on what it finds in there. Which is a whole lot better than just letting it run roughshod through your whole SQL Server DMV family and all that stuff. If you do not care for the robots, if you still care for human warmth and kindness, you can experience all of my human warmth and kindness out in the world.
I will be at Pass on Tour Chicago. That will be May 7th and 8th. From there, I’ll be heading over to Poland for SQL Day, May 11th and 13th.
Then I’ll be home for, I mean, just on the cusp of a month. Then heading to Data Saturday, Croatia, June 12th and 13th. And then, of course, we have, you know, the big Pass Summit.
I don’t know, is it Pass Summit or Pass on Tour West now? I don’t know exactly how they’re branding it. But that’s in Seattle, Washington, November 9th through 11th.
And with that out of the way, let’s play ball. Woo! Eddie! Yeah! All right. So, columnstore modifications. I’m going to drop and recreate this table because I forget what I’ve done so far.
But then while I do that, we’re going to talk a little bit about at least the delete portion of the world. So, when you delete data from a columnstore, it’s not like deleting data from a rowstore index, whether it be clustered or non-clustered, where it just sort of disappears. It’s a little bit more like when you delete data from a heap, kind of, like sort of kind of.
So, like with heaps, when you delete rows, the rows are actually gone, but SQL Server doesn’t deallocate the pages automatically under various conditions, which we’ve talked about in recent videos. So, if you miss those, go watch those. But what happens is SQL Server just marks rows as deleted in this, like, bitmap, and it sort of just leaves behind things in there.
But we have this marking that says the row is deleted. But if you’re doing a lot of deletes or, as we’ll talk about later, updates to a columnstore index, they can accumulate over time and they can degrade performance, especially on, you know, big tables where a columnstore is usually, you know, a little bit probably preferred for these, for, you know, some workloads. So, the delete marks the rows deleted in the bitmap, doesn’t remove them from the row groups, scans, still read deleted rows, and then filter via that bitmap.
The more deletes you have, the more sort of wasted IO and CPU your read queries will have when the number of deleted rows becomes significant. And segment elimination will not skip over those things either. So, with that table freshly loaded, let’s run this query.
And the only thing that I want to show you here, the results are entirely unimportant, but I will zoom in on them anyway, is we start, we read 10 segments, and we skip 41. All right. Now, we’re going to delete a bunch of rows.
Do-do-do-do-do. And this takes about 10 seconds. You know, we’re not deleting, like, a bajillion rows. So, like, let me go back up a little bit.
The table that I made is really just a copy of the votes table in the Stack Overflow 2013 database. So, it’s about 53, 54 million rows somewhere in there. So, if we look at how many rows we deleted.
Oh, I don’t have query plans turned on. That’s all right. So, what we can do is, let me turn on query plans despite just running a DMV query. We’ll see that we have about 1, 2, 3, 4 million, 4 and a half million, plus some others.
Right? And so, like, these row groups, even though, like, all the rows in them are deleted, we still have these compressed things hanging around in there. And if we run this query, and now we get no data back because, you know, we’re filtering on, like, the date for the date range that we just deleted everything from.
Like, we still scan that table and we still read everything. And if we look over here, we still read 10 entire segments. Right?
So, we don’t actually skip anything there. If we run, let me actually look at this first. Oops, that was control and R. So, we have this, right? So, this is our current situation.
If we run a reorg and we say compress all row groups, and why in SSMS 22 we still don’t understand that compress all row groups is valid syntax, I don’t know. That is interesting to me. I don’t know who to talk to about that, but maybe they’ll watch this video and maybe they’ll say, ah, Eric, you’re right.
We shouldn’t do that. So, now we have all these things, but they are now tombstones. Rather than, say, compress, these are tombstones.
And if we, so this was actually a good run where, like, I actually saw that happen. And now we get nothing back here. And if we look at the messages, we still have five segment reads.
And the reason we still have five segment reads is if we look at this, right? We have these tombstones in here, but we still have one, two, three, four, five row groups, segments or row groups with deleted rows in them. And SQL Server is like, well, I don’t know.
You might, you might, could be in there. Maybe there’s something that I need in there. But rebuilding the index, if I, if I were to run, there are some problems that running reorg a second time will fix. This is not one of them, right?
So, like, running reorg a second time won’t get rid of the, like, those last few things in there. But running a rebuild, fully rebuilding the columnstore will do it. You know, I’m a, I’m a pretty firm believer in the, the max top one thing for columnstore.
Um, you know, it can, it can drag stuff down pretty bad. Like, this thing has been going on for about 30 seconds now running this rebuild at max top one. But it really is the best way to get the best compression out of, uh, your columnstore indexes.
And now if we run this, we look at messages, we will see zero segment reads because we could completely rebuild stuff and got rid of all of the remaining deleted rows, right? So now nothing in there is, has a deleted row count. Unlike the last query, uh, if I just run this portion, you’ll see, of course, all the deleted rows have been removed.
So anyway, um, the other thing that can impact, um, columnstore indexes are inefficient inserts. So, uh, the Delta store, um, so like the Delta store is different from the deleted bitmap portion. The Delta store is like this clustered B tree that is uncompressed and just sort of brain leaches onto your clustered columnstore index.
Um, if you have uncompressed row groups, that’s where those live. Um, if you have a lot of open Delta row groups because you’re just accumulating inserts or, um, you’re doing a lot of updates, which is a delete and an insert for columnstore. Um, then you’ll, you could end up with, you know, uh, sort of like similar problems like we saw before where like, you know, you’re probably reading too much.
Now you’re reading also from this rowstore thing on top of the columnstore thing. The data is not compressed. So you’re just not getting maximum effort out of your columnstore indexes.
Uh, there are some things that the SQL Server will do, um, in the background to try to help you like the tuple mover. Uh, that’s a background process that runs every five minutes or so and compresses closed Delta row groups. But those row groups have to have, uh, greater than 102,400 rows in them.
Uh, you can, of course, run a reorg to fix that as well if you are impatient or, um, you know, you want something to happen that, you know, isn’t happening with, but with the tuple mover by itself. So let’s, uh, let’s run some intentionally bad inserts against our columnstore table. And what we’re going to do here is wait for this to run.
And I forget how long this takes. I should probably time these things. Didn’t I?
It’s probably a good idea. Anyway. Oh, it’s done. How lovely. So now if we look at our columnstore, we will see that we have this one row group that is open. And we have no deleted rows in it because that’s all inserts.
And if we run this query, uh, we will now see that we read 20 segments, but that’s not really the thing that I want to call out here. The thing that I want to call out is that now SQL Server registered actual logical reads associated with the columnstore. Now we can run a reorg.
And for some reason, like the, it’s either always either the first reorg or the second reorg that takes a second for things to happen. Um, but after running that and looking at the messages tab, we see that this is bumped down to zero. We do another segment read, but that’s because all those uncompressed rows that we were reading from up here are now in a new, uh, segment in the row group in the columnstore.
So where we just have another one of those to check through, um, that’s other thing that is, gets annoying is updates. So, uh, updates, uh, not only move row or not only mark rows is deleted in the bitmap, but we’ll also insert new rows into the Delta store. So columnstore indexes use this special row locator called call store lock or loc, I guess, uh, XXXX, and that XXXX will be a number for you.
Uh, but it identifies the row group and position within the, it identifies the row group and the rows position within the row group. Uh, it is a bit different from the rid that you see with heaps or clustering keys that you’re probably used to from normal tables. Um, and, uh, but this is why updates with columnstore indexes are particularly expensive.
You have to mark the row as deleted in the bitmap, and then you have to insert the new version of the row into either the Delta store or a new row group. You can’t just do in-place updates with these. They’re sort of like forwarded records.
There aren’t, they’re not forwarded records. They’re just sort of like them because there’s sort of similar overhead, uh, once they really start accumulate, once a lot of updates really start accumulating against your columnstore indexes. So let’s turn off query plans so they’re not on for this loop, and let’s, um, run an, run, run an update cursor.
Well, I mean, the, it’s not really an update cursor because you can’t cursor over a columnstore table, but you can cursor over a temp table. And you can use values from that temp table to do stuff with the columnstore table, which is an interesting sort of, um, I don’t know, something on the, on the, uh, documentation. Cause it’s like, no cursors with columnstore, but then you’re like, ah, but I’m cursoring over this other table and using stuff from that other table to update the columnstore.
But what this is doing is essentially updating, oh, what a thousand or so rows at a time. And, uh, then that’s going to give us a bunch of uncompressed row groups. And, um, so like we have this thing that’s open.
And then like, if you look through these, like look at all the deleted rows, right? Just like a small number of deleted rows in each one of these, right? So we have about 10,000 total, well, 10,872 total rows deleted.
That’s the number we started with, uh, at the start of the loop. Uh, we have, um, this thing and this is, is the new, uh, uncompressed row group that we created from the 10,872 updates we did, right?
So we like, essentially like every update, like I said, is a delete with an insert. So we do like when we, every, we, we updated 10,872 rows total, which means now we have 10,872 rows across deleted bitmaps. Then we have 10,872 rows in an open row group that is uncompressed.
That’s, that is the Delta store, right? And if we look at this thing, we will see over in the messages tab, uh, some number of logical reads. It’s not a ton here.
Like this is not like break columnstore numbers bad. This is just easy numbers to show you. So you can see what I mean that, uh, we can, of course, uh, attempt to fix it with, by compressing all row groups and then coming back and looking at this.
And what we’ll see is that, well, now we have these two tombstones. We still have a small hundred number of deleted rows in here and like, it, like things are maybe just not like where we would want them to be for this.
We would of course need to rebuild our columnstore index again, and that would bring us back to a joyful columnstore state. So just a few messages here about columnstore.
Um, you know, be careful how you, uh, a put data into them and be, if you find yourself doing a lot of deletes and updates against your columnstore, be prepared for those deletes and updates to maybe be a little less efficient than you’re used to when you are dealing with rowstore indexes.
You can solve some problems either waiting for the tuple mover to do its thing or by reorganizing with compress all row groups equals on. But there are some problems that just don’t get fixed until you rebuild your columnstore indexes from scratch, right?
So now with all that stuff out of the way, if we look at messages, we’ll have those 21 segment reads that we were used to. And we have gotten rid of the logical reads for this, which means we are not reading from the Delta store.
Again, I am not a big logical reads person. Uh, they are a secondary artifact of a slow moving query at times, but they do not like, like I’m a much more on the CPU duration front. CPU and duration was never hurt by the, the, the columnstore stuff that we did.
Uh, it’s just an easy way to show you the behavior. So anyway, uh, this is all part, uh, this is a small snippet of material from my course Learn T-SQL with Eric.
Um, it’s available for sale. Of course, uh, the link with a coupon code is down in the video description. So if this kind of stuff is what you’re into learning about in your free spare time or for your job, or maybe, I don’t know, like watching these sorts of videos on the job and you can be like, boss, I’m learning, uh, then I would highly recommend purchasing the course so that you can learn all of these things.
Anyway, that’s about it for here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where we are going to, uh, I believe we’re going to make fun of partitioning. So that’s always a good time.
Anyway, thank you 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.

SQL Server Performance Office Hours Episode 59

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 59


To ask your questions, head over here.

Chapters

  • *00:00:00* – Introduction
  • *00:01:15* – Query Compilation vs Execution Time
  • *00:04:07* – Lowering MAXDOP Reduced CX Costs but Blocking Got Worse
  • *00:07:29* – Top Three Mistakes When Reading Execution Plans
  • *00:10:12* – Query Compilation vs Execution Time
  • *00:13:50* – Conclusion

Full Transcript

Erik Darling here with Darling Data. Back to Rock’em Sock’em Monday, Monday, Monday. We are going to do our world-famous Office Hours episode in which I answer five user community submitted questions about life, love, SQL Server, protein shakes, exercise routines, whatever, really whatever is on the minds of the good people of the world. So, suppose it’s time that we start doing that, then. Anyway, down in the video description, you will find all sorts of helpful links, not least of which is a way for you to ask me Office Hours questions, but you can also do other things that I find useful, like hire me for consulting, buy my training, become a supporting member of the channel in order to fund my endeavors to keep bringing you this high-quality SQL Server content, and, of course, if you do it, enjoy this, and you think someone else might enjoy this, then you have many options before you. You may like, you may subscribe, and you may tell a friend that this channel exists, and then that friend can subscribe to the channel and like things, too, and then they can tell a friend, and so on. We get the network effect, don’t we, right?
It’s a very important thing with the social medias and other stuff, right? Broad user base. If you like free SQL Server monitoring, boy, have I got a deal for you. I’ve got a free SQL Server monitoring tool that looks at all the stuff that I would look at if I were monitoring SQL Server, which, coincidentally, I am now.
So, that’s what you get. Cool, huh? Wait stats, blocking, deadlocks, query, CPU, memory, disk, tempDB, you name it. I monitor it, and I’ll even tell you when it stinks. I got built-in robots, so if you want to have your robots talk to my tool set, you can turn that on, and you can have your robots connect to the MCP tools, and those MCP tools can look directly at the performance data and just the performance data and have a very, very well-defined set of things to look at and tell you interesting things about.
So, I would highly suggest that you ditch that lousy Sunsetware paid monitoring tool that you have and jump on the free future of SQL Server monitoring. If you want to learn stuff from me out in the world, I will be out in the world. I don’t know why my voice is getting like this. I think I’ve been talking too long. I will be in order. I will be at Pass on Tour, Chicago, May 7th and 8th. I will be at SQL Day Poland, May 11th through 13th.
I will be at Data Saturday, Croatia, June 12th and 13th, and then I will be at Pass Summit in Seattle, Washington, November 9th through 11th. What is happening between June and November? I have no idea. If you have a conference between June and November that you might like me to show up and do a pre-con at, like these other nice folks have, well, drop me a line. Let me know.
Maybe I can show up and do one there. Who knows? Crazy things happen in the world, don’t they? All right. All right. Anyway, for now, it is April and we are home and we are recording videos and we are making sure that the SQL world stays well informed. So, let’s answer some office-y, hour-y questions.
Does optimize for unknown ever really solve parameter sniffing or does it mostly just hides it? Well, it 100% just hides it, right? Because you don’t sniff parameters anymore. SQL Server comes up with an average guess and uses the same average guess for every single query that runs.
This can, in rare circumstances, fix a problem. I’ll never say never, right? I’ll never tell you to never do something. Well, there are a few things I would tell you to never do.
Like, don’t tattoo your eyes. That looks really painful. That’s not my favorite look. But, yeah, sure, everyone, yeah, optimize for unknown, you know, it prevents parameter sniffing, right? Which can, in turn, prevent parameter sensitivity.
But it can also present a pretty lousy plan to SQL Server that a lot of, that all your queries will then use. Kendra Little once referred to this as optimize for mediocre. I think Kendra Little may have changed her mind a bit about this because she has bragged to me about using optimize for unknown in several places to solve a parameter sensitivity issue, which apparently an average guess was better across a variety of query executions than parameter sensitivity was.
So, yeah, it can eventually, it can occasionally solve a problem in the space. But, you know, for me, there are other more attractive solutions that I would invoke, largely involving either recompile hints or dynamic SQL. Maybe a little query and index tuning to go along with them.
Who knows? Crazy. But, yeah, it, it, it, it does mostly just hide it. By mostly, I mean 100% just hides it. So, moving on.
Let’s see. How much do implicit conversions actually matter in real life workloads? Is this one of those, it depends things? Oh, just, no, just one of those depends things.
Not it depends, just depends things. Yeah. So, there are really two different types of implicit conversion that you might see crop up in an execution plan. But, there are ones that may affect cardinality estimates, which, which don’t, don’t, don’t really rile me up so much.
You know, SQL Server throws, we’ll, we’ll show that in an execution plan. And I’m kind of like, well, okay, where? Like, was that cardinality estimate going to be great anyway?
But the, the seek affecting ones, those are the ones that get to me more. Because those are the ones where someone has mismatched a data type in such a way where SQL Server must convert the column values to match the value of some input variable or parameter. And those are the ones that I find are far more deleterious to performance than their counterpart, which is the, the, the one that might affect a cardinality estimates or with a plan affecting convert or something.
So, you know, while it does depend, it is also one of those, you know, one of those, one of those errors that just seems so mind-bogglingly simple to not make, right? Just a completely unforced error that, that, well, sometimes they’re forced on you large, I see a lot of ORMs mess this up, where they will infer some data type, regardless of the column it is being compared whence to. Is an envarkar of a varying length, right?
Like, like, like, like, you’ll see plans where, like, you know, like, if, like, like, let’s say it’s a, like a, well, I mean, it doesn’t really happen so much with dates because dates sort of have a standard length to them, like, you know, 20, 26, 0, 3, 2, 8, at 3, 23 PM, right? Like, like, like, like, the length of those is all kind of standard. But if you were to look at, like, a name, like, you know, my name, my name has four letters in it.
So the, the, the, the ORM word would infer it as an envarkar 4. But if you said, like, Tom, it would be an envarkar 3. And if you said Bobby, it would be an envarkar 5.
So, like, you would see all different, like, length parameters getting passed in. So not only would you have, like, this sort of lousy implicit conversion if you’re, let’s say, first name column or not, we’re, we’re, we’re a varcar or not an envarkar. But you also have the sort of, like, I’m going to create a query plan for every variation here problem, which, which sucks too.
But, um, yeah, you know, it, it, it, when, when you, when you find a problem with it and you solve a problem with it, um, it is often a night and day difference in query performance. But, um, you know, again, we’ll depend, how night and day will depend much on the size of the table and other, other things going on. Let’s see what we got here.
Lowering max dot, reduced CX weights, but blocking got worse. Why would changing parallelism affect blocking at all? Well, when you lower max stop, you lower the amount of available CPUs that a query might have, uh, at its, at, at, at hand in order to execute.
So let’s say that you have a query that runs for, I don’t know, one second at max stop eight, and then you change max stop to four. And now it takes like, I don’t know, four seconds or even two seconds. Um, that’s another two to four seconds of the query executing and another two to four seconds of the query holding locks, maybe.
All right. It’s, you slow the whole thing down, right? So the lowering max stop, sure. Yeah.
CX weights go down. Congratulations. You cracked the case, bucko. But, uh, now all your queries are going to be dot slower because they have dot fewer cores to, to process rows with. Welcome to the world of trade-offs.
Take an economics class. Or read an economics book. I’ve never taken an economics class, so it would be silly of me to tell you to do that. Uh, but you could, you could read a book on economics where you would learn about these sorts of trade-offs in the world.
Uh, anyway. What are the top three mistakes people make when reading execution plans? All right.
Looking at costs, looking at costs, and looking at costs. Those are the top three. How often does query compilation actually matter versus execution time? Um, it can certainly matter.
Um, I’ve run into some funny cases where, um, like, you know, adding a recompile hint, uh, would, of course, you know, get, like, solve, like, problems with either local variables or parameter sensitivity. But, um, now like, you know, like a query, let’s say that when, uh, you, let’s say that, uh, you weren’t using optimize or you weren’t using option recompile, right? You just had a standard plan that came in, and for most query executions, it was totally fine.
So you pay that one-time compilation cost. And let’s say that one-time compilation cost was 500 milliseconds. And then every time the query ran after that, it was, like, zero milliseconds.
Except when you hit a parameter sensitivity problem where it ran for, like, five seconds. So every once in a while, the query would be, like, five seconds. Ah!
But the rest of the time would be very, very fast. Like, let’s just say it’s, like, a date range or something. And most of the time, people are looking at, like, the last hour of data. But then every once in a while, that knucklehead comes along and is, like, I want to see six months of data. And that knucklehead’s query takes five seconds, okay?
So then now, you throw an option recompile hint over here. And you no longer have the occasional five seconds. But now every single time that query runs, it takes 500 milliseconds. Because you have to compile that plan every single time now.
So it does get interesting when you think about, like, again, the economics of these things, the trade-offs that happen. So you do have to balance all this stuff out. And maybe this is a better case than, like, instead of just saying option recompile every time, you could use dynamic SQL.
And you could do something, like, if the difference in, like, days or months or whatever between two parameters is more than an hour, you recompile. But the rest of the time, you don’t recompile. So you do get rid of the parameter sensitivity, right?
Because you no longer have that, oh, the same parameters. I mean, you used a plan. It was bad. No kidding. And now you replace that with every once in a while, you pay an extra 500 milliseconds, and you recompile and do that. But the rest of the time, you’re not recompiling, and everything stays okay.
So how often does it actually matter? I don’t know. If you hire me, I can tell you. But in general, it can certainly matter.
It can be a very interesting thing to deal with. Anyway, that’s the last question. That’s fine. Save the best for last on that one.
Thanks for watching. Hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, where we will get back to the Learn T-SQL with Eric material. And we will continue our voyage through different storage materials, different storage media, I guess, in SQL Server, right?
I think this week we’re going to be talking about columnstore, partitioning, partition views, some other stuff, right? Interesting things afoot in the world. Anyway, that’s it for me.
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.

Learn T-SQL With Erik: Nonclustered Index Interactions

Posted on by Erik Darling

Learn T-SQL With Erik: Nonclustered Index Interactions

Chapters

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are going to continue with our little baby slices of material from my Learn T-SQL with Erik course. And today we are going to talk about nonclustered indexes and their relationship to either the heap or clustered table that they were created on. And of course, this, you know, again, this is not specifically like, here’s how to write a query, Learn T-SQL, SQL Server stores data, and learning about how those data structures relate both to each other and to the base tables that they are created on. In this case, is very, very valuable for people who want to seriously practitioners T-SQL, because without this knowledge, your queries are going to suck. They’re going to be terribly slow, and people are going to be angry at you. You can be as advanced as you want in the writing of T-SQL, but unless it, unless those queries ever finish, no one’s ever truly going to apply and appreciate them. So, that’s why we must talk about these things. So, down in the video description, you will see all sorts of very helpful links for you to give me money. You can hire me for consulting. I still need to tell people what to do with their SQL servers, which is fine with me. I don’t mind doing that. In fact, I’m rather accustomed to it. You can purchase my training materials. There is a link, actually, with a discount code attached for this very course.
Right? Bloop, bloop, bloop, bloop, bloop, bloop, bloop. You can become a supporting member of the channel to keep the wind in my sails, the gas in my engine, whatever. Does the gas go into the engine? I don’t know. I’ve never had a car. But to keep creating this high-quality SQL Server content for you or one of your friends, if you share this video with one of them after you like and subscribe, if you haven’t done either of those already. I don’t want to be repetitive here, but unless I say these things, people just sort of passively watch and then I never see them again.
If you would like to monitor your SQL servers for free, I have a very, very free $0, zero-cost monitoring tool available up on GitHub. It’s this link, but it’s down in the video description. You got to go click on something, right? But it’s totally free, totally open source, no email, no phone home, no weird telemetry data. It’s just all the stuff that I would look at if I were a SQL Server monitoring tool, which now I am. So now you know what I’d look at. Congratulations.
Weight stats, blocking, deadlocks, queries, CPU, memory, disk I.O., all that fun stuff. You know, normal SQL Server monitoring stuff, just way better. And if you are the type of robot-loving person that seems to make up the majority of the IT world today, you can turn on, optionally, if you can opt in to using MCP servers to allow your robots to talk to purely your monitoring data.
And they can make a much better sense out of what I have collected than if you were to just go allow them to run random DMV queries on their own. Because, I mean, look, I’m not trying to get on the case of the LLMs and, like, all that, but they still mess a lot of stuff up, and they still make a lot of stuff up, at least, you know, in my dealings with them.
No, I don’t know that I would just let them go do that. But the MCP tools that I built in here are all very well defined. The schema is all sort of predefined.
The results, like, the columns that they represent is all predefined. And so it’s much easier for them to manage that. There’s nothing to hallucinate there. So they could still make things up, but they can’t hallucinate things existing or not existing, which is supposedly a step in the right direction.
Anyway, if you would still, if you still value human interactions, I will be leaving the house. And when I leave the house, these are the places I’ll be going, right?
I’ll be in, well, let’s do this in order, kind of. I’m going to be at Pass on Tour. I’m going to lose my entire arm up here. I’m going to be at Pass on Tour in Chicago, May 7th and 8th, right?
And then from there I fly to Poland from May 11th to the 13th. And then from there I fly home. And after I’m home for about a month, I fly to date a Saturday, Croatia, June 12th and 13th.
And then I’m going to be home for a little, as far as I know, I’m going to be home for a little bit. And then I go to Seattle, Washington, November 9th through 11th. So get your dance and rain boots on for that.
But for now, it is April. And April is baseball and floating hot dogs. Because this gentleman database is clearly either, that thing is about to drop to the ground.
Or, I don’t know, maybe it’s like floating to his mouth. I don’t know what happened there. But everyone’s got beer and they’re happy because they won.
At least they think they did. This guy looks angry. You know, like angry eyebrows. This guy’s kind of like sad looking. This guy’s angry looking too. Champion’s guy looks happy.
But he’s, again, wearing red. So I don’t know how he got into this picture. I feel like, oh, this isn’t brown. I don’t know. Someone’s about to get shot, maybe. Anyway, let’s talk about nonclustered indexes. Now, we cannot talk about nonclustered indexes without some sort of actually type person piping up and saying.
But what about, what about, what about, I don’t know, chicken little. What if the sky does fall on your head? I don’t know what to tell you.
What, won’t they slow down inserts, updates, and deletes? I’m like, yeah, merges too. They do have an impact on these things. But we’ve got this query over here that’s running for like a minute.
And if we add an index, it’ll run really fast. So maybe reducing that query’s impact on the server sort of amortizes the cost of maintaining this index for the inserts, updates, and deletes. Maybe that’ll happen.
Maybe there are trade-offs that we have to make in life. Maybe there are things that we as professionals in the database world have to test. But make sure that they work as expected.
I know, it’s crazy. So yes, every index is a separate object on disk and memory. It is a copy of the data from either the heap or clustered table.
Every index will add some overhead to modifications because you’re going to have to write those changes to the transaction log. You’re even going to have to lock them to complete writes. All right?
My goodness. Even indexes that are unused by reads. And we hope to get rid of those. We do seek to remove indexes that are not helping our queries go faster. So don’t keep those around. Need to be maintained because the SQL Server doesn’t know when a query might show up and need to use them.
All right? So we need to read those indexes into the buffer pool. Sometimes a little teeny bit of the index if we do a seek. Sometimes a lot of an index if we do a scan.
Right? So the main points that poor indexing habits can introduce, the main problems that they can introduce to your systems, consist of typically these three points.
Buffer pool contention because you have more objects vying for space in your buffer pool. You have more transaction log activity because there are more objects you need to write information about changes to the transaction log. And you may even see increased locking and maybe even increased lock escalation attempts and sometimes even successes.
Assuming that there are no competing locks that would prevent a lock escalation from becoming a success after it gets attempted. So there are things that we do have to be aware of when we are indexing things. So all of these things being true simultaneously, we must come to an agreement, you and I.
We must come to an arrangement that suits everybody. We must compromise and we must say that we need indexes but we should enjoy them responsibly. The same way that we enjoy other leisure activities responsibly so that we don’t end up throwing up on ourselves.
All right? But we also need to query responsibly. Don’t we?
The wider your indexes, or rather the wider your queries are, the wider your indexes may have to be in order for SQL Server to actually use them. All right? Because if we are writing select star-ish queries, or if we have queries that are, you know, attempting to sort of kitchen sink, evaluate 14 different columns in a table, well, if we don’t have indexes that SQL Server considers covering for those very wide queries, if our queries have a very wide berth, we’ve got wide hips on that query, well, guess what?
SQL Server might say, ah, no, I’m just going to scan that clustered index instead. And let’s talk about why that happens. That’s a great question.
Why does that happen? I’ve created this index on the reputation column in the users table. It maxed up one so that I get a more consistent statistics histogram. Sometimes that works.
Sometimes it doesn’t. But anyway, if we, the important thing that I want to show you in this query, or rather across these three queries, is the cost of the key lookup in the plan.
Because remember, our index up here is only on the reputation column. And so when we go to get other columns from the table, and I’m telling SQL Server here for a bit of demo stability that it must use this index, or else it will be in big trouble, then SQL Server has to get this column from somewhere, and so a key lookup gets involved.
If we run this query, and we look at the cost of the key lookup, SQL Server says, I think that’s going to cost 47.3194 query bucks to fetch that one column, that one integer column, from the users table, a total of 15,656 times, right?
Because we have to do that many loops to go get that set of columns, or that column from the clustered index down here. Okay, remember that number, four, gosh darn it, you, 47.3194, alright? What if we say, I need to go get an envarchar max column, because this is a count ID column again, this is an integer, right?
And this about me column, well this is not an integer at all. If that other helpful thing will show up, we can see this, about me is indeed an envarchar maximum, alright? Max envarchar there. How much does this cost?
Well, shockingly, 47.3194 query bucks. Oh dear. So, SQL Server assigns the same cost to going to get a 4 byte integer as it does to going to get a book with a key lookup.
Isn’t that interesting? That completely leaves out that we might have to do, like, lob reads, or read off-road pages, or off-road data, or any of that other stuff. It just has the same cost, right?
Wild. And even still, if we sell SQL Server, hey, go get everything from that table. I guarantee you, my friends, this is not a metadata cost that we are concerned about here. Anyone who talks about metadata in these situations should be hit with a hammer.
We have the same plan, right? But we have, also, we have the same cost assigned here. 47.3194 query bucks.
So, getting everything from the table has the same cost, with a lookup, has the same cost as getting just one integer column from the table, right? So, you don’t even have to do a select star query.
Remember, all the folks on LinkedIn who give you the groundbreaking advice to avoid select star. My goodness, you geniuses, you. They are not quite, not quite all together, are they?
Because you don’t have to do select star. All you have to do is get one extra column, even just one integer column that is not represented in your nonclustered index, for SQL Server to need to evaluate the cost of lookup versus clustered index scan plans, and to maybe not use your index just based on that one column difference.
So, while I do agree that select star makes things a little bit harder, it also doesn’t take select star to get index choice to be a sort of painful point during query optimization.
You can ignore that number. That was a previous run. I guess, I don’t know. I installed a cumulative update, math changed on me, or something. Hard to tell.
But, that’s all. All I know is that the last time I ran through this, that’s how much it cost it did. Funny. All right. So, let’s see.
When we let SQL Server choose which index to use, right? And we’re going to look for reputation equals 16 now. SQL Server says 38,000. Nope.
Scanning the clustered index. That takes 234 milliseconds to do that. Okay. That’s fine. Keep in mind that the clustered index scan is a fixed cost, right? So, if we needed to go get, say, like, that was, how many?
38,000? I think 21 is like 58,000 or something. Yeah, 51,000. This will have the same fixed cost, right?
Because we still have to scan the clustered index and do the same amount of work either way. So, that’s 60, oh, let’s see. 68.3519 query bucks.
And that is, execution plan. 68.3519 query bucks. So, because scanning the clustered index represents the same amount of work, no matter how many rows we estimate might come out of that clustered index scan, it is a fixed cost based on pages and whatnot, right?
So, the bigger your clustered index is the more expensive it gets, at least from a CPO and IO perspective. Lookup costs are dynamic, and they are based on the number of times the lookup has to be performed. I find this to be a very sensible arrangement.
If we were to go look at the execution plan for this, we would see that the cost of this lookup is no longer 47.3819 query bucks, but for one single row, it is, oh, you know what? Oh, that’s the right place. 0.0032831 query bucks.
A very small number. So, lookup costs are dynamic based on the number that SQL Server has to get done. There is some math done around sort of like the first one is expensive, but then following ones are less expensive because SQL Server sort of figures, well, like this one might be from an empty, it’s going to be from an empty buffer pool, it assumes. And then like the next one will, like maybe the day is going to be on the same page, so I can read that from memory.
So, like the first one’s expensive, but then the next ones are, the cost is reduced for each one. So, good things to know when one is looking at query plans and whatnot. There is a fun quirk with unique versus non-unique nonclustered indexes.
In the users table, we have this account ID column, which is unique. It is entirely unique. It is a somewhat monotonically increasing number.
However, it is not exactly the same as the identity column that is the clustered primary key on the table, which is the ID column, but we can create a unique and a non-unique nonclustered index on the account ID column, right? I think I got that one. The other one’s showing me a red squiggle.
Yeah, okay, I got that one. So, when you create a non-unique nonclustered index, the clustered primary key acts as a hidden key column in the index definition. So, if we run this query and we tell SQL Server to use our non-unique index, we will get a double seek into the nonclustered index.
We can see both where ID and account ID are evaluated with the literal values that we passed in. So, we get a double seek there. In a unique nonclustered index, that clustered primary key column is more like an include in the index, where we are no longer able to double seek.
Notice that we do have a seek predicate here to account ID, but now we have a residual predicate up here on ID. So, that’s the truth about that. But when I talked about how nonclustered indexes have a relationship both to the table that they get created on and other nonclustered indexes.
Well, let’s take a look at some of that. There’s a type of plan that SQL Server can use, a type of execution plan SQL Server can use called index intersection. And hopefully, this all works out.
Where SQL Server has taken… Let’s do our magic zoom trick here. Oh, that’s too big. There we go.
SQL Server has done a seek into two non-clustered… So, what’s annoying is that it’s very… I don’t know why those ellipses kick in so soon, but if we were to look here, we would see an index seek on this nonclustered index, and then an index seek into this nonclustered index. And then SQL Server is able to merge join those two indexes together and produce the result that we care about.
And if we look at the merge join, it is a residual, well, this is a merge join, on the ID column in one index being matched to the ID column in the other index. So, when I talked about how nonclustered indexes inherit the clustered index key column in some form, depending on uniqueness, SQL Server does a merge join here because that ID column is the second key column. And so, that data is in order after we do equality predicates to the actual key column that we defined the index on.
Interesting stuff, right? If we look at index intersection, we can even get index intersection with a key lookup, right? And we’re going to hint SQL Server to use those indexes, right?
Because that’s the easiest way to get this sort of plan. But now, we have what we saw before, which is the merge join and the two nonclustered index thingies here. But now, we also have a lookup back to the clustered index to get that additional column, right?
Because now, we’re trying to get display name, but display name is not in either of our indexes. So, SQL Server can do index intersection plus a lookup. But the index hint order is significant, right?
So, up here, we have the non-unique account ID and reputation. And down here, we have reputation first and then the non-unique one, right? So, if you’re going to be the type of person who hints indexes, you may want to consider the order that you’re hinting them in because SQL Server will hit them in the order that you hint them, right? Because we hit reputation first in this one and we hit the non-unique one second in this one, right?
But up here in this query, right? If you look at this plan again, right? Execution plan, this hits non-unique first and then this one hits reputation second.
So, very interesting things when you start hinting in indexes, especially multiple indexes. We also have the concept of an index union plan. This is where SQL Server brings the results of two indexes together, like if you were doing a union or a union all type query, right?
So, this is merge join concatenation. This is not just a merge join. This is concatenating results from both of these.
And we’ll see once again that, well, we don’t really have much of a, we don’t have a join condition because this is not a merge join. It is a concatenation operator. But it still says merge join, which is a little confusing, but that’s okay.
I’m sure Microsoft is on the case, right? But we hit both of our nonclustered indexes and then we did that. And then we can also see that index union with a key lookup is also a perfectly valid plan choice, right? So, that’s all that happening in there.
So, index intersection happens most commonly with AND predicates. You can see, well, you can see there are a variety of different join types that might be chosen, sometimes even a hash join, depending on your index setup. Both indexes must be fairly selective.
The result set must be fairly small. The optimizer doesn’t choose these quite as often because usually it will do either a covering index or, you know, scan a clustered index or just do a regular old key lookup. Those don’t always require hints, but, you know, sometimes if you want it to happen, it’s not going to happen naturally.
You have to tell it to happen. Index union is more common with OR predicates. You see a concatenation operator that combines streams of data from two nonclustered indexes.
You may even see a stream aggregate to remove duplicates, which is a nice thing. Each branch has to be seekable. I guess it’s somewhat more commonly chosen than index intersection, but, you know, whatever.
They don’t get used a lot. Like, they don’t get, those plans don’t get chosen very often. The cost model assumes some overhead to, you know, doing that type of stuff that is unfriendly to them.
It prefers to hit a single index path when possible. Sometimes statistically, or rather, the statistics that you have, the histograms that you have, may not, like, present a very big benefit to the optimizer for using them. And then there’s always some risk of eliminating duplicates, adding a bunch of overhead, which, you know, for a, that’s why they have to be selective.
But they can be very beneficial if you have selective AND or OR conditions, and each predicate has a good candidate index to seek into. So, if you are sort of, you know, hoping for that type of execution plan, then you will have to sort of do some work to get one quite often, either with a foreseek hint or hinting the indexes to use in the appropriate order to use them. Anyway, that went on a lot longer than I thought it was gonna.
I’m gonna go finish my coffee now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Today is Friday. Nah, I shouldn’t have saved a long video for Friday.
No one watches the long videos on Friday. But I will see you on Monday, where we will do yet another Office Hours. And hope that someone out there cares.
Alright. Thank you 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.