sql server top 10 IO性能查询

最新推荐文章于 2022-08-18 16:27:19 发布

weixin_33834137

最新推荐文章于 2022-08-18 16:27:19 发布

阅读量834

点赞数

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/593044

版权

use master
go

select top 10 substring(qt.text,(qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end -qs.statement_start_offset)/2)+1)
,database_name=db.name
,qs.execution_count
,qs.total_logical_reads
,logical_reads_per_run=cast(qs.total_logical_reads*1.0/qs.execution_count as decimal(12,2))
,qs.last_logical_reads
,qs.total_logical_writes
,qs.last_logical_writes
,qs.total_worker_time
,qs.last_worker_time
,total_elapsed_time_in_s=qs.total_elapsed_time*1.0/1000000
,last_elapsed_time_in_s=qs.last_elapsed_time*1.0/1000000
,qs.last_execution_time
,qp.query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
inner join sys.databases as db
on qp.dbid=db.database_id
--where qs.execution_count>=100
--order by qs.total_logical_reads/qs.execution_count desc --logical reads per run
--order by qs.total_logical_writes/qs.execution_count desc --logical writes per run
--order by qs.total_worker_time/qs.execution_count desc --cpu time per run
--order by qs.total_logical_reads desc --logical reads
--order by qs.total_logical_writes desc --logical writes
order by qs.total_worker_time desc --cpu time