As DBA's we often do repetative tasks when it comes to managing our servers. Often we run a script on one server, and then change the connection to run the same script again, and repeat this until we have looked at all of our servers. SQL 2008 and Policy Based Management will help to reduce the redundant tasks that we as DBA's do, but there will always be those tasks that don't exactly fit into Policy Based Management. For these, there is another feature that should help simplify your life as a DBA, Multiple Server Queries.

I happened upon these by accident, and they aren't documented well in the Books Online. In fact the only reference I could find for inclusion in this article was a blog on the SQL Server Managebility Team blogsite. However, actually performing multi-server queries is very simple, and if you have already done Policy Based Management, then you probably already meet the only requirement, that being that you have at least one registered server group.

To create a registered Server Group in SSMS is really simple, and only takes a few seconds and is identical to SSMS 2005. First open the Registered Servers Toolbar by pressing Ctrl+Alt+G, or selecting it from the View menu. Then open the Database Engine. By right clicking on Local Server Groups, you can create a new Group. Then add registered Servers to the Group. To start a Multiple Server Query, right click on the group folder that you want to run the query against, and then select New Query.

When you do this, some specific environmental changes will occur. First the Database Dropdown box will only contain the names of databases that are common to all of the Servers in the group you selected.

and the status bar will turn pink to show you that this is a Multiple Server Query Window:

You can now execute any query that is valid to run on all of the servers in the group. Some really good examples of queries that this would be good with exist in the Troubleshooting Performance in SQL 2005 Whitepaper from Microsoft. To find out what the most inefficient query plans are on all of the servers in a group you can issue the following command for example.

select top 2
 highest_cpu_queries.total_worker_time,
 q.[text]
from 
 (select top 50 
 qs.plan_handle, 
 qs.total_worker_time
 from 
 sys.dm_exec_query_stats qs
 order by qs.total_worker_time desc) as highest_cpu_queries
 cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

This particular command requires that the servers be SQL 2005 or SQL 2008 since it is utilizing the dynamic managment views, but you can also query SQL 2000 servers as a part of a group if the query is coded to run against SQL 2000. If you were to run the above query against a SQL 2000 Server, you would get an error like the following:

SQL2000TEST(OUTBACKNT\jkehayias): Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'apply'.

SQL2005TEST(OUTBACKNT\jkehayias): (2 row(s) affected)

SQL2008TEST(OUTBACKNT\jkehayias): (2 row(s) affected)

To demonstrate the cross platform abilities, you can issue the following query:

SELECT updatedate
FROM syslogins
WHERE name = 'sa'

This will provide the last date that the sa user was updated, in the case of this environment, updates to the sa user account only occur for password changes, so this represents the last date that the sa user account password was changed. When you get the results back, they are aggregated, and will include an additional column containing the Servername in it.

You can turn this on or off as an option in Management Studio in the Options dialog:

Multiple Server Queries bring yet another powerful new way of managing tasks on multiple servers and with multiple versions of SQL Server running. The number and kinds of queries that can be run against multiple servers are really unlimited.