求助 SQL update 语句更新问题

Question

update 语句根据主键 更新一条记录时，用了20分钟还没完成。
如 update table_a set  col_b = 1000
where col_a = ‘A’在执行过程中 ，select * from table_a  where col_a = ‘A’没问题。
查询其它列都没问题。
另外恢复一个数据库备份到其它地方，进行测试，update很快完成。
搞不明白为何出现这种问题?

Answer 1

1、贴出update的执行计划，
2、进行update的时候执行select * from sys.sysprocesses where blocked<>0看看有没有阻塞

Answer 2

查看SP_LOCK是否该表一直被X锁定而此时载UPDATE无法获得独占锁，可以考虑使用HINT（with  updlock）

Answer 3

所以我说要查阻塞，不然你下次update还是有可能出现

Answer 4

暂时查不到堵塞的情况，但是select * from sys.sysprocesses 这个语句执行的结果显示如下，这样又看不到堵塞的具体语句？就算看到了又该如何处理？1 1964 0 0x0000 0 PREEMPTIVE_XE_CALLBACKEXECUTE                                                                                                                                                                                                                                                                     0 1 764 0 0 2013-05-14 02:42:58.853 2013-05-14 02:42:58.853 0 0 background                     0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                                                                                                                                                                                                                                                                              RESOURCE MONITOR                                                                                                                                                                                                                                                                                             sa                                                                                                                               0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0 0

Answer 5

假设你找到了一个阻塞的spid，可以用DBCC INPUTBUFFER SPID来查看这个SPID是做什么的