Azure SQL Database – Automatic Index Tuning

Introduction: -

We live in an era of “Automation”.  Planes run on auto pilot mode, cars will soon be auto driven, so why not have auto indexed databases? This article will discuss the automatic index tuning feature available in Azure SQL Database.

Problem Statement:

Application development is moving at a rapid pace and its very common for application teams to develop a template database design and deliver it to multiple customers.  It is even more common in cloud where solutions are delivered as “software as a service” and it is way too challenging for application development team to analyze the workload of individual database, design specific indexes for them and tune them.

Automatic tuning in Azure SQL Database

Azure SQL Database, the cloud Platform as a service (PAAS) offering of SQL Server has attempted to address the above problem by introducing “Automatic tuning”. As of date, “automatic tuning” has the following features

·         Automatic plan correction – Plan regressions or sudden change in query plan resulting in performance degradation are tracked and last good plan available is forced. Automatic plan correction is already well explained over here

·         Automatic Index Tuning:  Automatic index tuning feature of Azure SQL Database will study the workload executed against the database, identify useful indexes and create them automatically. Automatic index tuning will also drop indexes that are unused over 93 days or any duplicate indexes present.

How to enable automatic index tuning

Once logged in to Azure Portal (portal.azure.com) one can enable the same by navigating to SQL Databases-> Click on the database -> Automatic tuning

·         Force plan -> Enables Automatic Plan Correction

        ·         Create / Drop Index -> enables automatic index tuning

Features can be enabled at server level and can be inherited at the database level as well.

TSQL Script for enabling automatic index tuning provided below

ALTER DATABASE DBName

          SET AUTOMATIC_TUNING (CREATE_INDEX = ON, 
      DROP_INDEX = ON)

Example:

To test how Auto Indexing works, following things were performed

·         A sample database with 5 tables with no indexes was created

·         10 to 15 select queries against the tables were executed repeatedly few thousand times over 3 to 4 hours

·         After one day, automatic indexing found the recommendations and rolled them out when there was less or no workload on the database.  Auto Indexing requires minimum one day of monitoring to identify recommendations

 This can be verified on Azure Portal by clicking on the “Database name” -> “Performance Recommendation”

The same can also be verified via the DMV sys.dm_db_tuning_recommendations, documented clearly over here

Key pointers of Automatic Index Tuning

·         Indexing recommendations are obtained using advanced artificial intelligence rules applied on the monitored workload. The recommendations are expected to be lot more accurate than earlier missing index recommendations from SSMS

·         The best part of the feature is it is non-blocking during the rollout of new indexes. 

·         Index changes wouldn’t be rolled out and will be postponed, if DTU usage was above 80% anytime in the last 20 minutes

·         Automatically created indexes don’t enforce any dependency on the columns. When the column with automatically created index is dropped, index is dropped as well

·         Auto created indexes can be tracked via sys.indexes – auto_created column

Learn – Adapt - Verify

One of the key features of Automatic Index tuning is the ability to monitor the impact of the changes done via automatic tuning. Azure SQL Database closely monitors the automatically created or dropped indexes and verifies if it has achieved the desired performance improvement. If it doesn’t then the change is automatically reverted to prevent any negative impact to the database. The reverted changes can be tracked from sys.dm_db_tuning_recommendations DMV as well.

Suggested enhancements

·         Automatic indexing is available only as an Azure SQL Database feature and is not available on SQL Server on premise. If would be wonderful if the feature could be extended to on premise SQL Server.

·         Automatic Index tuning should be extended to fixing fragmentation issues of the indexes as well.

Conclusion

 Automatic Index Tuning does simplify one of the major tasks of a database administrator and a developer. Automatic Index Tuning may not be 100% perfect to get the choices correct all the time but it is certainly a step in the right direction. Expect Microsoft to release more of such features in the days to come not just in indexing but also all areas of database maintenance

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.

Adaptive Query Processing and Automatic Tuning - Adaptive Joins - 2

Continuing from the introduction written over here, let’s look at one of the aspects of Adaptive Query Processing which is Adaptive Joins.

Before we start Adaptive Joins, SQL Server's Query optimizer internally has 3 join types.

Nested Loop Join - Usually picked by optimizer, when tables participating in the joins are small or when one of the table is big and indexed on joining column
Hash Join: One of the table is big but doesn't have an index
Merge Join: Both the tables are big, and the joining column is indexed

So, the size of the table or the rows participating in the join plays a major role SQL's Query optimizer picking the correct join type. Let’s say, SQL Server estimates few rows participating in the join at compile time and picks "nested join" but during runtime, the join extracts larger number of rows and thereby making the choice of plan ineffective. 

SQL Server 2017's adaptive join addresses this problem. With Adaptive Join, SQL Server comes with plan attribute called "Adaptive Row Threshold" which is a row count threshold estimated by SQL Server.  "Adaptive Row Threshold" will help SQL Server dynamically alter the plan choice at run time. Adaptive join in short will work in the following way

• If the number of rows participating in the join are greater than "Adaptive Row Threshold" rows, then "Hash Join" operator is used for join operation
• If the number of rows participating in the join are lesser than "Adaptive Row Threshold" rows, then "Nested loop Join" operator is used for join operation

Consider the following query:

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] = 360;

Observe the picture below:

Query Optimizer fixes 68 rows as Adaptive join threshold.

Estimated number of rows for the outer table in the join was 213. As 213 is greater than 68, estimated join type is Hash Match Join. During runtime, 206 rows from outer table participated in the join and as it was greater than "Adaptive join threshold" of 68, Hash match join was selected.

Sharing another example where Adaptive Join switches the join type in runtime. Refer to pic below

In this case, threshold was 46 rows and estimate were 87 rows. However, runtime row count was just 36 rows hence switching the join type to Nested loop join from an estimate of "Hash Join"

 The ability to alter execution plan operator in runtime would make SQL Server's query optimizer weed out most of the incorrect plan choices during runtime.

Adaptive Query Processing and Automatic Tuning - Introduction - 1

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.