Naveen PK (7/23/2015)
My company has central database server and all other database servers subscribe to that central server to keep a local copy (nightly replicated). That way I dont need to use linked server queries to read variables from central db server. We are using 3 part queries (db.schema.tablename) on same server. Now managers want to have the central server keep server details, that means make all 3 part queries to become linked server queries.
I am opposing that but dont have alternatives. Pulling to local table does not work as there are too many tables. In short, managers want to execute everything from central db server by using serverIDs for other servers. Any suggestions to address this?
Any query, that involves context switching between multiple database instances and pulling data across the network wire, will perform significantly slower than a query that joins only to tables within it's own instance. Even with optimal indexing and expert usage of query hints, you will still be looking at a 25% runtime increase minimal. The network wire is a bottleneck, and there is no way to get around that, unless you co-locate all the instances on the same physical machine.
The best way to approach this would be to take one of your more complex stored procedures, retrofit it to use distributed joins, deploy it to production under a different name, and then compare the performance of the distributed join version versus the locally joined version using same parameters. If you can prove that distributed queries will significantly impact your queries in a bad way, then you will create F.U.D. in the mind of the whomever is advocating this idea and hopefully they'll forget the whole thing. https://en.wikipedia.org/wiki/Fear,_uncertainty_and_doubt
Or... maybe after experimenting with it you'll discover it's not a significant impact after all.
However, if management is hell bent on going down this path of distributed joins regardless of the impact, then there are ways to mitigate the issue. For example, you can use OPENQUERY to pull the remote recordset you need into a local temp table, and then join your local tables to the temp table.http://blogs.msdn.com/b/sqlsakthi/archive/2011/05/09/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server.aspx
"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."