Query slows down if a unused join is removed

Viewed 555

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?

This query takes around 15 seconds:

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

This query takes around 4 seconds:

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

The only difference between the two queries, is the LEFT JOIN Dimensions.Nace AS N ON C.NACE_Id = N.NACE_Id. However nothing from this table is used in the select statement.

Facts.Financials has ~60 mio. rows, Dimensions.Customer ~17 mio. rows, Dimensions.Organization ~25.000, Dimensions.Nace ~1.000 rows.

Customer_Id = bigint
CRU_Id = bigint
Nace_id = varchar(4)

I have following indecies on the tables:

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)

This is the execution plan for the slow query (15 sec.)

Slow execution plan Slow execution plan XML: Slow execution plan

This is the fast execution plan (4 sec.)

Fast execution plan Fast execution plan XML: Fast execution plan

Can anyone point me in the right direction of what I am missing? Do I have a wrong index or how can this happen?

I am running SQL Server 2014

3 Answers
Related