Different ways to sort multi-server SQL Server query results using Central Management Server

By: Svetlana Golovko   |   Updated: 2018-02-20   |   Comments (3)   |   Related: More > Central Management Servers

Problem

Central Management Server (CMS) is a SQL Server feature that allows you to manage multiple SQL Servers at the same time. You can run T-SQL scripts or execute Policy-Based Management policies on multiple servers. You can also group SQL Servers and run the scripts on a specific group of SQL Servers.

How else can CMS be used? How can we overcome some of the limitations when we run queries against CMS Server Groups?

Solution

In this tip we will show you a couple of CMS tricks. In our examples below we will show how you can sort the CMS query results for different scenarios. We will also provide a couple of examples of alternative uses of CMS.

Note, that the same techniques could be applied to SQL Server Management Studio (SSMS) Registered Servers.

Sorting a Query's Results in the CMS

One of the limitations of Multi-Server Queries is that results cannot really be sorted the same way as for a single server query. The results are sorted inside each server's query and then grouped by server:

Note, that the first column in our result is not sorted by the SQL Server name.

The entire result set is tricky to sort, but we will show you how to get sorted results in specific scenarios.

Note, that results in our examples below are sorted, but may not be always sorted as expected (depending on how fast/slow SQL Server is, etc.). See examples and explanations below.

Sorting the CMS Query Results by SQL Server Version

In this example we will sort our query results by SQL Server Version.

USE [master]
GO

DECLARE @delay VARCHAR(12)

SELECT  @delay = ISNULL('00:00:' + RIGHT('00' +
                 CAST(CAST(CAST(ISNULL(SERVERPROPERTY('ProductMajorVersion'),
                 REPLACE(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(200)), 2), '.', ''))
                 AS VARCHAR(2)) AS INT) - 8 AS VARCHAR(2)), 2) + '.' +
                 LEFT(CAST(ISNULL(SERVERPROPERTY('ProductBuild'), '000') AS VARCHAR(4))
                 , 3), '00:00:00.000')

/* -- or if you don't have in your environment SQL Server 2005 and lower (or other versions 
    -- that don't support "ProductBuild" Server Property) replace the code above with this:
SELECT  @delay = '00:00:' + RIGHT('00' + CAST(CAST(CAST(SERVERPROPERTY('ProductMajorVersion')
                 AS VARCHAR(2)) AS INT) - 8 AS VARCHAR(2)), 2) + '.' + 
                 LEFT(CAST(SERVERPROPERTY('ProductBuild') AS VARCHAR(4)), 3)
*/

WAITFOR DELAY @delay

SELECT COUNT([name]) number_of_dbs, 
   SERVERPROPERTY('ProductVersion') product_version, 
   @@VERSION full_version 
FROM sys.databases
GO

We use SQL Server Major Version and subtract 8 from it to generate seconds for a delay in our query.

The WAITFOR statement that we use in the query above accepts arguments that have a datetime data type.

Fractional precision of the datetime data type has 3 digits (for example, "2018-01-01 23:59:59.997"). Note, that SQL Server's Build number that we use in our query has 4 characters (12.0.5553.0), but in our query we only use the 3 left characters ("555"). There is a chance that you are going to have, for example, version 12.0.5553.0 and 12.0.5557.0. So, you should be aware that the query above doesn't guarantee sorted results for the cases like this.

For example, in our demo above we have SQL2014_TEMP and SQL2014_TEMP_2 SQL Servers with the following versions and delay calculated based on the version:

Here is another example. Let's assume we have the following servers (the versions are made up for this example):

Because the difference in the delay is only 1 millisecond, the results might be sorted differently each time you run the query. You may get "SQL2014_TEMP_3" above "SQL2014_TEMP_4" one time and another way the next time.

There are a couple of reasons for that:

1. The servers may have a different response time depending on a speed of a network connection, how busy the server is etc.
2. If you check Microsoft's documentation for the datetime data type you will notice that milliseconds are rounded to increments of .000, .003, or .007 seconds. Here is a query that demonstrates this rounding:

USE [master]
GO

SELECT '00:00:04.556' AS char_delay,
CAST('00:00:04.556' AS DATETIME) AS delay_datetime, 
DATEADD(MILLISECOND , 1, CAST('00:00:04.556' AS DATETIME)) AS delay_time_added
UNION
SELECT '00:00:04.557',
CAST('00:00:04.557' AS DATETIME), 
DATEADD(MILLISECOND , 1, CAST('00:00:04.557' AS DATETIME))
UNION
SELECT '00:00:04.558',
CAST('00:00:04.558' AS DATETIME), 
DATEADD(MILLISECOND , 1, CAST('00:00:04.558' AS DATETIME))
UNION
SELECT '00:00:04.559',
CAST('00:00:04.559' AS DATETIME), 
DATEADD(MILLISECOND , 1, CAST('00:00:04.559' AS DATETIME))
UNION
SELECT '00:00:04.560',
CAST('00:00:04.560' AS DATETIME), 
DATEADD(MILLISECOND , 1, CAST('00:00:04.560' AS DATETIME))
UNION
SELECT '00:00:04.561',
CAST('00:00:04.561' AS DATETIME), 
DATEADD(MILLISECOND , 1, CAST('00:00:04.561' AS DATETIME))
GO 

