Forcing execution plan with local join hints I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.

In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:

List item


SORT(Distinct Sort)
Stram Aggregate(Aggregate)


Are these actions something I can choose, or are they dependent on join types/order choosed during the query?

Both plans are created by XML extracted from Query Store.

Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V 

Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4
I want to enforce the join types to be somewhat the same and will
  therefore use local joins hints also for these


Adding join hints should be a last resort. There should be ways to rewrite the query / add indexes to get a more consistent result. 

These plans are also estimated execution plans, in this case only you knows how well / bad the actual query will perform. 

If the issue is parameter sniffing, OPTION(RECOMPILE) would be the easiest solution. 

Is the LEFT JOIN only used for filtering? A NOT EXISTS might work better to filter earlier. 

Having said all that, with the limited information given, here are some possible, quick rewrites.

Rewrite #1 LEFT JOIN to NOT EXISTS

The OPTION(RECOMPILE)is added to get better estimates based on the parameters provided.

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
  FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full 
  WHERE --PeriodeStartDato &lt; @dato--GETDATE()
   ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato) 
    OR (Kjorenr_k = @kjorenr AND PeriodeStartDato &lt; @dato)
   )
  AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  GROUP BY
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato),
   MONTH(PeriodeStartDato)
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato &lt;= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);


Rewrite #2 Also removing the OR by using UNION

   INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
 INNER JOIN
  (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
    MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
    MAX(AjourholdDato) AS maxAjourholdDato
    FROM 
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full 
    WHERE --PeriodeStartDato &lt; @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato) 
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko      
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full 
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato &lt; @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A
GROUP BY
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato),
MONTH(PeriodeStartDato)
 ) 
  ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato &lt;= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);


Rewrite #3 Adding an additional temp table to store the inner join

By adding a temp table to split up the queries, the optimizer might get better estimates on the final query.

 SELECT
   AvtaleNummer,
   MedlemskapNummer,
   Dekningstype,
   StartAlder,
   OpphorsAlder,
   YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
   MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
   MAX(AjourholdDato) AS maxAjourholdDato
INTO #TEMP
    FROM 
    (
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM
    [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full 
    WHERE --PeriodeStartDato &lt; @dato--GETDATE()
    ( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
    (PeriodeStartDato BETWEEN @StartDato AND @SluttDato) 
    )
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko      
    UNION
    SELECT
    AvtaleNummer,
    MedlemskapNummer,
    Dekningstype,
    StartAlder,
    OpphorsAlder,
    PeriodeStartDato,
    AjourholdDato
    FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full 
    WHERE
    (Kjorenr_k = @kjorenr AND PeriodeStartDato &lt; @dato)
    AND KVID_Kontotype IN (189 --ArligInnskudd
        ,412,413 --AdmRes
        ,190,407,408,409,410,411,591) --Risiko
  ) AS A

INSERT INTO dbo.cte_MDTForsikringssum
 SELECT DISTINCT
   mdtp.AvtaleNummer
  ,mdtp.MedlemskapNummer
  ,mdtp.Dekningstype
  ,mdtp.StartAlder
  ,mdtp.OpphorsAlder
  ,mdtp.PeriodeStartDato AS GjelderFraDato
  ,NULL GjelderTilDato
  ,mdtp.AjourholdDato AS EndretDato
  ,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
  ,0 AS Avkortningsfaktor
  ,0 AS PensjonsgivendeGrunnlag
  ,0 AS Folketrygd
  ,mdtp.Kjorenr_k
 FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp

 INNER JOIN
  (
  SELECT * 
  FROM #TEMP
  ) ajourholdD
 ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
 AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
 AND ajourholdD.Dekningstype = mdtp.Dekningstype
 AND ajourholdD.StartAlder = mdtp.StartAlder
 AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
 AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
 AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
 AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
 WHERE mdtp.PeriodeStartDato &lt;= @dato
 AND NOT EXISTS
 (
 SELECT * FROM
 dbo.cte_MDTForsikringssum dest
 WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
 AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
 AND dest.Dekningstype = mdtp.Dekningstype
 AND dest.StartAlder = mdtp.StartAlder
 AND dest.OpphorsAlder = mdtp.OpphorsAlder
 AND dest.GjelderFraDato = mdtp.PeriodeStartDato
 AND dest.EndretDato = mdtp.AjourholdDato
 )
OPTION(RECOMPILE);


DROP TABLE #TEMP;


Closing note

If there is more information, like the table definitions and some sample data, more can be done, these three rewrites simply look like the fastest and easiest wins at the time of writing.