SQL Server 2005
  　SQL 查询优化



                                目录
SQL Server 2005:SQL查询优化    3
实验安装    4
练习一 ：使用SQL Server Profiler工具解决死锁问题    5
练习二： 使用SQL Server Profiler工具隔离运行速度慢的查询语句    9
练习三： 检查执行计划     11
练习四： 使用数据库引擎优化顾问工具(Database Tuning Advisor)    12



SQL Server 2005 SQL查询优化
目标
注释:  本实验侧重于这个模块中的概念，因此不必遵循微软的安全建议。
注释:  SQL Server 2005的最新详细资料, 请访问www.microsoft/sql/.
完成本实验之后, 你可以实现以下目标:
▪ 使用SQL Server Profiler工具解决死锁问题
▪ 为一个低性能查询制定一个查询计划，并将它以XML格式的文档保存。
▪ 使用数据库引擎优化顾问工具(Database Tuning Advisor)
场景
假设你是AdventureWorks 数据库的数据库管理员.
你的数据库用户经常遇到死锁问题而且你很关心死锁是不是导致系统性能低的一个原因。你已经隔离了一个经常与死锁有关的查询。你将使用SQL Server Profiler工具来跟踪导致死锁的事件并详细记录死锁的信息。
追踪到死锁的原因之后，你发现这个原因并不是导致系统性能下降的主要原因，所以你决定检查那些关键的查询。通过检测为那些关键查询制定的查询计划来分析它们，然后你可以使用索引优化顾问工具来提出最适当的索引。
前提条件
▪ SQL Server 2000管理任务的基本经验
▪ 熟悉T-SQＬ语言
▪ 完成SQL Server Management Studio 的动手实验。
估计完成实验所需时间
45分钟


实验安装
任务
详细步骤
1. 登录
1. 使用administrator账户登录，密码是Pass@word1.

练习一
使用SQL Server  Profiler工具解决死锁问题
场景
在这个练习中，你会使用SQL Server Profiler工具捕获死锁信息。死锁发生时，这两个进程互相阻塞对方，因为每个进程占有一个排他锁阻碍了另一个进程完成其事务。
当两个进程正试图以不同的顺序访问同一数据库表中的记录时也会发生死锁。死锁可以涉
及两个以上的进程。举例来说，进程Ａ阻塞进程Ｂ，Ｂ阻塞进程Ｃ，Ｃ阻塞进程Ａ．
  SQL Server检测进程死锁是通过定期寻阻塞进程的"循环"链条。一旦检测到一个死锁，SQL Server将选择其中一个进程，作为"牺牲品" ，中断它的会话并回滚它当前的事务，释放所有进程占有的锁，并允许任何受阻塞的进程继续正常执行。
  谨慎地设计和良好的索引可以减少死锁发生的可能性. 最好的预防措施就是使你的事务尽可能的短而且绝不允许跨越成批处理的事务。另外, 无论如何你都应该按同样的顺序访问数据表。例如, 如果你有几个不同的进程需要修改三个同样的表, 它们应该按照同样的顺序修改这些表。即使使用谨慎的计划，，，也不可能完全避免死锁. 它们一旦发生，你可以使用SQL Server Profiler工具去捕获那些引起死锁的事件来裁决陷入死锁的实际对象和TSQL语句。
  SQL Server 2005 允许你可以使用XML文档来保存死锁图表, 最终通过SQL Server Profiler工具中的工具来读取死锁的XML文档和显示死锁图表, 它可以清楚地指示死锁中的进程所申请的资源, 哪些锁被允许的， 在什么地方会在死锁检测之前发生阻塞。
任务
详细步骤
1. 定义一个跟踪捕获的死锁信息
注释:不是所有的数据项对于每个事件都是可用的.  对于SQL Server 2000 Profiler同样适用, 但是在选择数据选项的方式有明显的不同。例如, 性能数据选项, 像持续时间, 可读性, 和可写性对于开始事件并非都可用。这些性能数据都只能用于像SP:Completed  和SP:StmtCompleted 。另外, 一些事件只有 EventSubClass,BinaryData和IntegerData而其他类型的数据则没有。
注释:  你不需要为这个练习申请任何附加的过滤器。但是你可能要探索哪些数据允许过滤，哪些数据不允许过滤。一些数据允许一定范围内的过滤.你可以使用过滤器来指定与条件匹配的具体数据或通过大于或小于操作符获取一定范围内的指定数据。其它数据项允许模式匹配的过滤器,你可以使用“类似于” 或 “不类似于” 操作符。如果你右键单击任何一个数据项或事件名称，其快捷菜单包括选项 组织列. 这个选项允许你可以重新输出数据项的显示顺序。
1. 在 Windows 任务栏中, 选择开始 | 程序| Microsoft SQL Server 2005 | 性能工具| SQL Server Profiler 菜单项.
2. 点击新建追踪按钮 (SQL Server Profiler工具栏的第一个按钮).
3. 当连接到服务器对话框出现时, 验证服务器类型设置为数据库引擎, 服务器名称设置为localhost, 身份验证选择Windows 身份验证. 点击 连接.
4. 如果SQL Server Profiler信息框显示服务器获取的跟踪文件，点击 确定.
打开跟踪属性对话框. 第一个标签常规, 类似于Server 2000 Profiler跟踪定义的 常规 标签.
5. 在跟踪名称文本框中, 输入 Deadlock1. 在使用模板下拉列表中, 选择 空白.
6. 选择保存到文件复选框并将目录改为C:\MSLabs\SQL Server 2005\User Projects. 使用默认文件名 点击 保存. 将跟踪属性对话框的其他选项设置为默认值。
7. 点击事件选择标签.
  SQL Server 2005在SQL Server Profiler中捕获事件的定义过程不同于SQL Server 2000. 事件是在展开之后的事件种类中进行选择的，然后选中那些你想捕获的事件。
8. 通过复选框选中下列事件并确保它们所有可用的数据值被选中:
▪ Locks 的种类：:
o Deadlock graph
o Lock: Deadlock Chain
▪ Stored Procedures 的种类:
o RPC:Completed
o RPC:Starting
o SP:StmtCompleted
o SP:StmtStarting
在2005 SQL Server Profiler中, 过滤器被设置在事件选择屏幕中。
9. 向右滚动水平滚动条直到你到DatabaseID 列. 你可以通过增加列的宽度来看列的完整名称。
10. 右键单击DatabaseID 列标题和选中 编辑列筛选器展开不等于节点,并在方框中填写数字４. 点击 确定. 过滤器将筛选掉msdb数据库中的所有行为.
11. 右键单击DatabaseName 列标题选中编辑列筛选器. 展开不类似于节点, 并在方框中填写ReportServer. 点击确定. 过滤器将筛选掉ReportServer 数据库中的所有行为.
12. 点击事件提取设置标签.  选中分别保存死锁XML事件复选框. 验证当前目录是否为C:\MSLabs\SQL Server 2005\User Projects, 在文件名输入 “XMLDeadlock”, 然后点击保存.
13. 点击选中 不同文件中的每个死锁XML 批, 然后点击运行. 跟踪就开始了。
2. 产生一个多表的死锁
注释: 你已经执行了BEGIN TRAN 但是没有执行COMMIT TRAN或ROLLBACK TRAN数据库优化sql语句. 一般想避开死锁就不应该这样运行，因为事务持有的公开交叉成批处理有更多的机会陷入死锁。但是这个练习故意建议去引起死锁。.
注释：没有成为死锁牺牲品的事务仍然需要决定是提交还是回滚，尽管它已经完成它的两个修改。因为它既没有提交，也没有回滚。
1. 在Windows 任务栏中, 选择 开始 | 程序| Microsoft SQL Server 2005 | SQL Server Management Studio.
2. 当连接到服务器对话框出现时, 输入 localhost 作为服务器名称, 并验证Windows 身份验证 被选中为身份验证 方法. 点击连接.
3. 选中文件 | 打开 | 项目/解决方案 菜单项，打开C:\MSLabs\SQL Server 2005\Lab Projects\Tuning Lab\Exercise 1.　然后选中Exercise 1.ssmssln 并单击打开.
4. 在解决方案资源管理器窗口中, 在查询文件夹下双击Copy tables.sql 打开SQL脚本.
5. 按F5 或单击执行工具栏按钮.
  此脚本执行需要花费几分钟，不过时间的长短取决于机器的运行速度。该脚本从AdventureWorks 数据库复制了几个表. 当你在下列任务中修改数据时，修改的是表的副本，所以任何修改都不会影响原来的数据. 检查完这些语句之后，可以关闭脚本。.
