April 18, 2009 at 5:52 am
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?
April 18, 2009 at 6:32 am
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
April 18, 2009 at 6:41 pm
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
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]
April 18, 2009 at 7:42 pm
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.
April 20, 2009 at 6:31 am
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
April 20, 2009 at 6:34 am
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
April 21, 2009 at 8:18 am
Thank you very much guys!
Your posts are very useful.
April 21, 2009 at 8:25 am
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]
April 21, 2009 at 8:58 am
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
April 21, 2009 at 9:04 am
thanks Grant 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 22, 2009 at 10:05 am
April 23, 2009 at 12:19 pm
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?
April 23, 2009 at 1:06 pm
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
April 23, 2009 at 1:31 pm
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?
April 23, 2009 at 1:41 pm
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