SQLServer2005性能调优.ppt

GTSCUNIVERSITY,SQLServer2005性能调优,哪些因素影响性能？,等待系统资源内存，CPU，IO错误的配置硬件&软件不优化的查询&设计写法不好,设计不周索引问题和工作量与资源配置没有关系不好的执行计划和客户端网络交互太多,课程安排,SQL2005对系统资源的使用和监控方法语句执行计划优化执行计划预测与选择执行计划重用与重编译有用的性能监视指标,有效地搜集信息,SQLTraceSQLProfiler对性能的影响可能比较大ServerSideTracePerformanceMonitorDMV&DMF(动态管理视图和函数)数据库定义以及数据统计信息,使用SQLDiag,在SQL2000里面的PSSDiag缺省在SQL2005里面就有安装可以自动搜集SQLTracePerformanceLogSQLDiagreportWindowsEventLogs附带两个模板SD_Detailed.XMLSD_General.XML,Lab1,使用SQLDiag监视SQL2005的运行,共享的资源,数据库级共享的资源数据库性能受到日志文件最大写入能力的限制，日志的写入必须是串行的！可以通过以下改进性能增加更多的物理硬盘增加数据库的数目，以增加日志的数目服务器级共享的资源TEMPDBMemory(64-bit)Memory(32-bit)32-bitAWE扩展的内存只能cache数据页面Proccache,locks,userconnections,sorting还是只能使用2-3GB的地址空间可以通过在一台机器上安装多个instance解决机器级共享的资源CPU和网络,向上扩展（scalability）规则,数据库的scalability受到日志文件最大写入能力的限制DiskI/O实例（Instance）的scalability受到进程最大资源数目的限制Memory服务器的scalability受到机器能力的限制CPU网络带宽,硬盘读写性能问题,确定问题的特征写瓶颈日志文件(100%串行写)LazyWriter(随机)读瓶颈随机vs.串行测试某个硬件配置的IO能力（不使用SQLServer):SQLIOSim特殊考虑:日志文件一颗CPU一个Tempdb数据文件,硬盘读写瓶颈,I/O瓶颈通常比较容易发现对日志文件一定要小心使用独立的设备使用RAID10RAID5写性能问题:EachRAID5write=2READS+2WRITES!最近的测试结果是RAID5的写性能比RAID0+1要差50%,阻塞问题,阻塞是由于并发的连接争抢共同的资源，但是没有形成死锁检测工具SQL2005Profiler就可以检测出DMVsSp_who2&sp_lockSnapshotIsolation-RowVersioning读不阻塞写,检测阻塞,DMFsys.dm_db_index_operational_stats()可以看出资源争抢的对象Rowlockscounts（行锁申请数目）Rowlockwaitscounts（行锁等待次数）Totalwaittimeforblocks（总共被阻塞的时间）可以算出发生阻塞的百分比和平均等待时间row_lock_wait_count/row_lock_countrow_lock_wait_in_ms/row_lock_wait_count,检测阻塞,Sp_lock&sp_who2listsrealtimeblocksTraceforhistoricalanalysisCapturelongblocksusingtheTraceEvent“BlockProcessReport”Sp_configure“blockedprocessthreshold”,15(seconds),Lab2,确认SQLServer内部的阻塞问题,Tempdb资源争抢,Tempdb在SS2005里使用量更大Tempdb的配置更加重要使用方式,1DBCCCHECKDB-smallchangeInternalobjects:workfile(hashjoin,SORT_IN_TEMPDB)Internalobjects:worktable(cursor,spool)-smallchanges4Largeobject(LOB)variables5ServiceBroker6Temporaryobjects:global/localtemptable,tablevariables7Temporaryobjects:SPsandcursors-smallchanges8Versionstore:General9Versionstore:MARS10Versionstore:Onlineindex11Versionstore:Rowversionbasedisolationlevels12Versionstore:Triggers13XML,Tempdb使用预测,Online索引重建:2x-3xsizeofindexSortsize,tempindexandrollbackVersioning:SizeofVersionStore=2*Versionstoredatageneratedperminute*Longestrunningtime(minutes)ofyourtransaction*numberofconcurrenttransactions/users可以通过PerformanceMonitor里面的counter进行监视事先就设置好一个合适的大小，tempdb自动增长会严重影响性能,TempdbTraceFlag1118,能够减少分配页的争抢如果存储过程大量地使用CreateTableandCreateIndex，就要考虑使用.,检查tempdb使用情况DMV,selectsum(user_object_reserved_page_count)*8asuser_objects_kb,sum(internal_object_reserved_page_count)*8asinternal_objects_kb,sum(version_store_reserved_page_count)*8asversion_store_kb,sum(unallocated_extent_page_count)*8asfreespace_kbfromsys.dm_db_file_space_usagewheredatabase_id=2,检查tempdb使用情况DMV,SELECTt1.session_id,(ernal_objects_alloc_page_count+task_alloc)asallocated,(ernal_objects_dealloc_page_count+task_dealloc)asdeallocated,t3.sql_handle,t3.statement_start_offset,t3.statement_end_offset,t3.plan_handlefromsys.dm_db_session_space_usageast1,sys.dm_exec_requestst3,(selectsession_id,sum(internal_objects_alloc_page_count)astask_alloc,sum(internal_objects_dealloc_page_count)astask_deallocfromsys.dm_db_task_space_usagegroupbysession_id)ast2wheret1.session_id=t2.session_idandt1.session_id50andt1.database_id=2-tempdbisdatabase_id=2andt1.session_id=t3.session_idorderbyallocatedDESC,编程时的注意事项,使用正确的，并且是尽量短的数据类型程序中声明的类型必须和数据库中的一致一次提交的命令长度要合适尽量使用RPCCall，而不是执行命令行calldbo.qi(M01,M01.0407040000000002)execdbo.qiv1=M01,v2=M01.0407040000000002addsADHOCqueryplansduetoSQLstringparsing尽量避免使用数据库端游标,CPU使用,连接在Runnable状态，完全是在等待CPU时间片执行CPU使用执行计划的compile和recompileSort，Join，Aggregation通常情况下，SQL的CPU使用量不会太大。相对于CPU,memory和disk更容易成为系统的瓶颈,编程时的注意事项,尽量避免从数据库中取出大量的数据业务逻辑可以用storedprocedure完成一次把所有的数据都从数据库里取走没有取走的数据在数据库中会用active的游标的方式处理，影响并发度如果客户端放弃取走所有数据，服务器还要清理这些数据SETNOCOUNTON避免INSERT,UPDATEandDELETE语句导致的不必要的网络传输,执行计划与它的生成,执行计划重用,Master.Sys.dm_exec_cached_plans包含存储过程和语句引用该缓存对象的其他缓存对象数自开始以来使用该缓存对象的次数可以重用的执行计划Procs,Triggers,ViewsDefaults,Checkconstraints,rulesadhocSQL,sp_executesql,检查计划重用情况,SQLBatchrequests/sec和SQLCompilations/sec作对比SQLCompilations/sec包含初始的compilesANDre-compiles去掉re-compilations，能大致算出初始compiles的数目在Sys.dm_exec_cached_plans里面找出usecounts最低的SQL语句SQLRe-compilations/sec语句一级的RecompilesCheckprofilerforsp:recompileeventtoidentifySQLstatement.,监视执行计划的compile和recompile,Perfmon:SQLServer:SQLStatisticsBatchrequests/sec1000s/secserverisbusySQLCompilations/sec10s/seccouldbeproblemSQLRecompilations/secOLTPshouldavoidhighrecompsRatioofcompiles/requestsisimportantCompilesrecompiles=initialcompilesPlanre-use=(Batchrequestsinitialcompiles)/BatchrequestsRecompile的原因:表格的定义发生变化先前的并发计划需要串行执行统计值更新过了表格更新的行数超过了限度sys.sysindexes.rowmodctr,使用SQLTrace观察执行计划重用量,主要的event有:SP:CacheMiss(eventID34inProfiler)SP:CacheInsert(eventID35inProfiler)SP:CacheRemove(eventID36inProfiler)SP:Recompile(eventID37inProfiler)SP:CacheHit(eventID38inProfiler)SP:Starting标志一个storedprocedure开始执行SP:StmtStarting标志单个语句开始执行Example:sequenceisSP:StmtStartingSP:CacheMiss(noplanfound)SP:CacheInsert(plancreated)注意：使用SQLProfiler可能会影响SQL性能！加入Eventsubclass字段可以显示recompile的原因,Eventsubclass字段显示recompile的原因,Lab3,观察执行计划重用情况,有用的性能监视器指标,Memory:Pagefaults/secMemory:pages/secPhysicalDisk:Avg.DiskQueueLengthPhysicalDisk:Avg.Disksec/TransferPhysicalDisk:Avg.Disksec/ReadPhysicalDisk:Avg.Disksec/WritePhysicalDisk:CurrentDiskQueueLengthProcessor:%ProcessorTimeSSAccessMethods:ForwardedRecords/secSSAccessMethods:FullScans/secSSAccessMethods:IndexSearches/secSSAccessMethods:PageSplits/secSSAccessMethods:RangeScans/secSSAccessMethods:TableLockescalations/secSSBufferManager:Checkpointpages/secSSBufferManager:Lazywrites/secSSBufferManager:PageLifeexpectancySSBufferNode:ForeignPagesSSBufferNode:PageLifeexpectancySSBufferNode:St