Saturday, December 23, 2017

Adaptive Query Processing - Adaptive Joins - Plan resue - 3



Continuing on the series on Adaptive Query Processing covered over here, this post would cover what happens to adaptive joins when plans are reused.

To give a little bit of context to the post, the earlier posts in the series explained how SQL Server 2017's Adaptive joins made it possible for SQL Server to switch between nested loop join and hash join in query plan during runtime depending upon the number of rows returned by one of the joining tables. "Adaptive join threshold" is a row count threshold used to decide whether to go for Nested loop join or hash join.
So, the question is what happens when the query plan is reused? Does the Query plan dynamically switch between the nested loop and hash join when the query plan is reused or does it just go with the last choice? What is the role of "Adaptive join threshold" when the plan is reused? To check it out, let's test it


DBCC FREEPROCCACHE

GO

EXECUTE sp_executesql

         N'SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]

FROM    [Fact].[Order] AS [fo]

INNER  JOIN [Dimension].[Stock Item] AS [si]

       ON [fo].[Stock Item Key] = [si].[Stock Item Key]

WHERE   [fo].[Quantity] = @quantity', N'@quantity int',@quantity = 360



EXECUTE sp_executesql

         N'SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]

FROM    [Fact].[Order] AS [fo]

INNER  JOIN [Dimension].[Stock Item] AS [si]

       ON [fo].[Stock Item Key] = [si].[Stock Item Key]

WHERE   [fo].[Quantity] = @quantity', N'@quantity int',@quantity = 130



Step 1: Clearing the cache
Step 2: Run the query using sp_executesql and pass the variable, so that plan is reused
Step 3: Run the same query with a different parameter value, so that the plan is reused with a different value.

Don't forget to turn on the Show query plan option to see the query plan.
Query plans for both the queries provided below







Both use Adaptive Joins which is a good news.
Now, let's compare the properties of Adaptive Join operator to understand more




First observation: Actual join type for both executions are different. So, Adaptive Join switches the join operator dynamically even when the plan is reused
 Second Observation: Adaptive threshold of rows remains the same and this implies it is fixed at the compile time or when the plan was first generated. Hence "Estimated Join Type" also remains the same.


 So, to summarize, Adaptive Join threshold is computed at the compile time. So, when the plans are reused, the same adaptive join threshold is used for all executions. However, Adaptive join still switches the join operator at each execution, using the adaptive join threshold calculated at the first time the plan was generated.

22 comments:

Unknown said...

nice post
SQL Server DBA Online Training

Azure DevOps said...

its a great article on your blog. Thank you for sharing.
Amazon Web Services Online Training

Muralidhara Raju Indukuri said...

Great and informative.
aws training in hyderabad

anirudh said...

i just go through your article it’s very interesting time just pass away by reading your article looking for more updates. Thank you for sharing.

Best SQL Training Institute

ashrith said...

thanks for sharing informationmsbi training in hyderabad

Azure Devops Online said...

Excellent article.Thanks for sharing....
Azure DevOps Online Training
Microsoft Azure DevOps Training Courses
Microsoft Azure DevOps online Training in Hyderabad

Anonymous said...

Thanks for sharing
Power BI Training In Hyderabad
Power BI Training
Power BI Online Training
Power BI Training Online

Chandra Sekhar Reddy said...

thank you for sharing information
Yaaron Studios is one of the rapidly growing editing studios in Hyderabad. We are the best Video Editing services in Hyderabad. We provides best graphic works like logo reveals, corporate presentation Etc. And also we gives the best Outdoor/Indoor shoots and Ad Making services.
Best video editing services in Hyderabad,ameerpet
Best Graphic Designing services in Hyderabad,ameerpet­
Best Ad Making services in Hyderabad,ameerpet­

gautham said...

Thanks for such a wonderful post azure online training hyderabad

TNK Design Desk said...

Thank you so much for these kinds of informative blogs.We are also a digital marketing company in gurgaon and we provide the website design services,
web development services, e-commerce development services.
best website design services in gurgaon
best web design company in gurgaon
best website design in gurgaon
website design services in gurgaon
website design service in gurgaon
best website designing company in gurgaon
website designing services in gurgaon
web design company in gurgaon
best website designing company in india
top website designing company in india
best web design company in gurgaon
best web designing services in gurgaon
best web design services in gurgaon
website designing in gurgaon
website designing company in gurgaon
website design in gurgaon
graphic designing company in gurgaon
website company in gurgaon
website design company in gurgaon
web design services in gurgaon
best website design company in gurgaon
website company in gurgaon
Website design Company in gurgaon
best website designing services in gurgaon
best web design in gurgaon
website designing company in gurgaon
website development company in gurgaon
web development company in gurgaon
website design company
website designing services

TNK Design Desk said...

Thank you so much for these kinds of informative blogs.We are also a digital marketing company in gurgaon and we provide the website design services,
web development services, e-commerce development services.
best web design company in gurgaon
best website design in gurgaon
website design services in gurgaon
website design service in gurgaon
best website designing company in gurgaon
website designing services in gurgaon
web design company in gurgaon
best website designing company in india
top website designing company in india
best web design company in gurgaon
best web designing services in gurgaon
best web design services in gurgaon
website designing in gurgaon
website designing company in gurgaon
website design in gurgaon
graphic designing company in gurgaon
website company in gurgaon
website design company in gurgaon
web design services in gurgaon
best website design company in gurgaon
website company in gurgaon
Website design Company in gurgaon
best website designing services in gurgaon
best web design in gurgaon
website designing company in gurgaon
website development company in gurgaon
web development company in gurgaon
website design company
website designing services

Bhanu Sree said...

This is excellent information. It is amazing and wonderful to visit your site.
SQL Azure Online Training
Azure SQL Training
SQL Azure Training

rajani said...

Thank you for sharing wonderful information with us to get some idea about it.
MS Azure Training in Hyderabad
MS Azure Training in Ameerpet

shankarjaya said...

Your blog provided us with valuable information to work with. Each & every tips of your post are awesome. Thanks a lot for sharing.

Salesforce Training in Chennai

Salesforce Online Training in Chennai

Salesforce Training in Bangalore

Salesforce Training in Hyderabad

Salesforce training in ameerpet

Salesforce Training in Pune

Salesforce Online Training

Salesforce Training

Veera Blogspot said...

Great article,This blog looks great content with Excellent Information.
Thank You...

Power BI Training

gaga said...

C#, VB.net, ASP.NET 4.5.2 Training Overview:Enterprise ASP.NET training teaches attendees how to build Windows and web-based enterprise applications using .NET 4.0 and C# 2010.

Anonymous said...

I really liked this post that you have shared on SQL, it is really interesting and can prove helpful to people who are looking out for such information if such topic intrigue you check out data science course in bangalore
data science course

deepa bahuguna said...

Thanks for sharing this.really a great post
SQL Kya Hai?
Kotlin Kya Hai?

TRONIX said...

Thanks for the article, Much interesting to read

Python training institution in Hyderabad

saikumar0801 said...

This is nice and informative, Thank you for sharing

Devops Training with Real-time Project

arif said...

nice post

vv software said...

Nice blog good explanation of the SQL thanks for sharing.
keep posting more