SQLServer2005SQL查询优化
目录
SQLServer2005:SQL查询优化..............................................................................................错误!未定义书签.错误!未定义书签.实验安装.....................................................................................................................................................................44练习一:使用SQLServerProfiler工具解决死锁问题..........................................................................................5练习二:使用SQLServerProfiler工具隔离运行速度慢的查询语句..................................................................9练习三:检查执行计划...........................................................................................................................................11练习四:使用数据库引擎优化顾问工具(DatabaseTuning
Advisor).........................................................12
SQLServer2005SQL查询优化
SQLServer2005SQL查询优化
目标完成本实验之后,你可以实现以下目标:使用SQLServerProfiler工具解决死锁问题
注释:注释本实验侧重于这个模块
中的概念,因此不必遵循微软的安全建议.
注释:注释SQLServer2005的最新详细资料,请访问microsoft/sql/.
为一个低性能查询制定一个查询计划,并将它以XML格式的文档保存.使用数据库引擎优化顾问工具(DatabaseTuningAdvisor)
场景
假设你是AdventureWorks数据库的数据库管理员.你的数据库用户经常遇到死锁问题而且你很关心死锁是不是导致系统性能低的一个原因.你已经隔离了一个经常与死锁有关的查询.你将使用SQLServerProfiler工具来跟踪导致死锁的事件并详细记录死锁的信息.追踪到死锁的原因之后,你发现这个原因并不是导致系统性能下降的主要原因,所以你决定检查那些关键的查询.通过检测为那些关键查询制定的查询计划来分析它们,然后你可以使用索引优化顾问工具来提出最适当的索引.
前提条件
SQLServer2000管理任务的基本经验熟悉T-SQL语言L完成SQLServerManagementStudio的动手实验.45分钟
估计完成实验所需时间
Page3of12
SQLServer2005SQL查询优化
实验安装
任务1.登录
详细步骤1.使用administrator账户登录,密码是Pass@word1.
Page4of12
SQLServer2005SQL查询优化
练习一使用SQLServerProfiler工具解决死锁问题
场景
在这个练习中,你会使用SQLServerProfiler工具捕获死锁信息.死锁发生时,这两个进程互相阻塞对方,因为每个进程占有一个排他锁阻碍了另一个进程完成其事务.当两个进程正试图以不同的顺序访问同一数据库表中的记录时也会发生死锁.死锁可以涉及两个以上的进程.举例来说,进程A阻塞进程B,B阻塞进程C,C阻塞进程A.SQLServer检测进程死锁是通过定期寻找阻塞进程的"循环"链条.一旦检测到一个死锁,SQLServer将选择其中一个进程,作为"牺牲品",中断它的会话并回滚它当前的事务,释放所有进程占有的锁,并允许任何受阻塞的进程继续正常执行.谨慎地设计和良好的索引可以减少死锁发生的可能性.最好的预防措施就是使你的事务尽可能的短而且绝不允许跨越成批处理的事务.另外,无论如何你都应该按同样的顺序访问数据表.例如,如果你有几个不同的进程需要修改三个同样的表,它们应该按照同样的顺序修改这些表.即使使用谨慎的计划,也不可能完全避免死锁.它们一旦发生,你可以使用SQLServerProfiler工具去捕获那些引起死锁的事件来裁决陷入死锁的实际对象和TSQL语句.SQLServer2005允许你可以使用XML文档来保存死锁图表,最终通过SQLServerProfiler工具中的工具来读取死锁的XML文档和显示死锁图表,它可以清楚地指示死锁中的进程所申请的资源,哪些锁被允许的,在什么地方会在死锁检测之前发生阻塞.任务1.定义一个跟踪捕获的死锁信息详细步骤1.2.3.4.在Windows任务栏中,选择开始|程序MicrosoftSQLServer2005|性能工开始程序|具|SQLServerProfiler菜单项.点击新建追踪新建追踪按钮(SQLServerProfiler工具栏的第一个按钮).新建追踪当连接到服务器连接到服务器对话框出现时,验证服务器类型服务器类型设置为数据库引擎服务器数据库引擎,连接到服务器服务器类型数据库引擎名称设置为localhost,身份验证选择Windows身份验证点击连接身份验证.连接.名称身份验证如果SQLServerProfiler信息框显示服务器获取的跟踪文件,点击确定确定.
打开跟踪属性对话框.第一个标签常规类似于Server2000Profiler跟踪定义的常规,常规常规标签.5.6.在跟踪名称跟踪名称文本框中,输入Deadlock1.在使用模板使用模板下拉列表中,选择空白空白.跟踪名称使用模板选择保存到文件保存到文件复选框并将目录改为C:\MSLabs\SQLServer2005\User保存到文件Projects.使用默认文件名Deadlock1.trc.点击保存将跟踪属性保存.跟踪属性跟踪属性对话框的其他选项设置为默认值.点击事件选择事件选择标签.事件选择
7.注释:不是所有的数据项注释对于每个事件都是可用的.对于SQLServer2000Profiler同样适用,但是在选择数据选项的方式有明显的不同.例如,性能数据选项,像持续时间,可读性,和可写性对于开始事件并非都可用.这些性能数据都只能用于像
SQLServer2005在SQLServerProfiler中捕获事件的定义过程不同于SQLServer2000.事件是在展开之后的事件种类中进行选择的,然后选中那些你想捕获的事件.8.通过复选框选中下列事件并确保它们所有可用的数据值被选中:Locks的种类::oDeadlockgraphoLock:DeadlockChainStoredProcedures的种类:
oRPC:Completed
Page5of12
SQLServer2005SQL查询优化任务SP:Completed和SP:StmtCompleted.另外,一些事件只有EventSubClass,BinaryData和IntegerData而其他类型的数据则没有.详细步骤
oRPC:StartingoSP:StmtCompletedoSP:StmtStarting
在2005SQLServerProfiler中,过滤器被设置在事件选择事件选择屏幕中.事件选择9.向右滚动水平滚动条直到你找到DatabaseID列.你可以通过增加列的宽度来看列的完整名称.
10.右键单击DatabaseID列标题和选中编辑列筛选器编辑列筛选器展开不等于不等于节点,并在方不等于框中填写数字4.点击确定过滤器将筛选掉msdb数据库中的所有行为.确定.
注释:注释你不需要为这个练习申请任何附加的过滤器.但是你可能要探索哪些数据允许过滤,哪些数据不允许过滤.一些数据允许一定范围内的过滤.你可以使用过滤器来指定与条件匹配的具体数据或通过大于或小于操作符获取一定范围内的指定数据.其它数据项允许模式匹配的过滤器,你可以使用"类似于"或"不类似于"操作符.如果你右键单击任何一个数据项或事件名称,其快捷菜单包括选项组织列.这个选项允许你可以重新输出数据项的显示顺序.
11.右键单击DatabaseName列标题选中编辑列筛选器展开不类似于编辑列筛选器.不类似于节点,并编辑列筛选器不类似于在方框中填写ReportServer.点击确定过滤器将筛选掉ReportServer数据确定.确定库中的所有行为.12.点击事件提取设置事件提取设置标签.选中分别保存死锁XML事件事件复选框.验证当前目事件提取设置分别保存死锁录是否为C:\MSLabs\SQLServer2005\UserProjects,在文件名文件名输入文件名"XMLDeadlock",然后点击保存保存.保存13.点击选中不同文件中的每个死锁XML批,然后点击运行跟踪就开始了.运行.运行
2.
产生一个多表的死锁
1.2.3.
在Windows任务栏中,选择开始|程序MicrosoftSQLServer2005|SQL程序|ServerManagementStudio.当连接到服务器连接到服务器对话框出现时,输入localhost作为服务器名称并验证服务器名称,连接到服务器服务器名称Windows身份验证被选中为身份验证方法.点击连接身份验证连接.连接选中文件|打开|项目解决方案菜单项,打开C:\MSLabs\SQLServer项目/解决方案文件2005\LabProjects\TuningLab\Exercise1.然后选中Exercise1.ssmssln并单击打开打开.打开在解决方案资源管理器解决方案资源管理器窗口中,在查询查询文件夹下双击Copytables.sql打开解决方案资源管理器查询SQL脚本.按F5或单击执行执行工具栏按钮.执行
4.5.
此脚本执行需要花费几分钟,不过时间的长短取决于机器的运行速度.该脚本从AdventureWorks数据库复制了几个表.当你在下列任务中修改数据时,修改的是表的副本,所以任何修改都不会影响原来的数据.检查完这些语句之后,可以关闭脚本..6.打开Firstpartmultitabledeadlock.sql.这个脚本允许你产生死锁.这种情况下你可以将Vendors活动状态更改为非活动状态.你也可以修改Vendors的联系方式来区别不同的所有者.
Page6of12
SQLServer2005SQL查询优化任务详细步骤在Firstpartmultitabledeadlock.sql中可以修改VendorID=1的NewVendor数据,然后你可以试着修改VendorID=1的NewVendorContact数据.不过你不要一次执行完整个脚本.注释:注释你已经执行了BEGINTRAN但是没有执行COMMITTRAN或ROLLBACKTRAN.一般想避开死锁就不应该这样运行,因为事务持有的公开交叉成批处理有更多的机会陷入死锁.但是这个练习故意建议去引起死锁..7.8.选中脚本的一部分,开始于第一行并在读到语句Onlyexecuteuptothispointinthefirstbatch之前结束,按F5或单击执行按钮.,执行打开Secondpartmultitabledeadlock.sql.在Secondpartmultitabledeadlock.sql中可以修改VendorID=15的NewVendorContact数据,然后你可以试着修改VendorID=15的NewVendor数据,不过你不要一次执行完整个脚本..9.选中脚本的一部分,从第一行开始直到读至语句行Onlyexecuteuptothispointinthefirstbatch.按F5或单击执行按钮.执行
10.在代码窗口右上角使用倒立箭标(活动的文件)选中Firstpartmultitabledeadlock.sql.只选中事务中的第二个UPDATE语句,在语句Next,startexecutionhere之后并点击执行按钮.执行这个修改语句不要全部执行,因为它会被在Secondpartmultitabledeadlock.sql脚本事务中的第一个修改语句所阻挡..但这不是死锁,因为只有一个阻塞的进程和一个被阻塞的进程.11.回到查询窗口的Secondpartmultitabledeadlock.sql.只选中事务中的第二个UPDATE语句,这个语句是在语句Next,startexecutionhere之后开始的,然后单击执行按钮.执行在Firstpartmultitabledeadlock.sql脚本事务中的第二个修改语句将会被第一个修改语句所阻塞.因为两个进程相互阻塞,这才真正的死锁.SQLServer将会监测到这个死锁并选择一个进程作为"牺牲品"放弃执行.你可以在结果结果窗口结果中看到这个关于死锁脚本的消息(进程ID可能与你机器上结果不同):
消息1205,级别13,状态45,第1行事务(进程ID57)与另一个进程被死锁在请重新运行该事务.
锁
资源上,并且已被选作牺牲品.
一旦产生死锁的错误消息时,这个事务将会被回滚,而且所有的死锁将会被释放.另一个"获胜"的事务将不会被阻塞可以完成它的第二个修改.12.运行事务中的最后一行(ROLLBACKTRAN),回滚那个事务,不过那不是死锁的牺牲品..
注释:没有成为死锁牺牲品的事务仍然需要决定是提交还是回滚,尽管它已经完成它的两个修改.因为它既没有提交,也没有回滚.
Page7of12
SQLServer2005SQL查询优化任务3.检查捕获到的死锁信息详细步骤1.在SQLServerProfiler中,返回到你运行的跟踪中,单击停止所选跟踪停止所选跟踪按停止所选跟踪钮,这个按钮有一个正方形的红色图标.你将会在靠近追踪的末端看到以Lock:DeadlockChain开头的两行记录.选中在Lock:DeadlockChain事件的下方的DeadlockGraph事件.当你在Profiler中选中了这个事件,DeadlockGraph信息出现在Profiler窗口的底部,类似于图表1中显示的.
2.
提示:提示通过在死锁图中椭
圆的上方,移动指针来观察查询的内容.
图表1:DeadlockGraph示例图这个图表直观显示了死锁中的进程,包括死锁进程所持有的死锁类型和模式.死锁类型就是每个进程等待的和一些死锁持有的实际资源的信息.
Page8of12
SQLServer2005SQL查询优化
练习二使用SQLProfiler隔离运行速度慢的查询
场景
在这个练习中,你将使用SQLServerProfiler去隔离一个在一批查询中的低性能查询.任务1.定义一个跟踪来捕获查询的性能信息详细步骤1.2.3.如果SQLServerProfiler没有被加载,则在Windows任务栏中,选中开始|开始程序|性能工具|程序MicrosoftSQLServer2005|性能工具SQLServerProfiler菜单.性能工具点击新建跟踪新建跟踪按钮(SQLServerProfiler工具栏的第一个按钮).新建跟踪当连接到服务器连接到服务器对话框打开时,检查服务器类型服务器类型设置为DatabaseEngine,服连接到服务器服务器类型务器名称设置为localhost,Windows身份验证被选中作为身份验证方法.务器名称身份验证单击连接连接.在跟踪名称跟踪名称文本框中,输入TuneBatch.在使用模板的下拉列表中,选择.使用模板TSQL.选中保存到文件保存到文件的复选框,并确认目录为C:\MSLabs\SQLServer保存到文件2005\UserProjects.使用默认的文件名TuneBatch.trc.点击保存将跟踪属保存,性对话框的其他选项设置为默认值.点击事件选择事件选择标签.选中显示所有事件显示所有列另外这些被选中的事显示所有事件和显示所有列显示所有列.事件选择件都是模板中默认的,选择下列事件,并且包括它们所有可用的数据值.Performance的种类:
4.
5.
oShowplanAlloShowplanXML
StoredProcedures的种类:
oSP:StmtCompleted
TSQL的种类:
oSQL:BatchCompleted
6.右键单击DatabaseName列并选中编辑列筛选器展开选项类似于并填入编辑列筛选器.编辑列筛选器类似于"AdventureWorks".点击确定确定.点击事件提取设置事件提取设置标签.选中分别保存XML显示计划事件显示计划事件复选框.验证目事件提取设置分别保存标目录是C:\MSLabs\SQLServer2005\UserProjects,输入文件名XMLQueryPlans,并单击保存保存.保存单击不同文件中的每个XML显示计划批然后单击运行跟踪就开始了.显示计划批,运行.运行如果你事先没有打开MicrosoftSQLServerManagementStudio,请先打开它.点击文件|打开|项目解决方案项目/解决方案解决方案打开C:\MSLabs\SQLServer2005\LabProjects\TuningLab\Exercise2.选中Exercise2.ssmssln并点击打开打开.打开在解决方案资源管理器解决方案资源管理器窗口中,在查询文件夹下双击BatchToTune.sql,来解决方案资源管理器打开SQL脚本.注释:如果你收到这样的一个错误提示"无法打开文件...C:\MSLabs\...\BatchToTune.sql."然后点击确定并再试一次.
除了AdventureWorks数据库中选中的事件,过滤器能够筛选掉所有的行为7.
8.2.隔离一个在一批查询中的低性能查询1.2.3.
Page9of12
SQLServer2005SQL查询优化任务详细步骤4.5.6.7.8.按F5或单击执行工具栏按钮.执行一旦脚本执行完毕,立即返回SQLServerProfiler运行的跟踪并通过单击工具栏中红色正方形区域的停止所选跟踪按钮所选跟踪按钮所选跟踪按钮,停止跟踪.向右滚动水平滚动条找Duration列.只有一部分事件的持续时间被列出.隔离掉那个在Duration列中数值最大的事件.在Profiler输出中选择该行.你将会在Profiler窗口中察看到那个查询.在你的C:\MSLabs\SQLServer2005\UserProjects\目录.你应该会发现几个.SQLPlan文件.选中那个最近被访问的文件,然后通过右键单击文件名选择打开,这个文件将在ManagementStudio中打开.打开,以XML格式保存的查询计划可以让第三方的vendors通过开发工具来说明和实现它们.另外,一个XML格式的查询计划可以被送给供应商,他们可以以图表的格式察看它并且可以在SQLServerManagementStudio中看到同样的输出.
Page10of12
SQLServer2005SQL查询优化
练习三检查执行计划
场景
在这项练习中,你将会使用包括实际的执行计划包括实际的执行计划选项检查为低性能查询制定的执行计划.包括实际的执行计划任务1.检查为低性能查询的统计数字和执行计划.详细步骤1.2.3.如果事先没有打开MicrosoftSQLServerManagementStudio,请先打开它.点击文件|打开|项目解决方案项目/解决方案解决方案打开C:\MSLabs\SQLServer2005\Lab文件Projects\TuningLab\Exercise3.选中Exercise3.sssmssln并单击打开打开.打开在解决方案资源管理器解决方案资源管理器窗口中,在查询文件夹下双击Slowquery.sql,打开解决方案资源管理器SQL脚本.注释:如果你收到一个错误指示"无法打开文件...C:\MSLabs\...\Slowquery.sql."然后点击确定确定并再试一次.确定4.5.6.7.右键单击查询窗口的任何地方,并单击查询选项查询选项.查询选项在左侧的面板中,选中执行|高级并选中SETSTATISTICSTIME的复选框.点击确定确定.右键单击查询窗口的任何地方,并单击包括实际的执行计划包括实际的执行计划(你也可以从工包括实际的执行计划具栏中的查询查询菜单中选择该选项).查询切换到SQLServerProfiler并通过单击启动所选跟踪启动所选跟踪按钮运行TuneBatch启动所选跟踪跟踪绿色箭标.如果你收到"已重置跟踪数据保存选项.,是不是在不保存输出的情况下继续操作?"的信息,点击是.切换到ManagementStudio执行查询.点击结果窗口中右侧的执行计划结果执行计划执行计划标签.
8.9.
注意:所有连接操作都是通过使用HashMatch连接来执行的.如果查询没有有效的索引时,SQLServer将使用hash连接.这就意味着需要优化.因为这些表没有索引,因此所有表的访问都是通过表的扫描来实现的.10.切换到SQLServerProfiler,选中执行SELECT查询的SQL:BatchCompleted事件,然后记录Duration列的数值,这个值你将会在下个练习中涉及到.
Page11of12
SQLServer2005SQL查询优化
练习四使用数据库引擎优化顾问工具(DatabaseTuningAdvisor)数据库引擎优化顾问工具使用数据库引擎优化顾问工具
场景
在这项练习中,你将使用数据库引擎优化顾问工具为你在练习三中检查低性能的查询建议索引.数据库引擎优化顾问工具是SQLServer2005索引优化向导工具的后续.对于这个练习,你只能优化单个的查询,但是数据库引擎优化顾问工具能够优化SQLServerProfiler跟踪捕获的一个事件的全部负载.这个负载能够涵盖在现实条件下跨越几个小时的多个查询.任务1.使用数据库引擎优化顾问为单个查询建议索引.详细步骤1.2.3.4.返回到SQLServerProfiler并通过点击停止所选跟踪停止所选跟踪按钮来停止任何运行停止所选跟踪的跟踪.选中工具|数据库引擎优化顾问,提供连接信息单击连接连接,(耐心点.加载这工具数据库引擎优化顾问,连接个对话框可能需要花一段时间.).在常规常规标签中,填写一个会话名称TuneQuery.常规对于工作负荷选中文件点击浏览工作负荷文件工作负荷,文件.浏览工作负荷文件按钮(望远镜图标的按钮,工作负荷浏览工作负荷文件在工作负荷文本框的左侧).在选择工作负荷文件工作负荷选择工作负荷文件对话框中,将文件类型文件类型更改选择工作负荷文件为SQL脚本(*.sql).浏览C:\MSLabs\SQLServer2005\LabProjects\TuningLab\Exercise3选中Slowquery.sql文件点击打开文件,打开.在常规标签的下半部分选择要优化的数据库和表选择要优化的数据库和表列表中,选中常规选择要优化的数据库和表AdventureWorks数据库复选框.检查优化选项优化选项标签,它包括高级选项不要改变高级选项高级选项.高级选项对话框中任何设优化选项高级选项置..点击开始分析开始分析按钮(绿色箭标)开始分析.等待分析完成,大约需要1到2分开始分析钟.当分析完成后,你将会看到三个标签:进度建议和报告点击建议标签并进度,建议,报告.建议清除标签底部的显示现有对象显示现有对象复选框(它在标签的底部用来显示重新执行显示现有对象的变化).
5.6.7.8.9.
10.选择操作|应用建议操作应用建议菜单项在应用建议应用建议对话框中单击确定验证立即应用确定,立即应用被应用建议确定立即应用选中.11.关闭数据库引擎优化顾问.12.在SQLServerProfiler中,重起TuneBatch跟踪.13.在SQLServerManagementStudio中返回到Slowquery.sql窗口,执行查询.14.在SQLServerProfiler中.,点击工具栏的红色方块区域的停止所选跟踪停止所选跟踪按停止所选跟踪钮,停止跟踪.检查SELECT语句的持续时间,它应该低于比你使用数据库引擎优化顾问工具建议索引之前在练习三中接收到的值.
Page12of12
