Distributed queries involve data that's stored on another server, be it on a server in the same room or on a machine a half a world away. Writing a distributed query is easy—so easy, in fact, that you might not even consider them any different from a run-of-the-mill local query—but there are many potential pitfalls waiting for the unsuspecting DBA to fall into. This isn't surprising given that little information about distributed queries is available online. There's even less information available on how to improve their performance.
To help fill this gap, I'll be taking an in-depth look into how SQL Server 2008 treats distributed queries, specifically focusing on how queries involving two or more instances of SQL Server are handled at runtime. Performance is key in this situation, as much of the need for distributed queries revolves around OLTP—solutions for reporting and analysis are much better handled with data warehousing.
The three distributed queries I'll be discussing are designed to be run against a database named SCRATCH that resides on a server named REMOTE. If you'd like to try these queries, I've written a script, CreateSCRATCHDatabase.sql, that creates the SCRATCH database. You can download this script as well as the code in the listings by clicking the Download the Code Here button near the top of the page. After CreateSCRATCHDatabase.sql runs (it'll take a few minutes to complete), place the SCRATCH database on a server named REMOTE. This can be easily mimicked by creating an alias named REMOTE on your SQL Server machine in SQL Server Configuration Manager and having that alias point back to your machine, as Figure 1 shows.
Having configured an alias in this way, you can then proceed to add REMOTE as a linked SQL Server on your machine (in this example, MYSERVER). While somewhat artificial, this setup is perfectly adequate for observing distributed query performance. In addition, some tasks such as profiling the server are simplified because all queries are being performed against a single instance.