数据库SQL Server2000 迁移至 SQL Server2005 查询性能优化总结.doc

数据库SQL Server2000 迁移至 SQL Server2005 查询性能优化总结 测试数据库：***数据库 数据库版本：SQL Server 2005 sp2 迁移方式：分离/附加，备份/还原。 问题简述：数据库迁移后，其中2/5存储过程执行时间超时。 优化工具： 1.1、数据库引擎优化顾问 使用SQL2005自带的“数据库引擎优化顾问”，会根据存储过程中所用到的表自动建立对应索引，并生成统计信息。用此方法90%的存储过程可以达到SQL2000中的执行速度，甚至更快。但小部分存储过程仍无法到达满意效果。使用到临时表的存储过程此工具无法全部应用。 1.2、SQLServer2005_PerformanceDashboard 此工具是微软提供的一个数据库性能监视的报表工具。需要安装在SQLServer2005sp2下，该工具功能： CPU瓶颈（什么查询最占CPU） IO瓶颈 （什么查询执行最多的IO） 查询优化器产生的索引（缺失索引） 锁 使用 数据库引擎优化顾问 优化了华理数据库中2000个存储过程，其中200个存储过程因为以下几种原因不能使用“优化顾问”优化。 ※ 不能优化的原因分以下几点： 1：表名无效，找不到表。 2：对使用临时表的存储过程无法优化。 (优化过程中发现，有的存储过程如[tb_sp_GetEntryStudentList]。这个存储过程中也使用了临时表，但是这个PROC中是先CREATE了临时表，再INTO到这个临时表中，这个PROC使用“优化顾问”优化成功了。另外不能优化的PROC都是没有CREATE 直接INTO到临时表。但是这个过程在SQL05中优化过去了，连到SQL2000的数据库优化时依然报告“临时表无效”的错误信息) 3：（UPDATE失败/ INSERT失败/ DELETE失败）因为下列SET 选项的设置不正确:QUOTED_IDENTIFIER。请确保 SET 选项可正确用于计算列和/或查询通知和/或 xml 数据类型方法的索引视图和/或索引。 大部分为临时表不能优化。 优化方案 ⑴：手工优化： 测试存储过程：tb_sp_GetDistributeResult 测试条件： 6,-1,-1,-1,-1,-1,,,,-1 SQL2005 首 次 执 行： ‘01：14’ 1521行 第 2 次执行： ‘01：11’ SQL2000 首 次 执 行： ‘00：05’ 1521行 第 2 次执行： ‘00：01’ 跟踪SQL2000与SQL2005中这条存储过程的实际执行计划做比较： 在这个存储过程中，2个版本同样是生成了6个步骤的执行计划来完成这个过程。 查询计划对比表： 版 本 查询1 查询2 查询3 查询4 查询5 查询6 2000 27% 13% 12% 40% 3% 5% 2005 38% 18% 17% 15% 6% 6% SQL Server Profiler跟踪SQL2005下这个存储过程： 整个存储过程95%的时间1分多钟都用在了选中的这条语句上，这条语句就是执行计划中 查询4： select five.*,one.ThesisBatchID,two.ThesisBatchCode,one.ThesisTopicID,one.TutorTeacherID,one.AnnotateTeacherID,one.TutorTeacherName,one.AnnotateTeacherName, case one.ThesisDistributeType when 0 then 自动 when 1 then 手工 else end as ThesisDistributeTypeName, case one.TeacherDistributeType when 0 then 自动 when 1 then 手工 else end as TeacherDistributeTypeName, case one.WishType when 1 then 第一志愿 when 2 then 第二志愿 when 3 then 第三志愿 when 4 then 随机分配 end as WishType ,one.IfPublic,three.ThesisTopicName,three.ThesisTopicReference,six.ThesisWriteApplySubmitTime into #TempResult from #Teacher one join tb_e_ThesisTopics three on one.ThesisTopicID=three.Thes