处理 SQL SERVER 数据库的连接查询相关问题

-- 查询数据库锁情况
SELECT request_session_id [会话ID], DB_NAME(resource_database_id) [数据库名], OBJECT_NAME(resource_associated_entity_id) [表名]   
FROM   sys.dm_tran_locks WHERE resource_type = 'OBJECT'

-- 查询指定数据库的连接情况（去掉条件就是所有）
SELECT * FROM master.dbo.sysprocesses
WHERE dbid = DB_ID('psydb_zknu')

-- 查询数据库某用户的连接情况
sp_who 'sa'

-- 关闭数据库连接（传入会话ID，连接关闭后，锁也就释放了）
kill 123

-- 查询哪个会话引起阻塞并且它们在运行什么
SELECT  DTL.[request_session_id] AS [session_id] ,
        DB_NAME(DTL.[resource_database_id]) AS [Database] ,
        DTL.resource_type ,
        CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )
             THEN DTL.resource_type
             WHEN DTL.resource_type = 'OBJECT'
             THEN OBJECT_NAME(DTL.resource_associated_entity_id,
                              DTL.[resource_database_id])
             WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )
             THEN ( SELECT  OBJECT_NAME([object_id])
                    FROM    sys.partitions
                    WHERE   sys.partitions.hobt_id = DTL.resource_associated_entity_id
                  )
             ELSE 'Unidentified'
        END AS [Parent Object] ,
        DTL.request_mode AS [Lock Type] ,
        DTL.request_status AS [Request Status] ,
        DER.[blocking_session_id] ,
        DES.[login_name] ,
        CASE DTL.request_lifetime
          WHEN 0 THEN DEST_R.TEXT
          ELSE DEST_C.TEXT
        END AS [Statement]
FROM    sys.dm_tran_locks DTL
        LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id]
        INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]
        OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C
        OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R
WHERE   DTL.[resource_database_id] = DB_ID()
        AND DTL.[resource_type] NOT IN ( 'DATABASE', 'METADATA' )
ORDER BY DTL.[request_session_id];