Post reply

EXPLAIN - compare before and after Statistics

  • April 1, 2016 at 5:01 pm

    #326237

    Hi,

    I have a query in which there are a bunch of tables, each of order around hundreds of millions.

    I have generated EXPLAIN plans Before and After creating Statistics on these large tables.

    Number of operations increased from 10 (Before Statistics) to 26 (After Statistics).

    How do I compare the costs between these two scenarios and demonstrate that despite increase in the number of operations, the cost actually has reduced AFTER statistics are created?

    thank you

  • April 1, 2016 at 5:17 pm

    #1869277

    Can you post the plans? Hard to say anything generic for this kind of thing.

    You'd be mostly looking for how the data movements changed, since those are usually the slowest part of a PDW query

    And for anyone else reading this, EXPLAIN is from SQL Server Parallel Data Warehouse/Azure SQLDW, the plan it returns is the distributed plan for the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 1, 2016 at 9:55 pm

    #1869289

    Glad you explained. "EXPLAIN PLAN" is an Oracle term, as well. I think they might be losing it. 😀

    --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)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

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