Note, that the delay value we generate will be rounded further in some cases and this may affect the sorting. Sorting only by SQL Server Major Version though should work fine (unless server's response is really slow, slower than 1 sec).

Please note that we don't have in our example SQL Server 2008. We assume that an environment has either SQL Servers 2008 or SQL Servers 2008 R2. If your environment has both - SQL Server 2008 and SQL Server 2008 R2 you may need to tweak the initial query.

Sorting the CMS Query Results by an Environment (Last 3 Characters of the SQL Server Name)

If you use SQL Server naming conventions to include your SQL Server Environment in some kind of suffix in server's name then probably it's a good idea to have these suffixes in increments that could be sorted.

In our example below we have the following suffixes for the naming conventions:

• "5nn" - Production Environment
• "6nn" - Test Environment
• "7nn" - Development Environment.

USE [master]
GO
DECLARE  @delay VARCHAR(12), @server_name SYSNAME 

SELECT @server_name = CASE WHEN @@SERVERNAME LIKE '%\%' 
            THEN LEFT(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME) -1) 
            ELSE @@SERVERNAME END

SELECT @delay = '00:00:0' + LEFT(RIGHT(@server_name, 3), 1) + '.' 
         + CAST(CAST(RIGHT(@server_name, 2 ) AS SMALLINT)*2 AS VARCHAR(12)) + '00'

WAITFOR DELAY @delay

SELECT @delay AS delay_set, COUNT([name]) number_of_dbs
 FROM sys.databases
GO

Depending on your naming conventions you may need to modify the query above.

Note, that for another execution we have a slightly different result as DEVSQL701\INST1 was responding slower this time and the difference in delay is only 10 milliseconds:

Sorting the CMS Query Results by the Environment (First 3 Characters of the SQL Server Name)

This is similar to the previous example, but SQL Servers in this example have a prefix that identifies the environment:

• "DEV" for the Development Environment
• "TST" for the Test Environment
• "PRD" for the Production.

We will use the first 3 characters for the sorting in our script below:

USE [master]
GO 
DECLARE  @delay VARCHAR(12)

SELECT @delay = '00:00:0' + CASEWHEN LEFT(@@servername,3 ) = 'DEV' THEN '1.000'
               WHEN LEFT(@@servername,3 ) = 'TST' THEN '2.000'
               WHEN LEFT(@@servername,3 ) = 'PRD' THEN '3.000' END

WAITFOR DELAY @delay

SELECT COUNT([name]) number_of_dbs FROM sys.databases
GO	

Sorting the CMS Query Results by SQL Server Started Date/Time

In this example we would like to have query results ordered by SQL Server last start time.

We will use sys.dm_os_sys_info Dynamic Management View (DMV) to get the start date and time.

Please note that this works only on currently supported versions of SQL Server (SQL Server 2008 and higher versions). The sys.dm_os_sys_info DMV in SQL Server 2005 did not have the sqlserver_start_time column that we use in our query. Also, we use the TIME data type that was introduced with other new data types in SQL Server 2008.

Here is a query we used to get steps for generating the "WAITFOR" delay:

USE [master]
GO
 
DECLARE  @delay VARCHAR(12)

