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

SQL Server CPU性能排查及优化相关SQL语句使用简介 我们知道，SQL Server数据库在做一些排序和聚合运算以及表连接操作时，会占用比较多的CPU资源，甚至可能会出现CPU使用率为100%的情况，本文我们主要介绍一些SQL Server CPU性能排查及优化的相关SQL语句的使用，接下来我们就开始一一介绍。 --使用DMV来 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 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. (select top 50 qs.* 10. 11. from sys.dm_exec_query_stats qs 12. 13. order by qs.total_worker_time desc) as c 14. 15. cross apply sys.dm_exec_sql_text(plan_handle) as q 16. 17. order by c.total_worker_time desc 18. 19. go -- 返回最经常运行的100条语句。 1. SELECT TOP 100 cp.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. (case when qs.statement_end_offset = -1 6. 7. then len(convert(nvarchar(max), qt.text)) * 2 8. 9. else qs.statement_end_offset end - qs.statement_start_offset)/2) as statement 10. 11. FROM sys.dm_exec_query_stats qs 12. 13. cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt 14. 15. inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.p lan_handle 16. 17. where cp.plan_handle=qs.plan_handle 18. and cp.usecounts>4 19. 20. 21. ORDER BY [dbid],[Usecounts] DESC -- 返回做IO数目最多的50条语句以及它们的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 。 1. select top 50 2. 3. (total_logical_reads/execution_count) as avg_logical_reads, 4. 5. (total_logical_writes/execution_count) as avg_logical_writes, 6. 7. (total_physical_reads/execution_count) as avg_phys_reads, 8. 9. Execution_count, 10. 11. statement_start_offset as stmt_start_offset, statement_end_offse t as stmt_end_offset, 12. 13. substring(sql_text.text, (statement_start_offset/2), 14. 15. case 16. 17. when (statement_end_offset - statement_start_offset)/2 <=0 then 64000 18. 19. else (statement_end_offset - statement_start_offset)/2 end) as exec_statement, sql_text.text,p lan_text.* 20. 21. from sys.dm_exec_query_stats 22. 23. cross apply sys.dm_exec_sql_text(sql_handle) as sql_text 24. 25. cross apply sys.dm_exec_query_plan(plan_handle) as plan_text 26. 27. order by 28. 29. (total_logical_reads + total_logical_writes) /Execution_count De sc -- 计算signal wait占整wait时间的百分比。 -- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明CPU紧张 1. select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_ti me_ms)) 2. 3. from Sys.dm_os_wait_stats -- 计算'Cxpacket'占整wait时间的百分比。 -- Cxpacket:Sql Server 在处理一句代价很大的语句，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当 >5% 说明有问题。 1. declare @Cxpacket bigint 2. 3. declare @Sumwaits bigint 4. 5. select @Cxpacket = wait_time_ms 6. 7. from Sys.dm_os_wait_stats 8. 9. where wait_type = 'Cxpacket' 10. 11. select @Sumwaits = sum(wait_time_ms) 12. 13. from Sys.dm_os_wait_stats 14. 15. select convert(numeric(5,4),@Cxpacket/@Sumwaits) -- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率 1. declare @dbid int 2. 3. select @dbid = db_id() 4. 5. Select dbid=database_id, objectname=object_name(s.object_id) 6. 7. , iindexname=i.name, 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_co unt) as numeric(15,2)) 12. 13. , row_lock_wait_in_ms 14. 15. , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / ( 1 + row_lock_wait_count) as numeric(15,2)) 16. 17. from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i 18. 19. where objectproperty(s.object_id,'IsUserTable') = 1 20. 21. and i.object_id = s.object_id 22. 23. and i.index_id = s.index_id 24. 25. order by row_lock_wait_count desc 关于SQL Server CPU性能排查及优化的相关SQL语句就介绍这么多，希望能够带 给您一些收获～