Select distinct spid,blocked,loginame,hostname,open_tran,waittime,lastwaittype,waitresource,last_batch 
from master..sysprocesses where blocked <> 0 
order by waittime desc

/*其中blocked是引起锁的进程,
在有多个锁的情况,要分析锁的引用关系,
比如56号进程在等待50号
70号进行在等待56
77号进行在等待70
这里最初引起锁的进程为50
再查看50最后提交的sql
*/
dbcc inputbuffer(50)

------解决方案--------------------
给你一段代码,
SQL code

USE tempdb
if exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')
DROP TABLE CCLOCKS
CREATE TABLE CCLOCKS
(SPID INT,DBID INT,OBJID INT,INDID INT,TYPE VARCHAR(10),RESOUSE VARCHAR(80),MODE VARCHAR(10),STATUS VARCHAR(10))
if exists(select * from tempdb..sysobjects where name like 'CWHOS%' and type='u')
DROP TABLE CWHOS
CREATE TABLE CWHOS
(SPID INT,ECID INT,STATUS VARCHAR(20),LOGINAME VARCHAR(50),HOSTNAME VARCHAR(20),
BLK VARCHAR(3),DBNAME VARCHAR(50),CMD VARCHAR(50),request_id int)
if exists(select * from tempdb..sysobjects where name like 'ACWHOS%' and type='u')
DROP TABLE ACWHOS
CREATE TABLE ACWHOS
(SPID INT,ECID INT,STATUS VARCHAR(20),LOGINAME VARCHAR(50),HOSTNAME VARCHAR(20),
BLK VARCHAR(3),DBNAME VARCHAR(50),CMD VARCHAR(50),request_id int)
INSERT CWHOS EXEC sp_who
INSERT ACWHOS EXEC sp_who active
INSERT CCLOCKS EXEC SP_LOCK

-- 取出前10个锁最多的进程
SELECT top 10 spid,count(*)as s FROM CCLOCKS group by spid order by s desc

-- 查进程执行的详细信息
sp_who2 [spid号]    --> 进程详细信息
DBCC INPUTBUFFER([spid号])   --> 执行的SQL

-- 强制结束进程,慎用!切记.
kill [spid号]

------解决方案--------------------
探讨

锁越多，是不是代表越慢呢？


引用:

给你一段代码,
SQL code

USE tempdb
if exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')
DROP TABLE CCLOCKS
CREATE TABLE CCLOCKS
(SPID ……


------解决方案--------------------
探讨

锁越多，是不是代表越慢呢？


------解决方案--------------------
做生意的上来了

------解决方案--------------------

探讨

给你一段代码,

SQL code


USE tempdb
if exists(select * from tempdb..sysobjects where name like 'CCLOCKS%' and type='u')
DROP TABLE CCLOCKS
CREATE TABLE CCLOCKS
(SPID INT,DBID INT,OBJID INT,INDID I……