欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlServer性能优化,查看CPU、内存占用大的会话及SQL语句

程序员文章站 2022-04-15 15:27:07
1,查看CPU占用量最高的会话及SQL语句 select spid,cmd,cpu,physical_io,memusage, (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text from master..sysprocesses ......
1,查看cpu占用量最高的会话及sql语句
 
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
 
2,查看缓存重用次数少,内存占用大的sql语句
 
select top 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql 
order by usecounts,p.size_in_bytes  desc