Can we enhance query performance without any code changes? Modifying applications can be an expensive endeavor or completely beyond your control. Therefore, developers and DBAs prefer that the query processor adapts to their workload requirements rather than relying on options and trace flags to improve performance. Adaptation is the foundational concept behind Intelligent Query Processing (IQP) in the latest versions of SQL Server. This demo-intensive presentation will explore the fifteen intelligent query processing features introduced in SQL Server 2022, 2019, and 2017. For each of these fifteen features, we will examine the issue it aims to resolve and the algorithm it uses to tackle the problem. We will evaluate the pros and cons of using these features. You will learn how to deploy them at various scopes tailored to your specific needs, such as server, database, session, or query levels. You will also be able to identify the features built on the Query Store.

Attending this session will allow you to learn about the new capabilities of intelligent query processing and gain powerful tools to persuade your peers to upgrade SQL Server and databases to the latest build, both on-premises and in the cloud.

Prerequisites:
A basic understanding of query processing, familiarity with estimated and actual execution plans, and knowledge of how to measure query execution performance are required.

Goals:
1. Explore the Intelligent Query Processing features introduced since SQL Server 2017.
2. Understand the issues each feature addresses and how it resolves them.
3. Learn how to turn these features on or off at the server, database, session, and query levels.