第一步：
每一类资源用了多少内存。
select
clerks.type,
sum(clerks.virtual_memory_reserved_kb) as Reserve,
sum(clerks.virtual_memory_committed_kb)as [Commit],
sum(clerks.awe_allocated_kb) as [AWE],
sum(clerks.shared_memory_reserved_kb) as [Sreserve],
sum(clerks.shared_memory_committed_kb) as [SCommit]
from sys.dm_os_memory_clerks as clerks
group by clerks.type;
go
第一步可以给我们一种大致上的把握、哪种对角用了、多少内存。
第二步：
sys.dm_os_buffer_descriptors 、返回有关 SQL Server 缓冲池中当前所有数据页的信息。
select dp.database_id,dp.page_type,sum(dp.row_count) as [RowCount]
from sys.dm_os_buffer_descriptors as dp
where dp.database_id=DB_ID(‘Studio‘)
group by dp.database_id,dp.page_type
order by dp.database_id;
go
第三步：
查看执行计划缓存
select plans.usecounts,plans.objtype,plans.size_in_bytes,stt.text
from sys.dm_exec_cached_plans as plans cross apply sys.dm_exec_sql_text(plans.plan_handle) as stt;
go