--All Connections:
--Find out which Client IP and Query using highest network bandwidth from SQL Server
--Do not lock anything and do not get held up by any locks.
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
CASE WHEN ST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_NAME,
ST.TEXT AS QUERY_TEXT, C.CONNECT_TIME, C.LAST_WRITE, C.LAST_READ,
C.NUM_READS, --Number of packet reads made on this connection.
C.NUM_WRITES, --Number of data packets that are written on this connection. (TDS/TCP Data Packets sent over the wire)
C.NUM_READS + C.num_writes 'NUM READS + NUM WRITES',
C.SESSION_ID, C.MOST_RECENT_SESSION_ID, C.NET_TRANSPORT, C.ENCRYPT_OPTION, C.AUTH_SCHEME,
C.PROTOCOL_TYPE, C.PROTOCOL_VERSION, C.NET_PACKET_SIZE, C.ENDPOINT_ID, C.CLIENT_NET_ADDRESS,
C.CLIENT_TCP_PORT, C.LOCAL_NET_ADDRESS, C.LOCAL_TCP_PORT, C.NODE_AFFINITY, C.CONNECTION_ID,
C.PARENT_CONNECTION_ID, C.MOST_RECENT_SQL_HANDLE,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_SCHEMA_NAME
FROM
SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST
WHERE
session_id <> @@SPID
ORDER BY 'NUM READS + NUM WRITES' DESC
--ORDER BY 'LAST_WRITE' DESC
--Connections aggregreted by Host, Program:
SELECT
HOST_NAME = HOST_NAME,
Program = program_name
,CPU = SUM(cpu_time)
,WaitTime = SUM(total_scheduled_time)
,ElapsedTime = SUM(total_elapsed_time)
,Reads = SUM(num_reads)
,Writes = SUM(num_writes)
,Connections = COUNT(1)
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = con.session_id
GROUP BY HOST_NAME, program_name
ORDER BY Connections DESC
--Connections aggregreted by Login
SELECT
[Login] = UPPER(original_login_name)
,CPU = SUM(cpu_time)
,WaitTime = SUM(total_scheduled_time)
,ElapsedTime = SUM(total_elapsed_time)
,Num_Reads = SUM(num_reads)
,Num_Writes = SUM(num_writes)
,Connections = COUNT(1)
from sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = con.session_id
GROUP BY original_login_name
ORDER BY Connections desc
--Requests:
SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle)
SELECT
s.Session_id,
r.Status,
r.open_transaction_count as 'TRX_Open', --Number of transactions that are open for this request
r.blocking_session_id 'Blk by',
r.Wait_Type, --If the request is currently blocked, this column returns the type of wait.
r.Last_Wait_Type, --If this request has previously been blocked, this column returns the type of the last wait.
r.Wait_Resource, --If the request is currently blocked, this column returns the resource for which the request is currently waitin
r.Wait_Time / (1000.0) 'Wait Sec', --If the request is currently blocked, this column returns the duration in milliseconds, of the current wait.
r.CPU_Time, --CPU time in milliseconds that is used by the request
r.Logical_Reads,
r.Reads,
r.Writes,
r.total_elapsed_time / (1000.0) 'Elaps Sec', --otal time elapsed in milliseconds since the request arrived
db_name(r.database_id) as DBName,
'"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS Statement_Text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid))
+ N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'')+'"' AS Command_Text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
r.blocking_session_id,
s.session_id
sysprocesses maps to:
sys.dm_exec_connections Dynamic management view
sys.dm_exec_sessions Dynamic management view
sys.dm_exec_requests Dynamic management view
Connections, sessions, Requests, Tasks, Workers:
Select * from sys.dm_exec_connections order by session_id asc --Connection information.
--The obvious reason why spid <= 50 are not reported in sys.dm_exec_connections is because internal SQL server processes don't need to make SQL
--Server connection at all like external users or client applications need to.
Select * from sys.dm_exec_sessions order by session_id asc --Session Information
Select * from sys.dm_exec_requests order by session_id asc --Request Information
Select * from sys.dm_os_tasks --Task Information
Select * from sys.dm_os_workers --Worker Information
Resources:
Different Status of a SPID in SQL Server and What do they mean
Understanding how SQL Server executes a query
|
|
|
|
|
|