By Creztian


2019-05-15 06:38:23 8 Comments

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

1 comments

@Randi Vertongen 2019-05-15 08:59:28

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.

@Creztian 2019-05-15 10:05:20

Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

@Randi Vertongen 2019-05-15 10:18:35

@Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

@Thomas Franz 2019-05-15 12:41:38

UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Azure SQL Server - big query choosing bad execution plan

2 Answered Questions

[SOLVED] Warning in Execution Plan

2 Answered Questions

[SOLVED] Row estimates always too low

1 Answered Questions

[SOLVED] Unused Memory Grant on Delete

1 Answered Questions

1 Answered Questions

[SOLVED] Same schema, same data, same execution plan?

Sponsored Content