sqlserver查询执行计划分析

《sqlserver查询执行计划分析》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、www.gdtesting.com看懂执行计划例子1以AdventureWorks的DatabaseLog查询为例：SELECT*FROM[AdventureWorks2008R2].[dbo].[DatabaseLog]•Cachedplansize–howmuchmemorytheplangeneratedbythisquerywilltakeupinstoredprocedurecache.Thisisausefulnumberwheninvestigatingcacheperformancei

2、ssuesbecauseyou'llbeabletoseewhichplansaretakingupmorememory.•EstimatedOperatorCost–we'vealreadyseenthisasthepercentagecostinwww.gdtesting.comwww.gdtesting.comFigure1.•EstimatedSubtreeCost–tellsustheaccumulatedoptimizercostassignedtothisstepandallprevio

3、ussteps,butremembertoreadfromrighttoleft.Thisnumberismeaninglessintherealworld,butisamathematicalevaluationusedbythequeryoptimizertodeterminethecostoftheoperatorinquestion;itrepresentstheamountoftimethattheoptimizerthinksthisoperatorwilltake.•Estimatedn

4、umberofrows–calculatedbasedonthestatisticsavailabletotheoptimizerforthetableorindexinquestion.Thelogicaloperatorsaretheresultsoftheoptimizer'scalculationsforwww.gdtesting.comwww.gdtesting.comwhatshouldhappenwhenthequeryexecutes.Thephysicaloperatorsrepre

5、sentwhatactuallyoccurred.Thelogicalandphysicaloperatorsareusuallythesame,butnotalwaysTheSubtreeissimplythesectionoftheexecutiontreethatwehavelookedatsofar,workingrighttoleftagain,andtoptobottom.Allestimationsarebasedonthestatisticsavailableonthecolumnsa

6、ndindexesinanytable.TheI/OCostandCPUcostarenotactualoperators,butratherthecostnumbersassignedbytheQueryOptimizerduringitscalculations.ThesenumbersareusefulwhendeterminingwhethermostofthecostisI/O-based(asinthiscase),orifwe'reputtingaloadontheCPU.Abigger

7、numbermeansmoreprocessinginthisarea.You'llnotethat,inthiscase,theoperatorcostandthesubtreecostarethesame,sincethetablescanistheonlyoperator.Formorecomplextrees,withmoreoperators,you'llseethatthecostaccumulatesastheindividualcostforeachoperatorisaddedtot

8、hetotal.Yougetthefullcostoftheplanfromthefinaloperationinthequeryplan,inthiscasetheSelectoperator.Againweseetheestimatednumberofrows.Thisisdisplayedforeachoperationbecauseeachoperationisdealingwithdifferentsetsofdata.www.gdtestin