SQL Server 2017 and Azure SQL Database are being touted as
the self learning, artificial intelligent database. One of the main reasons for
the same is due to release of the following features
1) Adaptive Query Processing
2) Automatic Tuning
Adaptive Query Processing is SQL Server's first attempt using artificial intelligence to fix poor performing queries due to incorrect cardinality
estimates. Query optimizer produces better plans using "Adaptive Query
Processing" which is enabled by default when one is on SQL 2017's compatibility
mode (CE 140). Queries can benefit via Adaptive Query Processing in the
following scenarios.
1) Adaptive Join -> Query optimizer makes a intelligent
choice between Nested Loop and Hash Join in runtime
2) Memory Grant Feedback -> Query optimizer adjusts the
memory granted to a query depending upon the previous run's execution results
3) Interleaved Execution -> Portion of the query is
executed while generating the query plan to come with the estimates. Applies
currently for Multi Statement Table Valued Functions alone
The above scenarios and operators are likely to expand in
upcoming releases or patches
Other aspect of auto driven query tuning is "Automatic
Tuning" feature. Automatic Query tuning helps in the following scenarios
Automatic Tuning:
1) Force Last Good Plan: Attempts to fix query regression by
detecting any sudden change in query performance due to plan change and forcing
the last good performing plan. one can turn it on using the command below
ALTER DATABASE DBName SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
2) Automatic Index creation and deletion: Feature available only in Azure SQL Database. By studying the workload, the database engine automatically deletes and creates indexes!!!
This is just a introduction post. As usual,will be writing a series of posts to cover the individual sections in detail.
No comments:
Post a Comment