--查询语句耗用
SELECT TOP 10
SS.plan_handle
,SS.SUM_EXECUTION_COUNT
,SS.SUM_TOTAL_ELAPSED_TIME, SS.SUM_TOTAL_WORKER_TIME, SS.SUM_TOTAL_LOGICAL_READS, SS.SUM_TOTAL_LOGICAL_WRITES
FROM
(
SELECT S.PLAN_HANDLE
,SUM(S.EXECUTION_COUNT) SUM_EXECUTION_COUNT
,SUM(S.TOTAL_ELAPSED_TIME) SUM_TOTAL_ELAPSED_TIME
,SUM(S.TOTAL_WORKER_TIME) SUM_TOTAL_WORKER_TIME
,SUM(S.TOTAL_LOGICAL_READS) SUM_TOTAL_LOGICAL_READS
,SUM(S.TOTAL_LOGICAL_WRITES) SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) as SS
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
select * from SYS.dm_exec_sql_text(0x06000700D52B6C0640A1298B000000000000000000000000)
--监视SQLServer缓冲池的数据库使用的数据总量来监视内存使用情况
SELECT COUNT(*) * 8 / 1024 AS 'Cached Size (MB)',
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY 'Cached Size (MB)' DESC
select COUNT(*) as 'Number Of Plans',
SUM(cast(size_in_bytes AS BIGINT))/2014/2014 AS 'Plan Cache Size (MB)'
from sys.dm_exec_cached_plans