6. 打开First part multi table deadlock.sql.
  这个脚本允许你产生死锁. 这种情况下你可以将Vendors活动状态更改为非活动状态。你也可以修改Vendors的来区别不同的所有者。
  在First part multi table deadlock.sql中可以修改Vendor ID =1的NewVendor数据，然后你可以试着修改Vendor ID =1的 NewVendorContact数据. 不过你不要一次执行完整个脚本。
7. 选中脚本的一部分, 开始于第一行并在读到语句Only execute up to this point in the first batch之前结束， 按F5 或单击执行 按钮.
8. 打开Second part multi table deadlock.sql.
在Second part multi table deadlock.sql 中可以修改Vendor ID=15的NewVendorContact 数据, 然后你可以试着修改Vendor ID =15的NewVendor 数据, 不过你不要一次执行完整个脚本。.
9. 选中脚本的一部分，从第一行开始直到读至语句行Only execute up to this point in the first batch. 按 F5 或单击执行 按钮.
10. 在代码窗口右上角使用倒立箭标（活动的文件)选中First part multi table deadlock.sql. 只选中事务中的第二个UPDATE 语句, 在语句Next, start execution here之后 并点击执行 按钮.
这个修改语句不要全部执行, 因为它会被在Second part multi table deadlock.sql 脚本事务中的第一个修改语句所阻挡。. 但这不是死锁， 因为 只有一个阻塞的进程和一个被阻塞的进程。
11. 回到查询窗口的Second part multi table deadlock.sql. 只选中事务中的第二个UPDATE 语句, 这个语句是在语句Next, start execution here之后开始的, 然后单击执行 按钮.
在First part multi table deadlock.sql 脚本事务中的第二个修改语句将会被第一个修改语句所阻塞. 因为两个进程相互阻塞, 这才真正的死锁。
SQL Server 将会监测到这个死锁并选择一个进程作为“牺牲品”放弃执行。你可以在结果窗口中看到这个关于死锁脚本的消息 (进程ID 可能与你机器上结果不同):
消息1205, 级别 13, 状态 45, 第１行
事务 (进程ID 57) 与另一个进程被死锁在　锁　资源上，并且已被选作牺牲品。请重新运行该事务。
一旦产生死锁的错误消息时，这个事务将会被回滚，而且所有的死锁将会被释放。另一个“获胜” 的事务 将不会被阻塞可以完成它的第二个修改。
12. 运行事务中的最后一行(ROLLBACK TRAN) ，回滚那个事务，不过那不是死锁的牺牲品。.
3. 检查捕获到的死锁信息
提示: 通过在死锁图中椭圆的上方，移动指针来观察查询的内容。
1. 在SQL Server Profiler中，返回到你运行的跟踪中，单击停止所选跟踪按钮, 这个按钮有一个正方形的红图标. 你将会在靠近追踪的末端看到以Lock:Deadlock Chain开头的两行记录。
2. 选中在Lock:Deadlock Chain 事件的下方的Deadlock Graph 事件。当你在Profiler中选中了这个事件, Deadlock Graph 信息出现在Profiler窗口的底部, 类似于图表１中显示的.
图表１: Deadlock Graph示例图
这个图表直观显示了死锁中的进程，包括死锁进程所持有的死锁类型和模式。死锁类型就是每个进程等待的和一些死锁持有的实际资源的信息。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。