Benjamin Nevarez Rotating Header Image

Multiserver Queries

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

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 the result set configuration 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

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>

GO

SELECT * FROM cpu_history

WHERE cpu_usage > 80