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

Coepd BA Trainings said...

We at Coepd declared Data Science Internship Programs (Self sponsored) for professionals who want to have hands on experience. We are providing this program in alliance with IT Companies in COEPD Hyderabad premises. This program is dedicated to our unwavering participants predominantly acknowledging and appreciating the fact that they are on the path of making a career in Data Science discipline. This internship is designed to ensure that in addition to gaining the requisite theoretical knowledge, the readers gain sufficient hands-on practice and practical know-how to master the nitty-gritty of the Data Science profession. More than a training institute, COEPD today stands differentiated as a mission to help you "Build your dream career" - COEPD way.

http://www.coepd.com/AnalyticsInternship.html

BrnInfotech said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your blog.
iOS course fee in bangalore
iPhone training classes in bangalore
Mobile app training institutes bangalore
iPhone job oriented course in bangalore

katetech said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
application developers in hyderabad
ios app development company in hyderabad
mobile app development in gachibowli
bmobile app development in kukatpally
mobile app development in ameerpet

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

Anonymous said...

Thanks for nice article you shared.
Complete Dot net and RPA tutorials

Online Training said...

Really nice post. Provided a helpful information.I hope that you will post more updates like this

AWS Online Training

Power BI Training said...

Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog onPower BI Online course.
Power BI Training In Hyderabad
Power BI Training
Power BI Online Training
Power BI Training In Ameerpet

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

play school in hyderabad said...

Thanks for sharing
Sanjary kids is the best playschool, preschool in Hyderabad, India. Start your play school,preschool in Hyderabad with sanjary kids. Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and Teacher Training Program.
play school in hyderabad, India
Preschool in hyderabad, India
Preschool teacher training course in hyderabad, India
pre and primary teacher training course in hyderabad,India
early childhood teacher training course in hyderabad, India

piping design course said...

Good Post
We are the best piping design course in Hyderabad, India. Sanjary academy Offers Piping Design Course and Best Piping Design Training Institute in Hyderabad. Piping Design Institute in India Piping Design Engineering.
Piping Design Course
Piping Design Course in india
Piping Design Course in hyderabad

qaqc course in india said...

Nice Information
Best QA / QC Course in India, Hyderabad. sanjaryacademy is a well-known institute. We have offer professional Engineering Course like Piping Design Course, QA / QC Course,document Controller course,pressure Vessel Design Course, Welding Inspector Course, Quality Management Course, #Safety officer course.
QA / QC Course
QA / QC Course in india
QA / QC Course in 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