Phil Factor

17 July 2020

• SQL Multi Script
• Database Performance Monitoring

Gathering SQL Server Performance Counters for Multiple Servers

Collecting performance counters for all databases on a server

When SQL Multi Script collects results from multiple servers, it does it intelligently, by sharing columns that are identical and providing columns with NULL values where it can’t. It always provides columns for the basic dimensions of ‘server’ and ‘database’. You can opt to hide them in the built-in result pane.

The way that SQL Multi Script handles results that are similar but not identical is more easily explained by demonstration than words.

We’ll start with code that displays the performance counters for all the databases on a server. These performance counters are to be found in sys.dm_os_performance_counters. This DMV has the fifty most useful performance counters for each database. If we are going to use SQL Multi Script to display this information, we need to execute it just once for each server. The most obvious way of doing this is to use master on each server. The code to get this pivot table is as follows:

We can test this query out on our favourite dev server, with the following results:

Collecting counters from multiple servers

The next thing to do is to create a list of servers in SQL Multi Script. You use a list like that for any code that operates at server level.

Here, I’ve added three servers to a server distribution list called JustTheServers, and now I am ready to execute any query I want on those servers. I then select our query to list the performance counters for all the databases and execute the query by hitting ‘Execute Now’ (F5).

Following is the single, combined resultset. Each row of results relates to a database on a server, and you will see NULLs where the database in the column doesn’t appear in the server in the row. Beware, though! SQL Multi Script does a case-sensitive comparison to decide whether the database names are the same, so you have to provide a value that is cased consistently It is easiest just to use all lowercase or all uppercase.

I’ve opted out of ‘Include Database Name in Results’ but this will only exclude these columns in the display, not the file saved from the result.

All these databases are there on Philf07, but in the screenshot below, you’ll see that the same isn’t true of secondworld or redgate_sqldatacatalog. Instead, there are NULLs. Basically, all columns from the query results are represented in the final result. They have values in them if values were returned for that database, otherwise there are NULLs.

Reading the results using PowerShell

If you were going to save this, you’d probably want to add a record that gives the date or name the file with the date. This file is easily read into any office application, or PowerShell, and digested as required. Here is a simple example of its use in PowerShell.

Here’s the result:

In the same way, you can get the totals for all performance counters on each server:

…or compare counter values for two databases on a server:

Summary

SQL Multi Select is just a tool for executing as many SQL Query scripts as you require on a list of databases, as fast as possible, and aggregating the results. It has value for doing those many ad-hoc or one-off monitoring tasks that aren’t worth automating, even when dealing with results that are pivot tables rather than relational tables. SQL Multi Script encourages the collecting instinct: it isn’t particularly useful by itself but requires a library of useful routines to run in it.

What sort of query you’d consider useful to execute on a list of databases depends on your role. It could be as simple as reporting on the last backup or doing ‘attack surface’ checks of encryption settings, keys, system databases, authentication settings, and service accounts.