查看cache中消耗性能的语句代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】查看cache中消耗性能的语句

作者:yangzhawen / 发布于2012/2/21/ 732

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

<div>/*
查询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 <a href="mailto:objtype=@objtype">objtype=@objtype</a> and
<a href="mailto:usecounts=@usecounts">usecounts=@usecounts</a> and size_in_bytes>@size_in_bytes
order by size_in_bytes desc
end
else
begin
print '                              ';
print '系统出错了!';
print '--------------------------';
print '输入类型不对,请核实';
print '--------------------------';
end
</div>

试试其它关键字

　消耗性能　

同语言下

可能有用的

yangzhawen贡献的其它代码(5)