Analyzing a Execution Plan | SQL Server Performance Forums

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%)-&gt;NestedLoops/Inner Join(0%)-&gt;BookMark LookUp Cost(0%)-&gt;FilterCost(0%)-&gt;<br />NestedLoops/Inner Join-&gt;BookMarkLook Up Cost(0%)-&gt;Filet COst(0%)-&gt;Sort/Distinct SOrt Cost(10%)-&gt;<br />Top Cost(0%)-&gt;Compute Scalar Cost(0%)-&gt;TableSpool/Eagor Spool-&gt;Cost(22%)-&gt;Clustered Index Cost(9%)-&gt;<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 here … so I wonder what happens when you move the join bits from the WHERE statement into proper JOIN clauses.