SQLServer2005性能调优

1、SQL Server 2005 性能调优等待系统资源等待系统资源内存，内存，CPU，IO错误的配置错误的配置硬件硬件 & 软件软件不优化的查询不优化的查询 & 设计设计写法不好写法不好, 设计不周设计不周索引问题索引问题和工作量与资源配置没有关系和工作量与资源配置没有关系不好的执行计划不好的执行计划和客户端网络交互太多和客户端网络交互太多 SQL 2005对系统资源的使用和监控方法 语句执行计划优化 执行计划预测与选择 执行计划重用与重编译 有用的性能监视指标 SQL Trace SQL Profiler对性能的影响可能比较大 Server Side Trace Performance Mon

2、itor DMV & DMF (动态管理视图和函数) 数据库定义以及数据统计信息 在SQL 2000里面的PSSDiag 缺省在SQL 2005里面就有安装 可以自动搜集 SQL Trace Performance Log SQLDiag report Windows Event Logs 附带两个模板 SD_Detailed.XML SD_General.XML 使用SQLDiag监视SQL 2005的运行数据库级数据库级 共享的资源共享的资源数据库性能受到日志文件最大写入能力的限制，数据库性能受到日志文件最大写入能力的限制，日志的写入必须是串行的！日志的写入必须是串行的！可以通过以下改进性

3、能可以通过以下改进性能 增加更多的物理硬盘增加更多的物理硬盘增加数据库的数目，以增加日志的数目增加数据库的数目，以增加日志的数目服务器级服务器级 共享的资源共享的资源TEMPDBMemory (64-bit)Memory (32-bit)32-bit AWE扩展的内存只能扩展的内存只能cache数据页面数据页面Proc cache, locks, user connections, sorting 还是只能使用还是只能使用 2-3GB 的的地址空间地址空间可以通过在一台机器上安装多个可以通过在一台机器上安装多个instance解决解决机器级机器级 共享的资源共享的资源CPU 和网络和网络数据库

4、的数据库的scalability 受到日志文件最大写入能力受到日志文件最大写入能力的限制的限制Disk I/O实例（实例（Instance）的）的 scalability 受到进程最大受到进程最大资源数目的限制资源数目的限制Memory服务器的服务器的 scalability受到机器能力的限制受到机器能力的限制CPU 网络带宽网络带宽确定问题的特征确定问题的特征写瓶颈写瓶颈日志文件日志文件 (100% 串行写串行写)Lazy Writer (随机随机)读瓶颈读瓶颈随机随机 vs. 串行串行测试某个硬件配置的测试某个硬件配置的IO能力（不使用能力（不使用SQLServer) :SQLIOSim

5、特殊考虑特殊考虑:日志文件日志文件一颗一颗CPU一个一个Tempdb 数据文件数据文件 I/O 瓶颈通常比较容易发现瓶颈通常比较容易发现 对日志文件一定要小心对日志文件一定要小心 使用独立的设备使用独立的设备 使用使用RAID 10 RAID5写性能问题写性能问题: Each RAID5 write = 2 READS + 2 WRITES ! 最近的测试结果是最近的测试结果是RAID5的写性能比的写性能比RAID 0+1要差要差50% 阻塞是由于并发的连接争抢共同的资源，阻塞是由于并发的连接争抢共同的资源，但是没有形成死锁但是没有形成死锁 检测工具检测工具 SQL 2005 Profiler

6、就可以检测出就可以检测出 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

7、_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) 确认SQL Server内部的阻塞问题Tempdb 在 SS2005里使用量更大Tempdb 的配置更加重要使用方式1DBCC CHECKDB - small chan

8、ge2Internal objects: work file (hash join, SORT_IN_TEMPDB) 3Internal objects: work table (cursor, spool) - small changes4Large object (LOB) variables 5Service Broker 6Temporary objects: global/local temp table, table variables 7Temporary objects: SPs and cursors - small changes8Version store: Genera

9、l 9Version store: MARS 10Version store: Online index 11Version store: Row version based isolation levels 12Version store: Triggers 13XMLOn line 索引重建: 2x-3x size of index Sort size, temp index and rollbackVersioning: Size of Version Store = 2 *Version store data generated per minute *Longest running

10、time (minutes) of your transaction * number of concurrent transactions/users可以通过Performance Monitor里面的counter进行监视事先就设置好一个合适的大小，tempdb自动增长会严重影响性能 能够减少分配页的争抢 如果存储过程大量地使用Create Table and Create Index，就要考虑使用.select sum(user_object_reserved_page_count)*8 as user_objects_kb,sum(internal_object_reserved_pa

11、ge_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_kbfrom sys.dm_db_file_space_usagewhere database_id = 2SELECT t1.session_id,(ernal_objects_alloc_page_count + task_alloc) as allocated,(ernal_o

12、bjects_dealloc_page_count + task_dealloc) as deallocated, t3.sql_handle, t3.statement_start_offset, t3.statement_end_offset, t3.plan_handlefrom 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

13、_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2where t1.session_id = t2.session_id and t1.session_id 50and t1.database_id = 2 - tempdb is database_id=2and t1.session_id = t3.session_idorder by allocated DESC 使用正确的，并且是尽量短的数据类型 程序中声明的类型必须和数据库中的一致 一次提交的命令

14、长度要合适 尽量使用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 尽量避免使用数据库端游标 连接在Runnable状态，完全是在等待CPU时间片执行 CPU使用 执行计划的compile和recompile Sort， Join， Aggregation 通常情况下，SQL的CPU使用量不会太大。相对于CPU, memory和disk更容易成

15、为系统的瓶颈尽量避免从数据库中取出大量的数据业务逻辑可以用stored procedure完成一次把所有的数据都从数据库里取走没有取走的数据在数据库中会用active的游标的方式处理，影响并发度如果客户端放弃取走所有数据，服务器还要清理这些数据SET NOCOUNT ON避免INSERT, UPDATE and DELETE 语句导致的不必要的网络传输Master.Sys.dm_exec_cached_plans包含存储过程和语句引用该缓存对象的其他缓存对象数 自开始以来使用该缓存对象的次数可以重用的执行计划Procs, Triggers, ViewsDefaults, Check const

16、raints, rulesadhoc SQL, sp_executesqlSQL 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语句一级的RecompilesCheck profiler for sp:recompile event to identi

17、fy SQL statement. http:/ Perfmon: SQLServer:SQL StatisticsBatch requests / sec 1000s/sec server is busySQL Compilations / sec 10s/sec could be problemSQL Recompilations / sec OLTP should avoid high recompsRatio of compiles / requests is importantCompiles recompiles = initial compiles Plan re-use = (

18、Batch requests initial compiles) / Batch requestsRecompile 的原因:表格的定义发生变化先前的并发计划需要串行执行统计值更新过了表格更新的行数超过了限度 sys.sysindexes.rowmodctr主要的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:Ca

19、cheHit (event ID 38 in Profiler)SP:Starting 标志一个stored procedure开始执行SP:StmtStarting 标志单个语句开始执行Example: sequence is SP:StmtStartingSP:CacheMiss (no plan found)SP:CacheInsert (plan created)注意：使用SQL Profiler可能会影响SQL性能！加入Eventsubclass 字段可以显示recompile的原因EventSubClass Description 1Schema changed.2Statisti

20、cs changed.3Deferred compile.4SET option changed.5Temporary table changed.6Remote rowset changed.7FOR BROWSE permission changed.8Query notification environment changed.9Partitioned view changed.10Cursor options changed.11OPTION (RECOMPILE) requested. 观察执行计划重用情况Memory: Page faults/secMemory: pages/se

21、cPhysical Disk: Avg. Disk Queue LengthPhysical Disk: Avg. Disk sec/TransferPhysical Disk: Avg. Disk sec/ReadPhysical Disk: Avg. Disk sec/WritePhysical Disk: Current Disk Queue LengthProcessor: %Processor TimeSS Access Methods: Forwarded Records/secSS Access Methods: Full Scans/secSS Access Methods:

22、Index Searches/secSS Access Methods: Page Splits/secSS Access Methods: Range Scans/secSS Access Methods: Table Lock escalations/secSS Buffer Manager: Checkpoint pages/secSS Buffer Manager: Lazy writes/secSS Buffer Manager: Page Life expectancySS Buffer Node:Foreign PagesSS Buffer Node:Page Life expe

23、ctancySS Buffer Node:Stolen PagesSS Databases: Log Flush Wait timeSS Databases: Log Flush Waits/secSS General Statistics: User ConnectionsSS Latches: Average Latch Wait Time(ms)SS Latches: Latch Waits/secSS Latches: Total Latch Wait Time (ms)SS Locks: Average Wait Time(ms)SS Locks: Lock requests/sec

24、SS Locks: Lock Wait Time (ms)SS Locks: Lock Waits/secSS Memory Manager: Memory grants pendingSS SQL Statistics: Auto-Params attempts/secSS SQL Statistics: Batch requests/secSS SQL Statistics: Safe Auto-Params/secSS SQL Statistics: SQL Compilations/secSS SQL Statistics: SQL Re-Compilations/secSystem:

25、 Processor Queue LengthResource IssueRuleDescriptionSourceProblem DescriptionDB Design1经常有多个表格join的语句出现Sys.dm_exec_sql_textSys.dm_exec_cached_plans 表格join次数太多，会影响OLTP系统的性能2经常更新的表格上有很多indexSys.indexessys.dm_db_operational_index_stats Index数目多，可以提高查询速度，但是会降低修改速度，增加阻塞和死锁发生的机率 3作了太多的硬盘读写range scanstable

26、 scansPerfmon objectSQL Server Access MethodsSys.dm_exec_query_stats Missing index, flushes cache 4没有用的index * Sys.dm_db_index_usage_stats Index maintenance for unused indexes Resource IssueRuleDescriptionValueSourceProblem DescriptionIO1Avg Disk seconds / read 10 ms Perfmon objectPhysical Disk Read

27、s should take 4-8ms with NO IO pressure2Avg Disk seconds / write 10 ms Perfmon objectPhysical Disk Writes (sequential) can be as fast as 1ms for transaction log.3Big IOsrange scanstable scans1 Perfmon objectSQL Server Access MethodsMissing index, flushes cache 4If Top 2 values for Wait stats includes:ASYNCH_IO_COMPLETIONIO_COMPLETIONLOGMGRWRITELOGPAGEIOLATCH_xTop 2 Sys.dm_os_wait_stats If top 2 wait_stats values include IO, there is an IO bottleneck Resource IssueRuleDescriptionValueSourceProblem DescriptionBlocking