SQL SERVER 2008 如何锁定引起锁等待的SQL语句

Question

碰上阻塞问题，用SQL Server 活动监视器，监测到经常会有Lock出现，影响了性能。
想请教一下，我该如何确定锁的原因和引起长时间锁定的SQL语句。

Answer 1

一个事务未提交，导致别的事务必须等待，这就是阻塞， 
查看阻塞可以用sp_lock，打开三个查询分析器， 
第一个写: 
begin transaction tran1 
 update products set productName=productName+'A' 
 where ProductId=1 
 waitfor delay '00:00:30' 
commit transaction tran1 
第二个写: 
select * from products 
第三个写: 
sp_lock 
依次运行第一个、第二个、第三个， 
然后查看第三个分析器，看看Status列， 
看是否有Status='Wait'的行，比如我这里查看有这么一行: 
53 6 117575457 1 KEY (010086470766) S WAIT 
其中ObjId=117575457 
然后运行: 
use northwind 
select object_name(117575457) 
可以看到对应的表为 Products 

Answer 2

常用的就是SQL profiler GUI进行跟踪
但是用这个跟踪对生产中的系统会产生大量额外的I/O操作
建议把
跟踪封装到存储过程 跟踪
然后对生成的trc分析
重点关注
duration列

Answer 3

感谢各位帮忙解答
这个方法我试过了，结果我没有发现有等待出现。
我再描述一下我的现象，以便大家帮助分析一下原因。
网站系统，数据库请求有600-1200次/秒。
在每次请求中会有写库的操作，正常情况下写入记录能达到300条/秒，在资源监视器中出现锁的情况下，写库速度会小于30条/秒。
这种现象会每分钟重复一次。难道是sql server的页锁？希望高手帮忙解答一下。

Answer 4

写速度从300下降到30，需要查看一下下降时，执行的SQL在等待什么资源。

Answer 5

我现在就是想请教一下，该如何查找这个等待的资源，我用1楼得方法试过了，没发现有Status='Wait'的行

Answer 6

我也想请教高人一点:
SQL SERVER自动自带的性能计数反而影响了系统影响咋办?

Answer 7

你可以在情景重现时多次执行(F5)下面的script:
 
select  
s.session_id, r.blocking_session_id,
s.host_name, s.login_name, 
databasename=db_name(r.database_id),r.command, r.status as sqlexecstatus,
current_execute_sql = substring(t.text,
r.statement_start_offset / 2 + 1,
case
when statement_end_offset = -1 then len(t.text)
else (r.statement_end_offset - statement_start_offset) / 2+1
end),
s.program_name,
s.status,
s.cpu_time, memory_usage_kb = s.memory_usage * 8, s.reads, s.writes,
s.transaction_isolation_level,
c.connect_time, c.last_read, c.last_write,
c.net_transport, c.client_net_address, c.client_tcp_port, c.local_tcp_port,
r.start_time, 
r.wait_time, r.wait_type, r.last_wait_type, r.wait_resource,
r.open_transaction_count, r.transaction_id
from sys.dm_exec_sessions s
left join sys.dm_exec_connections c
on s.session_id = c.session_id
left join sys.dm_exec_requests r
on s.session_id = r.session_id
and c.connection_id = r.connection_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where 1=1
and s.is_user_process = 1  
and command is not null其中，如果blocking_session_id有不为0的记录，表示正在被堵塞，如果为0，表示正在等待资源，另外，通过last_wait_type wait_resource可以查看当前行sql所等待的资源。