WEB开发网
开发学院数据库MSSQL Server SQL Server 2005性能排错(4) 阅读

SQL Server 2005性能排错(4)

 2007-05-15 09:28:31 来源:WEB开发网   
核心提示: select * from sys.dm_os_waiting_tasks where session_id=56waiting_task_address session_id exec_context_id wait_duration_ms wait_typeresource_addre
select * from sys.dm_os_waiting_tasks where session_id=56
waiting_task_address session_id exec_context_id wait_duration_ms   wait_type
resource_address  blocking_task_address blocking_session_id
blocking_exec_context_id resource_description
--------------------------------------------------------------------------
0x022A8898      56     0        1103500       LCK_M_S 
0x03696820     0x022A8D48      53         NULL      
ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00
mode=X associatedObjectId=72057594038321152

这个结果显示了会话56被会话54阻塞了,会话56已经为一个锁等待了1103500毫秒。

为了找到准许的锁或等待锁的会话,你可以使用sys.dm_tran_locks DMV。每行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁,准许请求指出了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。例如下列查询显示会话56被阻塞在资源1:143:3,该资源被会话53的X模式锁占有。

select
request_session_id as spid,
resource_type as rt, 
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd, 
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Here is the sample output
spid   rt      rdb     objname    rd      rm      rs
-----------------------------------------------------------------------------
56  DATABASE  9                S     GRANT
53  DATABASE  9               S     GRANT
56  PAGE    9    t_lock   1:143    IS     GRANT
53  PAGE    9    t_lock   1:143    IX     GRANT
53  PAGE    9    t_lock   1:153    IX     GRANT
56  OBJECT    9    t_lock         IS     GRANT
53  OBJECT    9    t_lock         IX     GRANT
53  KEY     9    t_lock   (a400c34cb X     GRANT
53  RID     9    t_lock   1:143:3  X     GRANT
56  RID     9    t_lock   1:143:3  S    WAIT

上一页  3 4 5 6 7 8 9 10  下一页

Tags:SQL Server 性能

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接