本文共 989 字，大约阅读时间需要 3 分钟。

前段时间网站访问量增大，云服务器后台监控告警提示IOPS过大。我使用以下sql语句很快就找到sql server占用IO在的相关功能模块。

select top 50     (total_logical_reads/execution_count) as avg_logical_reads,    (total_logical_writes/execution_count) as avg_logical_writes,    (total_physical_reads/execution_count) as avg_phys_reads,     Execution_count,     statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,substring(sql_text.text, (statement_start_offset/2), case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,  sql_text.text,plan_text.*from sys.dm_exec_query_stats  cross apply sys.dm_exec_sql_text(sql_handle) as sql_textcross apply sys.dm_exec_query_plan(plan_handle) as plan_textorder by --(total_logical_reads + total_logical_writes) /Execution_count Desc (total_logical_reads + total_physical_reads) /Execution_count Desc

可以找到avg_pshs_reads比较大的行，从text字段把对应的sql语句赋值出来。

常用的两种解决办法：

1、优化对应的sql语句（比如增加索引）

2、使用缓存

转载地址：http://hyoof.baihongyu.com/