数据库连接情况查询
--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 t1inner 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_idright 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_timego---哪個用戶連接數最多：
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_idright 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 descgo
---哪臺機器發起到數據庫的連接數最多：
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_idright 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_idright 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 descgo--查看數據庫阻塞情況
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 textfrom 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)) * 2else r.statement_end_offsetend - 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 qtwhere r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now , t2.blocking_session_id as [blocker sid] --- spid of blocker , ( select textfrom 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_addressgoselect 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_idright 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 descgo--查看阻塞其他進程的進程（阻塞源頭）
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 2go
--被阻塞時間最長的進程
select top 10t1.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 textfrom 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)) * 2else r.statement_end_offsetend - 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 qtwhere r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now , t2.blocking_session_id as [blocker sid] --- spid of blocker , ( select textfrom 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
轉載于:https://www.cnblogs.com/wolfocme110/p/7728814.html
總結
- 上一篇： 信用卡怎么薅羊毛？什么信用卡适合薅羊毛？
- 下一篇： 民生香格里拉白金卡年费多少？年费可以免吗