title: Query slows down if a unused join is removed
body: <p>I have a run into a "problem" which I cannot figure out how to solve. I have a query that runs in about 15 seconds, when I then add a join (which is not used in the select), the query actually speeds up to about 4 seconds, even thou nothing from the additional join is selected. I suppose SQL Server chooses a different execution plan which is faster - but how do I force it to choose the fastest query plan each time?</p>
  
  <p>This query takes around 15 seconds:</p>
  
  <pre><code>SELECT
      O.Lvl1_Business_Area_Cd
     ,O.Lvl1_Business_Area_Nm
     ,O.Lvl2_Division_Cd
     ,O.Lvl2_Division_Nm
     ,SUM(F.Economic_Capital) AS Economic_Capital
  FROM
      Facts.Financials AS F
      LEFT JOIN Dimensions.Customer AS C ON F.Customer_Id = C.Customer_Id
      LEFT JOIN Dimensions.Organization AS O ON C.CRU_Id = O.CRU_Id
  WHERE 
      F.Year_Month_Id = 201706
      AND Lvl1_Business_Area_Cd = 6008000
  GROUP BY
      O.Lvl1_Business_Area_Cd
     ,O.Lvl1_Business_Area_Nm
     ,O.Lvl2_Division_Cd
     ,O.Lvl2_Division_Nm
  </code></pre>
  
  <p>This query takes around 4 seconds:</p>
  
  <pre><code>SELECT
      O.Lvl1_Business_Area_Cd
     ,O.Lvl1_Business_Area_Nm
     ,O.Lvl2_Division_Cd
     ,O.Lvl2_Division_Nm
     ,SUM(F.Economic_Capital) AS Economic_Capital
  FROM
      Facts.Financials AS F
      LEFT JOIN Dimensions.Customer AS C ON F.Customer_Id = C.Customer_Id
      LEFT JOIN Dimensions.Organization AS O ON C.CRU_Id = O.CRU_Id
      LEFT JOIN Dimensions.Nace AS N ON C.NACE_Id = N.NACE_Id
  WHERE 
      F.Year_Month_Id = 201706
      AND Lvl1_Business_Area_Cd = 6008000
  GROUP BY
      O.Lvl1_Business_Area_Cd
     ,O.Lvl1_Business_Area_Nm
     ,O.Lvl2_Division_Cd
     ,O.Lvl2_Division_Nm
  </code></pre>
  
  <p>The only difference between the two queries, is the <code>LEFT JOIN Dimensions.Nace AS N ON C.NACE_Id = N.NACE_Id</code>. However nothing from this table is used in the select statement.</p>
  
  <p>Facts.Financials has ~60 mio. rows, Dimensions.Customer ~17 mio. rows, Dimensions.Organization ~25.000, Dimensions.Nace ~1.000 rows.</p>
  
  <pre><code>Customer_Id = bigint
  CRU_Id = bigint
  Nace_id = varchar(4)
  </code></pre>
  
  <p>I have following indecies on the tables:</p>
  
  <pre><code>Facts.Financials: Clustered Index (YearMonth, Customer_Id), Non-Clustered (Customer_Id), Non-clustered columnstore index (Economic_Capital)
  Dimensions.Customer: Clustered (Customer_Id, CRU_Id), Non-clustered (CRU_Id), Non-clustered (Nace_Id)
  Dimensions.Organization: Clustered (CRU_Id), Non-clustered (Lvl1_Cd, Lvl2_Cd, Lvl3_Cd) Include (Lvl1_Nm, Lvl2_Nm, Lvl3_Nm, Lvl4_Cd, Lvl4_Nm, CRU_Id, CRU_Name)
  Dimensions.Nace: Clustered (Nace_Id)
  </code></pre>
  
  <p>This is the execution plan for the slow query (15 sec.)</p>
  
  <p><a href="https://i.stack.imgur.com/hk1cu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hk1cu.png" alt="Slow execution plan"></a>
  Slow execution plan XML: <a href="https://www.brentozar.com/pastetheplan/?id=B1WtoQLDW" rel="nofollow noreferrer">Slow execution plan</a></p>
  
  <p>This is the fast execution plan (4 sec.)</p>
  
  <p><a href="https://i.stack.imgur.com/IYPlI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IYPlI.png" alt="Fast execution plan"></a>
  Fast execution plan XML: <a href="https://www.brentozar.com/pastetheplan/?id=HkZMimIvb" rel="nofollow noreferrer">Fast execution plan</a></p>
  
  <p>Can anyone point me in the right direction of what I am missing? Do I have a wrong index or how can this happen?</p>
  
  <p>I am running SQL Server 2014</p>