Azure SQL Database Advisor

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.
image

You see the list of recommendations, which include the Action, Description, and Impact:
image

Clicking on the recommendation takes you to further details:
image

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:
image

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.

About esteban

Esteban is the Founder and Chief Technologist at Nebbia Technology, an ALM consulting and Azure-powered technology company. He is a software developer with a passion for ALM, TFS, Azure, and software development best practices. Esteban is a Microsoft Visual Studio ALM MVP and ALM Ranger, Pluralsight author, and the president of ONETUG (Orlando .NET User Group).

2 thoughts on “Azure SQL Database Advisor

  1. Dew Drop - August 15, 2016 (#2309) - Morning Dew

    […] Azure SQL Database Advisor (Esteban Garcia) […]

  2. Sandesh Daddi

    Can I use Application Insights to show page views on my page itself?

    I wanted to show number of times this particular page has been viewed a the bottom of page. It is possible with Application Insights ?


Leave a Reply

Your email address will not be published. Required fields are marked *

Are you human? *