title: Why am I getting a different actual execution plan between my two servers?
body: <p>I have a SQL Server query that runs on both production and development environments. The EXACT same query.</p>
  
  <pre><code>SELECT DISTINCT 
      [Record_Transformation_ACCRUALS],
      [Record_Transformation_FA:AMORTIZATION],   
      [Record_Transformation_BONUS:AMORTIZATION],
      [Record_Transformation_CPH:BYLABOUR],
      [Record_Transformation_CPH:BYTARGETHOURS],
      [Record_Transformation_OVERHEAD:CULTURE],
      [Record_Transformation_DEDICATED COSTCENTER],
      [Record_Transformation_PUSHDOWN:EXPENSE],
      [Record_Transformation_OVERHEAD:FACILITIES],
      [Record_Transformation_OVERHEAD:GENOME],
      [Record_Transformation_TAXES:MANAGEMENT],
      [Record_Transformation_TAXES:MARKETING],
      [Record_Transformation_OVERHEAD:OFFICETECH],
      [Record_Transformation_EXPENSE:PASSTHROUGH],
      [Record_Transformation_OVERHEAD:PEOPLEPRACTICES],
      [Record_Transformation_OVERHEAD:RECRUITING],
      [Record_Transformation_TAXES:SALES],
      [Record_Transformation_Static Transfer],
      [Record_Label] 
  FROM
      Warehouse_20181204 
  WHERE 
      Is_Target_Employee = 1 OR Is_Source_Employee = 1
  </code></pre>
  
  <p>We've compared the create scripts for both of these tables and they are identical (aside from the name of the table in question).</p>
  
  <p>We've also verified they are both using an clustered columnstore index.</p>
  
  <p>On development this query takes less than a second. On prod it takes about one minute. We thought at first maybe the size of the data is the problem, however the difference is minimal (a few hundred thousand rows).</p>
  
  <p>We then checked the actual execution plans for both. On dev the actual execution plan is:</p>
  
  <p><a href="https://i.stack.imgur.com/0TR6c.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0TR6c.png" alt="Dev execution plan"></a></p>
  
  <p>On prod the actual execution plan is very different though:</p>
  
  <p><a href="https://i.stack.imgur.com/vu7vM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vu7vM.png" alt="Prod execution plan"></a></p>
  
  <p>We find ourselves stumped as to why this is. We have verified that the versions of SQL Server are identical:</p>
  
  <pre><code>Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64)   
  Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 &lt;X64&gt; (Build 14393:) (Hypervisor)
  </code></pre>
  
  <p>My question is two-fold:</p>
  
  <ol>
  <li>How can we identify why the execution plans are so different between prod and dev?</li>
  <li>How can we get the prod environment to run as fast as dev given a similar dataset?</li>
  </ol>
  
  <p>EDIT:</p>
  
  <p>Some additional requested details:</p>
  
  <ul>
  <li>Both servers have 8G memory, both running with over 1G free</li>
  <li>Both servers have 2 processors</li>
  <li>hardware is as identical as you get - both the same size aws instances</li>
  <li>We've verified the sql of both tables &amp; clustered columnstore indexes are identical</li>
  </ul>
  
  <p>Hopefully these will help with all the other details for the current SQL plans:</p>
  
  <p>DEV sql plan: <a href="https://gist.github.com/klick-barakgall/17a7ce926777a3257f7eecb32859458e" rel="nofollow noreferrer">https://gist.github.com/klick-barakgall/17a7ce926777a3257f7eecb32859458e</a></p>
  
  <p>PROD sql plan: <a href="https://gist.github.com/klick-barakgall/76eabf1008f5bfb0c51259c2ba3f509d" rel="nofollow noreferrer">https://gist.github.com/klick-barakgall/76eabf1008f5bfb0c51259c2ba3f509d</a></p>
  
  <p>Adding links to Paste the plan for those interested in digging into the execution.</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=rkfQew41E" rel="nofollow noreferrer">DEV</a></p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=SkMxlP41N" rel="nofollow noreferrer">PROD</a></p>