If you are using Azure SQL Databases (PaaS), over time, your database may need to be tuned to account for how your application accesses data. Azure has a feature called SQL Database Advisor, which will make suggestions on changes that can be applied to help your database perform better. Azure SQL Database Advisor provides guidance related to indexes, parameterized queries, and fixing schema issues. This is done by looking at query history on your database’s workload:
Create Index – SQL Database service detects a missing index
Drop Index – SQL Database service detects duplicate indexes
Parameterize queries – SQL Database service detects that you have one or more queries that are constantly being recompiled but end up with the same query execution plan
Fix schema issues – SQL Database service notices an anomaly in the number of schema related SQL errors happening on your Azure SQL Database. The following SQL errors trigger this recommendation:
- 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.
This is what the “Create Index” recommendation looks like:
When you get to the Azure portal, you will see this message: Improve your database performance. View database recommendation.
You see the list of recommendations, which include the Action, Description, and Impact:
Clicking on the recommendation takes you to further details:
You can click on “Apply” to have the change applied, or you can click on “View Script” and copy the script and run it yourself.
You can also set it so that Create and Drop Index recommendations are applied automatically:
NOTE: SQL Performance Tuning should not be taken lightly. You should not blindly accept every recommendation, make sure that you understand the impact that applying the recommended changes would have on your database and your app’s performance. If in doubt, contact someone that can help you.