Running a SQL Server Best Practice Assessment with PowerShell

My last post covered the SQL Assessment extension in Azure Data Studio and in this article I'm going to look at running the SQL Assessment API using PowerShell instead. Whilst in ADS we can export assessment results to HTML or into SQL via a script the PowerShell option is a bit more flexible, we can export straight to a SQL table and run the assessment across multiple instances.

I'll follow the PowerShell instructions from the SQL Assessment API web page; this is the code that will run the assessment on my localhost instance and it will also write the results to a table (Assessment.Results) in the SQLAssessmentDemo database, the -Force parameter will create the objects if they don't already exist:

Get-SqlInstance -ServerInstance 'localhost' |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

If required we're not just limited to one instance, in the following script I can run the assessment against my default instance and a named instance (localhost\MSSQLSERVER01).

Get-SqlInstance -ServerInstance 'localhost', 'localhost\MSSQLSERVER01' |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

But for added flexibility I can store the names of the SQL instances in a text file and loop through the content using the following:

$servers = Get-Content -Path C:\SQL\SQLServers.txt

ForEach ($server in $servers)

{

Get-SqlInstance -ServerInstance $server |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

}

The scripts will export the assessment data into the specified table where the check information is stored, along with items such as a timestamp, the ruleset being used and the help link. One thing that is missing is the tag which is available in ADS which I think would be really useful when querying the data.

One column of note is the TargetType, in ADS when the assessment is ran it returns checks both the instance and each of the databases but in PowerShell the server and database assessments need to be ran as separate scripts, here's the database level one which is using Get-SqlDatabase as opposed to Get-SqlInstance:

$servers = Get-Content -Path C:\SQL\SQLServers.txt

ForEach ($server in $servers)

{

Get-SqlDatabase -ServerInstance $server |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

}

And of course we can combine the server and database level scripts into one which will gather both sets of assessment results:

$servers = Get-Content -Path C:\SQL\SQLServers.txt

ForEach ($server in $servers)

{

Get-SqlInstance -ServerInstance $server |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

Get-SqlDatabase -ServerInstance $server |

Invoke-SqlAssessment -FlattenOutput |

Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force

}

Now the data is stored in SQL we can look at the results, for my two instances I have 1213 rows of assessment data to look through which is a lot. To filter on which instance I return assessment results for I can use the TargetPath column (I'm having to double up on the single quotes to avoid a syntax error):

SELECT * FROM

[SQLAssessmentDemo].[Assessment].[Results]

WHERE TargetPath = 'Server[@Name=''DESKTOP-MFKSHR7'']'

If I wanted to filter on just the server level assessment items then I can use the following:

SELECT * FROM

[SQLAssessmentDemo].[Assessment].[Results]

WHERE TargetPath = 'Server[@Name=''DESKTOP-MFKSHR7'']'

AND TargetType = 'Server'

Filtering for groups of items is a little more difficult without the Tag column which is available in Azure Data Studio. As the assessment has a rule for individual SQL Agent alerts based on their severity then we need to use a LIKE clause to return all the relevant results:

SELECT * FROM

[SQLAssessmentDemo].[Assessment].[Results]

WHERE TargetPath = 'Server[@Name=''DESKTOP-MFKSHR7'']'

AND TargetType = 'Server'

AND CheckId LIKE 'AgentAlerts%'

The recommendation itself is stored in the Message column, here's the field content for the  AgentAlertsSeverity10 CheckId row (that's a lot of alerts):

Create alerts for errors: 825, 833, 855, 856, 3452, 3619, 17179, 17883, 17884, 17887, 17888, 17890, 28036

The chances are you have one than one database on an instance of SQL Server and the database name of each assessment rule is stored in the TargetType column, such as; Server[@Name='DESKTOP-MFKSHR7']/Database[@Name='WideWorldImporters']  and again we'd need to double quote when filtering on the value in SQL. The following query will return all the database assessment results for the WideWorldImporters database:

SELECT * FROM [SQLAssessmentDemo].[Assessment].[Results]

WHERE TargetType = 'Database' 

AND TargetPath = 'Server[@Name=''DESKTOP-MFKSHR7'']/Database[@Name=''WideWorldImporters'']'

Database checks are very comprehensive, particularly because of the amount of checks on indexes that might be deemed unnecessary, items such as unused or missing indexes should always be reviewed very carefully. 

As an example on the results on my instance (WorldWideImporters) is to revise the unused index [FK_Sales_Customers_PostalCityID]. If I went ahead and dropped the index it would then be picked up by the assessment rule that checks for Foreign Keys with no index (FKNoIndexes).

If we wanted to remove data from the SQL table prior to exporting then a straightforward TRUNCATE TABLE can be used before repopulating the data: TRUNCATE TABLE [SQLAssessmentDemo].[Assessment].[Results] but as a Timestamp column is also exported we can easily build an automated weekly process (such as scheduling the PowerShell script to run via SQL Agent) that will store a complete repository over time of the assessment results which can be filtered on the date to track the history.

This is the big advantage of the PowerShell approach, it's really straightforward to implement an automated best practice assessment of your SQL Server instances and having the data in SQL Server means the creative types can build visualisations on top of the data for funky things like dashboards. Worth noting the table isn't indexed in any fashion by default so you'll need to look into that for better performing queries.

It's worth adding that there are plenty of third party options for SQL Server assessments out there that are both paid for and open source. Having the SQL Assessment API available is another option to take a look at, the underlying JSON is customisable too if you wanted to tweak some of the settings, disable rules or add your own and with the simplicity of PowerShell and the ability of running the assessment over multiple instances is a big advantage for me.

Exploring Azure Data Studio Extensions - SQL Assessment

Recently I've been spending more time using Azure Data Studio, for anyone not familiar with this tool you can read more about it here. The link has a handy comparison between Azure Data Studio (ADS) and SQL Server Management Studio (SSMS) and I have to admit whilst I do use SSMS for most things, ADS comes with lots of cool stuff, and who doesn't like cool stuff?

Cool stuff in ADS comes in the form of extensions. I was demoing the Azure SQL Migration extension in this recent post where I was looking at the recently added SKU recommendations for Azure migration targets. There are lots of other extensions available so I'm starting a few posts to take a look at the functionality they offer.

To begin with in this post I'm looking at the SQL Assessment extension which at the time of writing is in preview and is at version 0.6.1. The extension uses the SQL Assessment API (more about that here) to provide a best practice analyser for a SQL Servers configuration, you can read about using this with PowerShell in this post. 

To install the extension simply browse to the extension marketplace in ADS, select SQL Assessment and click install:

To use the extension right click an available connection in ADS and select Manage where SQL Assessment will be available under the General section:

This opens the SQL Assessment screen and there are two main options available that I want to demo, Invoke assessment and View applicable rules. Before I start an assessment I'll have a look at the rules to see the kind of things the assessment is looking for.

When it comes to the amount of rules, let's just say there's a lot (full list of rules here) and at first it's showing the rules that apply to various trace flags in SQL Server that can be enabled and by clicking each item a handy description is displayed underneath the rule window (apologies for the image format btw):

Each rule uses tags to indicate the area of SQL Server that the rule applies to, here the rule is checking TF 174 (increases plan cache bucket count) and the relevant Tags are TraceFlag (naturally), Memory and Performance and we can filter the view for specific tags by clicking the down arrow on the Tags menu bar:

Looking through each of the tags and rules will take a lifetime so let's get on with running the assessment on my local test instance; to do that just click Invoke assessment on the menu bar and the view quickly starts to fill up with messages! In total the assessment took just under a minute to run on my instance.

It would have been nice to have a visual summary at this point, perhaps showing a total of warnings/issues that the analyser picked up and broken down per database (as the HTML report does which I'll mention shortly) but I can filter on target (instance/database) as well severity and select to show just information or warnings. Edit: You can use the History tab to view number of Errors, Warnings and Information checks per assessment.

On my laptop screen the results pane is a little bit cramped:

Fortunately though there are two export options available either to HTML where you specify a local file which handily opens in a browser straight from ADS or export as a SQL script where the results can be loaded into a SQL table.

The HTML report is organised really well; the instance level items are first which are followed by the database level items and it's very concise! It includes some of the usual suspects for creating SQL Agent alerts, although I have to add, that's a lot of alerts:

Create alerts for errors: 825, 833, 855, 856, 3452, 3619, 17179, 17883, 17884, 17887, 17888, 17890, 28036

Create alerts for errors: 2508, 2511, 3271, 5228, 5229, 5242, 5243, 5250, 5901, 17130, 17300

Create alerts for errors: 802, 845, 1101, 1105, 1121, 1214, 8642, 9002

Create alerts for errors: 701

Create alerts for errors: 3624

Create alerts for errors: 605

Create alerts for errors: 5180, 8966

Create alerts for errors: 5572, 9100

Create alerts for errors: 823, 824, 832

And items such as Optimize for ad hoc workloads (which everyone was advised to do before this post came along from Erik Darling) - the percentage of single use plans currently in the plan cache (65%) is nice to see.

Enable 'Optimize for ad hoc workloads' setting on heavy OLTP ad-hoc workloads to conserve resources. Current amount of single use plans in cache is high (65%)

That post does raise a very important point; whilst the assessment tool provides a very in-depth exploration of your SQL Server and will raise lots of different potential tweaks just doing them all without understanding the impact could well be a recipe for disaster (though each rule does come with a Learn More link for further research).

Database rules go a little deeper. As well as checking things like the last DBCC CHECKDB, transaction log backups and compatibility levels there are a lot of index level checks such as tables with no index (CheckID = TableNoIndex) and foreign keys without indexes (FKNoIndexes) as well as checks for redundant and rarely used indexes (incidentally the Learn More link for these rules takes you to the DROP INDEX page which to me seems a little final!). 

There's also checks everyone's favourite debatable subject index fragmentation and I assume by the optimisers missing index recommendations a MissedIndex check as well - again these definitely should come with the warning not to blindly follow the advice. BTW a nice addition would be a rule for a duplicate index check.

I'm not going to cover the export as script option from ADS in this post, whilst I think it's a nice feature I think using the SQL Assessment API with PowerShell is a much easier way to export the results to a SQL table and I'll definitely cover that in a forthcoming post.

So to summarise; the SQL Assessment extension is a fantastic addition to ADS and provides a very concise assessment of how your SQL Server and databases are setup, configured and utilised. Yes, there's a lot of rules that will get flagged that are up for debate and some of the recommendations could be perhaps viewed as overkill, or maybe even a bad idea! As always the "rule" is not to blindly go implementing the advice and start changing settings or removing indexes for example and look further into the recommendations and use your own measured judgement.