SQL Server Performance Forum – Threads Archive
Analyzing a Execution Plan
Dear Friends<br /><br />On Executing a particular Sp I am getting several queries of which i am mentioning the execution plan of the query that costs the most(7.64%)<br /><br />Query 28<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery Cost (relative to the batch):7.64%<br />Query Text : UPDATE AB<br /> SET NxtAccrDt = CASE WHEN @p_Freq =’NA’ Or @p_Freq =’ONE’ THEN NULL –NxtAccrDt <br /> ELSE dbo.IRC_fCalcToDateWTHFrqTyp_V3(@p_CalcDt,@p_Freq,@p_FreqType,@p_NoOfDaysPerYr) END,<br />LstAccrDt = @p_CalcDt<br />FROM DEP_tAcctBaln AB, DEP_tDepAcctMst InstEnt, #TmptCalcSlabFinal SLAB<br />WHERE InstEnt.BrID = @p_BrID<br />ANDInstEnt.PrdID = @p_PrdID<br />ANDInstEnt.BrID = AB.BrID<br />ANDInstEnt.PrdID = AB.PrdID<br />ANDInstEnt.AcctID = AB.AcctID<br />ANDSLAB.BrID = AB.BrID<br />ANDSLAB.PrdID = AB.PrdID<br />ANDSLAB.AcctID = AB.AcctID<br />ANDNxtAccrDt = @p_ValueDt <br />ANDInstEnt.AcctStat = ‘L'<br />ANDInstEnt.Approve = ‘Y'<br /><br />TAbleScanCost(35%)->NestedLoops/Inner Join(0%)->BookMark LookUp Cost(0%)->FilterCost(0%)-><br />NestedLoops/Inner Join->BookMarkLook Up Cost(0%)->Filet COst(0%)->Sort/Distinct SOrt Cost(10%)-><br />Top Cost(0%)->Compute Scalar Cost(0%)->TableSpool/Eagor Spool->Cost(22%)->Clustered Index Cost(9%)-><br />Update Cost(0%)<br /><br />Apart From This There is an arrow pointing towards the first Nested Loops which is Dep_tAcctBalan (Index Seek Cost 12%)<br />There is another arrow pointing towards the second nested loop which is Dep_tDepAcctMst(Index Seek Cost 12%)<br /><br />What action should I make on these Plans<br /><br />Kindly guide me<br /><br />Deep Regards<br />Anil<br /><br /><br />Never Give Up… and I doubt whether the execution plan even takes into account the UDF – is it the 0% Compute Scalar Cost? Does the UDF just do a calculation, or does it run queries on its own? Also, there was a discussion about the join-less syntax herehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15494 … so I wonder what happens when you move the join bits from the WHERE statement into proper JOIN clauses.
]]>