Preview: SQL Assessment for SQL Server on Azure Virtual Machines

Published Nov 02 2021 09:34 AM 2,337 Views
Microsoft

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

 

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.

SQLAssessments.gif

 

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.

Workbook.png

 

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.

PlanUseRatio Rule Details.png

 

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.

LPIM Rule Details.png

 

You can go to your SQL virtual machine page on Azure portal and try it out today.

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2913466%22%20slang%3D%22en-US%22%3EPreview%3A%20SQL%20Assessment%20for%20SQL%20Server%20on%20Azure%20Virtual%20Machines%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%224%22%3EWouldn't%20it%20be%20great%20if%20there%20was%20a%20way%20to%20learn%20if%20your%20SQL%20Server%20on%20Azure%20Virtual%20Machines%20was%20configured%20optimally%3F%20Do%20you%20have%20the%20right%20options%20set%3F%20Do%20you%20have%20your%20tempdb%20on%20the%20right%20disk%3F%20Can%20your%20queries%20perform%20better%3F%20All%20these%20and%20more%20can%20be%20answered%20using%20the%20new%20Azure%20portal%20experience%20on%20the%20SQL%20virtual%20machine%20resource%20page.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fsql-assessment-for-sql-vm%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20Assessment%3C%2FA%3E%20feature%2C%20once%20enabled%2C%20will%20evaluate%20your%20SQL%20Server%20on%20Azure%20VM%20against%20configuration%20best%20practices%20to%20determine%20if%20your%20system%20is%20healthy%20and%20setup%20for%20success.%20This%20feature%20is%20currently%20in%20preview.%20We%20would%20love%20to%20hear%20your%20feedback.%3C%2FFONT%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--298912565%22%20id%3D%22toc-hId--298824213%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--2106367028%22%20id%3D%22toc-hId--2106278676%22%3EBefore%20you%20start%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%3E%3CFONT%20size%3D%224%22%3EIn%20order%20to%20enable%20SQL%20Assessment%2C%20your%20SQL%20Server%20on%20Azure%20VM%20(2012%20and%20higher%20versions)%20needs%20to%20be%20registered%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fsql-server-iaas-agent-extension-automate-management%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20IaaS%20extension%3C%2FA%3E%20in%20full%20mode.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fsql-agent-extension-automatic-registration-all-vms%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ERegistering%20your%20VM%3C%2FA%3E%20is%20easy%20and%20provides%20additional%20benefits%20that%20help%20you%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fmanage-sql-vm-portal%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Emanage%20your%20SQL%20Server%20on%20Azure%20VM%3C%2FA%3E.%20Did%20you%20know%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fsql-agent-extension-manually-register-single-vm%3Ftabs%3Dbash%252Cazure-cli%23full-mode%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Efull%20mode%20registration%3C%2FA%3E%20doesn't%20require%20a%20restart%20anymore%3F%26nbsp%3B%3C%2FFONT%3E%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20size%3D%224%22%3EAssessment%20results%20are%20uploaded%20to%20your%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Freview.docs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flogs%2Fquick-create-workspace%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELog%20Analytics%20workspace%3C%2FA%3E%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Freview.docs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fagents%2Flog-analytics-agent%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMicrosoft%20Monitoring%20Agent%20(MMA)%3C%2FA%3E.%20If%20your%20VM%20is%20already%20configured%20to%20use%20Log%20Analytics%2C%20the%20SQL%20Assessment%20feature%20uses%20the%20existing%20connection.%20Otherwise%2C%20the%20MMA%20extension%20is%20installed%20to%20the%20SQL%20Server%20VM%20and%20connected%20to%20the%20specified%20Log%20Analytics%20workspace.%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId-381145805%22%20id%3D%22toc-hId-381234157%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1426308658%22%20id%3D%22toc-hId--1426220306%22%3ELet's%20see%20it%20in%20action%3C%2FH2%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EWe'll%20walk%20through%20some%20examples%20to%20see%20how%20SQL%20Assessment%20can%20help%20you%20manage%20your%20SQL%20Server%20on%20Azure%20VM.%20In%20this%20scenario%2C%20we%20are%20looking%20at%20an%20environment%20where%20SQL%20Assessment%20has%20been%20enabled%20and%20multiple%20runs%20have%20been%20done.%20You%20can%20do%20on%20demand%20assessments%20using%20the%20Run%20Assessment%20button%20as%20well%20as%20schedule%20them%20to%20run%20automatically%20using%20the%20Configuration%20button.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SQLAssessments.gif%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323716i63BCF34575C1619F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SQLAssessments.gif%22%20alt%3D%22SQLAssessments.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3ELet's%20look%20at%20the%20latest%20run's%20results.%20You%20will%20see%20that%20your%20system%20already%20follows%20219%20recommendations.%20You%20don't%20need%20to%20worry%20about%20them%20but%20if%20you%20want%20to%20see%20the%20list%2C%20you%20can%20do%20so%20by%20clicking%20on%20the%20legend.%20The%20more%20interesting%20part%20is%20the%2021%20recommendations%20with%20severity%20high%20(2)%2C%20medium%20(10)%2C%20and%20low%20(9).%20There%20are%20also%204%20informational%20messages%20in%20this%20run.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Workbook.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323072iE76EF98078E465AD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Workbook.png%22%20alt%3D%22Workbook.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3ELet's%20look%20at%20the%20PlansUseRatio%20rule%20first.%20This%20is%20a%20medium%20severity%20issue.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3ERecommendation%3A%20Enable%20'Optimize%20for%20ad%20hoc%20workloads'%20option%20on%20heavy%20OLTP%20ad-hoc%20workloads%20to%20conserve%20resources.%20Current%20amount%20of%20single-use%20plans%20in%20cache%20is%20high%20(55%20%25).%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EDescription%20field%20explains%20why%3A%26nbsp%3B%20The%20'Optimize%20for%20ad%20hoc%20workloads'%20option%20helps%20relieve%20memory%20pressure%20by%20not%20allowing%20the%20plan%20cache%20to%20become%20filled%20with%20compiled%20plans%20that%20aren't%20reused.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EYou%20can%20further%20read%20about%20this%20issue%20by%20following%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Foptimize-for-ad-hoc-workloads-server-configuration-option%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehelp%20link%3C%2FA%3E.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EOnce%20you%20fix%20this%20issue%2C%20in%20the%20next%20assessment%20run%20results%20you%20should%20see%20it%20under%20the%20resolved%20issues%20tab.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PlanUseRatio%20Rule%20Details.png%22%20style%3D%22width%3A%20565px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323082i95322291AFC593F7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PlanUseRatio%20Rule%20Details.png%22%20alt%3D%22PlanUseRatio%20Rule%20Details.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3ELockedPagesInMemory%20rule%20is%20an%20example%20of%20a%20recommendation%20specific%20to%20SQL%20Server%20on%20Azure%20VM.%20This%20is%20a%20high%20severity%20rule%20so%20we%20recommend%20you%20take%20action%20on%20it%20as%20soon%20as%20possible.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3ERecommendation%3A%20Enable%20the%20'Lock%20pages%20in%20memory'%20option%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EDescription%20field%20explains%20why%3A%20This%20Windows%20policy%20determines%20which%20accounts%20can%20use%20a%20process%20to%20keep%20data%20in%20physical%20memory%2C%20preventing%20the%20system%20from%20paging%20the%20data%20to%20virtual%20memory%20on%20disk.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EYou%20can%20further%20read%20about%20this%20issue%20by%20following%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fazure-sql%2Fvirtual-machines%2Fwindows%2Fperformance-guidelines-best-practices-checklist%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehelp%20link%3C%2FA%3E.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CFONT%20size%3D%224%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LPIM%20Rule%20Details.png%22%20style%3D%22width%3A%20561px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323083i5BB60B340813ADE0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22LPIM%20Rule%20Details.png%22%20alt%3D%22LPIM%20Rule%20Details.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%224%22%3EYou%20can%20go%20to%20your%20SQL%20virtual%20machine%20page%20on%20Azure%20portal%20and%20try%20it%20out%20today.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2913466%22%20slang%3D%22en-US%22%3E%3CP%3ESQL%20Assessment%20feature%20will%20evaluate%20your%20SQL%20Server%20on%20Azure%20VM%20against%20configuration%20best%20practices%20to%20determine%20if%20your%20system%20is%20healthy%20and%20setup%20for%20success.%20This%20feature%20is%20available%20on%20the%20SQL%20virtual%20machine%20resource%20page%20and%20is%20currently%20in%20preview.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2913466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerAzureVM%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Nov 03 2021 04:53 AM
Updated by: