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.