数据库连接情况

 
--所有请求情况：

 
SELECT  s.session_id ,
        s.status ,
        DB_NAME(r.database_id) AS database_name ,
        s.login_name ,
        s.login_time ,
        s.host_name ,
        c.client_net_address ,
        c.client_tcp_port ,
        s.program_name ,
        r.cpu_time ,
        r.reads ,
        r.writes ,
        c.num_reads ,
        c.num_writes ,
        s.client_interface_name ,
        s.last_request_start_time ,
        s.last_request_end_time ,
        c.connect_time ,
        c.net_transport ,
        c.net_packet_size ,
        r.start_time ,
        r.status ,
        r.command ,
        r.blocking_session_id ,
        r.wait_type ,
        r.wait_time ,
        r.last_wait_type ,
        r.wait_resource ,
        r.open_transaction_count ,
        r.percent_complete ,
        r.granted_query_memory
FROM    Sys.dm_exec_requests r WITH ( NOLOCK )
        RIGHT OUTER JOIN Sys.dm_exec_sessions s WITH ( NOLOCK ) ON r.session_id = s.session_id
        RIGHT OUTER JOIN Sys.dm_exec_connections c WITH ( NOLOCK ) ON s.session_id = c.session_id
WHERE   1 = 1
        AND s.session_id > 50
ORDER BY s.session_id

-- 哪个用户连接数最多：

SELECT  login_name ,
        COUNT(0) user_count
FROM    Sys.dm_exec_requests r WITH ( NOLOCK )
        RIGHT OUTER JOIN Sys.dm_exec_sessions s WITH ( NOLOCK ) ON r.session_id = s.session_id
        RIGHT OUTER JOIN Sys.dm_exec_connections c WITH ( NOLOCK ) ON s.session_id = c.session_id
WHERE   s.session_id > 50
GROUP BY login_name
ORDER BY 2 DESC

 
--哪台机器发起到数据库的连接数最多：

SELECT  s.host_name ,
        c.client_net_address ,
        COUNT(0) host_count
FROM    Sys.dm_exec_requests r WITH ( NOLOCK )
        RIGHT OUTER JOIN Sys.dm_exec_sessions s WITH ( NOLOCK ) ON r.session_id = s.session_id
        RIGHT OUTER JOIN Sys.dm_exec_connections c WITH ( NOLOCK ) ON s.session_id = c.session_id
WHERE   s.session_id > 50
GROUP BY host_name ,
        client_net_address
ORDER BY 3 DESC

 

 

-- 进程状态：

SELECT  s.status ,
        COUNT(0) host_count
FROM    Sys.dm_exec_requests r WITH ( NOLOCK )
        RIGHT OUTER JOIN Sys.dm_exec_sessions s WITH ( NOLOCK ) ON r.session_id = s.session_id
        RIGHT OUTER JOIN Sys.dm_exec_connections c WITH ( NOLOCK ) ON s.session_id = c.session_id
WHERE   s.session_id > 50
GROUP BY s.status
ORDER BY 2 DESC

--结果（running数比较多，表面数据库压力比较大）：