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 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 currently in preview. We would love to hear your feedback.
Before you start
- In order to enable SQL 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 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 Assessment can help you manage your SQL Server on Azure VM. In this scenario, we are looking at an environment where SQL Assessment 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 219 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. The more interesting part is the 21 recommendations with severity high (2), medium (10), and low (9). There are also 4 informational messages in this run.
Let's look at the PlansUseRatio rule first. This is a medium severity issue.
Recommendation: Enable 'Optimize for ad hoc workloads' option on heavy OLTP ad-hoc workloads to conserve resources. Current amount of single-use plans in cache is high (55 %).
Description field explains why: The 'Optimize for ad hoc workloads' option helps relieve memory pressure by not allowing the plan cache to become filled with compiled plans that aren't reused.
You can further read about this issue by following the help link.
Once you fix this issue, in the next assessment run results you should see it under the resolved issues tab.
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.
You can go to your SQL virtual machine page on Azure portal and try it out today.
Posted at https://sl.advdat.com/3GIsZC6
