THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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).

 

clip_image002

 

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).

 

clip_image004

 

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

 

or

 

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

 

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

go

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 SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also 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