Wouldn't it be great if there was a way to learn if your SQL Server on Azure Virtual Machines was configured optimally? Do you have the right options set? Do you have your tempdb on the right disk? Can your queries perform better? All these and more can be answered using the new Azure portal experience on the SQL virtual machine resource page. SQL best practices assessment feature, once enabled, will evaluate your SQL Server on Azure VM against configuration best practices to determine if your system is healthy and setup for success. This feature is generally available.
Before you start
- In order to enable SQL best practices assessment, your SQL Server on Azure VM (2012 and higher versions) needs to be registered with SQL IaaS extension in full mode. Registering your VM is easy and provides additional benefits that help you manage your SQL Server on Azure VM. Did you know full mode registration doesn't require a restart anymore?
- Assessment results are uploaded to your Log Analytics workspace using Microsoft Monitoring Agent (MMA). If your VM is already configured to use Log Analytics, the SQL best practices assessment feature uses the existing connection. Otherwise, the MMA extension is installed to the SQL Server VM and connected to the specified Log Analytics workspace.
Let's see it in action
We'll walk through some examples to see how SQL best practices assessment can help you manage your SQL Server on Azure VM. In this scenario, we are looking at an environment where this feature has been enabled and multiple runs have been done. You can do on demand assessments using the Run Assessment button as well as schedule them to run automatically using the Configuration button.
Let's look at the latest run's results. You will see that your system already follows 360 recommendations. You don't need to worry about them but if you want to see the list, you can do so by clicking on the legend.
By the way, you can review all the recommendations we have in our github page for SQL Assessment API which is the recommendation engine used in this feature.
The more interesting part is the 54 recommendations with severity high (3), medium (26), and low (20). There are also 5 informational messages in this run.
You can filter the results using the dropdowns above the grid. You can focus on a particular database using the Name filter or pick a severity class to start working through the recommendations. Left hand side grid lists all the recommendations for your SQL Server on Azure VM with the number of occurrences for each. Clicking on one of them will filter the right hand side grid just to that recommendation. If you click on the Message field, you'll see more details about the recommendation.
Let's take a look at one of the recommendations more closely. LockedPagesInMemory rule is an example of a recommendation specific to SQL Server on Azure VM. This is a high severity rule so we recommend you take action on it as soon as possible.
Recommendation: Enable the 'Lock pages in memory' option
Description field explains why: This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.
You can further read about this issue by following the help link.
We hope you will use this feature to make sure your SQL Server on Azure VM is setup for success.
Microsoft
