Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to use the SQL Assessment API through Powershell

15 October,2019 by Tom Collins

Microsoft released a SQL Assessment API module , available to Powershell . What is the SQL Assessment API?  It's a new method to assess the SQL Server configurations against a set of SQL Server best practises. The SQL Assessment API is available through the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. 

I've installed the SQL Assessment API and started thinking about how to apply to multiple server.s The availability of the rules via Powershell is a massive plus. This raises fantastic opportunities for automation and inclusion into regular sql server health checks or certification process. One of the major benefits when working with Powershell is the power to iterate through multiple environments very quickly . 

The SQL Assessment API is available for server level and database level checks. In this post I'll take you through a server level check .

--check you have the commands required


Get-Command -Module SqlServer -Name *sqlassessment*

On my laptop , returns , which were the latest versions at the time . If you're not getting something similar to below , than you need to investigate why these powershell cmdlets , are not appearing. The main reason is normally because the latest Powershell SQLServer module is not installed or the modules are not installed in directory included as part of the PATH

CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Get-SqlAssessmentItem 21.1.18179 SqlServer
Cmdlet Invoke-SqlAssessment 21.1.18179 SqlServer

--To return all available checks on a server

Get-SqlInstance -ServerInstance 'myserver\instance' | Get-SqlAssessmentItem

--To invoke a SQL assessment and print out onto the screen 

Get-SqlInstance -ServerInstance 'myserver\instance' |Invoke-SqlAssessment

--or invoke a SQL assessment using a variable for the SQL Server Instance 

Get-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment


Sample output

Sev. Message Check ID Origin
---- ------- -------- ------
Info Consider enabling trace flag 174 to increase the plan cache TF174 Microsoft Ruleset 0.1.203
bucket count.
Info Enable trace flag 834 to use large-page allocations to improve TF834 Microsoft Ruleset 0.1.203
analytical and data warehousing workloads.


--Write the sqlassessment results to a table in a database.

If the database,schema & table doesn't exist , it will be automatically created - assuming privileges exist

$Table = "Results"
$Instance = "server\instance"
$DBname = "SQLAssessment"
$SchemaName = "Assessment"
Get-SqlInstance -ServerInstance $Instance | Invoke-SqlAssessment -FlattenOutput | Write-SqlTableData -ServerInstance $Instance -DatabaseName $DBname -SchemaName $SchemaName -TableName $Table -Force

If you want to enhance the monitoring of SQL Server vulnerabilities read How To Automate SQL Server Vulnerability Assessment


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to use the SQL Assessment API through Powershell | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer