title: SQL Server How to add a linked server to the same instance without performance impact
body: <p>in my company, we have several environments with MS SQL database servers (SQL 2008 R2, SQL 2014). For the sake of simplicity, let us consider just a TEST environment and a PROD environment and two sql servers in each. Let the servers be called <strong>srTest1</strong>, <strong>srTest2</strong>, <strong>srProd1</strong>, <strong>srProd2</strong> and each be running a default MS SQL Server instance. We work with multiple databases, say DataDb, ReportDb, DWHDb.</p>
  
  <p>We want to keep the same source code in T-SQL for both TEST and PROD, but the problem is the architecture or distribution of the above mentioned databases in each environment:</p>
  
  <p>TEST:</p>
  
  <ul>
  <li><strong>srTest1</strong> - DataDb</li>
  <li><strong>srTest2</strong> - DWHDb, ReportDb</li>
  </ul>
  
  <p>PROD:</p>
  
  <ul>
  <li><strong>srProd1</strong> - DataDb, ReportDb</li>
  <li><strong>srProd2</strong> - DWHDb</li>
  </ul>
  
  <p>Now, say, in ReportDb, we write stored procedures with many SELECTs referencing tables and other objects in DataDb and DWHDb. In order to have source code as universal as possible, we decided to create linked servers for each database on each db server in each environment and name them with respect to the database they're created for. Therefore, there'll be these linked servers:</p>
  
  <ul>
  <li>lnkDataDb, lnkReportDb and lnkDWHDb on <strong>srTest1</strong>,</li>
  <li>lnkDataDb, lnkReportDb and lnkDWHDb on <strong>srTest2</strong>,</li>
  <li>lnkDataDb, lnkReportDb and lnkDWHDb on <strong>srProd1</strong>,</li>
  <li>lnkDataDb, lnkReportDb and lnkDWHDb on <strong>srProd2</strong>.</li>
  </ul>
  
  <p>And we'll adjust the source in the stored procs accordingly. For instance:</p>
  
  <p>Instead of</p>
  
  <pre><code>SELECT * FROM DataDb.dbo.Contact
  </code></pre>
  
  <p>We'll write</p>
  
  <pre><code>SELECT * FROM lnkDataDb.DataDb.dbo.Contact
  </code></pre>
  
  <p>The example above is reasonable for a situation where the database from which you execute the query (ReportDb) lies on a different server than that with the referenced table (DataDb). Which is the case for the TEST environment. But not so in PROD. It is performance I'm here concerned about. The SQL Server will treat that SELECT as a "remote query" no matter whether, in fact, it is a reference to a local object or not.</p>
  
  <p><strong>Now, it comes the most important part:</strong></p>
  
  <p>If you check these 3 queries for their actual execution plans, you'll see an interesting thing:</p>
  
  <pre><code>(1) SELECT * FROM DataDb.dbo.Contact
  (2) SELECT * FROM srProd1.DataDb.dbo.Contact
  (3) SELECT * FROM lnkDataDb.DataDb.dbo.Contact
  </code></pre>
  
  <p>The first two (query #1 and #2) have the same execution plan (the fastest possible) even if you use the four-part name manner of referencing the table Contact in #2.
  The last query has a different plan (remote query, thus slower).</p>
  
  <p><strong>The question is:</strong></p>
  
  <p><em>Can you somehow create a linked server to self (the same sql server instance, the default instance actually) as an "alias" to the name of the host (<strong>srProd1</strong>) in order for the SQL server to be forced to understand it as local and not issue "remote execution" plans?</em></p>
  
  <p>Thanks a lot for any hints</p>
  
  <p>Pavel</p>