Multi-Server Queries – Underappreciated features of Microsoft SQL Server

This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on

After Activity Monitor, this is one of the other features that I was surprised to see in the list of underappreciated features. This feature has been a dream come true for anybody who manages more than one server at a time. The need to be able to query a bunch of servers in one go and the fact that this feature has been around since Microsoft SQL Server 2005 had made me think that this should have been pretty common in usage by now.

Anyway, let’s quickly see how to use this wonderful feature that Microsoft SQL Server Management Studio provides.

Running Multi-Server Queries

One of our esteemed SQL Server MVPs is Mr. Pinal Dave (blog). He has, on June 14, 2009 written a very short, concise and precise blog post on how to run Multi-Server Queries. You can find that particular post here.

Keep in mind that the most important usability change that you need to make in order to use this feature is that all servers participating in a multi-server query must be registered servers.

What Pinal’s blog post does not cover are some navigational aids and query execution options. I will attempt to cover them here.

Changes to the Query Editor window

The Query editor window in a multi-server query is similar to the regular single-instance query editor, but there is one minor difference. The SSMS query status bar informs us about the number of servers that a particular query will run against. On my test environment, let’s say I have two registered instances. When I attempt to run a multi-server query, this is what I see:


Query Execution Options:

In Pinal’s post, you would have already seen how the results would appear in your results pane by default. Results from both instances are available in a single grid, and the SQL Server instance name helps us identify which server contributed to the result. However, we can change the way these results are displayed. You can navigate out to Tools->Options->Query Results to see a window similar to the following. Please note that the values in the screenshot below are default values.


  • Add login name to the results – Setting this to TRUE adds a column containing the login name which produces the result row
  • Add server name to the results – Adds a column containing the server instance name for a particular result row when set to TRUE
  • Merge Results – Shows results from different servers in the same grid when set to TRUE

Allow me to change the SQL Server registration of one of the servers to use SQL Server authentication. Also, I will set the “Merge Results” property to FALSE, and the “Add login name to the results” to TRUE. Below will be the output when I run the following query (borrowed from the example in Pinal’s blog):

SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion


Important points to note


As of now, it is not possible to select a subset of servers from within a server group (you can select a single instance, but not a subset). The recommendation would be to have a separate group if such a selection needs to be made often.

This is not a programmability enhancement!

Also, this feature is nothing more than an ability to execute the same query against multiple registered servers and obtain their results within a single window. It is not a programmability enhancement, and hence the following should not be expected:

  1. Cross-server support for JOIN or ORDER BY clauses
  2. Transaction support across multiple servers

Security considerations

Quoting MSDN:

Because the connections maintained by a Central Management Server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.

You may also want to read:

This post completes the first part of my series on “Underappreciated features of Microsoft SQL Server”. Do share your feedback on how the journey has been till now, and what else would you like me to write on.

We will resume our journey to explore T-SQL Enhancements in the next couple of weeks. In the meanwhile, I will share with you some other interesting incidents that happened last month.

Until we meet next time,

Be courteous. Drive responsibly.


9 thoughts on “Multi-Server Queries – Underappreciated features of Microsoft SQL Server

  1. Jacob Sebastian

    I am using Multi-Server queries quite often and found it very helpful. Thanks for bringing this topic up.


  2. khemmerl

    You made the comment “you can select a single instance”. Can you explain how you would do that from a CMS query window?



  3. Nakul Vachhrajani

    @khemmerl: In the screenshot referenced above, you can select the entire “Local Server Group” as I have done in the example or select the individually registered server instances. What you cannot do is choose a subset of instances, for example, select 2 instances out of 5 registered instances.


  4. khemmerl

    Can you post the code showing how to select against individually registered server instances? I’m not able to get that to work.




  5. Nakul Vachhrajani


    There is no code as such. It’s about selecting individual instances from the UI.

    I have uploaded a [problem steps recorder][1] session output at [][2].

    Let me know if it helped.



  6. Nakul Vachhrajani

    @Ken: You are most welcome. Feel free to let me know in case you have any queries on any of my other posts.


  7. Nirav

    Before this i was working with linked server for monitoring of version details and backup details, your post is very help full to me to get this monitoring in one line code.

    Thanks Nakul



Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.