前述
最近一个项目CPU占用非常高,在IIS内设置CPU限制后系统频繁掉线,通过任务管理器发现SQLSever数据库占用CPU达到40%--70%,对于数据库本人也就处在增删查改几个操作水平层面,这次出了问题就硬着头皮上了,好在经过各种百度问题最终解决了,记性不好用的也不多,为防止忘掉就把主要排查方法记录下来。
查找耗时语句
SELECT TOP
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/ + ,
(CASE WHEN statement_end_offset = -
THEN LEN(CONVERT(nvarchar(max), text)) *
ELSE statement_end_offset
END - statement_start_offset)/)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
直接复制粘贴上面的语句就可以查出最耗时的前10条语句。
查看具体语句执行时间
切换到文本模式
使用下面的语句查看单条语句的执行时间
go
set statistics profile on
set statistics io on
set statistics time on
go
/** 欲查询CPU执行时间的目标语句**/
go
set statistics profile off
set statistics io off
set statistics time off
go
语句耗时的原因及对策
1.单表数据太多:分表、清理过时数据
排查中有两张相同的表,一张表数据量在7000条,另一张表由于问题插入大量无用数据达到三十万+,相同的语句针对两张表的查询时间分别为0ms/900ms,差距非常大。
表1执行时间 表2执行时间
2.数据表没有索引:建立聚集索引
对表结构进行分析,发现没有建立聚集索引,于是针对表及主要操作字段建立聚集索引,建立聚集索引后语句执行时间
3.对语句进行优化:这个只能特定语句特定分析,不断查询语句执行时间来查看优化结果
语句优化完成效果如图
上面2、3都是单独作用时的效果图,可以看到效果还是很明显的,只是并未达到令人满意的效果,但2、3结合使用后效果就惊人了