sqlserver2005调优.docVIP

Microsoft SQL Server 2005 性能调优 SQL Server 2005 性能调优 SQL Server 应用程序性能调优 压力测试 系统及数据库性能监控 优化软硬件配置 压力测试 压力测试工具——脚本录制 LoadRunner VS.Net ATC(Application Center Test) 模拟实际生产环境 并发数量 思考时间 参数化 压力测试工具 LoadRunner Virtual User Genarator 录制应用脚本 架构 协议 Transaction 参数化 Controller 模拟并发操作 设置think time Scheduler 监控服务器性能参数 Analysis 分析测试结果 表格方式 图表方式 系统及数据库性能监控 Windows性能监视器 添加性能参数 设置监控计划 SQL Server Profiler——跟踪SQL Server事件 查询计划 T-SQL 存储过程 表锁 游标 DMV（动态管理视图） 遵循 dm_* 命名约定 使用 sys 架构作为视图或函数名称的前缀 SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats GO 降低SQL Server速度的原因 资源瓶颈: CPU，内存，和I/O瓶颈 Tempdb 瓶颈 每个SQL Server 实例只有一个tempdb 性能和磁盘空间的瓶颈 不好的应用程序 过多的DDL和DML操作 缓慢运行的用户查询: 改变统计信息可以导致现有查询的较差的查询计划 全表扫描，降低查询性能 即使资源利用正常由于阻塞也可以导致应用程序运行缓慢 不良的应用程序设计或架构设计 错误的事务隔离级别 资源瓶颈 CPU 瓶颈 过多的编译和重编译 效率低的查询计划 内部查询的并行 拙劣游标使用 内存瓶颈 外部物理内存压力 外部虚拟内存压力 内部物理内存压力 IO瓶颈 CPU瓶颈 过多的编译和重编译 SQL Server: SQL Statistics: Batch Requests/sec SQL Server: SQL Statistics: SQL Compilations/sec SQL Server: SQL Statistics: SQL Recompilations/sec 被重编译次数最多的25个存储过程 select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num 1 order by plan_generation_num desc CPU瓶颈 如何确定存储过程的过度重编译 确定过度重编译的存储过程 确定导致重编译的原因 如何确定 使用SQL Server Profiler监控事件： SP:Starting,SP:Completed,SP:stmtStarting, SP:StmtCompleted 使用SQL Profiler抓取信息 EventSubClass,TextData 存储过程的重编译原因代码  统计数据变化的界定  如何减少过度重编译 避免在操作中修改选项（SET OPTION) 存储过程中需要使用临时表的，相关的架构 语句在存储过程的开始部分完成 有大量行变动的临时表可是考虑使用表变量 替代 对于临时表的SELECT加上KEEP PLAN选 项可避免6行的界定（500行） KEEP FIXEDPLAN可以避免统计数据对重 编译的影响，但不能解决架构变动对重编译 的影响 如何减少过度重编译 SQL 2000: 不支持存储过程中语句级的重编译（SQL 2005 支持） 大的存储过程可以使用sp_executesql分拆 将导致重编译的语句移出大的存储过程，建立独 立的存储过程 使用sp_autostats关闭自动更新统计可以避 免统计数据对重编译的影响 CPU瓶颈 效率低的查询计划 前50个累计使用CPU时间最多的 查询 select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_work