使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句
SELECT c.last_execution_time , --最后一次执行时间
c.execution_count , --执行次数
c.total_logical_reads , --总逻辑读(次)
c.total_logical_writes , --总逻辑写(次)
c.total_elapsed_time , --总运行(执行)语句使用的时间(微秒)
c.last_elapsed_time , --最后运行(执行)语句使用的时间(微秒)
q.[text] , --对应的sql语句
c.total_worker_time / 1000000 total_worker_time_second --c.total_worker_time 总工作时间(微秒)
FROM ( SELECT TOP 50
qs.*
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time DESC
) AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY c.last_elapsed_time DESC;
优化思路
1、将执行次数多、频率高、单次执行周期长的语句摘出来,如图:该语句共计执行495次,最后一次执行时间约1秒,像这样的SQL优化之后可缩短不少总执行时间。(PS:当然前面执行次数少执行时间长的肯定也是需要优化的,这种都是安全隐患,指不定哪天系统奔溃了就因为一条SQL语句)
2、优化语句,上图SQL语句如下,执行时间约1秒,经分析查询条件order_time未加索引,会对订单表做全表检索,数据量大概100万左右。
3、对表字段order_time创建索引之后,执行时间缩至203毫秒(平均200ms),如图:
4、发现查询值为count(*),条件中未用到ri、rp、sg、po表,故将这些关联表删除,删除之后执行时间变为178毫秒(平均180ms),如图:
5、这里表关联条件 oi.company_id = ci.Id不会出现关联不到的情况,故可以将LEFT JOIN改为INNER JOIN,修改后执行时间变为83毫秒(平均80ms),如图:
6、找到代码中的SQL语句进行调整。
这里讲的比较细,每条需要优化的SQL语句都可能存在不一样的情况,处理的方式也多种多样,需要根据实际情况调整。