Joins, parallel execution, memory grants, spills, and adaptive execution.
93 associated posts89 primary posts
Scalar functions are the butt of everybody's jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they're in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on.
Recently, we ran across yet another scenario where scalar UDFs were killing performance.
Sharing Is Caring
When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.
The problem becomes itself when queries start to over and under estimate their practical needs.
Second Hand Emotion
Queries ask for memory to do stuff. Memory is a shared resource.
Scalar functions and multi-statement table-valued functions are notorious performance killers. They hide in execution plans, their cost is under-estimated, the row estimates are way off, they cause queries to go single-threaded, I could go on and on.
Microsoft is bringing a fix in SQL Server 2019, and thanks to a newly published paper, we know more about how they're doing it. Folks from Microsoft and the Gray Systems Lab wrote Froid: Optimization of Imperative Programs in a Relational Database (17-page PDF, and slightly easier-to-digest 12-page PDF).
Monte Carlo Or Bust
In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans.
To figure some stuff out about why the plans changed, let's focus on the Nested Loops Join plan.
All The Rage
We've asked how much one column and even one row can change a plan before.
The doctor says I'm not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received.
Today, Jonathan Kehayias wrote about how fragmentation impacts execution plans. I'm really into performance tuning, so I read the post carefully, really interested to see how it impacted query runtime. You should read it too - it's a great post with a solid demo.
But oddly, he didn't mention the query speeds.
It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn't supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…
Earlier this week, I showed you two nearly identical queries and their estimated plans:
They look pretty darned identical - so how much worse can one query be? Here's how much worse:
There's no Top in the title
And that's because a TOP without an ORDER BY is non-deterministic, and you'll get yelled at on the internet for doing that. This is just a short collection of things that I've done in the past, and still find people doing today when troubleshooting performance. Sure, this list could be a lot longer, but I only have the attention span to blog.
In my work with clients and classes, some blog posts come up a LOT.
Forcing a Parallel Query Execution Plan by Paul White - Paul's posts usually cover execution plan components in incredible detail, but the real gem in this one is the section called "Parallelism-Inhibiting Components." If your T-SQL includes this stuff, the whole plan or a zone of it will go single-threaded.
This is a thought experiment
Just fair warning, you're probably not going to learn anything new here. This also isn't hidden in a CTP of SQL Server 2017, and as far as I know, it's not a consideration outside of this post. Seriously, lawyers, I know nothing.
I think I've mentioned that the most fun part of new features is testing them with old ideas to see how they react.
It occurred to me that if Adaptive Joins didn't work with APPLY, I might cry.
If you're not aware of the performance problems scalar valued functions can (and often do) cause, well, uh... click here. We'll talk in a few days.
If you are, and you're worried about them crapping on Adaptive Joins, follow along.
But is it faster?
Now, I know. There are very few "always" things out there in SQL Server. This is also true for functions.
A lot of the time -- I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.
There's a famous saying
Non-SARGable predicates don't get missing index requests.
And that's true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week?
I don't like it unless it's brand new
When new features drop, not everyone has time to jump on top of them and start looking at stuff. That's what consultants with nothing better to do are for.
I've been excited about this feature since talking to The Honorable Joseph Q. Sack, Esq. about it at PASS last October. My pupils dilated like I just found the bottom of a bottle of Laphroaig 18.
Long distance information
There was a rather interesting question posted on dba.stackexchange.com recently about CASE expression order of execution with an OR predicate. Mouthful, I know! When I saw it, I got all "I HAVE A REALLY GOOD ANSWER" and started to write demo queries.
Then I wrote this blog post instead. Sorry, j.r. -- you see, I hit an actual execution plan bug that did not bode well.
What does memory do?
In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along to eat up more of our RAM.
I Like Testing Things Especially Brent's patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it's faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…
In the classic spirit of my How to Think Like the Engine class, let's go take a look at the StackOverflow.Users table and find all the users named Brent. There's a DisplayName field, and I'm going to be querying that a lot in this blog post, so I'll go ahead and create an index on it, then look for the Brents:
