1 / 38

GTSC UNIVERSITY

GTSC UNIVERSITY. SQL Server 2005 性能调优. 哪些因素影响性能？. 等待系统资源 内存， CPU ， IO 错误的配置 硬件 & 软件 不优化的查询 & 设计 写法不好 , 设计不周 索引问题 和工作量与资源配置没有关系 不好的执行计划 和客户端网络交互太多. 课程安排. SQL 2005 对系统资源的使用和监控方法 语句执行计划优化 执行计划预测与选择 执行计划重用与重编译 有用的性能监视指标. 有效地搜集信息. SQL Trace SQL Profiler 对性能的影响可能比较大

hastin
Download Presentation

GTSC UNIVERSITY

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. GTSC UNIVERSITY SQL Server 2005 性能调优

  2. 哪些因素影响性能？ • 等待系统资源 • 内存，CPU，IO • 错误的配置 • 硬件 & 软件 • 不优化的查询 & 设计 • 写法不好, 设计不周 • 索引问题 • 和工作量与资源配置没有关系 • 不好的执行计划 • 和客户端网络交互太多

  3. 课程安排 • SQL 2005对系统资源的使用和监控方法 • 语句执行计划优化 • 执行计划预测与选择 • 执行计划重用与重编译 • 有用的性能监视指标

  4. 有效地搜集信息 • SQL Trace • SQL Profiler对性能的影响可能比较大 • Server Side Trace • Performance Monitor • DMV & DMF (动态管理视图和函数) • 数据库定义以及数据统计信息

  5. 使用SQLDiag • 在SQL 2000里面的PSSDiag • 缺省在SQL 2005里面就有安装 • 可以自动搜集 • SQL Trace • Performance Log • SQLDiag report • Windows Event Logs • 附带两个模板 • SD_Detailed.XML • SD_General.XML

  6. Lab 1 • 使用SQLDiag监视SQL 2005的运行

  7. 共享的资源 • 数据库级 共享的资源 • 数据库性能受到日志文件最大写入能力的限制，日志的写入必须是串行的！ • 可以通过以下改进性能 • 增加更多的物理硬盘 • 增加数据库的数目，以增加日志的数目 • 服务器级 共享的资源 • TEMPDB • Memory (64-bit) • Memory (32-bit) • 32-bit AWE扩展的内存只能cache数据页面 • Proc cache, locks, user connections, sorting 还是只能使用 2-3GB 的地址空间 • 可以通过在一台机器上安装多个instance解决 • 机器级 共享的资源 • CPU 和网络

  8. 向上扩展（ scalability）规则 • 数据库的scalability 受到日志文件最大写入能力的限制 • Disk I/O • 实例（Instance）的 scalability 受到进程最大资源数目的限制 • Memory • 服务器的 scalability受到机器能力的限制 • CPU • 网络带宽

  9. 硬盘读写性能问题 • 确定问题的特征 • 写瓶颈 • 日志文件 (~100% 串行写) • Lazy Writer (随机) • 读瓶颈 • 随机 vs. 串行 • 测试某个硬件配置的IO能力（不使用SQLServer) : • SQLIOSim • 特殊考虑: • 日志文件 • 一颗CPU一个Tempdb 数据文件

  10. 硬盘读写瓶颈 • I/O 瓶颈通常比较容易发现 • 对日志文件一定要小心 • 使用独立的设备 • 使用RAID 10 • RAID5写性能问题: • Each RAID5 write = 2 READS + 2 WRITES ! • 最近的测试结果是RAID5的写性能比RAID 0+1要差50%

  11. 阻塞问题 • 阻塞是由于并发的连接争抢共同的资源，但是没有形成死锁 • 检测工具 • SQL 2005 Profiler就可以检测出 • DMVs • Sp_who2 & sp_lock • Snapshot Isolation - Row Versioning读不阻塞写

  12. 检测阻塞 • 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

  13. 检测阻塞 • 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)

  14. Lab 2 • 确认SQL Server内部的阻塞问题

  15. Tempdb资源争抢 • Tempdb 在 SS2005里使用量更大 • Tempdb 的配置更加重要 • 使用方式 • 1 DBCC CHECKDB - small change • Internal objects: work file (hash join, SORT_IN_TEMPDB) • Internal objects: work table (cursor, spool) - small changes • 4 Large object (LOB) variables • 5 Service Broker • 6 Temporary objects: global/local temp table, table variables • 7 Temporary objects: SPs and cursors - small changes • 8 Version store: General • 9 Version store: MARS • 10 Version store: Online index • 11 Version store: Row version based isolation levels • 12 Version store: Triggers • 13 XML

  16. Tempdb使用预测 • On line 索引重建: • 2x-3x size of index – Sort size, temp index and rollback • Versioning: • [Size of Version Store] = 2 *[Version store data generated per minute] *[Longest running time (minutes) of your transaction] * number of concurrent transactions/users • 可以通过Performance Monitor里面的counter进行监视 • 事先就设置好一个合适的大小，tempdb自动增长会严重影响性能

  17. Tempdb – Trace Flag 1118 • 能够减少分配页的争抢 • 如果存储过程大量地使用Create Table and Create Index，就要考虑使用.

  18. 检查tempdb使用情况DMV select sum(user_object_reserved_page_count)*8 as user_objects_kb, sum(internal_object_reserved_page_count)*8 as internal_objects_kb, sum(version_store_reserved_page_count)*8 as version_store_kb, sum(unallocated_extent_page_count)*8 as freespace_kb from sys.dm_db_file_space_usage where database_id = 2

  19. 检查tempdb使用情况DMV SELECT t1.session_id, (t1.internal_objects_alloc_page_count + task_alloc) as allocated, (t1.internal_objects_dealloc_page_count + task_dealloc) asdeallocated , t3.sql_handle, t3.statement_start_offset ,t3.statement_end_offset, t3.plan_handle from sys.dm_db_session_space_usage as t1, sys.dm_exec_requests t3, (select session_id, sum(internal_objects_alloc_page_count)as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2 where t1.session_id = t2.session_id and t1.session_id >50 and t1.database_id = 2 --- tempdb is database_id=2 and t1.session_id = t3.session_id order by allocated DESC

  20. 编程时的注意事项 • 使用正确的，并且是尽量短的数据类型 • 程序中声明的类型必须和数据库中的一致 • 一次提交的命令长度要合适 • 尽量使用RPC Call，而不是执行命令行 • {call dbo.qi ('M01', 'M01.0407040000000002')} • exec dbo.qi @v1='M01', @v2='M01.0407040000000002' –adds ADHOC query plans due to SQL string parsing • 尽量避免使用数据库端游标

  21. CPU使用 • 连接在Runnable状态，完全是在等待CPU时间片执行 • CPU使用 • 执行计划的compile和recompile • Sort， Join， Aggregation • 通常情况下，SQL的CPU使用量不会太大。相对于CPU, memory和disk更容易成为系统的瓶颈

  22. 编程时的注意事项 • 尽量避免从数据库中取出大量的数据 • 业务逻辑可以用stored procedure完成 • 一次把所有的数据都从数据库里取走 • 没有取走的数据在数据库中会用active的游标的方式处理，影响并发度 • 如果客户端放弃取走所有数据，服务器还要清理这些数据 • SET NOCOUNT ON • 避免INSERT, UPDATE and DELETE 语句导致的不必要的网络传输

  23. 执行计划与它的生成

  24. 执行计划重用 • Master..Sys.dm_exec_cached_plans • 包含存储过程和语句 • 引用该缓存对象的其他缓存对象数 • 自开始以来使用该缓存对象的次数 • 可以重用的执行计划 • Procs, Triggers, Views • Defaults, Check constraints, rules • adhoc SQL, sp_executesql

  25. 检查计划重用情况 • SQL Batch requests/sec • 和SQL Compilations/sec作对比 • SQL Compilations/sec • 包含初始的compiles AND re-compiles • 去掉re-compilations，能大致算出初始compiles的数目 • 在Sys.dm_exec_cached_plans里面找出usecounts最低的SQL语句 • SQL Re-compilations/sec • 语句一级的Recompiles • Check profiler for sp:recompile event to identify SQL statement. • http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

  26. 监视执行计划的compile和recompile • Perfmon: SQLServer:SQL Statistics • Batch requests / sec { >1000’s/sec server is busy} • SQL Compilations / sec {>10s/sec could be problem} • SQL Recompilations / sec {OLTP should avoid high recomps} • Ratio of compiles / requests is important • Compiles – recompiles = initial compiles • Plan re-use = (Batch requests – initial compiles) / Batch requests • Recompile 的原因: • 表格的定义发生变化 • 先前的并发计划需要串行执行 • 统计值更新过了 • 表格更新的行数超过了限度 – sys.sysindexes.rowmodctr

  27. 使用SQL Trace观察执行计划重用量 • 主要的event有: • SP:CacheMiss (event ID 34 in Profiler) • SP:CacheInsert (event ID 35 in Profiler) • SP:CacheRemove (event ID 36 in Profiler) • SP:Recompile (event ID 37 in Profiler) • SP:CacheHit (event ID 38 in Profiler) • SP:Starting 标志一个stored procedure开始执行 • SP:StmtStarting标志单个语句开始执行 • Example: sequence is • SP:StmtStarting • SP:CacheMiss (no plan found) • SP:CacheInsert (plan created) • 注意：使用SQL Profiler可能会影响SQL性能！ • 加入Eventsubclass字段可以显示recompile的原因

  28. Eventsubclass 字段显示recompile的原因

  29. Lab 3 • 观察执行计划重用情况

  30. Memory: Page faults/sec Memory: pages/sec Physical Disk: Avg. Disk Queue Length Physical Disk: Avg. Disk sec/Transfer Physical Disk: Avg. Disk sec/Read Physical Disk: Avg. Disk sec/Write Physical Disk: Current Disk Queue Length Processor: %Processor Time SS Access Methods: Forwarded Records/sec SS Access Methods: Full Scans/sec SS Access Methods: Index Searches/sec SS Access Methods: Page Splits/sec SS Access Methods: Range Scans/sec SS Access Methods: Table Lock escalations/sec SS Buffer Manager: Checkpoint pages/sec SS Buffer Manager: Lazy writes/sec SS Buffer Manager: Page Life expectancy SS Buffer Node:Foreign Pages SS Buffer Node:Page Life expectancy SS Buffer Node:Stolen Pages SS Databases: Log Flush Wait time SS Databases: Log Flush Waits/sec SS General Statistics: User Connections SS Latches: Average Latch Wait Time(ms) SS Latches: Latch Waits/sec SS Latches: Total Latch Wait Time (ms) SS Locks: Average Wait Time(ms) SS Locks: Lock requests/sec SS Locks: Lock Wait Time (ms) SS Locks: Lock Waits/sec SS Memory Manager: Memory grants pending SS SQL Statistics: Auto-Params attempts/sec SS SQL Statistics: Batch requests/sec SS SQL Statistics: Safe Auto-Params/sec SS SQL Statistics: SQL Compilations/sec SS SQL Statistics: SQL Re-Compilations/sec System: Processor Queue Length 有用的性能监视器指标

  31. OLTP数据库设计指标

  32. 检查系统是否有IO瓶颈

  33. 判断系统是否有阻塞发生

  34. 是否有CPU瓶颈

  35. 是否有内存瓶颈

  36. Lab 4 • 分析SQLDiag搜集的日志文件

  37. Q&A

  38. GTSC UNIVERSTIY Thank You! 

More Related