您现在的位置： IT专家网 > 数据库子站 > 数据库技巧

在SQL Server 2005中编写sp_lock系统存储过程

作者: cyw, 　出处:IT专家网,　责任编辑: 李书琴,　

2008-03-04 08:07

　　sp_lock系统存储过程是用来检查数据库系统出现的锁定数量的工具。该过程返回当前活动的SQL Server会话对资源锁定的数量和类型。

　　【IT专家网独家】做为系统存储过程，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