Sql-server – How to improve the execution plan to speed up the query

performancequery-performancesql serversql-server-2012

I am trying to improve the execution plan to speed up the query. Currently the query won't finish after nine hours. Right now I am getting a lot of hash match and merge join. I have very limited knowledge of execution plans but from what I think I know the hash match is very slow and the merge join is not much better. Although I am not exactly sure what I am hoping to find as this part of the SQL is still really new to me.

Each table used except for ZZ_PropertyTable has a Primary key on Casino_ID, CasinoCode, Gamingdate, PlayerID in that order. Along with a unique nonclustered index which is the same columns in the same order but with an include clause to cover the this query. The ZZ_PropertyTable has a Primary key on Casino_ID, CasinoCode.

I did not think it mattered but just to be sure I put all the joins in the same order as the primary keys.

I am not sure if maybe I should write the code differently or if i need different indexes. Any help you can provide even if it just a link to something I can read to better understand this would be greatly appreciated.

I am using SQL Server 2012 below is the code as well as a link to the execution plan:

https://www.brentozar.com/pastetheplan/?id=rymXZWnHb

Select
     PL.Casino_ID
    ,PL.CasinoCode
    ,PT.PropertyName
    ,PL.PlayerID
    ,PL.Gamingdate
    ,1                              as TotalTrips
    ,isnull(TS.SlotTrips,0)         as SlotTrips
    ,isnull(TT.TableTrips,0)        as TableTrips
    ,isnull(GT.GamingTrips,0)       as GamingTrips
    ,Isnull(TS.SlotCoinIn,0)        as SlotCoinIn
    ,isnull(TT.TableDrop,0)         as TableDrop
    ,isnull(TS.SlotTheoWin,0)       as SlotTheoWin
    ,isnull(TT.TableTheoWin,0)      as TableTheoWin
    ,isnull(TS.SlotActualWin,0)     as SlotActualWin
    ,isnull(TT.TableActualWin,0)    as TableActualWin
    ,isnull(TS.SlotTheoWin 
        + TT.TableTheoWin,0)        as TotalTheo
    ,isnull(TS.SlotActualWin 
        + TT.TableActualWin,0)      as TotalActual
    ,isnull(TS.SlotPointsEarned,0)  as OasisSlotPointsEarned
    ,isnull(TT.TablePointsEarned,0) as OasisTablePointsEarned
    ,isnull(HP.SlotPointsEarned,0)  as HaloSlotPointsEarned
    ,isnull(HP.TablePointsEarned,0) as HaloTablePointsEarned
    ,isnull(TS.SlotPointsEarned 
        + TT.TablePointsEarned
        + HP.SlotPointsEarned
        + HP.TablePointsEarned
        + PA.PointAdjustment,0)     as TotalPointsEarned
    ,isnull(TS.SlotCompsEarned,0)   as OasisSlotCompEarned
    ,isnull(TT.TableCompsEarned,0)  as OasisTableCompEarned
    ,isnull(HC.SlotCompsEarned,0)   as HaloSlotCompEarned
    ,isnull(HC.TableCompsEarned,0)  as HaloTableCompEarned
    ,isnull(TS.SlotCompsEarned
        + TT.TableCompsEarned
        + HC.SlotCompsEarned
        + HC.TableCompsEarned
        + CA.CompAdjustment,0)      as TotalCompEarned
    ,isnull(TS.SlotMinutesPlayed,0) as SlotMinutesPlayed
    ,isnull(TT.TableMinutesPlayed,0)as TableMinutesPlayed
    ,isnull(TS.SlotMinutesPlayed
        + TT.TableMinutesPlayed,0)  as TotalMinutesPlayed
    ,isnull(F.FreePlayRedeemed*-1,0)as FreePlayRedeemed
    ,isnull(F.PointstoFreePlay*-1,0)as PointsToFreePlay
    ,isnull(F.PromoFreePlay*-1,0)   as PromoFreePlay
    ,isnull(C.CompRedeemed,0)       as CompRedeemed
    ,isnull(TS.SlotJackpots,0)      as SlotJackpots
    ,isnull(TT.TableJackpots,0)     as TableJackpots
    ,isnull(TS.SlotJackpots
        + TT.TableJackpots,0)       as TotalJackpots
    ,isnull(CA.CompAdjustment,0)    as CompAdjustment
    ,isnull(PA.PointAdjustment,0)   as PointAdjustment
    ,isnull(PR.RedeemedAmount,0)    as Points_to_Purchase
    ,isnull(TT.TableTotalIn,0)      as TableTotalIn
    ,isnull(A.Downloaded,0)         as AwardsDownloaded
    ,isnull(PC.RedeemedAmount,0)    as PromoChipRedeem
    ,Getdate()                      as LastUpdated

