sqlserver2005锁监控 基本信息查询

amwdnuff

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

SELECT resource_type, resource_description,resource_associated_entity_id, request_mode, request_statusFROM sys.dm_tran_locksWHERE 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

amwdnuff

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

SELECT resource_type, resource_description,resource_associated_entity_id, request_mode, request_statusFROM sys.dm_tran_locksWHERE 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

amwdnuff

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

SELECT resource_type, resource_description,resource_associated_entity_id, request_mode, request_statusFROM sys.dm_tran_locksWHERE 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