SQL SERVER 语句耗用查看

最新推荐文章于 2022-04-27 11:43:56 发布

感谢一路走过的人

最新推荐文章于 2022-04-27 11:43:56 发布

阅读量247

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/leejin_521/article/details/95986717

版权

SQL 专栏收录该内容

47 篇文章 0 订阅

订阅专栏

--查询语句耗用
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