--1. 首先我们来看一下目前数据库系统所有请求情况.sql 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 s.session_id > 50 ORDER BY s.session_id --2. 哪个用户连接数最多.sql 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 > 50group BY login_name ORDER BY 2 DESC --3. 哪台机器发起到数据库的连接数最多.sql 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 > 50group BY HOST_NAME,client_net_address ORDER BY 3 DESC --4. 这些连接在访问哪个库.sql SELECT DB_NAME(r.database_id) AS database_name, 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 > 50group BY r.database_id ORDER BY 2 DESC --5. 进程状态running数比较多，表面数据库压力比较大.sql 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 > 50group BY s.status ORDER BY 2 DESC --1. 查看数据库阻塞情况.sql SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database], t1.resource_associated_entity_id AS [blk object], t1.request_mode AS [lock req] -- lock requested , t1.request_session_id AS [waiter sid] -- spid of waiter , t2.wait_duration_ms AS [wait time], ( SELECT TEXT FROM sys.dm_exec_requests AS r WITH(NOLOCK) --- get sql for waiter CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id ) AS waiter_batch, ( SELECT SUBSTRING( qt.text, r.statement_start_offset / 2, ( CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 + 1 ) FROM sys.dm_exec_requests AS r WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id ) AS waiter_stmt --- statement executing now , t2.blocking_session_id AS [blocker sid] --- spid of blocker , ( SELECT TEXT FROM sys.sysprocesses AS p WITH(NOLOCK) --- get sql for blocker CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id ) AS blocker_stmt, GETDATE() TIME FROM sys.dm_tran_locks AS t1 WITH(NOLOCK), sys.dm_os_waiting_tasks AS t2 WITH(NOLOCK) WHERE t1.lock_owner_address = t2.resource_address --2. 查看阻塞其他进程的进程（阻塞源头）.sql SELECT t2.blocking_session_id, COUNT(0) counts FROM sys.dm_tran_locks AS t1 WITH(NOLOCK), sys.dm_os_waiting_tasks AS t2 WITH(NOLOCK) WHERE t1.lock_owner_address = t2.resource_address GROUP BY blocking_session_id ORDER BY 2 --3. 被阻塞时间最长的进程.sql SELECT TOP 10 t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database], t1.resource_associated_entity_id AS [blk object], t1.request_mode AS [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter , t2.wait_duration_ms AS [wait time], ( SELECT TEXT FROM sys.dm_exec_requests AS r WITH(NOLOCK) --- get sql for waiter CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id ) AS waiter_batch, ( SELECT SUBSTRING( qt.text, r.statement_start_offset / 2, ( CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 + 1 ) FROM sys.dm_exec_requests AS r WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id ) AS waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker , ( SELECT TEXT FROM sys.sysprocesses AS p WITH(NOLOCK) --- get sql for blocker CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id ) AS blocker_stmt, GETDATE() TIME FROM sys.dm_tran_locks AS t1 WITH(NOLOCK), sys.dm_os_waiting_tasks AS t2 WITH(NOLOCK) WHERE t1.lock_owner_address = t2.resource_address ORDER BY t2.wait_duration_ms DESC