急，诚心请教，解决马上结帖(100)

Question

在sqlserver2005中如何捕捉运行很久的SQL，还有耗资源的进程现在不知道什么原因，程序在运行一段时间后，总是会出现死锁，程序不能登录，能找出死锁ID，杀后过一段时间还会出现，我怎么能找出是哪出了问题？

Answer 1

sp_who active  --看看哪个引起的阻塞，blk
sp_lock  --看看锁住了那个资源id，objid ，select object_name(objid) 得到
dbcc inputbuffer(@blk) -- 看看是那个语句

Answer 2

/*--处理死锁 查看当前进程,或死锁进程,并能自动杀掉死进程 因为是针对死的,所以如果有死锁进程,只能查看死锁进程
 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4--*//*--调用示例 exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
 登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
 域名=nt_domain,网卡地址=net_address
into #t from(
 select 标志='死锁的进程',
  spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=a.spid,s2=0
 from master..sysprocesses a join (
  select blocked from master..sysprocesses group by blocked
  )b on a.spid=b.blocked where a.blocked=0
 union all
 select '|_牺牲品_>',
  spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=blocked,s2=1
 from master..sysprocesses a where blocked<>0
)a order by s1,s2select @count=@@rowcount,@i=1if @count=0 and @show_spid_if_nolock=1
begin
 insert #t
 select 标志='正常的进程',
  spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
  open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
 from master..sysprocesses
 set @count=@@rowcount
endif @count>0
begin
 create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
 if @kill_lock_spid=1
 begin
  declare @spid varchar(10),@标志 varchar(10)
  while @i<=@count
  begin
   select @spid=进程ID,@标志=标志 from #t where id=@i
   insert #t1 exec('dbcc inputbuffer('+@spid+')')
   if @标志='死锁的进程' exec('kill '+@spid)
   set @i=@i+1
  end
 end
 else
  while @i<=@count
  begin
   select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
   insert #t1 exec(@s)
   set @i=@i+1
  end
 select a.*,进程的SQL语句=b.EventInfo
 from #t a join #t1 b on a.id=b.id
end
go

Answer 3

在SQL Server 2005中解决死锁问题作者：佚名  2007-05-29     数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。     将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。 use Northwind
begin tran
　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　waitfor delay @#00:00:05@#
　　select * from Orders where CustomerId = @#ALFKI@#
commit
print @#end tran@#     SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。     现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：     下面利用的try ... catch来解决死锁。 SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
　　begin tran
　　
　　begin try　　　
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　　　waitfor delay @#00:00:05@#
　　　　select * from Orders where CustomerId = @#ALFKI@#
　　　　
　　　　commit
　　　　break
　　end try
　　　　
　　begin catch
　　　　rollback
　　　　waitfor delay @#00:00:03@#
　　　　set @r = @r + 1
　　　　continue
　　end catch
end     解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。     但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样: declare @r int
set @r = 1
while @r <= 3
begin
　　begin tran
　　
　　begin try　　　
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　　　waitfor delay @#00:00:05@#
　　　　select * from Orders where CustomerId = @#ALFKI@#
　　　　
　　　　commit
　　　　break
　　end try
　　　　
　　begin catch
　　　　rollback
　　　　waitfor delay @#00:00:03@#
　　　　set @r = @r + 1
　　　　continue
　　end catch
end
if ERROR_NUMBER() <> 0
begin
　　declare @ErrorMessage nvarchar(4000);
　　declare @ErrorSeverity int;
　　declare @ErrorState int;
　　select
　　　　@ErrorMessage = ERROR_MESSAGE(),
　　　　@ErrorSeverity = ERROR_SEVERITY(),
　　　　@ErrorState = ERROR_STATE();
　　raiserror (@ErrorMessage,
　　　　　　　 @ErrorSeverity,
　　　　　　　 @ErrorState
　　　　　　　 );
end     我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。     因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。 

Answer 4

1楼的或者打开sql server监视窗口，查看阻塞信息，看是哪些语句出现死锁，优化一下。

Answer 5

2005的话，你可以通过开启PROFILE去跟踪死锁的情况，然后分析。

Answer 6

select db_name(database_id),* from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)

Answer 7

-- ===========================================
-- 获取阻塞的 session_id 及阻塞时间
DECLARE @tb_block TABLE(
top_blocking_session_id smallint,
session_id smallint,
blocking_session_id smallint,
wait_time int,
Level int,
blocking_path varchar(8000),
PRIMARY KEY(
session_id, blocking_session_id)
)
INSERT @tb_block(
session_id,
blocking_session_id,
wait_time)
SELECT
session_id,
blocking_session_id,
wait_time = MAX(wait_time)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY session_id, blocking_session_id-- ===========================================
-- 处理阻塞的 session_id 之间的关系
DECLARE
@Level int
SET @Level = 1INSERT @tb_block(
session_id, top_blocking_session_id, blocking_session_id,
Level, blocking_path)
SELECT DISTINCT
blocking_session_id, blocking_session_id, 0,
@Level, RIGHT(100000 + blocking_session_id, 5)
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE session_id = A.blocking_session_id)
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
UPDATE A SET
top_blocking_session_id = B.top_blocking_session_id,
Level = @Level,
blocking_path = B.blocking_path 
+ RIGHT(100000 + A.session_id, 5)
FROM @tb_block A, @tb_block B
WHERE A.blocking_session_id = B.session_id
AND B.Level = @Level - 1
END-- ===========================================
-- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
-- 这里假设阻塞时间必须超过 1 秒钟(1000毫秒)
DELETE A 
FROM @tb_block A
WHERE NOT EXISTS(
SELECT * FROM @tb_block
WHERE top_blocking_session_id =A.top_blocking_session_id
AND wait_time >= 1000)-- ===========================================
-- 使用 DBCC INPUTBUFFER 获取阻塞进程的 T-SQL 脚本
DECLARE @tb_block_sql TABLE(
id int IDENTITY,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000),
session_id smallint)
DECLARE
@session_id smallint
DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT
session_id
FROM @tb_block
OPEN tb
FETCH tb INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @tb_block_sql(
EventType, Parameters, EventInfo)
EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
IF @@ROWCOUNT > 0
UPDATE @tb_block_sql SET
session_id = @session_id
WHERE IDENTITYCOL = @@IDENTITY FETCH tb INTO @session_id
END
CLOSE tb
DEALLOCATE tb-- ===========================================
-- 显示阻塞进程信息
;WITH
BLK AS(
SELECT
A.top_blocking_session_id,
A.session_id,
A.blocking_session_id,
A.Level,
A.blocking_path,
SQL = B.EventInfo
FROM @tb_block A
LEFT JOIN @tb_block_sql B
ON A.session_id = B.session_id
)
SELECT
-- BlockPath = REPLICATE(' ', Level * 2 - 2)
-- + '|-- '
-- + RTRIM(session_id),
BLK.top_blocking_session_id,
BLK.session_id,
BLK.blocking_session_id,
BLK.Level,
wait_type = P.waittype,
wait_time = P.waittime,
last_wait_type = P.lastwaittype,
wait_resource = P.waitresource,
P.login_time,
P.last_batch,
P.open_tran,
P.status,
host_name = P.hostname,
P.program_name,
P.cmd,
login_name = P.loginame,
BLK.SQL,
current_sql = T.text,
current_run_sql = SUBSTRING(T.text,
P.stmt_start / 2 + 1,
CASE
WHEN P.stmt_end = -1 THEN LEN(T.text)
ELSE (P.stmt_end - P.stmt_start) / 2+1
END)
FROM BLK
-- 简省代码起见，直接引用 sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
INNER JOIN master.dbo.sysprocesses P
ON BLK.session_id = P.spid
OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
ORDER BY BLK.top_blocking_session_id, BLK.blocking_path
来自：《SQL2005深入浅出》

Answer 8

跟踪出来后，总是有一个系统存储过程死锁　sp_special_columns 这是干什么用的

Answer 9

用SQL 2005的 事件分析 功能超级强