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 XML: Slow execution plan
This is the fast execution plan (4 sec.)
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