Search Results for &#8220;adaptive joins&#8221;

Adaptive Joins, Parameter Sniffing, SQL Server 2019

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

Last Updated May 26, 2020

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

Not Exactly The Catchiest Name There’s a TL;DR here, in case you don’t feel like reading the whole darn thing. Batch mode memory grant feedback works with stored procedures It takes several runs to adjust upwards to a final number It seems to only adjust downwards once (in this case by about 40%) This isn’t…

Adaptive Joins

Last Updated December 7, 2017

NEW NEW NEW This is an informational message. These types of joins are new in SQL Server 2017, and we don’t have enough experience with them to do any analysis. Right now we’re only flagging them, but in the future we’ll add some detail around the estimated join choice and average rows, most likely, unless…

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated May 19, 2017

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. So, here goes nothin’! Cross Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s…

How Scalar Functions Can Stop You From Getting Adaptive Joins

Last Updated May 1, 2021

Adaptive Joins, Execution Plans, SQL Server 2017

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. The big question I had is if various uses of scalar valued functions would inhibit…

Adaptive Joins And SARGability

Last Updated May 12, 2017

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? You betcha!© One Sided I’m going to paste in some queries, each with something non-SARGable. There are two tables involved: Users and Posts. Only…

Adaptive Joins And Local Variables

Last Updated May 12, 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality Estimator math here, we’re just going to look at Adaptive Join plan choice…

Look Ma, Adaptive Joins

Last Updated May 12, 2017

Execution Plans, SQL Server 2017

This probably won’t seem like a big deal soon But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is. I hope Joe Sack has strong ribs. And here’s the tool tip info! Dynamic Duo This…

What’s New in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

When you run a query, SQL Server guesses how much memory you’re going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with…

How Many Kinds Of Joins Are There?

Last Updated May 26, 2018

Adaptive Joins, Execution Plans

Quite So There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins. If you look closely at each of those joins, you’ll see all sorts of interesting things pop up. Hopefully none of them will be Sorts. NOT EXISTS Nested Loops has:…

Adaptive Blog Posts

Last Updated October 2, 2017

Adaptive Joins, Execution Plans

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…

Anatomy Of An Adaptive Join

Last Updated May 12, 2017

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…

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Last Updated May 12, 2017

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…

Registration is Open Now for My SQLBits Workshop!

Last Updated January 6, 2022

SQLBits

Registration just opened up for my SQLBits workshop on Mastering Parameter Sniffing! Move fast – due to COVID, they’re spacing out the attendees, so there are even less seats available than usual. My workshops usually sell out quickly, so if you want a seat, you’ll need to hustle in. Here’s the session abstract: You’re a…

Going to SQLBits? Join me for my Mastering Parameter Sniffing workshop.

Last Updated December 7, 2021

SQLBits

You’re a database developer or DBA with at least 3 years experience performance tuning queries and indexes. You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level…

[Video] Office Hours: Ask Me Anything at the Old Harbor in Reykjavik

Last Updated September 27, 2021

Videos

On a very chilly morning, I went through your highly-upvoted questions from here and talked through ’em: Here’s what we covered: 00:00 Introductions 01:02 Johnny: Good morning, Brent! VARCHAR or NVARCHAR; which one to use in our Western world? One could say that disk space is cheap so it doesn’t matter. But with SQL Server’s…

6 – How Execution Plans Use TempDB

Last Updated April 26, 2021

When you see a yellow bang warnings on sort, hash match, or adaptive join operators in an execution plan, that’s a sign that TempDB was invited to the party. When SQL Server runs out of memory, the data spills over into TempDB. Let’s see how it happens, and what we can do about it. Demo…

To access this incredible, amazing content, you gotta get Recorded Class Season Pass or Fundamentals of TempDB, or log in if you already shelled out the cash.

Statistics Aren’t Guarantees: SQL Server Still Checks.

Last Updated November 30, 2020

Statistics

When I’m reviewing plans with folks, I get this response a lot: But SQL Server should know there’s no data that matches! It has statistics! Statistics are guideposts, not guarantees. We’ll start with the Stack Overflow database and put an index on LastAccessDate, which also creates a statistic on that column. We’ll check the min…

The 201 Buckets Problem, Part 2: How Bad Estimates Backfire As Your Data Grows

Last Updated July 7, 2020