使用sql的一些tip

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

哈尔滨工业大学计算机学院研究中心zhaokai@hit.edu.cn如何在2000中显示查询计划。使用 sql查询分析器以文本方式显示执行计划SHOWPLAN_TEXTandSHOWPLAN_ALLThetwoSEToptionsSHOWPLAN_TEXTandSHOWPLAN_ALLletyouseetheestimatedqueryplanwithoutactuallyexecutingthequery.BothoptionsalsoautomaticallyenabletheSETNOEXECoption,soyoudon'tseeanyresultsfromyourquery—youseeonlythewaythat sqlServerhasdeterminedisthebestmethodforprocessingthequery.TurningNOEXECONcanbeagoodthingwhiletuningaquery.Forexample,ifyouhaveaquerythattakes20minutestoexecute,youmighttrytocreateanindexthatwillallowittorunfaster.However,immediatelyaftercreatinganewindex,youmightjustwanttoknowwhetherthequeryoptimizerwillevenchoosetousethatindex.Ifyouwereactuallyexecutingthequeryeverytimeyoulookedatitsplan,itwouldtakeyou20minutesforeverytuningattempt.SettingNOEXECONalongwiththeshowplanoptionwillallowyoutoseetheplanwithoutactuallyexecutingallthestatements.WARNINGSinceturningonSHOWPLAN_TEXTorSHOWPLAN_ALLimpliesthatNOEXECisalsoon,youmustsettheSHOWPLANoptiontoOFFbeforeyoudoanythingelse.Forexample,youmustsetSHOWPLAN_TEXTtoOFFbeforesettingSHOWPLAN_ALLtoON.使用 sql查询分析器以图形方式显示执行计划如何使用DBCCPAGE命令来查看数据页格式例子：usetestgoselect*fromstugo结果：首先来看sysindexes关系的内容。数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。列名数据类型描述IdInt表ID（如果indid=0或255）。否则为索引所属表的ID。StatusInt内部系统状态信息。Firstbinary(6)指向第一页或根页的指针。IndidSmallint索引ID：0=该表为新表，对应的表是一个堆1=聚集索引>1=非聚集255=具有text或image数据的表条目rootbinary(6)如果indid>=1和1重复。如果indid=255，rows设置为0。提供该列是为了向后兼容。selectid,first,indidfromsysindexeswhereid=object_id('stu')andindidin(0,1)DBCC的格式为:DBCCPAGE({dbid|dbname},filenum,pagenum[,printopt])为了获得filenum，filenum，执行下面的语句：selectid,first,indidfromsysindexeswhereid=object_id('stu')andindidin(0,1)idfirstindid4535766540x1900000001000然后运行DBCCdbcctraceon(3604)godbccpage('test',1,25,1)goDBCC执行完毕。如果DBCC输出了错误信息，请与系统管理员联系。PAGE:(1:25)------------BUFFER:-------BUF@0x18ED5A00---------------bpage=0x19620000bhash=0x00000000bpageno=(1:25)bdbid=7breferences=24bstat=0x9bspin=0bnext=0x00000000PAGEHEADER:------------Page@0x19620000----------------m_pageId=(1:25)m_headerVersion=1m_type=1m_typeFlagBits=0x0m_level=0m_flagBits=0x8008m_objId=453576654m_indexId=0m_prevPage=(0:0)m_nextPage=(0:0)pminlen=8m_slotCnt=8m_freeCnt=7938m_freeData=1316m_reservedCnt=0m_lsn=(6:166:2)m_xactReserved=0m_xdesId=(0:0)m_ghostRecCnt=0m_tornBits=2AllocationStatus-----------------GAM(1:2)=ALLOCATEDSGAM(1:3)= 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>