Data Exposed

SQL Server 2017 Adaptive QP

Play SQL Server 2017 Adaptive QP
Sign in to queue

Description

This week's Data Exposed show welcomes Joe Sack into the Channel 9 studio. Joe is a Principal PM on the SQL Server team, and is in the Channel 9 studios today to show us the awesome Adaptive Query Processor technology, the ability of the database to adapt based on customer characteristics in real-time conditions. Joe introduces 3 features of Adaptive QP in SQL Server 2017 and Azure SQL DB.

Today's AQP topics:

[02:20] - Memory Grant Feedback

[08:50] - Adaptive Joins

[16:20] - Interleaved execution

For more on Adaptive Query Processing, visit our docs page here: 

https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing

Tags:

Data, Performance, SQL Server

Embed

Download

Right click to download this episode

More episodes in this series

Related episodes

rss

The Discussion

  • User profile image
    CarlD

    Will be interesting to see how well this works in the coming years. I've found, working with large complex queries that return large numbers of rows that I've always had to heavily hint my queries to get predictable performance.

  • User profile image
    Joe Sack

    Thanks Carl. Ideally as we add adaptive mechanisms in QP this will also allow customers to reduce the need for explicit hints. We're just getting started in this space, but that is certainly the long-term goal.

  • User profile image
    Yoshihro​Kawabata

    Thanks nice presentation. and detail document.

    Are there any chart of Adaptive Query Plan feature with other DB engines ?
    Like Oracle, MySQL, SQLite, Azure Cosmos DB, previous SQL Server/Azure SQL Database.
    For sharing this feature with other DB engine users.

     

     

     

  • User profile image
    Debdutta

    In SQL Server 2014 New Cardinality Estimator has been introduced. It makes faster executions of lots of queries, but, we also observe slowness. Base on Microsoft recommendation, we use query hint Trace Flag 9481 (force to use old CE) and it worked as expected.

    My question is, does this great Adaptive Join feature solve this problem?

  • User profile image
    Joe Sack

    Hi Debdutta,

    Adaptive Joins for batch mode helps with issues related to incorrect estimates that impact join algorithm selection. It doesn't change any of the assumptions introduced with the New CE - however if there is a skew related to the New CE that impacts join selection, it may help in that specific case.

    In terms of query processing improvements and future strategy - rather than introducing a "new new" CE, we'll be focusing on specific features that address areas that are known to be historically problematic.

  • User profile image
    kboussif

    why it took Microsoft 10 years to get it ?
    in Oracle , it is already there

    Adaptive query processing in SQL databases
    Adaptive Query Optimization
    SQL profie
    Hint...

    When are we going see Cluster ACTIVE-ACTIVE like RAC ?

Add Your 2 Cents