Post reply

Tempdb Spills

  • July 24, 2017 at 8:00 am

    #322782

    Hello all 

    I have attached the query plan anonymised not sure what i can do to help the perfomance and the timing of this sql. Have tried a couple of things that they didnt seem to work.

    Any suggestions much appreciated

  • July 24, 2017 at 8:06 am

    #1952373

    fixed it with changing the MAXDOP to 4

  • July 24, 2017 at 8:21 am

    #1952374

    For those who may be interested, here is the anonymised SQL from the plan:
    SELECT
      Object1.Column1
    ,  Object1.Column2
    ,  Object2.Column3
    ,  Object2.Column4
    ,  (CONVERT(CHAR(10), Object1.Column5, ?))
    ,  Object1.Column6
    ,  Object1.Column7
    ,  Object1.Column8
    ,  Object3.Column9
    ,  Object4.Column10
    ,  Object5.Column11
    ,  Object6.Column12
    ,  Object7.Column13
    ,  Object7.Column14
    ,  Object7.Column15
    ,  Object7.Column16
    ,  Object7.Column17
    ,  Object8.Column18
    FROM
      Object9 Object1
    ,  Object10 Object2
    ,  Object11 Object4
    ,  Object12 Object13
    ,  Object14 Object3
    ,  Object15 Object5
    ,  Object16 Object17
    ,  Object16 Object6
    ,  (Object18    Object7
    LEFT OUTER JOIN Object19 Object8 ON Object7.Column1 = Object8.Column1
                 AND Object7.Column2 = Object8.Column2)
    WHERE (
        Object1.Column5 BETWEEN Variable1 AND Variable2
        AND Object1.Column19 = Variable3
        AND Object1.Column20 = Variable4
        AND Object1.Column21 = Object2.Column4
        AND Object4.Column12 = Object2.Column12
        AND Object4.Column4 = Object2.Column4
        AND Object13.Column22 =
        (
         SELECT Function1(Object20.Column22)
         FROM Object12 Object20
         WHERE
           Object13.Column1  = Object20.Column1
           AND Object13.Column23 = Object20.Column23
           AND Object13.Column24 = Object20.Column24
           AND Object20.Column22 <= Function2(CONVERT(CHAR, Function3(), ?), Variable5, Variable6)
        )
        AND Object13.Column25 =
        (
         SELECT Function1(Object21.Column25)
         FROM Object12 Object21
         WHERE
           Object13.Column1  = Object21.Column1
           AND Object13.Column23 = Object21.Column23
           AND Object13.Column24 = Object21.Column24
           AND Object13.Column22 = Object21.Column22
        )
        AND Object13.Column23 = Object4.Column10
        AND Object3.Column16 = Object13.Column16
        AND Object5.Column23 = Object4.Column10
        AND Object17.Column26 = Object1.Column1
        AND Object17.Column27 = Variable7
        AND Object17.Column12 = Object2.Column12
        AND Object6.Column26 = Object1.Column1
        AND Object6.Column27 = Variable8
        AND Object6.Column12 = Object3.Column12
        AND Object1.Column1 = Object7.Column1
        AND Object1.Column2 = Object7.Column2
    );

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 24, 2017 at 8:48 am

    #1952378

    This sort of thing occurs because the memory granted was not enough, typically because the estimated rows are drastically inaccurate.

    This exactly happens in your query plan. The sort that is spilling estimated 1117.81 rows. Instead, it actually sorted 38,440,706 rows, so the estimates are off by a very, very large amount.

    On that note, you also have a warning in that plan for a conversion that could affect estimates, stemming from a conversion of a column to CHAR(10) (also, just as a note, whatever you're using to anonymize the plan didn't anonymize the table and column reference in that warning).

    You really should address that issue. Using MAXDOP 4 might mask the problem just by giving you a new query plan that is less affected, but it's not addressing the root problem.

    Cheers!

  • July 24, 2017 at 9:15 am

    #1952395

    So you apparently have scalar UDFs, which a) hose the estimating ability of the optimizer, b) prevent the use of index seeks if appropriate and c) void the use of parallelism. 

    You have two SELECT scalars in WHERE clauses, which are difficult to optimize.

    You have a BETWEEN variables in the WHERE clause, which opens you to the parameter-sniffiing-issue-inducing widely-varying-inputs problem.

    Potential solutions would include:

    1) Inlining the UDF logic into the query(s). 

    2) Combining the double hits on object12 into a single output into a temp table (NOT table variable). This is likely only a win if you cut down to a few rows with this. Even putting both into separate temp tables could be a win

    3) Eliminating the CONVERT

    4) By some other means deriving a restricted set of rows into a temp table if possible

    5) OPTION (RECOMPILE). This one will likely be mandatory in any case due to the BETWEEN

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 24, 2017 at 10:34 pm

    #1952491

    zouzou - Monday, July 24, 2017 8:06 AM

    fixed it with changing the MAXDOP to 4

    Maybe not.  Adding MAXDOP would cause a recompile.  Perhaps recompiling the code without a MAXDOP change would have fixed it.

    Also, why have you deleted the plan you apparently had attached?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply