数据库系统表（查询缓存、连接数）

 

系统表：

--连接
select * from Sys.dm_exec_connections
WHERE  session_id > 50

--请求
select * from Sys.dm_exec_requests
WHERE  session_id > 50

--会话
select * from Sys.dm_exec_sessions
WHERE  session_id > 50

--文件
select * from sysfiles

--用户表
select * from sysusers

--系统表

select * from SYSDATABASES

 

--查看连接数
SELECT login_name,
       Count(0) user_count
FROM   Sys.dm_exec_requests dr WITH(nolock)
       RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
                     ON dr.session_id = ds.session_id
       RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
                     ON ds.session_id = dc.session_id
WHERE  ds.session_id > 50
GROUP  BY login_name
ORDER  BY user_count DESC

 

--查询缓存
 select COUNT(*)*8/1024 as 'Cache Size',
       case database_id 
        when 32767 then 'ResourceDb'
        else DB_NAME(database_id) end as 'Database'
from sys.dm_os_buffer_descriptors
group by DB_NAME(database_id),database_id
order by 'Cache Size' desc

 

转载于:https://www.cnblogs.com/amusement1992/p/10036639.html