sys.dm_tran_locks&&sp_lock系统存储过程-浅谈

做为系统存储过程，sp_lock可以用来了解服务器的运行情况，通过查看系统的锁定信息诊断SQL Server可能出现的问题。不过系统存储过程sp_lock本身存在一些缺陷。对于数据库管理新手来说，其返回的结果不够直白，花费了大量的工作来显示系统中哪个会话造成了最多锁定，却并没有提供多少关于这些对象或会话的相应详细信息。虽然我们可以创建自定义的脚本来查看这些信息，但是返回的结果往往过于复杂，而充其量能返回一些质量低下的信息。也有其他的一些系统表可以用来查看锁定信息，例如syslockinfo，但信息的细节同样不够明了。此外，sp_lock和syslockinfo还有一个更大的问题，那就是他们都是“不建议使用的特性”，所以将来的SQL Server版本中可能不再包含这些特性。SQL Server 2005提供的新的动态管理视图包含了大量锁定细节，并使我们能够将锁定信息关联起来，看起来可以更一目了然。

　　sys.dm_tran_locks

　　新的动态视图sys.dm_tran_locks能够返回系统中当前活动的锁管理器资源信息。这个视图返回的信息类型和sp_lock一样，但提供了更多细节。关键是这是一个视图，允许数据库管理员轻松的将其连接到其他表。

　　自定义sp_lock例子

USE MASTER
GO  
CREATE PROCEDURE [dbo].[sp_Lock_Detail]
AS
BEGIN
   SELECT

     SessionID = s.Session_id,
     resource_type,  
     DatabaseName = DB_NAME(resource_database_id),
     request_mode,
     request_type,
     login_time,
     host_name,
     program_name,
     client_interface_name,
     login_name,
     nt_domain,
     nt_user_name,
     s.status,
     last_request_start_time,
     last_request_end_time,
     s.logical_reads,
     s.reads,
     request_status,
     request_owner_type,
     objectid,
     dbid,
     a.number,
     a.encrypted ,
     a.blocking_session_id,
     a.text   
   FROM  
     sys.dm_tran_locks l
     JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
     LEFT JOIN  
     (
       SELECT *
       FROM  sys.dm_exec_requests r
       CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
     ) a ON s.session_id = a.session_id
   WHERE
     s.session_id > 50
   END
首先来看看在这个程序中使用的JOIN语句。该语句将sys.dm_tran_locks表和sys.dm_exec_sessions连接起来，以检索关于当前服务器会话的锁定信息。JOIN语句使我们能够将会话的详细信息和与该会话相应的锁的详细信息进行关联。

　　而后，程序在子查询中使用了LEFT JOIN语句，用来检索与目前正在执行的语句执行过程相关的信息。使用LEFT JOIN子句是因为服务器中很可能有会话持有目前没有执行的某种特定类型的锁。如果有查询执行数据当然是最好的了，如果没有，那也不用担心，因为有LEFT JOIN。

　　注意在子查询中使用了带有CROSS APPLY操作符的sys.dm_exec_sql_text函数。这样我们就可以使用存储在sys.dm_exec_requests视图中的sql_handle字段来确定正在执行的语句。sql_handle包含当前正在执行的SQL语句的哈希值，如果你想要解决出现的问题，这就是最有用的信息之一了。

　　需要注意的是，强烈建议不要使用“*”从一个查询中返回所有的行(尤其是在一个生产数据库中)，这里只是为了举例而使用的。

　　该查询剩下的部分用来返回从视图和在连接中使用子查询所获得的详细信息。在WHERE语句中，过滤掉任何小于等于50的数据库会话，以消除任何系统会话的影响。在这个系统过程中，只需要关注用户会话。可以以视图形式实施以上的过程而不需要用存储过程

　　标记一个系统存储过程

　　我们可以把自己创建的存储过程标记为系统存储过程，这样就可以在任何数据库环境中运行该存储过程，并检索该数据库的特定信息。主数据库里创建一个对象，算完成了将一个对象标记为系统存储过程的第一步。一旦在主数据库中有了该存储过程，接下来就要运行另外一个系统存储过程来标记该对象。调用另外一个系统存储过程来将自定义的存储过程标记为系统存储过程如下：

　　USE MASTER
　　EXECUTE sp_ms_marksystemobject 'sp_Lock_Detail'


　　这样我们就可以在自己的SQL Server 实例下的任何数据库环境中执行sp_Lock_Detail存储过程，并返回该数据库的锁定信息。这比在每个用户数据库里都创建一个相同的存储过程容易多了。

　　接下来我们就来看看如何使用这个新的系统存储过程。首先，创建一个表并向表中载入一些数据。

　　CREATE TABLE LockMyData
　　(
　　IDCol INT IDENTITY(1,1) PRIMARY KEY, NumberField VARCHAR(5) ) GO DECLARE @I INT SET @I = 5000 WHILE @i > 0 BEGIN
　　INSERT INTO LockMyData( NumberField) SELECT datepart(ms, getdate()) + @I SET @I = @I - 1 END


　　接下来更新表中某事务的记录并使该事务保持开放状态。在更新过程中，该事务将持有对这些记录的锁定。然后添加WITH(HOLDLOCK)锁提示，确保该数据库的事务隔离水平不会影响到我们的测试。

　　BEGIN TRANSACTION UPDATE TOP(2000) LockMyData WITH(HOLDLOCK) SET NumberField = NumberField + IDCol

　　另起一个独立的查询会话，执行我们之前创建的sp_Lock_Detail存储过程。我们将在与之前发布的UPDATE语句相同的数据库环境下执行该存储过程。

　　EXECUTE sp_Lock_Detail

　　我们的返回结果超过了2000行，表明发布的UPDATE指令使用了行级锁(KEY)来发布该指令。

　　该存储过程生成了很多有用的数据。我们可以立刻就能看到谁发布了这些指令、哪些程序被用来发布这些指令、锁的类型等等信息。如果我们在原始UPDATE指令运行的时候运行这条指令，那么我们就能够看到引起锁定的确切指令了。

　　总结

　　系统存储过程是很有用的工具，使我们能够在一个数据库中编写存储过程，然后在其他数据库环境下运行该过程。本文的sp_Lock_Detail只是一个例子，告诉我们如何编写自定义的系统存储过程来监控自己的数据库。系统存储过程的用途还有很多，包括获取存储在数据库中所有表的大小分配，同时生成用来存储SQL语句以节省编写代码的时间等等。有兴趣的读者可以自己尝试，相信您一定会有所收获的。

转载于:https://www.cnblogs.com/sweet-sun/archive/2012/03/23/2413770.html