一个汉化的sp_lock2

最新推荐文章于 2025-09-11 20:53:34 发布

转载最新推荐文章于 2025-09-11 20:53:34 发布 · 90 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lenya/archive/2011/01/07/3706746.html

文章标签：

#数据库

本文介绍了一个SQL Server的存储过程，用于查询指定进程ID（SPID）的锁信息或者在没有参数时显示所有锁信息。该存储过程能够帮助数据库管理员诊断与锁相关的性能问题。

CREATE procedure guan.tool_sp_lock2 --- 2002/11/24 00:00 @spid1 int = NULL, /* server process id to check for locks */ @spid2 int = NULL /* other process id to check for locks */ as set nocount on /* ** Show the locks for both parameters. */ create table #t (spid varchar(100), dbid varchar(100), objid varchar(100), indid varchar(100), type varchar(100), resource varchar(100), mode varchar(100), status varchar(100) ) if @spid1 is not NULL begin insert into #t (spid, dbid, objid, indid, type, resource, mode, status) select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR ' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS ' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L ' and req_spid in (@spid1, @spid2) end /* ** No parameters, so show all the locks. */ else begin insert into #t (spid, dbid, objid, indid, type, resource, mode, status) select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR ' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS ' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L ' order by spid end select spid, dbid, db_name(dbid) as '数据库 ', Objid, object_name(objid) as '对象名称 ', indid, object_name(indid) as '索引名称 ', type, case type when 'DB ' then '数据库 ' when 'FIL ' then '文件 ' when 'IDX ' then '索引 ' when 'PAG ' then '页面 ' when 'KEY ' then '索引键值 ' when 'TAB ' then '表 ' when 'TEXT ' then '区域 ' when 'RID ' then '行标志号 ' end as '资源标志 ', resource, mode, case upper(mode) when 'S ' then '共享锁 ' when 'X ' then '排它锁 ' when 'IS ' then '意向锁 ' when 'IX ' then '意向排它锁 ' when 'SIX ' then '共享意向排它锁 ' when 'SCH-S ' then '调度稳定性锁 ' when 'SCH-M ' then '调度修改锁 ' when 'BU ' then '批量更新锁 ' end as '锁定模式 ', status, case status when 'GRANT ' then '锁定状态 ' when 'WAIT ' then '等待状态 ' when 'CNVRT ' then '转换状态 ' end as '请求状态 ' from #t return (0) -- sp_lock2 GO

引用swordmanli在 awaiting command 这语句是什么意思- MS-SQL Server / 基础类上的回答,谢谢swordmanli

转载于:https://www.cnblogs.com/lenya/archive/2011/01/07/3706746.html