Post reply

Query plan analysis

  • August 16, 2016 at 8:12 am

    #329347

    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,

  • August 16, 2016 at 8:33 am

    #1895669

    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,

    I don't know that there is such a thing as a "guaranteed" bad-plan thing (although a scalar UDF/multi-statement TVF really does come close for me personally). Given what you put I will state that a table scan is absolutely the best way to read any reasonable fraction of the data in the table (without a covering index being available).

    So "bad plan" is a HUGELY dependent thing. A plan that is PERFECT for a given set of data or collection of parameters could be DISASTROUSLY BAD for a different set of data or collection of input parameters.

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

  • August 16, 2016 at 8:49 am

    #1895676

    Quick suggestion, opening the plan in SQL Sentry Plan Explorer is probably the easiest way to do this, trying to query/analyse the plan can be a very complex task.

    😎

  • August 16, 2016 at 9:38 am

    #1895697

    I'd say that a bad plan is when estimates aren't similar to reality. That would cause choosing the wrong operation.

    There's no such thing as a bad operator, there are only operations that are not adequate to some cases.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 16, 2016 at 10:24 am

    #1895713

    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,

    I agree with the others, no such thing as a "bad" plan. There's just a plan that does what it does based on the query, structures, and statistics available to the optimizer for its decisions.

    As to what to look for, here are a few things [/url]to get you started. After that, I'd suggest reading my book. It's free to download.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 16, 2016 at 1:29 pm

    #1895763

    I'd say that a plan containing an implicit cast points to a bad query. The plan itself isn't bad (or misunderstood) but the query that generated it is one to target for improvement.

    Also, let me echo Grant's advice: Read his book about query plans. And no, he doesn't pay me to say this. 😛

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 16, 2016 at 5:18 pm

    #1895815

    Thanks for everyone's help.

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

Viewing 8 posts - 1 through 7 (of 7 total)

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