------------================================----------------------------
--查询数据库里IO开销最大的前十条查询语句的信息
------------================================----------------------------
SELECT TOP 10
[IO总计] = (qs.total_logical_reads + qs.total_logical_writes)--IO总计
, [平均IO] = (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count--IO平均值
, qs.execution_count AS [执行的次数]--计划自上次编译以来所执行的次数
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]--单个语句
, qt.text AS [Parent Query]--整个语句块
, DB_NAME(qt.dbid) AS DatabaseName--对应的数据库名字
, qp.query_plan--执行计划
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [IO总计] DESC
--查询数据库里IO开销最大的前十条查询语句的信息
------------================================----------------------------
SELECT TOP 10
[IO总计] = (qs.total_logical_reads + qs.total_logical_writes)--IO总计
, [平均IO] = (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count--IO平均值
, qs.execution_count AS [执行的次数]--计划自上次编译以来所执行的次数
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]--单个语句
, qt.text AS [Parent Query]--整个语句块
, DB_NAME(qt.dbid) AS DatabaseName--对应的数据库名字
, qp.query_plan--执行计划
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [IO总计] DESC