Finding Adaptive Join Inefficiencies

Joe Obbish walks us through a scenario with adaptive joins in SQL Server 2017:

The estimated costs for the two queries are very close to each other: 74.6842 and 74.6839 optimizer units. However, we saw earlier that the tipping point for an adaptive join on this query can vary between 22680 and 80388.3 rows. This inconsistency means that we can find a query that performs worse with adaptive joins enabled.

Click through to see the queries Joe is using.  Based on this, I’d guess that this is probably a knife-edge problem:  most of the time, adaptive join processing is better, but if you hit the wrong query, it’s worse.

Related Posts

Preventing Issues With Columnstore Indexes

Kevin Chant has a post covering issues in his experience with the creation of columnstore indexes: Error due to online statement It’s a very common error caused usually by somebody copying a rowstore “Create Index” command. The “online=on” option you use with rowstore indexes does not work with creating columnstore indexes yet. For the record the […]

Read More

Avoid Key Lookups On Clustered Columnstore Indexes

Joey D’Antoni points out a potential big performance problem with clustered columnstore indexes: In the last year or so, with a large customer who makes fairly heavy use of this pattern, I’ve noticed another concern. Sometimes, and I can’t figure out what exactly triggers it, the execution plan generated, will do a seek against the […]

Read More

Categories

October 2017
MTWTFSS
« Sep Nov »
 1
2345678
9101112131415
16171819202122
23242526272829
3031