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:

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:

What I personally love is Performance Recommendations:

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.

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…