SQL Server 2014: Linked Server + Synonym + Distributed Query = slow performance
<p>In a pair of SQL Server 2014 environments, we are having some extreme performance problems related to linked servers and synonyms. It appears that there is some <a href="https://msdn.microsoft.com/en-us/library/ms188721.aspx" rel="nofollow">distributed query</a> logic that SQL Server uses to determine alternative execution paths that lead to significantly different speeds. I suspect this is because sometimes it decides to execute the query purely remotely and sometimes it decides to select all rows from the remote server and apply the where clause locally, which in this case is 8+ million rows and causing significant delays.</p>

<p>So my question is, is there any way to FORCE SQL Server to always execute distributed queries remotely, and avoid these performance problems?  We are in a situation where we NEED to use synonyms or some other non-server-address-specific code in our stored procedures, so 4-part addressing is not a viable permanent option.</p>

<p><strong>Problem cases</strong> (5+ minutes execution time)</p>

<pre><code>-- Returns 1 Row
SELECT  S.* 
FROM    [SYNONYM] S
WHERE   S.ID = 'ABC123'
</code></pre>

<p><strong>Successful Cases</strong> (1+/- second execution time)</p>

<pre><code>-- Returns 1 Row
SELECT  T.* 
FROM    [SERVER].[DATABASE].dbo.[TABLE] T
WHERE   T.ID = 'ABC123'

-- Returns 1 Row
SELECT  TOP 1
        S.*
FROM    [SYNONYM] S
WHERE   S.ID = 'ABC123'
</code></pre>

<p>Referencing <a href="http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/" rel="nofollow">The top 3 linked server performance killers</a></p>

<ol>
<li>SQL Server 2014 allegedly fixed linked server statistic permissions</li>
<li>There are no join statements</li>
<li>There are no functions</li>
</ol>

<p>Referencing <a href="http://www.sql-server-performance.com/2007/linked-server/" rel="nofollow">Linked Servers</a> (and performance problems)</p>

<ol>
<li>OPENROWSET... not going to get into how terrible the syntax and security of dynamic sql is</li>
<li>OPENQUERY... see above regarding OPENROWSET</li>
<li>Collation is a possibility.  The collation on the remote server is non-standard and differs from the local server.  However that does not (readily) explain why some forms of the syntax work and some don't</li>
<li>Network speed is likely ruled out because both servers are on the same network in the same data center.  Also some forms of the query working are proof that it's a query-level problem not a network speed problem.</li>
<li>Extending the timeout doesn't fix the original performance problem</li>
<li>CURIOUSLY my tests have shown that including the TOP clause causes the OPPOSITE effect that this article claims.  In my tests the TOP clause causes performance to dramatically improve</li>
</ol>

<p>My attempts to confirm that the statistics are properly accessible have not gone well either:</p>

<pre><code>DBCC SHOW_STATISTICS('[SYNONYM]') WITH STAT_HEADER;
</code></pre>

<p>Yields:</p>

<blockquote>
  <p>Unable to process object ID ##### (object "[SYNONYM]")
  because it is a synonym. If the object referenced by the synonym is a
  table or view, retry the operation using the base object that the
  synonym references.</p>
</blockquote>

<p>And when I change that to the 4-part name:</p>

<pre><code>DBCC SHOW_STATISTICS('[SERVER].[DATABASE].[SCHEMA/DBO].[TABLE]') WITH STAT_HEADER;
</code></pre>

<p>It yields:</p>

<blockquote>
  <p>Unable to process object '[SERVER]' because it is
  a four-part name, which is not supported by any DBCC command.</p>
</blockquote>

<p>Any suggestions regarding synonyms and/or linked servers (SQL Server 2014) would be greatly appreciated</p>