Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Multiple Server Queries with SSMS 2008

By Jonathan Kehayias,

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.

Total article views: 5526 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Manage Multiple SQL Server Installations and Databases with OSQL

This article explains how to use a simple batch file and script file to manage multiple databases on...

BLOG

Executing scripts on multiple servers the easy way

Central Management Server and server groups provide a convenient way to manage multiple servers and ...

FORUM

multiple server Query without linked server

multiple server Query without linked server

FORUM

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

FORUM

Manage Multiple SQL Server's from Single User Interface

There is any native or third part tools to manage multiple sql server's instance's

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones