查看cache中消耗性能的语句

查看cache中消耗性能的语句2014-03-14 csdn博客 ocpyangsqlserver服务器内存偏高，查看下cache中sql消耗情况！

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

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

if @objtype in （'Adhoc','proc','Prepared'）beginselect 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 ） bwhere a.plan_handle=b.plan_handle and objtype=@objtype andusecounts=@usecounts and size_in_bytes>@size_in_bytesorder by size_in_bytes descendelsebeginprint ' ';print '系统出错了！';print '--------------------------';print '输入类型不对,请核实';print '--------------------------';end