sqlserver2005锁监控

基本信息查询
从sys.dm_tran_lock中可以查到一些有用信息。

SELECT resource_type, resource_description,
        resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id > 0

对查询范围的限制，查自己想要的信息。

 resource_associated_entity_id > 0

I am interested only in locks that are actually held on data resources

可以增加更多的过滤条件。

结果样例：

resource_type resource_description resource_associated_entity_id request_mode
 request_status
------------- -------------------- ------------------------------ ------------ -------------

KEY (92007ad11d1d) 72057594045857792 X GRANT
PAGE 1:5280 72057594045857792 IX GRANT
OBJECT 722101613 IX GRANT

字段含义：
resource_associated_entity_id is an allocation_unit_id. For the OBJECT locks, the resource_associated_entity_id is a table

继续查找响应值的具体信息：
SELECT object_name(722101613)

既能查出已获取的资源，也可以查看在等待的资源，例如：

resource_type resource_description resource_associated_entity_id request_mode request_status
------------- -------------------- ------------------------------- ------------ -------------

OBJECT 722101613 X WAIT