Azure SQL Database learns and adapts with your application and provides customized recommendations enabling you to maximize the performance of your SQL databases. Your performance is continuously assessed by analyzing your SQL Database usage history. The recommendations that are provided are based on a database unique workload pattern and help improve its performance.
Create index recommendations
Azure SQL Database continuously monitors the queries being executed and identifies the indexes that could improve the performance. Once enough confidence is built that a certain index is missing, a new Create index recommendation will be created. Azure SQL Database builds confidence by estimating performance gain the index would bring through time. Depending on the estimated performance gain, recommendations are categorized as High, Medium, or Low.
Indexes created using recommendations are always flagged as auto_created indexes. You can see which indexes are auto_created by looking at sys.indexes view. Auto created indexes don’t block ALTER/RENAME commands. If you try to drop the column that has an auto created index over it, the command passes and the auto created index is dropped with the command as well. Regular indexes would block the ALTER/RENAME command on columns that are indexed.
Once the create index recommendation is applied, Azure SQL Database will compare performance of the queries with the baseline performance. If new index brought improvements in the performance, recommendation will be flagged as successful and impact report will be available. In case the index didn’t bring the benefits, it will be automatically reverted. This way Azure SQL Database ensures that using recommendations will only improve the database performance.
Any Create index recommendation has a back off policy that won’t allow applying the recommendation if the resource usage of a database or pool is high. Back off policy takes into account CPU, Data IO, Log IO, and available storage. If CPU, Data IO, or Log IO was higher than 80% in last 30 minutes create index will be postponed. If available storage would be below 10% after the index is created, recommendation will go into error state. If after couple of days automatic tuning still believes that index would be beneficial the process will start again. This process will repeat until there is enough available storage to create an index or index is not seen as beneficial anymore.
Drop index recommendations
In addition to detecting a missing index, Azure SQL Database continuously analyzes the performance of existing indexes. If index is not used, Azure SQL Database will recommend dropping it. Dropping an index is recommended in two cases:
- Index is a duplicate of another index (same indexed and included column, partition schema, and filters)
- Index is unused for a prolonged period (93 days)
Drop index recommendations also go through the verification after implementation. If the performance is improved the impact report will be available. In case performance degradation is detected, recommendation will be reverted.
Parameterize queries recommendations
Parameterize queries recommendations appear when you have one or more queries that are constantly being recompiled but end up with the same query execution plan. This condition opens up an opportunity to apply forced parameterization, which will allow query plans to be cached and reused in the future improving performance and reducing resource usage.
Every query issued against SQL Server initially needs to be compiled to generate an execution plan. Each generated plan is added to the plan cache and subsequent executions of the same query can reuse this plan from the cache, eliminating the need for additional compilation.
Applications that send queries, which include non-parameterized values, can lead to a performance overhead, where for every such query with different parameter values the execution plan is compiled again. In many cases the same queries with different parameter values generate the same execution plans, but these plans are still separately added to the plan cache. Recompiling execution plans use database resources, increase the query duration time and overflow the plan cache causing plans to be evicted from the cache. This behavior of SQL Server can be altered by setting the forced parameterization option on the database.
To help you estimate the impact of this recommendation, you are provided with a comparison between the actual CPU usage and the projected CPU usage (as if the recommendation was applied). In addition to CPU savings, your query duration decreases for the time spent in compilation. There will also be much less overhead on plan cache, allowing majority of the plans to stay in cache and be reused. You can apply this recommendation quickly and easily by clicking on the Apply command.
Once you apply this recommendation, it will enable forced parameterization within minutes on your database and it starts the monitoring process which approximately lasts for 24 hours. After this period, you will be able to see the validation report that shows CPU usage of your database 24 hours before and after the recommendation has been applied. SQL Database Advisor has a safety mechanism that automatically reverts the applied recommendation in case a performance regression has been detected.
Fix schema issues recommendations (preview)
Microsoft is in the process of deprecating "Fix schema issue" recommendations. You should start using Intelligent Insights for automatic monitoring of your database performance issues, which include schema issues that previously "Fix schema issue" recommendations covered.
Fix schema issues recommendations appear when the SQL Database service notices an anomaly in the number of schema-related SQL errors happening on your Azure SQL Database. This recommendation typically appears when your database encounters multiple schema-related errors (invalid column name, invalid object name, etc.) within an hour.
“Schema issues” are a class of syntax errors in SQL Server that happen when the definition of the SQL query and the definition of the database schema are not aligned. For example, one of the columns expected by the query may be missing in the target table, or vice versa.
“Fix schema issue” recommendation appears when Azure SQL Database service notices an anomaly in the number of schema-related SQL errors happening on your Azure SQL Database. The following table shows the errors that are related to schema issues:
|SQL Error Code||Message|
|201||Procedure or function '' expects parameter '', which was not supplied.|
|207||Invalid column name '*'.|
|208||Invalid object name '*'.|
|213||Column name or number of supplied values does not match table definition.|
|2812||Could not find stored procedure '*'.|
|8144||Procedure or function * has too many arguments specified.|
Monitor your recommendations and continue to apply them to refine performance. Database workloads are dynamic and change continuously. SQL Database advisor continues to monitor and provide recommendations that can potentially improve your database's performance.
- See Azure SQL Database automatic tuning for automatic tuning of database indexes and query execution plans.
- See Azure SQL Intelligent Insights for automatically monitoring database performance with automated diagnostics and root cause analysis of performance issues.
- See Performance recommendations in the Azure portal for steps on how to use performance recommendations in the Azure portal.
- See Query Performance Insights to learn about and view the performance impact of your top queries.