SQL Server Consulting, Education, and Training
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.
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.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
This year at the PASS Data Summit, I’m presenting my precon The Foundations Of SQL Server Performance Tuning:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Goals:
Understand which metrics matter for performance
Using Extended Events and Query Store to find problems
Solving locking, deadlocking, and general query performance issues
A lot of the time when I teach performance tuning, I like to show attendees a specific query with some specific problem(s), and how I go about fixing those in different scenarios.
But a lot of the time when I talk to clients and other folks in the SQL Server community, they have a hard time even knowing where to start and what to look for.
In this session, I want to show you how I figure out what kind of shape a server is in, performance-wise, and how I use different clues to figure out which queries to go after.
Anyone can find a slow query; I want to find the queries that users complain about when their loading screens sit there spinning for a millennia. And that’s what I want to show you how to do.
Think of yourself like a conductor in front of an orchestra of totally untuned instruments. You may find some things:
All attendees will get a coupon code to my full training catalog.
Along with that, everyone loves these t-shirts, so I’ll be bringing them back.
I’m also going to be making a new one that is TOP secret, along with some spankin’ new stickers for you to slather your laptops with.
Only precon attendees will get one of these.
If you were thinking about coming to my @SQLBits precon but needed a bribe, how about a T-shirt? pic.twitter.com/Hj9VHK7PRq
— Erik Darling Data (@erikdarlingdata) February 10, 2022
Thanks for reading, and see you there!
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.
Like in yesterday’s post about Cost Threshold For Parallelism, changing MAXDOP settings will have a universal effect on the workload.
This is true whether you change it at the server level for all databases, or at the database level using a database scoped configuration for a single database.
It is a guardrail to prevent unwanted conditions as a whole, like excessive concurrent parallel queries causing worker thread starvation (THREADPOOL waits), or just pushing CPU to 100% for extended periods of time.
You may also find times when parallel threads within a single query are more prone to involvement in blocking or deadlocking.
The problem is similar to yesterday’s post, and requires some level of attention to detail at the query level after you change MAXDOP.
Don’t just look at all the various CX waits, pronounce that there are fewer of them, and call the job done.
You should examine parallel query metrics to make sure that runtimes are still adquate.
If we take a single query and run it at reduced DOPs, you can see the pattern I’m talking about.
Yesterday, we looked at DOP 8 vs. DOP 1, and I’m including a DOP 1 run here too. It’s just not as important, unless you’re the kind of wackadoo SharePoint admin who changes MAXDOP to 1.
(I’m going to be honest with you here, it’s been YEARS since I’ve seen a SharePoint database, or looked at the SharePoint documentation to see if the MAXDOP 1 requirement still exists).
When you change DOP, it’s up to you to monitor (you can use Query Store for this) execution and CPU time to look for big regressions.
Keeping the above picture in mind:
Taking a full three seconds longer at DOP 1 is probably out the door for queries that users care about, but that’s more likely from changing Cost Threshold For Parallelism.
A diligent caretaker for SQL Server may even look for critical queries that currently use parallel execution plans, and time them with a lower DOP to make sure there’s no substantial regression before making the change.
If there’s a big increase in duration, add the higher DOP hint to queries where it makes sense.
Thanks for reading!
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.
First, I’m not suggesting that anyone should be using the default value for Cost Threshold For Parallelism. It’s old and moldy and not a good fit for most workloads functioning on modern hardware.
My apologies to Azure SQLDB users who can’t change this setting and leave it up to Microsoft to maybe manage it for them based on ???
Some people out there really like fiddling with settings in a usually ill-informed reaction to Some Script They Found On The Internet, without reading the fine print.
Constantly pushing Cost Threshold For Parallelism up higher because there’s “too much parallelism” is usually a bad strategy, for several reasons.
You should be looking at:
Just to put some easy numbers out there, let’s say you have a query that runs at DOP 8 and:
If you push Cost Threshold For Parallelism up to the point where that query no longer qualifies for a parallel plan and runs at DOP 1, it will:
See why this isn’t a bold and exciting strategy embraced by experts around the world?
Of course, in the real world, parallelism rarely scales perfectly linearly. That’s okay, as long as you’re seeing a reasonable reduction in wall clock time.
In this picture, I’ve captured the same query running at DOP 8 and 1.
What I said above holds close enough to true.
While we used more CPU time at DOP 8, we only used about 2.5 seconds more of it, and we used it for about 3 fewer seconds overall.
This is the efficiency tradeoff parallelism offers, and this is especially desirable for queries that have to process a lot of data.
Sticking with DOP 8, I’d rather process 10 million rows across 8 threads than 10 million rows on a single thread (especially in Row Mode, but it would be still be preferable in Batch Mode).
Anyway, if you’ve got Cost Threshold For Parallelism set to the default, you should probably bump it up. I still think 50 is a good starting place to get you to a spot where you can make sane decisions.
Remember, global settings are the guard rails for your workload. They are the general rules you want queries to use.
After you set up the guard rails, it’s up to you to define the exceptions.
Thanks for reading!
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.
I absolutely adore the SQLBits conference, and it was the Biggest Bummer In Bummsville last year when I had family travel plans that overlapped with the conference dates.
So this year, I’m back with a mach 5 vengeance, and ready to get back to the most highingest-flyingest conference in the world.
If you’re interested, and you wanna throw your hat in the ring, here are the links for general and full training day submissions:
To learn more about speaking at the event, click here.
To learn more about attending the event, click here.
I’ll have all my fingers and toes crossed until November 14th when the training day schedule is announced.
Thanks for reading!
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.
In this video, I delve into the fascinating world of parallelism in SQL Server queries and introduce a crucial aspect that can significantly impact query performance: CXConsumer weights. Parallel execution plans are designed to distribute workloads across multiple threads for faster processing, but sometimes, these threads don’t utilize resources evenly, leading to inefficient use of parallelism. By examining CXConsumer and CXPacket weights, you can identify when queries might be underperforming due to skewed thread distribution. I walk through a practical example using the `parallel, serial` view to demonstrate how to analyze execution plans for signs of inefficiency, including looking at query time stats and wait types like CX sync port. Additionally, I highlight important metrics such as DOP (Degree of Parallelism) and thread usage, which can provide valuable insights into whether your parallel queries are truly benefiting from multiple threads or if they’re just wasting resources.
Erik Darling here with Darling Data, of course, still to this very day, always and forever, faithfully yours. In this video, we’re going to talk about, well, we’ve got a couple things to cover. It’s an admittedly interesting subject that has to do with parallelism and which weights you can use to indicate that a parallel query may have had inefficient usage of parallel threads. Now, what that means is you might see some queries that use like .threads to process rows, right? You have the coordinator thread zero, then you have some number of rows, whatever your .threading is, it will have rows given to them to do work. Sometimes, the distribution of rows across those threads is nice and tidy and near perfect. Other times, it’s very skewed towards a single thread, or just like, you know, one thread has like 90% of the rows and the other threads have like a smattering of rows. There are all sorts of different weird reasons this can happen. But the important thing is that you need to be able to figure out if it’s happening on your server.
Now, a while back, Microsoft introduced a weight called CXConsumer to split up CXPacket versus, well, to split up how parallelism is measured within a query. So like, while a consumer thread is waiting on parallel threads, you get CXConsumer, while parallel threads are doing stuff, you get CXPacket. Now, because some guy who works at Salesforce was like, CXConsumer is totally benign, a lot of scripts that you might find out there on the internet will automatically screen out CXConsumer.
I won’t do that to you because it can be really important. But SQL Server, well, I guess the really bad part about that is they get screened out of actual execution plans. So like, you’ll see CXPacket weights, but you won’t see the CXConsumer weights in there. And the CXConsumer weights in there can really help you figure out why or really be an indicator of why your parallel query was quite slow. The kind of good news is that SQL Server 2022 has inherited a weight stat that both Azure Managed Instance and Azure SQL DB use.
Thankfully, no guy who works at Salesforce is around to tell you that this weight is negligible and benign and that you don’t have to worry about it. So, so that you can, you can find in your query plans when parallel might be inefficient. So what I’m going to do is run this first part of the script and this first part of the script, well, it’s going to be a little over, a little, little, little bit more than I would care to normally capture.
I don’t usually want the query plans for the, those temp table inserts, but in this case, we’re just going to live with it because it makes, makes more sense to kind of do it all in one shot. So, so right now we’re selecting data from this view called parallel, but serial, which you can probably infer from the name is a query that generates a parallel execution plan. But the, the, the, the rows all end up on a single thread.
So in effect, it’s actually just a serial execution plan. So let’s dig a little bit into what we have here. We have an execution plan right here.
And if we zoom in a little bit on the execution plan, we can see all this lovely stuff. See all this lovely data, there’s all these lovely parallel operators. And we might be wondering, well, why did this, why did this parallel query take 24 seconds?
The reason, if we dig in a little bit more closely, now we’re going to do some advanced stuff here, right? We’re going to, well, actually zoom it, zoom it kind of, zoom it, zoom it kind of jinx me on that one. So this is how, this is how many rows our query is dealing with.
The problem is that when we look at the properties over here, and this is a little, little wonky at the moment. Let me fix that for you. There we go.
Let me go and slide this on over. Very professional presentation over here. You can see that all the rows end up on this thread one. So it’s not very good.
And if we carry on, we actually go and look at this one. See that all the rows end up, still end up on thread one here as well. So that problem persists throughout the query plan.
And part of it is the lack of a repartition streams operation anywhere here. We have a distribute streams operation, which attempted to divide rows evenly out to different threads. But the round robin partitioning type kind of messed that up a little bit.
Anyway. So digging into what went on here. If we look at the properties and we look at, well, there’s two important things. One of them is the query time stats.
So one thing that you want to be on the lookout, if you have a parallel execution plan that runs for a while and you’re like, what the hell took so long? What were all those threads doing? A very important thing to look at is in the query time stats section of the property.
If you go to the select and you get the properties there, pretty much do what I just did in the video. You’ll see this. CPU time and elapsed time are just about even.
And that’s not good because the whole point of a parallel execution plan is that you give more CPUs work so that you spend less time on the wall clock. Right. You have multiple CPUs.
I’ll do a bunch of work. Assemble that work later. Those threads all do an equal amount of work. Like let’s say your query runs at DOP 8. Your query should be eight times faster than wall clock time, assuming that parallelism scales linearly. It may not always, but you know, neighborhood of, you should see some improvement there.
If we go into the wait stats, and I’m going to bring this back out. If we go into the wait stats for this query, we’ll see a whole bunch of CX sync port. This is that new wait type that I was talking about.
It’s in SQL Server 2022, Azure Managed Instance, and Azure SQL DB. And this can kind of help you figure out when queries have skewed parallelism. Now, something that eagle-eyed watchers might notice is that the wait time on this wait is nearly even with CPU and elapsed time for the query itself.
So this is a pretty good indicator that the whole time this query was running, the 23 or so seconds this thing ran for, most of the time we were waiting on parallel thread stuff. And then if we dig, again, dig a little bit further into the execution plan, let’s get rid of this. We can look at almost anything that shows where rows end up.
We can look at this index seek operator, and we can look at the actual number of rows, and that will show us all the rows ending up on a single time. Right there. We could look at any one of the lines that shows where rows were distributed among threads and see it there.
Remember, initially, we looked at this line and this line. You would also see that if you look at this key lookup, where all of the rows, just like before, ended up on a single thread. And sort of likewise in the query plan, those sections where the rows are terribly skewed.
Oops, didn’t need that tooltip. Thanks anyway, though, tooltip. Very useful otherwise.
Those rows are where things in the query plan really did accumulate the most time. So basically all 23 seconds of the runtime for the plan was just in this chunk right here. So again, always be looking at your actual execution plans.
Always be paying attention to operator times. And if you have a parallel plan that you think is running very slowly, it’s always worth investigating if one or a couple of the threads ended up with the majority of the work, and you had a bunch of threads waiting around, sitting there doing nothing.
Now, another sort of interesting point in here is if we look at the thread stat for this query, we will see that this query was allowed to have three active branches. So three parallel branches were allowed to be running simultaneously.
We reserved 24 threads for this whole query to run, but we only used 17 of them. So at certain points, what’s that? Four, well, seven threads.
Remember, because it’s DOP8. At some point, seven threads are just like, I got nothing to do. Good. Not doing much over here.
So that’s another good indicator that perhaps your parallel query did not do the work that you thought it did. Now, what I have to do is over here, I have to figure out why I have a bunch of negative numbers in here and why I have a bunch of duplicates in here, because all I did was insert rows into this table for my SPID and then join that temp table over here.
I think I found a bug in SQL Server where parallel queries don’t clear out when you open new windows and you use the sys.dm exec session waitstats view. Something is a muck in there.
So that’s fun. It’s a lot of fun, actually. Really, really happy to find these things. One last thing that I will point out is that a lot of this information is available to you in the plan cache and query store. So if you go digging around the plan cache or query store, you might see times when you have a parallel query.
Last DOP 8, last elapsed time, 23 seconds. Last worker time, 23 seconds, where things are quite even despite a high DOP, and that could be a pretty big warning to you. There’s also these.
Well, I mean, we already looked at the DOP column, but let’s give it a pink hug anyway, where the last reserved threads 24, last used thread 17. So it kind of brings you that information that we saw in the actual execution plan. And, of course, that is our query here.
So morale of the story, you will not see this in cache plans. Cache plans don’t save the row stuff for you, right? You don’t have all that cool information about which rows ended up on which threads in cache plans.
So that’s the plan cache, query store. Estimated plans won’t show you that, but we can go digging because that’s what we are. We are query plan archaeologists.
We can go digging more into actual execution plans and figure this stuff out. So anyway, I hope you learned something. I hope you enjoyed yourselves.
What was the other thing? If you like this video at all, please consider throwing it a thumbs up. If you enjoy this sort of hard-hitting investigative SQL Server content, then feel free to like and subscribe to my channel.
Like the video, subscribe to the channel, in that order. And you will get helpful notifications every time I post this sort of hard-hitting investigative SQL Server content. Anyway, thank you for watching.
I will see you in another video some other time.
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.
To vote for this issue, please click here.
In this video, I delve into an intriguing issue that caught my attention recently—specifically, how sort operators spill in row mode versus batch mode plans within SQL Server. This isn’t something you can easily fix; it’s more of a Microsoft problem they need to address. The video explores two queries with different memory grant hints, highlighting the surprising performance difference between sorts that spill and those that don’t. I also touch on some practical tips, like using Ctrl+R in SQL Server Management Studio to toggle result set visibility, which can be quite handy for keeping your workspace organized. Additionally, I discuss how batch mode sort spills can significantly impact query performance, making it crucial to always examine actual operator timings rather than relying solely on percentage-based metrics.
Erik Darling here with Darling Data, coming to you on this crisp, clean, beautiful Friday. And in this video, I wanted to talk about something that I came across sort of recently, and there’s an issue open with Microsoft about it. It’s not something you can really do anything about. It’s an issue they have to fix. It has to do with when sort operators spill in in row mode versus batch mode plans. So I didn’t mean to spoil things there too much for you, but come on, grab the thing, do the thing, do it right. So there are two interesting things about this demo when we look at the row mode execution plans. First one is that when you look at how operators time leading up to this, you can see that we spent a little under a second, a little under a second, well, not exactly five seconds because repartition streams is weird. And then about 11, well, actually, let’s call it 12, 11.8. That’s, that’s close enough to 12 for me. About 12 seconds spilling out to disk. And then the entire query finishes in just about 12 and a half seconds. Now, when you compare that to a query down here, it doesn’t spill at all.
We still hit, we hit about 20 seconds here. Now this is weird, right? This is a strange circumstance. There’s a query that spills to disk that takes almost half as long as a query that doesn’t spill at all. It gets, in fact, if you look at the queries, I’m just going to get rid of that screen real quick. If you, if you use SQL Server Management Studio, and you ever get annoyed that, uh, like the results set is in the way, like the results set is in the way, just hit Ctrl and R. It’ll bring the results set window down and you can, you can just hit Ctrl and R again to bring it back up. It’s pretty, pretty useful, at least for me anyway. So, uh, this first query is hinted to have a max grant percent of one. And this second query is hinted to get 100% of the memory grant it wants. Now, the reason why the first query finishes so much faster is because I have really good I.O. hiding underneath my laptop at some Lenovo, Opel, SSD deal. Uh, and I.O. there is really fast. So when the sort starts writing out to disk, it can do, it can do two things. It can write to disk in small chunks and it can, it can build the sort in small chunks.
And doing a whole bunch of small things is sometimes a lot faster than doing one big thing. Um, the first time that I ever was, uh, sort of introduced to that within the context of databases was not with a loop or a cursor. It was, uh, an Adam mechanic session on cross apply where he was using cross apply and there was a row number thing inside it. And you still had to sort data, but you only had to sort data for a specific thing inside the cross apply rather than doing one big sort and bringing the whole thing over along.
So in this case, it is faster. I know this is going to sound crazy to you. It is faster for a sort to spill to disk and build the sort up in tiny chunks reading from disk than it is to do the entire sort in memory. Now you are probably not as lucky as I am. You are probably using some sand catastrophe where, uh, I mean, the disks might be good, but you know, the, the storage network from the, the server to the, to the disks might not be great. Uh, you might be on cloud storage, which is, uh, uh, pathologically wretched and the prices that you get charged for it should, there should be, there should be an SEC violation or something attached to those.
Uh, but where things get even more interesting is with batch mode. Now you might think, wow, batch mode super fast, right? Cause it’s good for all those big, crazy data warehousey, uh, insane aggregation queries.
Those like, like, like, like far edge of the world workloads where, you know, you have to aggregate the Kajagoogoo billion rows. But it turns out batch mode sort spills. Uh, so let’s take a look at these two queries, right?
Now, in row mode, the batch mode, I’m sorry, in row mode, the sort that spilled was wicked fast. And the sort that didn’t spill, well, not awful, was still 20 seconds. It was still almost twice as slow as the sort that spilled.
With the batch mode plan, have a gander at this. This sort spilled for a full four minutes and 11 seconds. That’s, that’s wall clock time.
That’s time on, that’s time out of your life. That’s time out of whoever ran this query, which is me. It’s time out of my life. And then the, the sort that didn’t spill only took about 3.2 seconds. So there are a couple messages here.
One, uh, sort spills in row mode might not be the end of the world for query performance. Remember to always get those actual execution plans and always look at operator times. If you are the sort of foolish person who looks at percentages, God help you.
You’re, you’re living in a far distant past of irrelevant query metrics. Sorry to say it. Second message is, if, uh, you are the type of futuristic forward-looking person who has embraced batch mode, either via fancy tricks, like, like empty filtered columnstore indexes or empty columnstore objects that you do funny little joins to, or you’re on, uh, 2019 and up and you’re using batch mode on rowstore, you might have to be very, very careful when you see batch mode spills.
Now, this, you should still always, of course, be getting the actual execution plan and looking at the actual operator timings on these things. Uh, there’s a very big difference in what’s actually, what’s actually good and bad in this query, percentage-wise, when you, when you look at, look at the actual operator times.
Uh, there’s something else profound and prolific that I was gonna say. Uh, so yeah, this is a, this is a Microsoft problem. Uh, this is something that, uh, they’re going to have to address.
Uh, the reason why the batch mode sort spills, uh, take as long as they do, apparently has to do with, uh, well, a couple issues. One is the, the way that, uh, some, uh, string data, deep data is stored in batch mode or dealt with during the sort, something like that. And the other is, uh, to do with, uh, how the, the batch mode operation, uh, reads the sort from disk, which is one big write, one big read, which apparently is, is not good for performance, I hear.
So, uh, there’s that. Well, anyway, uh, I’ve got other videos to record. And, uh, since I switched over to Camtasia and I’ve got this, this crazy partner app to Camtasia called Audiate.
Uh, after I, after I stop recording this, I get to go, uh, edit the audio portion of this so that you don’t have to hear any of my biology or anything else, which you probably don’t want to hear, which I don’t even want to hear. So, anyway, if you enjoyed this video, in the least, there was a slightest bit of joy or interest brought to you by this video, give me the old, that old thumbs up.
Uh, if you like this sort of SQL Server content, uh, you could pretty please subscribe to my channel. It’s a great channel. It’s a great channel.
Especially now that I have reliable recording software. Even if I have to do more work for you on the back end, it’s, it’s a reliable product that I get to, I get to share with you. Uh, I hope you enjoyed yourselves.
I hope you learned something. I hope you laughed. I hope you loved. I hope you lived. I hope you don’t buy home decorations from Pier 1. Uh, I don’t know.
Every once in a while they actually have some decent Halloween stuff though. I think we got some real spooky Halloween stuff in years past that I think we still, I think, didn’t break, which is amazing for Halloween stuff. Usually, on November 1st, that stuff just starts to like crumble.
It’s got a timer on it. Anyway, thank you for watching and, uh, we got another one to record. Actually, maybe, maybe one or two to record after this. We’ll see, we’ll see how motivated I am.
It is after all, Friday. Alright. Uh, I’m gonna very smoothly and suavely hit the stop button here. I’m not even looking at it.
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.
Sometimes people will ask me penetrating questions like “why does SQL Server choose a bad execution plan?” or “why is this query sometimes slow?”
Like many things in databases, it’s an endless spiral of multiverses (and turtles) in which many choose your own adventure games are played and, well, sometimes you get eaten by a Grue.
In this post, I’m going to talk at a high level about potential reasons for both.
I can’t possibly go in depth on both in a single blog post, but I thought it would be fun to talk about all the things that could possibly cause your Unfortunate Circumstances™️.
At any rate, I hope you enjoy it. And maybe learn something. And maybe feel bad about blaming SQL Server for things that are all your fault.
A lot of assumptions are gonna get made here, because a lot of things can stand between you and your query getting to SQL Server.
We’re talking networks, security, Active Directory, the cloud. Who knows? Not me. I stick to what happens inside the server.
Just know that there are a lot of moving parts between your application server and your database server.
I’ve seen logins take 30+ seconds to process. Not on purpose, mind you. I stick to what happens inside the server.
But hey, maybe we can just say this all worked quickly, and now we can talk about the stuff I stick to.
Inside the server.
There are even more things that have to happen now, even after you log in.
Your connection has to pass some checks and do a bit of routing, and then at a high level, your query will either:
This is when SQL Server’s query processor comes along and goes through the various phases of figuring things out:
There’s one step in that process that seem important. The actual optimization portion. In there, you have:
During plan exploration is when the optimizer starts applying various rules to your query.
If you want to see the names of all the crazy rules that get may get applied, look in here:
SELECT
deqts.*
FROM sys.dm_exec_query_transformation_stats AS deqts
ORDER BY
deqts.name;
In SQL Server 2022, there are about 440 of them. It’s a wonder anything gets done around here.
This process has to happen quickly. So when you throw your crazy-town queries at SQL Server, it may not figure out the single best possible plan ever.
If your queries are parameterized, this brand spankin’ new plan will be created for those parameters.
At this point, it would be good to remind you that your query hasn’t even started executing yet.
It’s just been waiting to get going.
Once it has an executable plan, that happens.
Inside the server.
Once your query gets on its horse, you’re subject to many potential holdups, both physical and logical. It may even have to wait for memory to compile an execution plan at times.
Not enough memory? Wait. Not enough CPU? Wait. Need to read data? Wait. Need to write data? Wait. Blocked? Wait.
And that doesn’t even count all the work your query might have to do while it’s running and not waiting on anything.
The next time you have a gripe about a slow query, try to keep all this stuff in mind.
There’s a lot going on.
Inside the server.
A great way to see all the stuff your queries are waiting on all at once is to run sp_PressureDetector.
Inside the server.
Thanks for reading!
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.