From Analytics.dbo.ZZ_PlayerIDList as PL
    Left Join Analytics.dbo.ZZ_HaloComps as HC
        on      PL.Casino_ID    = HC.Casino_ID
            and PL.CasinoCode   = HC.CasinoCode
            and PL.GamingDate   = HC.GamingDate
            and PL.PlayerID     = HC.PlayerID

    Left Join Analytics.dbo.ZZ_HaloPoints as HP
        on      PL.Casino_ID    = HP.Casino_ID
            and PL.CasinoCode   = HP.CasinoCode
            and PL.GamingDate   = HP.GamingDate
            and PL.PlayerID     = HP.PlayerID

    Left Join Analytics.dbo.ZZ_FreePlay as F
        on      PL.Casino_ID    = F.Casino_ID
            and PL.CasinoCode   = F.CasinoCode
            and PL.GamingDate   = F.GamingDate
            and PL.PlayerID     = F.PlayerID

    Left Join Analytics.dbo.ZZ_Comp as C
        on      PL.Casino_ID    = C.Casino_ID
            and PL.CasinoCode   = C.CasinoCode
            and PL.GamingDate   = C.GamingDate
            and PL.PlayerID     = C.PlayerID

    Left Join Analytics.dbo.ZZ_PropertyTable as PT
        on      PL.Casino_ID    = PT.Casino_ID
            and PL.CasinoCode   = PT.CasinoCode

    Left Join Analytics.dbo.ZZ_TrackedSlot as TS
        on      PL.Casino_ID    = TS.Casino_ID
            and PL.CasinoCode   = TS.CasinoCode
            and PL.GamingDate   = TS.GamingDate
            and PL.PlayerID     = TS.PlayerID

    Left Join Analytics.dbo.ZZ_TrackedTable as TT
        on      PL.Casino_ID    = TT.Casino_ID
            and PL.CasinoCode   = TT.CasinoCode
            and PL.GamingDate   = TT.GamingDate
            and PL.PlayerID     = TT.PlayerID

    Left Join Analytics.dbo.ZZ_GamingTrips as GT
        on      PL.Casino_ID    = GT.Casino_ID
            and PL.CasinoCode   = GT.CasinoCode
            and PL.GamingDate   = GT.GamingDate
            and PL.PlayerID     = GT.PlayerID

    Left Join Analytics.dbo.ZZ_PointRedeem as PR
        on      PL.Casino_ID    = PR.Casino_ID
            and PL.CasinoCode   = PR.CasinoCode
            and PL.GamingDate   = PR.GamingDate
            and PL.PlayerID     = PR.PlayerID

    Left Join Analytics.dbo.ZZ_PointAdj as PA
        on      PL.Casino_ID    = PA.Casino_ID
            and PL.CasinoCode   = PA.CasinoCode
            and PL.GamingDate   = PA.GamingDate
            and PL.PlayerID     = PA.PlayerID

    Left Join Analytics.dbo.ZZ_CompAdj as CA
        on      PL.Casino_ID    = CA.Casino_ID
            and PL.CasinoCode   = CA.CasinoCode
            and PL.GamingDate   = CA.GamingDate
            and PL.PlayerID     = CA.PlayerID

    Left Join Analytics.dbo.ZZ_Award as A
        on      PL.Casino_ID    = A.Casino_ID
            and PL.CasinoCode   = A.CasinoCode
            and PL.GamingDate   = A.GamingDate
            and PL.PlayerID     = A.PlayerID

    Left Join Analytics.dbo.ZZ_PromoChip as PC
        on      PL.Casino_ID    = PC.Casino_ID
            and PL.CasinoCode   = PC.CasinoCode
            and PL.GamingDate   = PC.GamingDate
            and PL.PlayerID     = PC.PlayerID

The outer joins are all needed (cannot be inner joins). ZZ_PlayerIDList table has a list of every player that exist in any of the other tables. So while they will exist in one of the other ZZ_ Tables they might only be in one of them.

Statistics are up to date. I made sure to update them with a fullscan prior to getting the estimated plan.

Best Answer

First let's start where the problem likely isn't to be. Cardinality estimates look fine. You aren't doing any filtering and all of the joins are left joins on the primary keys of the tables. Table access methods look fine. You've defined the best possible indexes for the tables and SQL Server is doing index scans on those tables. Seems perfectly reasonable because you need all of the rows from the tables. Join order seems ok. None of the tables will decrease or increase the size of the result set (if ZZ_PlayerIDList is the starting table). A poor join order could lead to some unnecessary repartitioning but I don't see that here.

That means the join type. There are four parallel merge joins and the rest are parallel hash joins. There are some edge cases with parallel merge join that perform very poorly. I'm not a fan of it for this type of query in which you data is already sorted. The parallel aspect of it means you read the sorted data in parallel (which breaks the sorting) only to have to sort it again. There's a lot of repartitioning rows and sorting which would be avoided with MAXDOP 1 merge joins. There are also hints in the query plan that this query isn't getting as much memory as it wants.

I would try adding a MAXDOP 1 hint to the query and making changes so that you get all merge joins. For testing purposes it may be helpful to add OPTION (MERGE JOIN) as a hint. I would not expect that query to take more than 9 hours. If it does that sounds like some kind of hardware, configuration, or blocking issue.

From a data model point of view, you could improve performance of the query by combining together some of your tables. All of them have the same primary keys. For example, UX_ZZGamingTrips has 18260100 rows and UX_ZZTrackedSlot has 18259900 rows. Would combing those tables into one table really be a bad thing? What do you gain by separating them?

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested. The graphical execution plan seems to indicate this is the case -- TOP is the "last" step. But it appears there is more going on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes. An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close(). As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator). This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards. This row-at-a-time model is essential in forming a sound intuition for query execution.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives. The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets. The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed. The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree. The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal. This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans. Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans. More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it. Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best. Which option is appropriate in your case depends on the details of course. The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.

Different execution plan for the same query if I change a value in the predicate

The problem was histograms, I ran statistics and disabled histogram creation and the execution plan used nested loops:

BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'MIDAS', TABNAME => 'MINCISOC', 
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;

If I run it with FOR ALL COLUMNS SIZE AUTO again the same problem because it uses hash join. Thanks to Phil for the suggestion.

Best Answer

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

Different execution plan for the same query if I change a value in the predicate

Related Question