查看cache中消耗性能的语句

sqlserver服务器内存偏高，查看下cache中sql消耗情况！

/*
查询cache中的语句
说明:可以根据类型、用户数、大小查询

*/

declare @objtype varchar(20)
declare @usecounts int
declare @size_in_bytes int
set @objtype='Adhoc'   --只能是Adhoc、proc、Prepared ,默认是Adhoc
set @usecounts=1     --数目,默认是1
set @size_in_bytes=1  --大小,默认是1
print '输入的对象类型为@objtype';
print '输入缓存次数为@usecounts';
print '输入缓存大小为@size_in_bytes';

if @objtype  in ('Adhoc','proc','Prepared')
begin
select  top 1000 usecounts as [缓存次数],
size_in_bytes/1024 as [占用Mb],
cacheobjtype as [缓存类型],
objtype as [对象类型],
b.text as [语句]
from sys.dm_exec_cached_plans a ,
(select   plan_handle,
qt.text FROM   
sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt ) b
where a.plan_handle=b.plan_handle and objtype=@objtype and
usecounts=@usecounts and size_in_bytes>@size_in_bytes
order by size_in_bytes desc
end
else
begin
print '                              ';
print '系统出错了!';
print '--------------------------';
print '输入类型不对,请核实';
print '--------------------------';
end