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
查看本欄目
