Understanding execution plan

  • I use set showplantext on to get execution plan of my query, but I feel that I am lack of theoretical knowledge about it. Where can I read about execution plan and how to understand it?

  • Check this blog series out. http://sqlinthewild.co.za/index.php/2007/08/20/reading-execution-plans/

    Grant Fritchley's written a book on reading exec plans. I don't know if it's still available for download. I'm sure he'll drop by later and say.

    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
  • It is and a very good book I tell all developers to read this, saves me headache later -.-.

    Link: http://downloads.sqlservercentral.com/dissecting_contents.pdf

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • fayilt (4/18/2009)


    I use set showplantext on to get execution plan of my query, but I feel that I am lack of theoretical knowledge about it. Where can I read about execution plan and how to understand it?

    You are going in the right track, execution plans is one of the tools to boost your performance of your server as Gail and Mohit mentioned go through the resources they have provided and that should be suffice at the beginning.

    You can look at the Display Estimated Execution plan or Actual Execution plan which is quite easier for a beginner to read.

  • Yes, you can follow the link provided above and download the book for free. It's also available in a dead-tree version from Amazon if you prefer reading from paper.

    Also, you might check out some of the links from the PASS Top 10 Execution Plans list. Some of them are introductory and intermediate. A few are advanced.

    "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

  • Krishna Potlakayala (4/18/2009)


    fayilt (4/18/2009)


    I use set showplantext on to get execution plan of my query, but I feel that I am lack of theoretical knowledge about it. Where can I read about execution plan and how to understand it?

    You are going in the right track, execution plans is one of the tools to boost your performance of your server as Gail and Mohit mentioned go through the resources they have provided and that should be suffice at the beginning.

    You can look at the Display Estimated Execution plan or Actual Execution plan which is quite easier for a beginner to read.

    For what it's worth, I'd limit the use of the Estimated Execution plans. Quite frequently they can mask issues that are readily exposed in the Actual Execution plan. I usually only look at them when I have to (for example on queries that run incredibly long periods of time) or when I'm looking at the procedure cache (which stores the estimated plans, not the actual plans).

    "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

  • Thank you very much guys!

    Your posts are very useful.

  • Hi Grant,

    I can't actually access that link to your book 🙁

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Try this one

    http://www.red-gate.com/specials/Grant.htm

    I'm not sure it's still valid. Maybe the real book & Kindle are all that's available now.

    "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

  • thanks Grant 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • I am reading articles and that book from redgate - and it is much better now, thank you guys. And I hope I will be able to read execution plans fluently in the end. I believe that it can happen sooner with your help =)

    For example, I am not sure that I understand what is physical and logical reads\writes? Is it like fact and plan?

  • Physical reads = from disk

    Logical reads = from memory

    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
  • Thank you.

    How can I clean cache, for example, when I want to compare perfomance of two queries and I want to know how many physical reads will be for each query?

  • DBCC FREEPROCCACHE- you can clear the procedure cache

Viewing 15 posts - 1 through 15 (of 17 total)

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