Let's Help the Optimizer
Details
Agenda:
• 17:30-18:00 - Gathering, Networking, Hugs and Kisses
• 18:00-18:15 - Opening, Announcements, and More...
• 18:15-19:00 - First Session (Be a Friend! Help the Optimizer)
• 19:00-19:30 - Break (More Networking)
• 19:30-20:30 - Second Session (Adaptive Query Processing)
Sessions:
- Be a Friend! Help the Optimizer – Matan Yungman (40 Minutes)
The SQL Server optimizer is very smart and can generate good execution plans for fairly complex queries. However, it does have its limits, and sometimes we have to adjust our queries in order to help it make better decisions.
In this session, we will talk about a few problematic query patterns that hurt the optimizer's ability to generate a good execution plan, resulting in poor query performance.
Among other examples, we will see how changing a table structure can allow the optimizer to generate a parallel plan, how breaking down big queries generates much faster queries, and why reuse and encapsulation are a good programmatic habit, but can be bad in terms of performance.
Keep things simple. The optimizer will like it!
- Adaptive Query Processing – Adi Cohn (50 Minutes)
Starting with Azure SQL Database and SQL Server 2017, Microsoft has made a major modification to the way that SQL Server and the optimizer work. Up until a few months ago, the optimization phase of the query was always done before query execution. The query was activated only after there was a query plan, and the query plan could not be modified.
This behavior led sometimes to sub-optimal query plans and wrong allocation of resources to queries. In this presentation, we'll learn about adaptive query processing – a feature that allows SQL Server to modify some of the operators of the query plan during execution time. We will see the advantages and also the limitations of this new feature.
