SQL Server CPU性能排查及优化相关SQL语句使用简介.doc

我们知道，SQL Server数据库在做一些排序和聚合运算以及表连接操作时，会占用比较多的CPU资源，甚至可能会出现CPU使用率为100%的情况，本文我们主要介绍一些SQL Server CPU性能排查及优化的相关SQL语句的使用，接下来我们就开始一一介绍。-使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前50名。1. select 2. 3. c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time, 4. 5. q.text 6. 7. from 8. 9. (selecttop50qs.* 10. 11. fromsys.dm_exec_query_statsqs 12. 13. orderbyqs.total_worker_timedesc)asc 14. 15. crossapplysys.dm_exec_sql_text(plan_handle)asq 16. 17. orderbyc.total_worker_timedesc 18. 19. go- 返回最经常运行的100条语句。1. SELECTTOP100cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,qs.statement_start_offset,qs.statement_end_offset,qt.dbid,qt.objectid 2. 3. ,SUBSTRING(qt.text,qs.statement_start_offset/2, 4. 5. (casewhenqs.statement_end_offset=-1 6. 7. thenlen(convert(nvarchar(max),qt.text)*2 8. 9. elseqs.statement_end_offsetend-qs.statement_start_offset)/2)asstatement 10. 11. FROMsys.dm_exec_query_statsqs 12. 13. crossapplysys.dm_exec_sql_text(qs.sql_handle)asqt 14. 15. innerjoinsys.dm_exec_cached_plansascponqs.plan_handle=cp.plan_handle 16. 17. wherecp.plan_handle=qs.plan_handle 18. 19. andcp.usecounts4 20. 21. ORDERBYdbid,UsecountsDESC- 返回做IO数目最多的50条语句以及它们的执行计划。1. selecttop50 2. 3. (total_logical_reads/execution_count)asavg_logical_reads, 4. 5. (total_logical_writes/execution_count)asavg_logical_writes, 6. 7. (total_physical_reads/execution_count)asavg_phys_reads, 8. 9. Execution_count, 10. 11. statement_start_offsetasstmt_start_offset,statement_end_offsetasstmt_end_offset, 12. 13. substring(sql_text.text,(statement_start_offset/2), 14. 15. case 16. 17. when(statement_end_offset-statement_start_offset)/25% 说明有问题。1. declareCxpacketbigint 2. 3. declareSumwaitsbigint 4. 5. selectCxpacket=wait_time_ms6. 7. fromSys.dm_os_wait_stats 8. 9. wherewait_type=Cxpacket10. 11. selectSumwaits=sum(wait_time_ms) 12. 13. fromSys.dm_os_wait_stats 14. 15. selectconvert(numeric(5,4),Cxpacket/Sumwaits)- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率1. declaredbidint 2. 3. selectdbid=db_id() 4. 5. Selectdbid=database_id,objectname=object_name(s.object_id) 6. 7. ,iindexname=,i.index_id-,partition_number 8. 9. ,row_lock_count,row_lock_wait_count 10. 11. ,block%=cast(100.0*row_lock_wait_count/(1+row_lock_count)asnumeric(15,2) 12. 13. ,row_lock_wait_in_ms 14. 15. ,avgrowlockwaitsinms=cast(1.0*row_lock_wait_in_ms/(1+row_lock_wait_count)asnumeric(15,2) 16. 17. fromsys.dm_db_index_operational_stats(dbid,NULL,NULL,NULL)s,sys.indexesi 18. 19. whereobjectproperty(s.object_id,IsUserTable)=1 20. 21. andi.object_id