用sp_lock诊断SQL Sever的性能问题

sp_lock (Transact-SQL)

SQL Server 2008 R2

其他版本

• SQL Server 2008
• SQL Server 2005

报告有关锁的信息。

重要提示
后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。若要获取有关 SQL Server 数据库引擎中的锁的信息，请使用 sys.dm_tran_locks 动态管理视图。

Transact-SQL 语法约定

语法

---

复制

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]
[ ; ]

参数

---

[ @spid1 = ] ' session ID1 '

来自用户想要锁定其信息的 sys.dm_exec_sessions 的 数据库引擎 会话 ID（SQL Server 2000 和更早版本中的 SPID）号。session ID1 是 int，默认值是 NULL。执行 sp_who 可获取有关该会话的进程信息。如果未指定会话 ID1，则显示有关所有锁的信息。

[ @spid2 = ] ' session ID2 '

来自 sys.dm_exec_sessions 的另一个数据库引擎进程 ID 号，该进程 ID 号可能与 session ID1 同时具有锁，并且用户也需要其有关信息。session ID2 是 int，默认值为 NULL。

返回代码值

---

0（成功）

结果集

---

在 sp_lock 结果集中，由 @spid1 和 @spid2 参数指定的会话所持有的每个锁都对应一行。如果既未指定 @spid1 又未指定 @spid2，则结果集将报告当前在数据库引擎实例中处于活动状态的所有会话的锁。

列名	数据类型	说明
spid	smallint	请求锁的进程的数据库引擎会话 ID 号。
dbid	smallint	保留锁的数据库的标识号。可以使用 DB_NAME() 函数来标识数据库。
ObjId	int	持有锁的对象的标识号。可以在相关数据库中使用 OBJECT_NAME() 函数来标识对象。值为 99 时是一种特殊情况，表示用于记录数据库中页分配的其中一个系统页的锁。
IndId	smallint	持有锁的索引的标识号。
Type	nchar(4)	锁的类型： RID = 表中单个行的锁，由行标识符 (RID) 标识。 KEY = 索引内保护可串行事务中一系列键的锁。 PAG = 数据页或索引页的锁。 EXT = 对某区的锁。 TAB = 整个表（包括所有数据和索引）的锁。 DB = 数据库的锁。 FIL = 数据库文件的锁。 APP = 指定的应用程序资源的锁。 MD = 元数据或目录信息的锁。 HBT = 堆或 B 树索引的锁。在 SQL Server 中此信息不完整。 AU = 分配单元的锁。在 SQL Server 中此信息不完整。
Resource	nchar(32)	标识被锁定资源的值。值的格式取决于 Type 列标识的资源类型： Type 值：Resource 值 RID：格式为 fileid:pagenumber:rid 的标识符，其中 fileid 标识包含页的文件，pagenumber 标识包含行的页，rid 标识页上的特定行。fileid 与 sys.database_files 目录视图中的 file_id 列相匹配。 KEY：数据库引擎内部使用的十六进制数。 PAG：格式为 fileid:pagenumber 的数字，其中 fileid 标识包含页的文件，pagenumber 标识页。 EXT：标识区中的第一页的数字。该数字的格式为 fileid:pagenumber。 TAB：没有提供信息，因为已在 ObjId 列中标识了表。 DB：没有提供信息，因为已在 dbid 列中标识了数据库。 FIL：文件的标识符，与 sys.database_files 目录视图中的 file_id 列相匹配。 APP：被锁定的应用程序资源的唯一标识符。格式为 DbPrincipleId:<资源字符串的前 2 个到 16 个字符><哈希运算值>。 MD：随资源类型而变化。有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL) 中 resource_description 列的说明。 HBT：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。 AU：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。
模式	nvarchar(8)	所请求的锁模式。可以是： NULL = 不授予对资源的访问权限。用作占位符。 Sch-S = 架构稳定性。确保在任何会话持有对架构元素（例如表或索引）的架构稳定性锁时，不删除该架构元素。 Sch-M = 架构修改。必须由要更改指定资源架构的任何会话持有。确保没有其他会话正在引用所指示的对象。 S = 共享。授予持有锁的会话对资源的共享访问权限。 U = 更新。指示对最终可能更新的资源获取的更新锁。用于防止一种常见的死锁，这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。 X = 排他。授予持有锁的会话对资源的独占访问权限。 IS = 意向共享。指示有意将 S 锁放置在锁层次结构中的某个从属资源上。 IU = 意向更新。指示有意将 U 锁放置在锁层次结构中的某个从属资源上。 IX = 意向排他。指示有意将 X 锁放置在锁层次结构中的某个从属资源上。 SIU = 共享意向更新。指示对有意在锁层次结构中的从属资源上获取更新锁的资源进行共享访问。 SIX = 共享意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源进行共享访问。 UIX = 更新意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源持有的更新锁。 BU = 大容量更新。用于大容量操作。 RangeS_S = 共享键范围和共享资源锁。指示可串行范围扫描。 RangeS_U = 共享键范围和更新资源锁。指示可串行更新扫描。 RangeI_N = 插入键范围和 Null 资源锁。用于在将新键插入索引前测试范围。 RangeI_S = 键范围转换锁。由 RangeI_N 和 S 锁的重叠创建。 RangeI_U = 由 RangeI_N 和 U 锁的重叠创建的键范围转换锁。 RangeI_X = 由 RangeI_N 和 X 锁的重叠创建的键范围转换锁。 RangeX_S = 由 RangeI_N 和 RangeS_S 锁的重叠创建的键范围转换锁。 RangeX_U = 由 RangeI_N 和 RangeS_U 锁的重叠创建的键范围转换锁。 RangeX_X = 排他键范围和排他资源锁。这是在更新范围中的键时使用的转换锁。
Status	nvarchar(5)	锁的请求状态： CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。 GRANT：已获取锁。 WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。

