查看锁表SQL(blocking_session_id为锁表session_id)
```sql
SELECT
es.session_id,
database_name = DB_NAME(er.database_id),
er.cpu_time,
er.reads,
er.writes,
er.logical_reads,
login_name,
er.status,
blocking_session_id,
wait_type,
individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2),
parent_query = qt.text,
program_name,
host_name,
nt_domain,
start_time
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
es.session_id > 50
AND es.session_Id NOT IN (@@SPID)
ORDER BY
1, 2
```
查询session_id的tcp连接信息(获取到对方的ip和端口)
```sql
SELECT *
FROM sys.dm_exec_connections
WHERE session_id = [session_id]
```
`其他获取session_id的信息方法`
创建sp_who3
```sql
CREATE PROCEDURE sp_who3
(
@SessionID INT = NULL
)
AS
BEGIN
SELECT
SPID = er.session_id
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM
sys.dm_exec_requests er
OUTER APPLY
sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN
sys.dm_exec_sessions ses
ON
ses.session_id = er.session_id
LEFT JOIN
sys.dm_exec_connections con
ON
con.session_id = ses.session_id
WHERE
er.session_id > 50
AND
@SessionID IS NULL OR er.session_id = @SessionID
ORDER BY
er.blocking_session_id DESC
,er.session_id
END
GO
```
查看session_id的信息
```sql
exec [db_name].dbo.sp_who3 [session_id]
```
