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
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接