SQLServer性能调优.pptVIP

GTSC UNIVERSITY SQL Server 2005 性能调优 哪些因素影响性能？ 等待系统资源 内存，CPU，IO 错误的配置 硬件 软件 不优化的查询 设计 写法不好, 设计不周 索引问题 和工作量与资源配置没有关系 不好的执行计划 和客户端网络交互太多 课程安排 SQL 2005对系统资源的使用和监控方法 语句执行计划优化 执行计划预测与选择 执行计划重用与重编译 有用的性能监视指标 有效地搜集信息 SQL Trace SQL Profiler对性能的影响可能比较大 Server Side Trace Performance Monitor DMV DMF (动态管理视图和函数) 数据库定义以及数据统计信息 使用SQLDiag 在SQL 2000里面的PSSDiag 缺省在SQL 2005里面就有安装 可以自动搜集 SQL Trace Performance Log SQLDiag report Windows Event Logs 附带两个模板 SD_Detailed.XML SD_General.XML Lab 1 使用SQLDiag监视SQL 2005的运行 共享的资源 数据库级 共享的资源 数据库性能受到日志文件最大写入能力的限制，日志的写入必须是串行的！ 可以通过以下改进性能 增加更多的物理硬盘 增加数据库的数目，以增加日志的数目 服务器级 共享的资源 TEMPDB Memory (64-bit) Memory (32-bit) 32-bit AWE扩展的内存只能cache数据页面 Proc cache, locks, user connections, sorting 还是只能使用 2-3GB 的地址空间 可以通过在一台机器上安装多个instance解决 机器级 共享的资源 CPU 和网络 向上扩展（ scalability）规则 数据库的scalability 受到日志文件最大写入能力的限制 Disk I/O 实例（Instance）的 scalability 受到进程最大资源数目的限制 Memory 服务器的 scalability受到机器能力的限制 CPU 网络带宽 硬盘读写性能问题 确定问题的特征 写瓶颈 日志文件 (~100% 串行写) Lazy Writer (随机) 读瓶颈 随机 vs. 串行 测试某个硬件配置的IO能力（不使用SQLServer) : SQLIOSim 特殊考虑: 日志文件 一颗CPU一个Tempdb 数据文件 硬盘读写瓶颈 I/O 瓶颈通常比较容易发现 对日志文件一定要小心 使用独立的设备 使用RAID 10 RAID5写性能问题: Each RAID5 write = 2 READS + 2 WRITES ! 最近的测试结果是RAID5的写性能比RAID 0+1要差50% 阻塞问题 阻塞是由于并发的连接争抢共同的资源，但是没有形成死锁 检测工具 SQL 2005 Profiler就可以检测出 DMVs Sp_who2 sp_lock Snapshot Isolation - Row Versioning读不阻塞写 检测阻塞 DMF sys.dm_db_index_operational_stats() 可以看出资源争抢的对象 Row locks counts （行锁申请数目） Row lock waits counts （行锁等待次数） Total wait time for blocks （总共被阻塞的时间） 可以算出发生阻塞的百分比和平均等待时间row_lock_wait_count / row_lock_countrow_lock_wait_in_ms / row_lock_wait_count 检测阻塞 Sp_lock sp_who2 – lists real time blocks Trace – for historical analysis Capture long blocks using the Trace Event “Block Process Report” Sp_configure “blocked process threshold”,15 (seconds) Lab 2 确认SQL Server内部的阻塞问题 Tempdb资源争抢 Tempdb 在 SS2005里使用量更大 Tempdb 的配置更加重要 使用方式 Tempdb使用预测 On line 索引重建: 2x-3x size of index – Sort size, temp index and rollback Versioning: [Size of Version Store] = 2 *[Version store d