--sp_who 可以指定数据库名，查询指定数据库的连接情况
sp_who
go
select DB_NAME(database_id) dbname, login_name, t1.session_id, t1.request_id, t2.status, t1.start_time, host_name
from sys.dm_exec_requests t1
inner join sys.dm_exec_sessions t2 on t1.session_id = t2.session_id
go
--包含正在 Microsoft SQL Server 实例上运行的进程的相关信息。这些进程可以是客户端进程或系统进程。若要访问 sysprocesses，您必须位于 master 数据库上下文中，或者必须使用由三部分构成的名称 master.dbo.sysprocesses。
select * from sys.sysprocesses
--查询指定数据库的连接情况
select *
from [Master].[dbo].[SYSPROCESSES]
where [DBID] in ( select [DBID]
from [Master].[dbo].[SYSDATABASES]
where name='bpm'
)
go
---数据库系统所有请求情况
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.login_time
go
---哪个用户连接数最多：
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
go
---哪台机器发起到数据库的连接数最多：
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
go
--进程状态
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
go
--查看数据库阻塞情况
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
go
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 > 50
group by r.database_id
order by 2 desc
go
--查看阻塞其他进程的进程（阻塞源头）
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
go
--被阻塞时间最长的进程
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
