Search Results for “adaptive joins”

  1. Home
  2. Search Results

3.2 How Adaptive Joins Help, and How to Get Them

It takes a lot of work on your part to cache multiple plans for the same query. SQL Server 2017 adds a feature to make it easier, automatically, by watching out for situations where the amount of data coming out of one table might influence whether it should do an index seek or a scan…

Read More
1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

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…
Read More

Adaptive Joins

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…
Read More

Adaptive Joins And SARGability

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…
Read More

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

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…
Read More

Adaptive Blog Posts

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…
Read More

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

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…
Read More

Registration is Open Now for My SQLBits Workshop!

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…
Read More

6 – How Execution Plans Use TempDB

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.
Read More