title: SQL Server : Table Spool when adding ORDER BY
body: <p>I have a very simple query that I am trying to optimize. issues_notes is a simple table, while meta_users is a table on a remote database server that I am querying via a view.</p>
  
  <p>when I run the query without ordering it returns immediately, but when I add <code>ORDER BY date</code> it takes about 4 seconds to return. I had SQL Server show the execution plan, and it appears that the slowness is introduced in a <code>Table Spool</code> operation that happens only on the join. Is there a way to prevent this "optimization"?</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT 
      [issues_notes].[date], 
      [meta_users].[firstname], 
      [meta_users].[lastname], 
      [issues_notes].[note] 
  FROM 
      [issues_notes] 
      LEFT JOIN [issues_issue] 
          ON ([issues_notes].[issue_id] = [issues_issue].[id]) 
      LEFT OUTER JOIN [meta_users] 
          ON ([issues_notes].[author_id] = [meta_users].[userid]) 
  WHERE 
      ([issues_issue].[issue_hash] = '%s' ) 
  </code></pre>
  
  <p>Execution Plan without Order By:</p>
  
  <p><img src="https://i.stack.imgur.com/wuEOZ.png" alt="enter image description here"></p>
  
  <p>Execution Plan With Order By: </p>
  
  <p><img src="https://i.stack.imgur.com/ZcjhQ.png" alt="enter image description here"></p>