THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

Are You Using Multiserver Queries?


If you administer multiple instances of SQL Server and you have not used 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 pink and that 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 this and two other configuration options 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




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 <db_name>


select * from cpu_history

where cpu_usage > 80

Published Wednesday, July 29, 2009 10:03 PM by Ben Nevarez
Filed under:
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin's blog is at and can be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement