[個人紀錄]MS SQL連線數查詢

查詢目前連線數：
USE master
SELECT cntr_value AS User_Connections FROM sys.sysperfinfo AS sp
WHERE sp.object_name='SQLServer:General Statistics'
AND sp.counter_name='User Connections'

查詢目前連線明細：
USE master
SELECT c.session_id, c.connect_time, s.login_time, c.client_net_address, s.login_name, s.status
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
查詢目前連線數2：
select * from master.dbo.sysprocesses
SELECT c.session_id, c.connect_time,s.login_time, c.client_net_address,
s.login_name,s.status
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s
on c.session_id = s.session_id

要踢出去的連線編號，就可以把咬住的連線踢出去了
sp_lock
dbcc inputbuffer(58)
kill  58

查詢前10名最常使用之SQL指令:
SELECT TOP 10
substring(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( case statement_end_offset when -1 then DATALENGTH (st.text) else QS.statement_end_offset end - QS.statement_start_offset ) / 2 ) + 1 ) as statement_text ,
total_worker_time / 1000 as total_worker_time_ms,
execution_count,
( total_worker_time / 1000 ) / execution_count as avg_worker_time_ms,
total_logical_reads,
total_logical_reads / execution_count as avg_logical_reads,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC