Understanding of Execution Plan????? Pls help me out!!
-
Hello friends!! In Execution plan please somebody help me to understand I am new to it. If I run one select statement having Execution plan... 1.first of all what we will watch closely so that it will understand easily i.e related to costing.... 2.Here I am seeing factors like 'SELECT' ,' TOP' , Hash Match/Left Anti Semi Join. 3.Index Scan : Physical Operation and Logical Operation Here only I can see some factores like Physical Operation And Logical Operation a)Row Count =========== 26,795,748 b)Estimated Row Size ==== 132 (What this exactly means??) c)I/O Cost ============ 209 d)CPU Cost ============ 29.5 e)Number Of Executes ==== 1 f)Cost ============== 238.884090(57%) h)Subtree Cost ======== 417 i)Estimated Row Count==== 26,795,748 4.Computer Scalar: 5.Stram Aggregate/Aggregate 6.Table Scan Like That........ Please somebody guide me in that I will be thankful.. (Please Give me detail description where i can judge all factors of execution plan so that i can reduce the cost of query if possible) T.I.A Shashank
-
Hello friends!! In Execution plan please somebody help me to understand I am new to it. If I run one select statement having Execution plan... 1.first of all what we will watch closely so that it will understand easily i.e related to costing.... 2.Here I am seeing factors like 'SELECT' ,' TOP' , Hash Match/Left Anti Semi Join. 3.Index Scan : Physical Operation and Logical Operation Here only I can see some factores like Physical Operation And Logical Operation a)Row Count =========== 26,795,748 b)Estimated Row Size ==== 132 (What this exactly means??) c)I/O Cost ============ 209 d)CPU Cost ============ 29.5 e)Number Of Executes ==== 1 f)Cost ============== 238.884090(57%) h)Subtree Cost ======== 417 i)Estimated Row Count==== 26,795,748 4.Computer Scalar: 5.Stram Aggregate/Aggregate 6.Table Scan Like That........ Please somebody guide me in that I will be thankful.. (Please Give me detail description where i can judge all factors of execution plan so that i can reduce the cost of query if possible) T.I.A Shashank
I typed in to Google the search "SQL Server Execution Plan" and I got back this excellent link: http://www.sql-server-performance.com/query_execution_plan_analysis.asp[^]
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Hello friends!! In Execution plan please somebody help me to understand I am new to it. If I run one select statement having Execution plan... 1.first of all what we will watch closely so that it will understand easily i.e related to costing.... 2.Here I am seeing factors like 'SELECT' ,' TOP' , Hash Match/Left Anti Semi Join. 3.Index Scan : Physical Operation and Logical Operation Here only I can see some factores like Physical Operation And Logical Operation a)Row Count =========== 26,795,748 b)Estimated Row Size ==== 132 (What this exactly means??) c)I/O Cost ============ 209 d)CPU Cost ============ 29.5 e)Number Of Executes ==== 1 f)Cost ============== 238.884090(57%) h)Subtree Cost ======== 417 i)Estimated Row Count==== 26,795,748 4.Computer Scalar: 5.Stram Aggregate/Aggregate 6.Table Scan Like That........ Please somebody guide me in that I will be thankful.. (Please Give me detail description where i can judge all factors of execution plan so that i can reduce the cost of query if possible) T.I.A Shashank
1. The most important thing, generally, to look at is whether the operations being performed are what you think should be performed. Are any indexes being maintained that you think are unnecessary? Is an index being ignored? If there are multiple steps in a query (perhaps this was a batch of statements, or a stored procedure) look first at the one that consumed the most time - this is reported as a percentage. Concentrate your efforts on improving the subtrees that consumed the most time. 2. These are the operations being performed. The SELECT operator returns the rows returned by the operations to its right to the client - as such it doesn't add very much overhead at all. TOP guides the operations to the right to stop once the given number of rows are returned. A Hash Match[^] is a way of performing a join, useful if there are a lot of join conditions. The server builds a hash table for both the left and right sides and compares the hash values to determine if there's a match. The Left Anti Semi Join[^] means that rows that are in the first but not in the second are returned - your query probably uses
NOT IN, or similar. Hash matches tend to be used if no suitable index was used. If a suitable index is available, and the number of rows to be returned is small, SQL Server will normally use nested loops with an index seek to find rows in the second table. 3. An Index Scan is where SQL Server starts at the beginning of the index and reads every row, to the end, looking for rows that match. Compare this to an Index Seek, where SQL Server looks directly for a particular value by looking in the right place. It's the difference between looking for an entry in a phone book by starting at the beginning and looking at every page, and looking instead for the first letter, then the next letter, etc. a) This is the number of rows that were actually returned by this operator. b) This is how big each