Home Forums SQL Server 2014 Development - SQL Server 2014 Query plan analysis RE: Query plan analysis

  • August 16, 2016 at 11:12 pm

    #1895830

    yujinagata72 (8/16/2016)

    Hi,

    If I get a query plan XML, what kind of information will make it to be considered as a 'bad' plan?

    I can think of the following:

    - PhysicalOp="Clustered Index Scan"

    - PhysicalOp="Index Scan"

    - PhysicalOp="Table Scan"

    And any thing else?

    Thanks in advance,

    To be honest, all 3 of those can be a god-send for performance depending, of course, on what you're doing. I also agree with Kevin that anything that has a scalar or mult-statement table valued function bears looking at. I also say that any internal row count that has a count larger than the table it originated from can be a serious problem but, again, "It Depends".

    And unless it's for a proc that's shredding XML to store it in properly normalized tables, just about any occurrence of any XML function is a real problem in my book.

    --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)