注释

---

用户可以通过下列方式控制读取操作的锁定：

• 使用 SET TRANSACTION ISOLATION LEVEL 指定会话的锁定级别。有关语法和限制，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

• 使用锁定表提示来为 FROM 子句中引用的各个表指定锁定级别。有关语法和限制，请参阅表提示 (Transact-SQL)。

有关数据库引擎使用的锁类型的详细信息，请参阅数据库引擎中的锁定。

所有没有与会话相关联的分布式事务都是孤立事务。数据库引擎为所有孤立的分布式事务赋予 SPID 值 -2，这使得用户能够更容易标识阻塞的分布式事务。有关详细信息，请参阅使用标记的事务（完整恢复模式）。

权限

---

需要 VIEW SERVER STATE 权限。

示例

---

A. 列出所有锁

以下示例显示数据库引擎实例当前持有的所有锁的信息。

复制

USE master;
GO
EXEC sp_lock;
GO

B. 列出单服务器进程的锁

以下示例显示进程 ID 53 的信息（其中包括锁信息）。

复制

USE master;
GO
EXEC sp_lock 53;
GO

在IT 专家中有一种普遍的误解，就是认为“锁定是不好的东西”，你必须尽一切可能保证数据库锁定不会使得进程无法正常运行。为了能够确保一个一致的数据库环境，在对资源进行修改时，数据库引擎必须利用一种机制来获得对资源的独占权。
SQL Server中也用锁定，它们是指为了达到这种一致性，数据库引擎用来保证每一次只有一个线程同时访问同一个资源的对象。如果不用锁定的话，各个进程同时进行数据修改就可能发生，这就会使数据库处于一种不一致的状态。这样看来，锁定就成了好东西；但是，你应该以特定的方式来计划你的应用程序，让涉及的锁定的数量降到最少。在这篇文章中，我将讨论一个让你能够分析数据库锁定问题的存储过程。
找出什么被锁定了
系统的反应迟缓意味着你应该做一些调查了。你的查找最好从测定系统发生锁定的数量和频率开始。如果你的系统环境处理事务性很高的话，这样各个应用程序争夺资源就会很常见，从而引起锁定。解决这些问题的关键就在于能够确定被锁定的资源和争夺资源的进程。
sp_lock
sp_lock这个系统存储过程与SQL Server 2000 打包在一起，它将使你对在你系统中发生的锁定有深入的了解。这个程序会从主数据库中的syslockinfo中返回与锁定相关的大量信息，而主数据库是一个包括了所有允许、转换和等待锁定请求信息的系统工作台。
让我们来看一下运行 sp_lock 程序之后，它会为我们提供什么信息：
EXECUTE sp_lock 
在我的系统中，这是该存储过程返回的内容。sp_lock 返回的信息并不是一目了然的，要获得有用的数据，还需要做一些查找。但是，你也可以复制该存储过程的文本，然后创建一个新的，从而得到关于系统进程的更好的解释。（在这篇文章中，我们将集中讨论sp_lock返回的数据。）
从上面的结果我们可以看到spid、dbid、objid、indid、type、resource、mode和status字段。spid是进程标识号码，用于识别到SQL 服务器的连接。要发现哪些用户和该spid相连，你就要执行存储过程sp_who，并将spid作为一个参数传输给该程序。dbid是锁定发生的数据库，你可以在主数据库中的sysdatabases表格中找到它。字段objid用来显示在数据库中锁定发生所在的对象。要查看这个对象，你可以在主数据库中的sysobjects表格中查询指定的objid。
在以上的屏幕截图中产生的单一记录并不一定能显示正在你的工作环境中发生的真实情况。在运行这个程序时，你想要找到500到1000个甚至更多结果。每一次你执行sp_lock，都将有可能得到不同的结果，因为又发生了新的锁定，而部分旧的锁定已经被解除了。如果你发现sp_lock返回的结果中，大量的结果都有着相同的spid，很有可能该进程正在进行大型的处理，同时这些锁定可能开始阻止新事务的发生。
当你发现一个spid 获得了大量的数据库锁定时，这将有助于确定什么存储过程或语句正在运行。为了达到这个目的，运行以下 DBCC 命令：
DBCC INPUTBUFFER(spid) 
这个DBCC命令将返回正在EventInfo字段中运行的语句的相关信息。
一个可靠的起点
系统运行缓慢可能说明你的表格上有大量的锁定。造成这些锁定的原因较多，如某个用户正在你的系统中运行一个相当长的查询，一个进程占用大量资源或者两个关键进程争夺同一资源，经常造成死锁。
一旦发现你认为正在减缓你系统速度的进程，应该怎么办？在大多数情况下，不能采取任何措施，只能监控系统。结束这个进程并不是明智之举，因为它包括了很多系统锁定，除非你完全肯定不会有其他的负面影响。不然的话，你就应该想办法自动分析锁定状况。还有一个解决办法就是想出一种方法，使得在一天的特定时间内，当系统锁数量达到极限时，发出通知。
你对自己的系统信息收集的越多，在解决问题时，你的优势就越大。
Tim Chapman 是肯塔基州路易维尔市一家银行的SQL Server数据库管理员，他有超过7年的行业经验。