Microsoft Dynamics NAV on Azure SQL: performance tuning

In the previous days, we’ve discussed a serie of benefits of using Azure SQL Database with NAV if you’re planning for a cloud-based solution.

One more important aspect that Azure gives us with Azure SQL Database is performance monitoring. If you think to your classic NAV on-premise installation, I’m sure that on your SQL Server you have active tasks for indexes and statistics management and for monitoring queries execution.

Azure SQL Database gives us all these monitoring features for free, by identifying potential problems in your database and providing recommended actions and intelligent tuning suggestions that can improve your database performances. On Azure Portal, if you select your Azure SQL Database you can find this section:

AzureSQLPerfMonitoring_01.jpg

Here:

  • Performance overview gives you an overall monitoring of your database performances.
  • Performace recommendations gives you recommendations on how you can optimize your database accordingly to the query it receives (workloads).
  • Query Performance Insight gives you informations about the top resource consuming queries.
  • Automatic Tuning permits you to automatically tuning the database.

You can enable automatic tuning for a single database by selecting the Automatic Tuning option and changing the Inherit from section:

AzureSQLPerfMonitoring_02.jpg

What I personally love is Performance Recommendations:

AzureSQLPerfMonitoring_03

Azure SQL monitors your database and after at least 1 day of monitoring it shows you a table with suggestions ordered by impact (high, medium, low). You can click on a suggestion, analyze it and then you can decide if apply it or not.

AzureSQLPerfMonitoring_04.jpg

With an NAV database deployed on Azure SQL, this is a very useful feature: you have a centralized panel where you can easily monitor all your database activities and apply optimizations without using stored procedure or other server-side modifications. You can also be notified (for example via email) if some of your metrics is not ok.

In my experience, I see every day NAV databases with SQL on-premise that are not optimize or never maintained (no maintenance plans) and with zero monitoring of activites and queries. Azure SQL helps you on this… it’s like having a sysadmin/DBA in the cloud 😉

Think on this…

3 Comments

  1. Hi Stefano,another one brilliant blog post 🙂
    Just a question, since sql server agent doesn’t exist in sql azure,do you thing performance tuning as shown above is ok for NAV dbs or somebody should think about using azure automation to do maintenance tasks?
    Regards

    Like

  2. Regarding Azure SQL, the automatic performance tuning works really good. Azure SQL is a managed service and the platforms makes automatic optimization based on usage, loads and queries.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s