Executing SQL Server Assessments from PowerShell

Doctor Scripto

Dr Scripto

Summary: Using the SQLServer module cmdlets to review and monitor SQL Server instance and database configuration

Q: Hey, Doctor Scripto!

I have loads of SQL Servers in my area of responsibility and I know they all need certain configuration settings but I’m never confident that they are all set just right so I spend a long time every month visiting them all over RDP to give reassure myself. How can I automate this work?


A: Hello AB, I know the very person that can answer that question for you. It’s my good friend Jonathan Allen. Let me introduce him to you.

He’s a SQL Server PFE from the UK. He also speaks at and attends PowerShell and Data and AI conferences in the UK and Europe.

As a side note – He help runs the SQLBits conference once a year. Take it away Jonathan!

Thanks, Doctor Scripto!

To solve this challenge you need to take a look at SQL Server Assessments, the latest thing in the SQL Server PowerShell module – available here on the PowerShell Gallery.

How do we get the module?

As the SQLServer module is published on the PowerShell Gallery it is available right at your command line. Check if you have SQLServer already available with this command and check the Version information in the results

Get-Module SQLServer

If you don’t see any results or you see any version older than 21.1.18147 then you need to run

# get the latest (possibly prerelease) version of SQLServer module Update-Module sqlserver -RequiredVersion 21.1.18147 -Scope CurrentUser

(We use -Scope CurrentUser to avoid having to run this as Administrator)

Let’s check that we have the two commands we need by listing the commands in the module with

Get-Command -Module SqlServer -Name *sqlassessment*

This should give us the results of:


Now that we have the cmdlets we need, let’s take a look at what they do, first of all let’s see the what Get-SQLAssessmentItem does. I have a SQL Server 2014 test instance on my laptop so I’ll use that

# see what can be in the instance assessment set-location SQLSERVER:\SQL\localhost\sql2014 Get-SqlAssessmentItem

We get 45 results that point to instance level features such as TraceFlags usage, Deprecated Features, Extended Events sessions, update status, plan use and more.

We can see some of the assessments done on databases by running

# see what can be in the database assessment Set-Location SQLSERVER:\SQL\localhost\sql2014\databases Get-SqlDatabase | Get-SqlAssessmentItem | Group-Object

Here we see properties relating to Statistics, Query Store, Indexes, Constraints etc.

So, how do we actually run an assessment then?

Still working against the test instance on my laptop, we can run an assessment with this command

# running an assessment $SQLInstance = “$ENV:COMPUTERNAME\SQL2014” Get-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment

This will give us a lot of results (at least my test instance did!) but they all share the same properties -we have CheckId, CheckName, HelpLink, Message, Severity, TargetPath, TargetType. Here’s one result with example values for each property

CheckNameAmount of single use plans in cache is high
MessageAmount of single use plans in cache is high (66%). Consider enabling the Optimize for ad hoc workloads setting on heavy OLTP ad-hoc workloads to conserve resources.

Now, getting a stream of results on your console from one computer is hard to analyse, let alone if you run this against multiple servers so let’s look at collecting information into a management database from multiple instances. Let’s create a database on our test server

# step 1 – we need a database $qry = @’ create database SQLAssessmentDemo go ‘@

#step 2 – what instance are we storing the data in $SQLManagementInstance = “$ENV:COMPUTERNAME\SQL2019” Invoke-Sqlcmd -ServerInstance $SQLManagementInstance -Database ‘master’ -Query $qry

Now we can use that database to store the output from Write-SQLTableData like this

$SQLManagementInstance = “$ENV:COMPUTERNAME\SQL2019” Get-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment | Write-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

Get-SqlDatabase -ServerInstance $SQLInstance | Where-Object status -eq ‘normal’ | Invoke-SqlAssessment | Write-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

This creates a table called Results in the Assessment schema in our SQLAssessmentDemo database and places the results of the Instance assessment and the database assessment in there.

We need to alter the Results table just a little to make it easier to analyse the collected data and keep it as a live record of the SQL Server assessments that we run by adding a CollectionDate column.

$SQLManagementInstance = “$ENV:COMPUTERNAME\SQL2019” $qry = @’ ALTER TABLE ASSESSMENT.Results ADD CollectionDate datetime not null default cast(getdate() as varchar(17)) ‘@

Invoke-Sqlcmd -ServerInstance $SQLManagementInstance -Database ‘SQLAssessmentDemo’ -Query $qry

This makes sure that each time we run an assessment the results are stamped with the date and time that the collection was run and we can then track changes in our servers or make sure that remediation work is progressing as we want it to.

Once that column is added then we can run the assessment against multiple servers as and when we want to with this

# multiple server collection $SQLManagementInstance = “$ENV:COMPUTERNAME\SQL2019” $Instances = @(“$ENV:COMPUTERNAME\SQL2014”, “$ENV:COMPUTERNAME\SQL2016”, “$ENV:COMPUTERNAME\SQL2019”) foreach ($SQLInstance in $Instances) { #instances Write-Output “Assessing $SQLInstance” Get-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment | Write-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

#databases Get-SqlDatabase -ServerInstance $SQLInstance | Where-Object status -eq ‘normal’ | Invoke-SqlAssessment | Write-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force }

Once this runs successfully then we can step into SQL Server Management Studio or PowerBI and start to review / analyse our Results table to see where we need to take action first.

Where can I get more information?

Introducing SQL Assessment API (Public Preview) https://techcommunity.microsoft.com/t5/SQL-Server/Introducing-SQL-Assessment-API-Public-Preview/ba-p/778570

Github API https://github.com/microsoft/sql-server-samples/tree/master/samples/manage/sql-assessment-api

Install SQL Server PowerShell module https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

So, AB that is all there is to starting to use SQL Server Assessment cmdlets to monitor your SQL Server estate.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Forum. See you tomorrow. Until then, peace.

Your good friend, Doctor Scripto

PowerShell, Doctor Scripto, Jonathan Allen, SQL Server



Comments are closed.