SELECT  @delay = CAST(DATEADD(ss, DATEDIFF(dd, sqlserver_start_time, GETDATE()),
                 CAST('00:00:00.' + 
                 LEFT(RIGHT('00' + CAST(24 - DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) +
                 CAST(60 - DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) AS TIME)) 
                 AS VARCHAR(12))
FROM sys.dm_os_sys_info   

WAITFOR DELAY @delay

SELECT sqlserver_start_time,
   CAST(sqlserver_start_time AS DATE) AS date_started ,
   DATEDIFF(dd, sqlserver_start_time, GETDATE()) AS N_days_before,
   CAST(sqlserver_start_time AS TIME) AS time_started,
   DATEPART(hh, sqlserver_start_time) AS hour_started,
   24-DATEPART(hh, sqlserver_start_time) AS hour_started_desc,
   DATEADD(ss,  DATEDIFF(dd, sqlserver_start_time, GETDATE()), 
      CAST('00:00:00' AS TIME)) AS generate_waitfor_days_only,
      '.' + LEFT(RIGHT('00' + CAST(24 - DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) + 
      CAST(60 - DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) 
      AS generate_milliseconds,
   CAST(DATEADD(ss, DATEDIFF(dd, sqlserver_start_time, GETDATE()), CAST('00:00:00.' + 
      LEFT(RIGHT('00' + CAST(24 - DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) +
      CAST(60 - DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) AS TIME)) 
      AS VARCHAR(12)) AS generate_waitfor_desc,
   CAST(DATEADD(ss, 31 - DATEDIFF(dd, sqlserver_start_time, GETDATE()),CAST('00:00:00.' + 
      LEFT(RIGHT('00' + CAST( DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) +
      CAST(DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) AS TIME)) 
      AS VARCHAR(12))  generate_waitfor_asc
FROM sys.dm_os_sys_info
GO

Seconds are "N_days_before" column for descending order or “31 - DATEDIFF(dd, sqlserver_start_time, GETDATE())” for ascending order.

Milliseconds generated from an hour started (or “24 - DATEPART(hh, sqlserver_start_time)” ) and tens of minutes.

Here is the final query to sort the results in descending order by SQL Server start time:

USE [master]
GO 
DECLARE  @delay VARCHAR(12)

SELECT @delay = CAST(DATEADD(ss, DATEDIFF(dd, sqlserver_start_time, GETDATE()),
                CAST('00:00:00.' + 
                LEFT(RIGHT('00' + CAST(24 - DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) +
                CAST(60 - DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) AS TIME)) 
                AS VARCHAR(12)) 
FROM sys.dm_os_sys_info   

WAITFOR DELAY @delay

SELECT @delay delay_gen, CAST(@delay AS DATETIME) AS delay_datetime, sqlserver_start_time 
FROM sys.dm_os_sys_info
GO	

Here is the final query to sort the results in ascending order by SQL Server start time:

USE [master]
GO 
DECLARE @delay VARCHAR(12)

SELECT @delay = CAST(DATEADD(ss, 31 - DATEDIFF(dd, sqlserver_start_time, GETDATE()),
                CAST('00:00:00.' + LEFT(RIGHT('00' + 
                CAST( DATEPART(hh, sqlserver_start_time) AS VARCHAR(2)), 2) +
                CAST(DATEPART(mi, sqlserver_start_time) AS VARCHAR(2)), 3) AS TIME)) 
                AS VARCHAR(12)) 
FROM sys.dm_os_sys_info  
 
WAITFOR DELAY @delay

SELECT @delay delay_gen, CAST(@delay AS DATETIME) AS delay_datetime, sqlserver_start_time 
FROM sys.dm_os_sys_info
GO	

The result might not be sorted perfectly by time (depending on a server response as we noted above), but this should work fine for sorting results just by date. If your servers restarted on the same day then you can slightly modify the queries above and sort by time only.

If you need to run a query with sorted results for SQL Server 2005 or earlier you can use another method to find SQL Server start time from this article.

Other Uses of the CMS or Registered Servers

Using Registered Servers to Mask SQL Servers Names in the CMS

Sometimes it's hard to find a perfect set of SQL Servers for your demos. Especially, if you don't want to expose real SQL Servers names. You can use the CMS Registered Server name as server's alias for these purposes.

Here is an example of a SQL Server registered in CMS under a different name:

Note, that the actual SQL Server name is REALSQL1 and the registered server name is DEMOSRV1.

Below is an example of the CMS Server Group with multiple SQL Servers registered under different names:

Double click on DEMOSRV1 server or right click and select "Object Explorer":

Note, that when you connect to one of these "masked" SQL Servers from the CMS the SQL Server's name displayed in the SSMS will match the registered name in the CMS server's name. But, when you run the following query for the "Demo Servers" group you are still going to see the real SQL Server names:

USE [master]
GO

SELECT @@SERVERNAME AS [@@servername]
GO	

Existing Tip to Run T-SQL Scripts on Different Databases

In this tip you can find out how to run a query against multiple databases (not necessarily servers) with the same database structure using CMS or Registered Servers.

Use the CSM System View and PowerShell to Sort Results by the Registered SQL Server Name

As you could see the examples there are still some limitations.

Another way to run multi-server queries is with PowerShell that can use a sorted SQL Servers list from CMS sysmanagement_shared_registered_servers view as a starting point:

$CentralManagementServer = "SQLCMS"

# get all registered SQL Server names from CMS
$Result =  Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database msdb -Query "SELECT DISTINCT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers ORDER BY server_name" -ErrorAction Stop

foreach($item in $Result)
   {$Inst=$item.server_name
   Invoke-Sqlcmd -ServerInstance  ${Inst} -Database master -Query "SELECT @@SERVERNAME AS srv_name, COUNT([name]) AS num_of_dbs FROM sys.sysdatabases" -ErrorAction SilentlyContinue 
  }
 
			

Conclusion

We have provided a couple of examples to getting sorted results using CMS queries.

Note, that a query with a WAITFOR for a high number of servers may run quite long. Also, the results might not be sorted in every case (see the explanations of limitations above).

There are limitations, but this technique is good enough to produce quick results that are easier to read for multi-server queries and can help you with some of the DBAs checks. You can use it as a starting point for other multi-server queries that need to be sorted.

Next Steps

• Read all Central Management Servers tips.
• Read more tips about SQL Server Management Studio.
• Read this tip about Customizing the Status Bar for each SQL Server SSMS Connection.
• Find out how to create a CMS Server and a Server Group here.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips