Post reply

What to look for in Execution plans when a query is slow ?

  • December 20, 2013 at 5:39 am

    #287629

    Hi Pals,

    Assuming the query execution is slow , I have collected the actual execution plan. What should I look for in the actual execution plan . What are the top 10 things I need to watch out for?

    I know this is a broad and generic question but I am looking for anyone who is experienced in query tuning to answer this question.

    Thanks in Advance.

  • December 20, 2013 at 5:52 am

    #1675108

    This may help. http://www.sqlskills.com/blogs/paul/query-plan-analysis-first-steps/

    Also, grab a copy of Grant's Exec plans book, should be lots in there, more than you'll get as forum replies

    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

  • December 20, 2013 at 6:03 am

    #1675110

    Oracle_91 (12/20/2013)

    Hi Pals,

    Assuming the query execution is slow , I have collected the actual execution plan. What should I look for in the actual execution plan . What are the top 10 things I need to watch out for?

    I know this is a broad and generic question but I am looking for anyone who is experienced in query tuning to answer this question.

    Thanks in Advance.

    actually a great question, and it makes me wish i had saved some images to backup some examples. seeing is understanding in things like this.

    I'll start adding things, and i know my peers will pitch in with more.

    in no particular order, here's some i can think of

    » in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.

    »Table Scan : if it exists,the table is a heap, and could benefit from adding a clustered index. clustered is always better.

    »key lookup: an index was used to find a reference to get an additional column value: adding or modifying an existing index to INCLUDE that column could help.

    » the obvious, in your face missing index statement needs to be reviewed; it might be correct, or it might be that an existing index could be tweaked to resolve the same query.

    »if a scalar function is being used at all int he query, since they scale poorly on large numbers of rows.

    »if a cursor is being used at all, it's most likely doing RBAR when a set based solution could do the same work orders of magnitude faster.

    »Index Scan: if an index scan was used, theres probably not an index that helps the query well enough to do an index seek

    »the output list for a node: if it's using an index, maybe adding an index with to match the WHERE, and which has the INCLUDE columns found in the list might help

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 20, 2013 at 6:08 am

    #1675111

    Lowell (12/20/2013)

    » in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.

    Not necessarily. There's a number of other reasons for cardinality estimates being wrong

    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

  • December 20, 2013 at 6:20 am

    #1675117

    GilaMonster (12/20/2013)

    Lowell (12/20/2013)

    » in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.

    Not necessarily. There's a number of other reasons for cardinality estimates being wrong

    oh yes, i agree; but it's certainly something to watch for.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 20, 2013 at 6:55 am

    #1675129

    For just getting started, these are the things[/url] that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.

    "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

  • December 20, 2013 at 10:30 am

    #1675242

    Hi Grant,

    Thanks for that cool stuff. It would be a great help for any starter and want to dig deep dive.

    I also thank Gail, Lowell for putting up all the nice information. Thanks very much.

  • December 20, 2013 at 11:54 am

    #1675276

    I have read Grant's book SQL Server Execution Plans book and I would certainly recommend it.

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

  • December 22, 2013 at 5:40 pm

    #1675484

    Grant Fritchey (12/20/2013)

    For just getting started, these are the things[/url] that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.

    Freakin' awesome, Grant. You're such a good speaker.

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

  • December 23, 2013 at 7:57 am

    #1675614

    Jeff Moden (12/22/2013)

    Grant Fritchey (12/20/2013)

    For just getting started, these are the things[/url] that I immediately look at for a plan. Yeah, there are tons more details, but this is the starting point. I did a presentation on this at the PASS Summit and it's available here.

    Freakin' awesome, Grant. You're such a good speaker.

    Ha! Thanks Jeff.

    "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

Viewing 10 posts - 1 through 9 (of 9 total)

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