Otdfbt

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 < @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 < @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 <= @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 < @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 < @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 <= @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 < @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 < @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 <= @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.