【047】在 SQL Server 中查询锁表的表名

锁表进程查询与释放

select request_session_id 锁表进程,OBJECT_NAME(resource_associated_entity_id) 被锁表名
from sys.dm_tran_locks where resource_type='OBJECT'  
and OBJECT_NAME(resource_associated_entity_id) is not null
order by request_session_id

-- kill 74 --进程ID

详细信息

select t1.resource_type                                [资源锁定类型]
     , DB_NAME(resource_database_id)                as 数据库名
     , t1.resource_associated_entity_id                锁定对象
     , t1.request_mode                              as 等待者请求的锁定模式
     , t1.request_session_id                           等待者SID
     , t2.wait_duration_ms                             等待时间
     , (select TEXT
        from sys.dm_exec_requests r
               cross apply
             sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id) as 等待者要执行的SQL
     , t2.blocking_session_id                          [锁定者SID]
     , (select TEXT
        from sys.sysprocesses p
               cross apply
             sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
)                                                      锁定者执行语句
from sys.dm_tran_locks t1,
     sys.dm_os_waiting_tasks t2
where t1.lock_owner_address = t2.resource_address

通过查询器查询当前慢SQL

SELECT TOP (100)
    (qs.total_elapsed_time/qs.execution_count)/1000 N'平均时间ms' 
    ,qs.total_elapsed_time/1000 N'总花费时间ms' 
    ,qs.total_worker_time/1000 N'所用的CPU总时间ms' 
    ,qs.total_physical_reads N'物理读取总次数' 
    ,qs.total_logical_reads/qs.execution_count N'每次逻辑读次数' 
    ,qs.total_logical_reads N'逻辑读取总次数' 
    ,qs.total_logical_writes N'逻辑写入总次数' 
    ,qs.execution_count N'执行次数' 
    ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句' 
    ,qs.creation_time N'语句编译时间' 
    ,qs.last_execution_time N'上次执行时间' 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time/qs.execution_count DESC;