监视SQLServer数据库内存
可以通过监视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
执行结果为:
查看计划缓存总数及计划缓存占用空间的SQL:
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
执行结果为: