In this day and age, join operations in SQL Server can be made using one of the following algorithm types: Nested Loops, Merge or Hash. In theory, each one provides better performance under different conditions (e.g. amount of data).
- Nested Loops: One of the tables is designated as the outer and other as the inner table. Each row of the outer table, is validated against all rows from the inner table and if a match is found, the row is included in the result. Nested Loop is tipically preferred when tables are small. There are three variants for a Nested Loop Join:
- Naive: Scans an entire table or index;
- Index: Uses an existing index to seek data;
- Temporary: Index is built as part of the query plan (and destroyed upon completion of the query);
- Hash Match: Hash Match consumes two inputs, corresponding to the joined tables. A hash table is created using the first input (also known as Build), then the same is done for the second input (Probe). The process of creating a hash table starts with hashing joined key values of the Build table and placing them on the appropriate bucket, depending on the hash value. Afterwards, Probe is processed, same hash values are applied, target bucket is determined and finally the value is compared to the ones already inside. If there is a match and after SQL Server verifies that no hash collision happened, the row is returned. Build should always be smaller because it will be stored in memory.
And that’s it. No, I did not forget about Merge Join, it happens that Merge was just ignored (like Internet Explorer or Terms of Agreement). Adaptive Join allows SQL Server to choose between Nested Loops and Hash joins on the same execution plan, deferring this choice, so it can understand which one will be better, based on the query input.
So, if until now (at least in rowstore) there wasn’t a way to adapt to different workloads inside the same execution plan and specific join algorithms thrive under different conditions, how can, in theory, Adaptive Joins save the day?
The Horrors of Parameter Sniffing
Parameter sniffing is easily one of the most common problems that almost anyone who works with databases have to deal with. Well, to be fair, parameter sniffing is not a problem by itself. The problem is giving the power of choice to users. Choice is the root of all evil.
A store procedure gets executed for the first time, the plan is created based on its input and then is cached. If no recompile is done, the plan will remain the same and future executions will reuse it, regardless of the input. In theory that’s good, because bad things happen when there isn’t enough memory available – and creating new plans from scratch is quite expensive. The problem begins when, in a subsequent execution, the user changes the input in a way that the query returns a million rows, instead of ten. As mentioned in the previous section, each join type thrives under distinct conditions. If in the first execution, a small amount of data was scanned, SQL Server has potentially used Nested Loops and done something like an Index Seek plus a couple Key Lookups. In the later execution, when SQL Server tries to process a million rows using the same approach, it suddenly has a heart attack. Telephones start to ring and clients start o yell as its soul quickly ascends to heaven.
We can try defibrillating by using RECOMPILE, but if that query runs too often, CPU will quickly escalate to 100% and we lose the patient, again. We can also try to optimize the query to a particular case by specifying a join hint or using OPTIMIZE FOR hint, but even if we get a plan that fits appropriately to the majority of executions, the deviations will potentially cause SQL Server to die in agony. Dividing the store procedure into multiple store procedures according to the input, can do the trick, but in some cases, queries can be too complex, not to mention that business logic and magic numbers hardcoded in queries are generally not a good idea. So, what’s left besides a bunch of bad ideas?
Perhaps Adaptive Joins is the hero we deserve. Basically, SQL Server defines a threshold (related to the number of rows) that represents the tipping point from which becomes more efficient to use Nested Loops instead of Hash Match. Adaptive Join will start the execution as a Hash Join, then, after processing the input from the Build phase, it will compare the resulting number of rows to this threshold. If the number of rows is greater or equal, it will proceed execution, otherwise the algorithm type is change to Nested Loops.
Therefore, SQL Server becomes able to adapt a single plan to different workloads. If a large number of rows are being processed, it can use Hash Join, otherwise, if few rows are involved, Nested Loop Join will be chosen. No recompile. No hints. No Columnstore wizardry needed.
Now, let’s check if Adaptive Joins will live to the expectations.
Improvise Adapt Overcome
For the purposes of this demo, I’m using a sample of FootballData public stats. Additionally, I’ve added some data in order to facilitate the demonstration of some concepts and behaviors. Bear in mind that in Standard Edition you won’t be able to use this feature.
We’ll firstly set database compatibility level to 140 and execute a store procedure with two different parameters, so we can understand the behavior prior to SQL Server 2019.
ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 140 GO SET STATISTICS IO ON GO CREATE OR ALTER PROCEDURE GetHomeVictoriesByTeamId @TeamId INT AS BEGIN SELECT m.Id, m.FTHG HomeGoals, m.FTAG AwayGoals, d.Att Attendance, d.Stadium, d.Att FROM Matches m JOIN MatchesDetails d ON m.id = d.MatchDetails WHERE m.HomeTeam = @TeamId AND FTR = 'H' AND d.Att > 20000 ORDER BY d.att DESC END GO EXEC GetHomeVictoriesByTeamId 2 EXEC GetHomeVictoriesByTeamId 1
Ouch! Both execution plans used Nested Loops and Index Seek to retrieve results. The plan was created and cached in the first execution that retrieved 18 rows. Then SQL Server reused the plan to process 500304 victories from Benfica (Id = 1) and things got nasty. We can confirm in Messages that the second plan was not efficient.
By executing the same statement again with recompile, we can notice how a more appropriate plan differs. MatchesDetails’ Scan count dropped from 500304 to 5 and logical reads from 1597472 to 1539. That’s more like it! But, as we discussed, using option recompile is far from being always appropriate, due to the fact that it is a heavy resource consumer.
EXEC GetHomeVictoriesByTeamId 1 WITH RECOMPILE;
Next, we’ll set database compatibility level up to 150 and run the store procedure again, so we can see Adaptive Joins in action.
ALTER DATABASE [FStats] SET COMPATIBILITY_LEVEL = 150 GO EXEC GetHomeVictoriesByTeamId 1; EXEC GetHomeVictoriesByTeamId 2; EXEC GetHomeVictoriesByTeamId 1;
By looking into the first execution plan, we’ll notice the “new” Adaptive Join operator (this looks intuitive), so it looks like we’re ready to adapt and overcome. There are a few things going on in this plan, so let’s analyze it step by step.
The properties of this operator will show us some interesting information, e.g. that the threshold for the algorithm to change to Nested Loops is 9607.83. We also can see that 500304 rows were returned, so the chosen join type was “HashMatch”. This is the expected behavior as the number of rows is higher than the threshold, hence not changing to Nested Loops.
If we recall the execution plan, it’s easy to corroborate this information. Since the number of rows exceeded the threshold and a Hash join was done, the execution went to the Index Scan branch. This can also be confirmed by the displayed number of rows that flowed through this operator, in this case 500304. Correspondingly, the lower Index Seek branch, had no rows.
The second plan, corresponding to an input that retrieves far less rows, shows a different execution branch. In the Adaptive Join operator info, it’s possible to notice that the chosen join algorithm type (NestedLoops) is appropriate to the number of rows – 18. It’s also visible that these rows were retrieved by an Index Seek instead of an Index Scan. So far, so good.
The third and last plan, with the same input as the first will be exactly the same, right? Well… Sadly no. Although the operators are the same and the execution went through the same branch, it looks like we landed on spillage village. Both Sort and Adaptive Join have warning signs on them, stating that data was spilled to tempdb and we can see that the query took longer to run. Despite that, IO statistics maintain the same values of the first execution. Interestingly, this behavior is caused by another adaptive query processing feature that was explored in a previous post– Adaptive Memory Grant Feedback.
If you are lucky enough, after a couple executions, SQL Server will be able to adapt the memory grant to its ideal value and just like clouds, when these warnings disappear, it will be a beautiful day. Unfortunately, sometimes SQL Server isn’t able to do that and gives up on adapting the grant (for more details, check the mentioned post). Let’s execute the store procedure a couple more times and check the outcome.
EXEC GetHomeVictoriesByTeamId 1 GO 30
Under these circumstances, SQL Server wasn’t able to adapt the memory grant and we didn’t get the stable ending we were looking for. Nevertheless, things could be worse: The initial memory grant is fairly adequate, so no spills this time. Furthermore, the chosen join algorithm is appropriate, IO statistics and execution time are pretty similar too.
In conclusion, looks like Adaptive Joins can really improve the performance of our servers, with virtually no effort needed. Nonetheless, it’s probably not a good idea to go all-in and change your production server compatibility level to 150, hoping that the robots will do what they were programmed to do. You must have seen enough movies to know that it’s not how it ends. But seriously, Adaptive Joins won’t fix all problems related to parameter sniffing and can actually cause problems that aren’t easy to troubleshoot. So, it’s reasonable to proceed cautiously and make use of tools like Query Store to fix regressions.