SQLServer性能优化宝典

ＳQLServeｒ性能优化宝典ＴOC\ｏ”1—3"\h\ｚ\ｕHYPERLＩNK\l＂＿Toc３１77５9９1１”1)．优化方法论ﻩPＡＧＥREF＿Tｏc317759９11\ｈ6HYＰERLINK\l"_Ｔoc317759912"1。分析实例的等待ﻩＰＡＧEREF＿Toc３１7７5９912＼h６ＨYPＥＲLＩNK2）.XMLﻩＰAGＥＲＥF＿Toc317759922＼h１1HＹPERLＩＮK\ｌ"_Tｏｃ31775992３”３）．图形ﻩPAGERＥＦ＿Toc317759９２3\h1１ＨＹPEＲLIＮK\l＂＿Ｔｏｃ３1775９924＂３.sｙsｃachobjecｔｓﻩＰAＧEREF_Toｃ31775９９2４\ｈ11ＨYＰＥRＬINK\l＂＿Toc3１775992５"4．清空缓存(数据/查询计划） ＰＡGEREＦ_Tｏc3１7759925\h１１HＹＰEＲLＩNK＼ｌ”_Tｏc３177５9926”5.动态管理对象 PAＧＥREF_Ｔoｃ３177５9926＼h1１HYPＥRＬINK\ｌ＂_Ｔoc３1775９92７"６．STATISTIＣSIOﻩＰAGEREF＿Toｃ３１775992７\h11HＹPＥＲLINK\l＂_Toｃ317７59９28"7。STＡTISTICSTＩＭEﻩPＡGEＲEF＿Tｏc31７759928\ｈ12ＨYPEＲLINK\l”＿Toc31775９92９”8。分析执行计划ﻩＰAＧERＥF_Ｔoｃ３1７７5992９\h１2HYPERＬＩNK＼ｌ"_Toc317７599３0"8.1．取得缓存中每个计划的ＸＭＬ计划ﻩPＡＧＥＲＥF_Toc3１7７599３0\h13HＹPERLIＮＫ＼l＂＿Tｏc317７599３1"8.２.过多的重新编译ﻩPAGEREF＿Ｔoｃ317７59931\h1４HYＰＥRＬIＮK\l”＿Tｏc31７７５99３2＂８。３.不够高效的查询计划ﻩＰＡGEREF_Toc３17７５9９3２＼ｈ14ＨＹPＥRLIＮK\ｌ"＿Ｔoc31７759933”8.4.执行计划重用次数ﻩPAGEＲＥF_Toｃ３1７75993３\h1５HYＰＥRLINK\ｌ＂_Toc317759934＂9．索引优化ﻩPAGＥREＦ_Toｃ31７759934＼h15ＨＹＰERLINK\l＂_Tｏｃ31775９935"9。1．索引优化概要和分析ﻩＰAGＥRＥF_Ｔｏｃ3177599３５＼h1５HYPERLＩＮＫ\ｌ”＿Tｏc3１7759９36＂９．２.查看索引碎片信息ﻩPAＧEREＦ_Ｔoc3177５９9３6＼h15HYPERLINK\ｌ＂_Ｔoc３177５9９3７”9。３。重新生成和重新组织索引ﻩPAGEREF＿Ｔoc31７75９937＼ｈ１7HYPERLINK＼l"_Toc3177５9938"９。4.查看没有使用的索引ﻩPAＧＥRＥF_Ｔoｃ31７759938\h１8HYPＥRＬIＮＫ＼l＂＿Ｔｏc317759９39"9.5．查看已经使用且如何使用索引列表ﻩＰAGEＲＥF＿Ｔoｃ317759９３9\ｈ1９HYPＥRLINＫ\ｌ"_Tｏｃ3１775９９4０”9．６.查看每个表的索引结构 PＡGEREF＿Ｔｏc３17７５９940\h２0ＨYＰＥＲLＩＮK\l”＿Tｏc31775９９41＂９.7。查看索引列挨次 PAGEＲEF_Tｏｃ３17759941\h2０HYPERLINK\l”_Toc３177５９942”9．８。同时查看使用和未使用索引及列位置信息ﻩPＡGＥREＦ_Toc31７75９９42\h21HＹPERＬINＫ\ｌ＂＿Tｏｃ3１775９９4３"9．9。查看聚集表个数ﻩＰAGERＥF＿Toc3177５９943\ｈ2２HYPERLＩNＫ\l＂＿Toｃ31７７５99４4＂９。１0．索引使用效率评估ﻩＰAGＥＲEF_Toc317７5９9４4\h2３HYPＥRＬＩNK＼l"_Tｏc317759９45＂9．11.评估索引的选择性ﻩＰAGEＲEＦ＿Tｏｃ31７759９45\ｈ23HYPERLINK\l＂_Ｔoｃ３１77５9946"１０.数据库和文件空间 ＰＡGEＲＥF＿Ｔoc3１7７５9９46＼h23HＹPＥＲLIＮK1０.1查看数据库空间ﻩＰＡGERＥＦ＿Toc３１775９9４7＼h２３ＨYPEＲＬＩＮK\l＂_Toc３１7７５994８”10.２查看数据库中每个表的占用空间ﻩPＡGEREF＿Tｏｃ３１7759948＼ｈ２３HYPEＲＬINＫ10。９．查看数据库的版本和补丁信息ﻩPAGＥＲEＦ＿Toc3１7７５99５５\h27ＨYＰＥRLINK\l”_Ｔoc31775９９56”1１．监视命令ﻩＰＡＧEREＦ_Toc３17759956\ｈ27HYＰERLIＮK\l”_Ｔoc3１775９９５７”１２．SQLﻩPAGＥＲＥF_Ｔｏc3１７７５９９57＼h28HYPERLIＮK\l"_Toc317759９58"1３.跟踪 PAGEＲEF_Tｏc31775９958\ｈ２９ＨＹPEＲLINＫ\l＂＿Ｔoｃ31７７５99５９＂3）．性能故障检测方法ﻩPAＧＥＲＥF＿Tｏｃ3１7759９59\ｈ３１ＨYＰＥRLIＮK\l”_Ｔoｃ３177599６0＂１.CPUﻩＰAGEREF_Toc3177５9９60\ｈ31HYPEＲLINＫ\ｌ"_Toc3１７７5996１"１。1可运行状态下的工作进程数量ﻩPAＧＥREF_Ｔoｃ31７759961\ｈ3１HYＰEＲLINK\ｌ"_Ｔoｃ3177５99６２"１。2工作进程在可运行状态下花费的时间ﻩＰＡGEＲEF_Toｃ3177５９962＼h31HYＰERLＩNK\ｌ”_Toc31７7５9９6３＂１．3每次执行过程中占用ＣＰＵ最多的前1０位查询 ＰAGEREF＿Ｔoc3177５９963\ｈ31HYPERLＩNＫ＼l"_Ｔｏc317７5９964”1.４每次执行过程中运行最频繁的查询［与1.2同用］ﻩＰAＧEＲEF_Toc317７5９9６4＼h3２HYＰERＬIＮＫ＼ｌ”_Ｔoc317759９65"1。5编译和重编译ﻩPAGEＲＥF_Toc３1７759965＼h３2HYPEＲLINK\l＂_Tｏc317759９66"１.6猎取安排给用于存储优化查询计划的过程高速缓存的内存 ＰＡGEＲEF_Toc317７599６6\h32HYPERLＩＮＫ\ｌ"＿Tｏｃ３1７759967＂1．７确定服务器的活动ﻩＰAＧEＲＥＦ_Tｏｃ317７５99６7\h３３HYPＥＲLINＫ\l"＿Ｔoc317759968"2．内存ﻩPAＧEＲEF_Tｏｃ3１７７5996８\h33HYPEＲＬIＮK\ｌ”_Ｔoc31７７５9９69"２．1物理内存压力的检测ﻩPＡGERＥF＿Toｃ3１77５99６9＼h3３HYＰEＲLINK＼l”_Tｏc3１77５9９7０"２。2虚拟内存压力的检测ﻩPAGＥＲＥF_Toｃ３1７759970＼ｈ３４HYＰＥRLＩＮK\l"_Tｏｃ317759９71"２.３内存压力的隔离和排查ﻩPＡGＥＲＥF_Toc317７59９7１\ｈ35HＹPＥRＬＩNK＼ｌ”＿Toc３1７7５9９7２"3。I/OﻩＰAGEREF_Ｔoc３17７59９7２\h３6HYPＥＲＬINＫ\l”_Tｏc31７7５99７3"3.1Ｉ/O瓶颈的检测ﻩPAGEＲＥF_Ｔｏｃ３17759９７３＼ｈ3６ＨYＰＥRLIＮK\l”_Toc31７7５9９74”3.2I／Ｏ瓶颈的隔离和排查ﻩPAGERＥF_Toc３１７759974\h３7ＨYPＥRＬＩNK\ｌ"_Ｔoc317７599７５"4．tempdbﻩPAＧＥＲＥＦ＿Toｃ3１77５9975＼h３8HYPERLINK\ｌ"＿Toc317７5997６”4。１tｅmｐdｂ性能问题的检测ﻩPAＧEREＦ_Toc3１77５9976＼h38ＨＹPEＲＬＩNＫ\l＂_Toc31７7５997７”4.２tempdｂ瓶颈的隔离和排查ﻩPAＧＥＲEＦ_Toc317７59９７７＼h39HYＰEＲLINK\ｌ”_Ｔｏc3１７75９9７8”4.３检查ｔｅmpdb空闲空间方法ﻩPＡGEＲEF_Toc３17７5997８\h40HYPERLINK\ｌ"＿Ｔoc3１7759979＂5.堵塞 PAＧＥREＦ_Ｔoｃ3１77599７9＼ｈ４0HYPＥRLINK＼l"_Toc３1７7５998０”５。１堵塞的检测（Ｖ)ﻩPAＧEＲEＦ_Ｔoc３１７７５９98０\h40HＹPＥＲLＩＮK＼l”_Toc3177599８1”５.2隔离和排查堵塞故障ﻩPAGEREF_Tｏc31７75９981\h41ＨYPERLINＫ＼ｌ”_Tｏｃ3１775９9８２”6.死锁ﻩPＡGＥREＦ＿Toc3177599８2\h43ＨYＰERＬＩNＫ＼l"_Tｏc317759９83"6.1使用ｓys。dm_tｒａn_locｋsDＭV在给定的时间点探测表锁（X）ﻩＰＡGEＲEF_Tｏc3177５９９8３\ｈ４３ＨYPＥRLＩＮK6．２防止锁升级—设置表在１小时内防止锁升级ﻩＰAＧEREＦ_Ｔoｃ３１775９984＼ｈ４3HＹＰERＬINＫ\l＂＿Toｃ３１77599８5”6.３显示发生5秒以上的等待(V) ＰＡGＥＲEF_Toｃ３1775９985\ｈ43ＨYPERLＩNK\l”＿Tｏc317７599８6"6.4显示处于ＷAIT状态的锁（A)（Ｖ)ﻩＰＡGＥREF＿Toc３17７59986＼ｈ４4ＨYPEＲLINK＼l＂_Toc3１77５９9８7＂6.5显示每个等待资源已授权和等待中的锁（B）（Ｖ）ﻩPAGEＲＥF_Toc３１７７599８７\h４４HYPEＲLIＮＫ\ｌ＂_Ｔoc３1775998８＂6。6返回ｒｅsource＿asｓociatｅd_entｉty＿iｄ表示的实际对象（Ｃ）（V） PAGＥＲＥF_Toc３17759988＼h45HYPERLINＫ＼l”_Toｃ３1７7５99８9"6。７提取等待查询文本（Ｖ）ﻩPAGＥREＦ＿Toc31７7５９９89\h４６ＨＹPERLINK6.1０跟踪标记1204 ＰAＧERＥF_Toc317７59992\h48HＹPＥRＬＩＮK＼l”_Toc3１７759993"６.１1查看锁信息［存储过程１]ﻩPＡGEＲEF＿Toｃ317759993\h4８ＨYPEＲLＩNＫ\ｌ＂_Ｔoc３１77599９4＂６。１2查看锁信息［存储过程2］ ＰAGＥＲEF_Ｔoｃ31７7５９9９4\h４9HYＰＥRLIＮK\ｌ＂_Toc3１7７59995”7.排解故障ﻩPAGERＥF＿Ｔｏc317７５９99５\h５1HＹPERLIＮK\l”_Tｏｃ3177５999６”7．１2０７错误提示ﻩPAGEREF_Ｔoc31775９9９6\h５1HYPERLINＫ＼l”_Tｏc31７759997”7.2查看连接数 ＰAGERＥＦ＿Toc３１7７599９7\ｈ５1HYPERLＩNＫ\l”_Ｔoc3177599９8"8.信息查询ﻩＰＡＧEＲEF_Ｔoc317７5９9９８＼h51HＹPERＬINＫ\l＂＿Toc３177５９9９9”8．１查看数据库是否启用ＡWEﻩＰAGＥＲＥF_Ｔoc３17７５９99９＼h51ＨYＰERLINK9.存储引擎ﻩPAＧERＥＦ_Tｏc317760００1\ｈ52ＨYＰERＬINＫ\ｌ"_Ｔoc317７6０002＂９。１查看数据库信息（实例／数据库/文件信息） PAGEＲEＦ_Tｏc３177６0０02＼h52ＨYPEＲＬINK＼ｌ＂_Tｏｃ3１776００0３＂9。2查看数据库信息(表/对象)ﻩPAＧＥＲEF_Toｃ3１７７6００03\h５２ＨYPＥRLＩNＫ\l”_Toｃ３17７6００0４＂9。3查看数据库信息(对象存储视图）ﻩＰAＧＥREF_Toc３1７７６0０0４＼ｈ5４HＹPEＲLＩNK＼l"_Ｔｏc３1７7600０５”9．４查看页面结构ﻩPＡGＥREF_Toｃ3１7７600０５\h５６HYＰＥＲLＩＮＫ\ｌ＂_Toc31776０0０6”９.5ＰFS页面结构ﻩPAGERＥＦ_Toｃ31７760006＼h５8HYPEＲLＩNＫ\l”_Ｔoc３17760007"９.６ＩＡM页面结构 PＡGEＲEＦ＿Toｃ3１77６0007＼ｈ58ＨYＰEＲLIＮK9。8猎取表的页面信息ﻩＰＡGEＲＥF＿Ｔoc3177６０0０9＼h61HYPERLINK\l”_Toc3177６001０"９．9转换页面地址为页号ﻩPAＧEREF＿Tｏc317760０10\ｈ６２HYPＥRＬIＮK1０。1ＳQＬＳERVER等待类型ﻩＰＡＧＥRＥF_Tｏｃ317760０1２＼h63ﻬ1)．优化方法论—＞分析实例的等待－>联系等待和队列—>确定方案—>细化到数据库／文件级—＞细化到进程级—＞优化索引／查询1。分析实例的等待１.1返回系统中的等待(按类型排序）-－SQＬSｅrｖeｒ2005SELECＴｗaiｔ_typｅ，ｗａitｉnｇ_tasks_count，wａiｔ＿time＿ms，max_waiｔ_ｔime＿ms，signal＿ｗａit_ｔime＿msFRＯMsys．ｄm_os_ｗａit＿statsOＲDERBYwａit＿tｙpe；-—ＳQLＳeｒｖeｒ２000ＤBＣＣＳQLＰEＲＦ(ＷAＩTＳTＡTS);-－列解释列名数据类型说明ｗait_ｔypeｎvarchar(60)等待类型的名称。waitiｎg＿tａsks_ｃoｕntbigint该等待类型的等待数。该计数器在每开头一个等待时便会增加。ｗait＿tｉｍe_ｍｓbigiｎｔ该等待类型的总等待时间（毫秒）。此时间包含signal_wait＿tｉｍｅ。ｍaｘ_waiｔ＿ｔime_mｓｂｉｇｉｎt该等待类型的最长等待时间。sｉｇnaｌ_wait＿timｅbｉｇinｔ正在等待的线程从收到信号通知到其开头运行之间的时差。—-行解释(ｓｑl2０05联机帮助）ms-helｐ：//MS。SQＬCC。ｖ9／MS。SＱLSＶR。ｖ9.zｈ-CHS/tsｑlｒeｆ９/ｈtml/５６８ｄ89ｅd—2c9６—４７９5—8a0ｃ-２f３e375081ｄa.ｈtｍ注意：这些统计信息在每次重新启动SQLServer时都不能持续存在，并且全部的数据均为自上次重置统计信息或启动服务器以来累积的数据。１.２重置系统中的等待-－ＳＱLＳeｒｖｅｒ2005－—该命令将全部计数器重置为０。-－或重启数据库服务器DBCCSＱLPERF(＇syｓ.ｄｍ_oｓ_wａｉt_stats’，ＣＬＥAＲ）;--SＱLＳerｖｅｒ２000DBCCSQLPＥＲF(ＷＡITSTATS，CLＥAR）；１。3返回系统中的等待（9０％重量级)WＩTHWaitｓAS(SＥLECＴwait_tyｐｅ，wａit_tiｍe＿ms／１0０0。ASwait＿timｅ＿s，１00。*wａｉt＿time＿ｍs／ＳUＭ(wａit_time＿ｍs)ＯＶER（）ASpct，ROＷ_NＵＭBＥR（）OＶER(ORＤERBＹｗait＿ｔｉme＿ｍsＤESC）ＡSrｎＦROMsys。dm＿oｓ＿ｗaｉt_sｔaｔsＷHERＥｗaiｔ_typｅＮOＴＬIKE’%SLEEP％'-—filtｅroｕtaddｉｔｉｏnａｌirrｅｌｅvaｎtwaitｓ）SＥLECＴW１。ｗaｉｔ_ｔype,ＣAＳT(W1.wait_tｉme_sＡSＤECIMAL(１２,2)）ASｗait_ｔiｍｅ＿s，CAＳＴ(W1.ｐcｔASDECＩMAL(12,２）)ＡＳpct，CＡSＴ（SUＭ（W2.pｃt)ＡSDEＣIMAL(1２，２))ASrunninｇ＿pｃtFROMＷaiｔsＡSW1ＪOINWａｉｔsＡSＷ2OＮW２．ｒn＜＝W1.rｎＧROUPBＹW1．rn,Ｗ1．ｗait＿tｙpe,W1．waｉt_tiｍe＿s，W1.pctＨAVＩNGＳUM(W２。ｐct）-Ｗ1。ｐｃt〈９0-—pｅｒｃｅnｔａgｅthrｅshｏｌｄＯＲＷ１。ｒn＜=1０—－至少１0行［可选条件］ＯRDERＢYW1．rn;GO1。4制作等待分布图TＳQL－查询Pａge76，可以得到一天中各种类型等待发生什么时间。2.联系等待和队列主要依据上面１中结果,分析计数器。分析过程概述见TSQL—查询Page80－－ＳＱLServｅr２00５SEＬＥCＴｏbjeｃt＿namｅ，couｎteｒ_ｎame,iｎsｔａnｃe_naｍe,cｎtr_vaｌｕe，cntr_typｅFROＭsｙs。dm_oｓ_ｐeｒformａnce＿cｏｕnters;－—ＳＱLＳｅrvｅｒ200０ＳＥLECTobjｅｃt_nａｍe，coｕntｅｒ_nａme,iｎstancｅ_ｎａme,cｎｔr_ｖaｌuｅ,cｎtr＿tｙpeFＲＯＭmaｓtｅｒ。dbｏ。syspｅｒｆiｎfｏ；GO3．确定方案4.细化到数据库/文件级［Page82］以I/Ｏ为例，如下语句可以查询哪个数据库中的哪类文件导致大部分I/O及等待：-—AｎａlｙzeDBIOＷＩTHＤBＩOＡS（SＥLECＴDB_NAME（IVFS．daｔabａｓe_id）ＡSｄb，ＣＡSEWHENMＦ。ｔype＝１THＥN＇ｌog'EＬＳE'daｔａ'ENDASfile_typｅ,ＳＵM（ＩVＦS。nuｍ_of_ｂytｅｓ_reａd+IＶFS.nｕm_of_bｙtｅｓ_ｗｒitten）ＡSｉｏ，SUＭ（IＶFＳ．io_stａlｌ)ASio_ｓtａｌｌFＲOMsys。ｄm_iｏ＿ｖｉｒｔual_ｆiｌe_ｓtats（NULL,NＵＬL）AＳＩVFSJＯINｓｙs。masｔer_fｉleｓASMFONIＶFＳ．ｄaｔａｂase_iｄ=ＭF.dａtａbase＿idANDＩVＦＳ.fｉｌｅ_ｉd=MF．ｆｉle_ｉdGROＵPＢYDＢ_NAMＥ（IVＦＳ。dａｔａbase_id），ＭＦ．typｅ)ＳELEＣＴｄb，file＿ｔyｐe，CＡST（1．＊iｏ／(１02４＊10２4)ＡＳDEＣIMＡL（12,2）)AＳio_ｍｂ，CＡST（io_sｔaｌl/1０0０.ASＤＥCIＭAＬ（1２,2))ＡＳio_stａｌl_s，ＣＡＳT（100．*iｏ_staｌl/SＵM(iｏ＿ｓｔａlｌ)OVER()ＡＳDECIＭAL（10,2)）ＡSｉo_sｔａlｌ_pct,ＲOＷ＿ＮUＭBER（）OＶEＲ（ＯRDERBYio＿ｓtａllDESC）AＳrｎFRＯMDBＩOOＲDＥRＢYｉｏ_stａlｌDＥSC；－—SQＬSerｖeｒ2００0SELEＣT＊FROM:：fn_virｔuaｌfｉleｓtats(1５,１）；GO5.细化到进程级(指存储过程，查询等）5.１生成跟踪存储过程（附件：SQL跟踪存储过程。txｔ）ﻩﻩ5.２调用跟踪存储过程——StａrtthetｒaceDECＬＡRE＠ｄbidASINT,＠trａceiｄＡＳＩＮT；ＳＥT@dbiｄ＝ＤＢ_ＩD(＇Peｒｆormancｅ’)；EXＥCdbｏ。sp_pｅｒfwｏrkｌｏａd_ｔrace_ｓtａrt＠dｂｉｄ=@dｂiｄ，＠tｒaｃeｆiｌｅ='c:\temp\Perfwｏrｋloaｄ＇，--c：＼ｔeｍp\Ｐｅrfwoｒｋｌｏａd.trc＠traｃeid=@ｔｒａceｉｄOＵTＰＵT；ＧO-－Sｔopthｅｔrace（不要与上面在一块执行）EXEＣｓｐ_tｒaｃe_sｅｔsｔaｔus2,0；－－停止EＸEＣｓp＿tｒaｃe＿setstａｔus2，２；-－移除GO—－s