2021-09-22

Sql server 锁表语句查寻

–查找被占用进程信息
SELECT s.loginame
,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id
,qs.wait_type ,qs.wait_time ,qs.wait_resource
,qs.transaction_id
FROM SYS.DM_EXEC_REQUESTS qs
LEFT JOIN (
SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES GROUP BY spid
) s ON qs.session_id=s.spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qs.status = N’suspended’
–and s.loginame<>’’
ORDER BY qs.wait_time DESC

–查找占用源信息
SELECT SP.spid
,CASE WHEN ST1.text IS NULL THEN ST2.text
ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2,
(
CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2
ELSE SR.statement_end_offset
END - SR.statement_start_offset)/2
)
END AS [T-sql]
,SP.loginame
,DB_NAME(SP.dbid) AS [db_name]
,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP
LEFT JOIN SYS.DM_EXEC_REQUESTS SR ON SP.spid=SR.session_id
LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC ON SP.spid=SC.session_id
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN
(
SELECT BLOCKED FROM SYS.SYSPROCESSES WHERE BLOCKED<>0
)
AND SP.BLOCKED=0