--占用IO 最多的10条SQL SELECT TOP 10 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid, [Total_IO]=total_logical_reads+total_logical_writes FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text ORDER BY total_logical_reads+total_logical_writes DESC--运行次数最多的10条SQL SELECT TOP 10 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid, [Total_IO]=total_logical_reads+total_logical_writes FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text ORDER BY execution_count DESC--re-compile(重复编译)次数最多的10条SQL SELECT TOP 10 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid, [Total_IO]=total_logical_reads+total_logical_writes FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text ORDER BY plan_generation_num DESC
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2:查询动态管理试图 ( sys.dm_exec_query_stats)
-- 具体的字段含义参考 book online
select [text],plan_generation_num,execution_count,max_physical_reads,max_logical_reads
from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle)
看reads、writes
2、被运行次数最多的语句
这个直接group by出来
3、被重用最少的语句
这个也直接group by出来
--占用IO 最多的10条SQL
SELECT TOP 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid,
[Total_IO]=total_logical_reads+total_logical_writes
FROM
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text
ORDER BY total_logical_reads+total_logical_writes DESC--运行次数最多的10条SQL
SELECT TOP 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid,
[Total_IO]=total_logical_reads+total_logical_writes
FROM
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text
ORDER BY execution_count DESC--re-compile(重复编译)次数最多的10条SQL
SELECT TOP 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid,
[Total_IO]=total_logical_reads+total_logical_writes
FROM
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) AS sql_text
ORDER BY plan_generation_num DESC