Benjamin Nevarez Rotating Header Image

Are You Running Multiserver Queries?

If you administer multiple instances of SQL Server and you have not tried multiserver queries, you are going to love this new SQL Server 2008 feature.

Multiserver queries allows you to run T-SQL statements against multiple SQL Server instances at the same time, returning the results in the same or separate result sets. In order to run multiserver queries you need to create server groups using the Registered Servers window, and register servers to become members of these groups. This can be accomplished by selecting either Local Server Groups or Central Management Servers.

Once you have created your groups and registered your servers you can right-click on any of the configured groups and select ‘New Query’. You can also create a query to be executed against all your groups and servers by selecting Local Server Groups or your Central Management Server. To verify that you are about to run a query against multiple servers make sure that the Status bar of the Query Editor is color pink and shows the name of the server group (or DatabaseEngineServerGroup if you have selected all the groups).


The registered servers could be any combination of SQL Server 2008 or SQL Server 2005 instances. I even successfully tested it with SQL Server 2000 instances.

Once in the Query Editor there is an endless list of interesting things you can do. A few examples follow.

Do you need a report of all the databases in all your servers? Just run a simple

SELECT * FROM sys.databases

Notice how the results sets for each instance (shown in the next picture) by default are concatenated and the final result set includes an additional ‘Server Name’ column indicating which instance these records are coming from. You can change the result set configuration by selecting Tools, Options, Query Results, SQL Server and Multiserver Results in Management Studio.


How about getting a report of when each of your SQL Server instances was started? Try

SELECT start_time FROM sys.dm_exec_requests WHERE session_id = 1


SELECT login_time FROM sysprocesses WHERE spid = 1

or if you have SQL Server 2008 instances only

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Or maybe a report of the disk space available on every drive on every server? Just run

EXEC xp_fixeddrives

Of course you can also query user databases, but the query needs to be valid on all the instances. For example, if you are like me and have a performance data collection database on each SQL Server instance, you can run a query to obtain some specific performance counters that you collect periodically, like in

USE <database_name>


SELECT * FROM cpu_history

WHERE cpu_usage > 80

About the author

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of three books, “High Performance SQL Server”, “SQL Server 2014 Query Tuning & Optimization” and “Inside the SQL Server Query Optimizer” and has also coauthored other books including “SQL Server 2012 Internals”. Benjamin has also been a speaker at many SQL Server conferences and events around the world including the PASS Summit, SQL Server Connections and SQLBits. His blog can be found at and he can also be reached on twitter at @BenjaminNevarez.


  1. […] This post was mentioned on Twitter by Benjamin Nevarez, Ben Nevarez. Ben Nevarez said: New blog post: Are You Running Multiserver Queries? […]

  2. This is such a great resource that you are providing and you give it away for free. I enjoy seeing websites that understand the value of providing a prime resource for free. I truly loved reading your post. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *