取得目前造成資料庫鎖定的源頭

2013-04-09

2876
0
SQL

摘要:取得目前造成資料庫鎖定的源頭

有時因為系統設計不良，或資料量龐大容易造成查詢A 因為查詢B 而咬住，但查詢B 又真正的元兇，因為他可能是被查詢C 咬住，而C 也可能只是連鎖效應的一個中間者。

在管理工具可以透過"監視器(SQL2005~2008)" 或. "目前活動(SQL2000)" 觀察，到底兇手是哪位，但在系統發生連鎖效應時以上工具常常是打不開的，因為DB反應真的慢到爆。

附件的SP , 是透過一些系統內建的sp 函式加工處理後，直接把肉粽串的頭抓出來，在上面工具卡住打不開時，以另外一種比較節約資源的方式把兇手抓出來。

通常這類的連鎖問題，當把帶頭的砍掉以後，其他後續的程序就會因為等待資源已經被釋出，自動就會解鎖。但在砍之前記得把帶頭大哥的SQL 內容記錄一下，看看後續是否需要做索引的調整或查詢語法的改善。如果只是單純kill 掉，因為系統的操作與運行，剛好又有使用者踩到這個地雷程式，那大哥可能又會從出江湖，帶頭作亂。

ps : SQL 2000 跟SQL2005 以上，內建sp 有些許差異。


-- For SQL 2000
USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetLockSource]    Script Date: 11/01/2012 18:53:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		rachen
-- Create date: 2012.11.01
-- Description:	取得資料鎖定的主要來源.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetLockSource]
AS
BEGIN

	set nocount on;

	create table #sp_who2 (
		[SPID] smallint null ,
		[Status] nvarchar(30) null ,
		[Login]	nvarchar(30) null ,
		[HostName] nvarchar(30) null ,	
		[BlkBy]	varchar(3000) null , /*CommandText*/
		[DBName] nvarchar(100) null ,	
		[Command] nvarchar(100) null ,
		[CPUTime] int null ,
		[DiskIO] int null ,
		[LastBatch]	varchar(30) null , /*IsLockSource*/
		[ProgramName] nvarchar(60) null ,
		[BlkBySPID] smallint null
	);
	/*把 sp_who2 的結果塞入 temp 表單 */
	insert into #sp_who2 exec sp_who2;

	/*將 [BlkBy]處理成數字，以利後續處理 */
	update #sp_who2 set [BlkBySPID] = convert(smallint,Replace(Rtrim(Ltrim(BlkBy)),'.','')) , [BlkBy] = '' , [LastBatch] = '0';

	declare @SPID smallint;
	declare @CommandText varchar(3000);
	declare @Handle binary(20);

	declare SPIDList cursor  for
		select distinct M.SPID
		from #sp_who2 M 
		join #sp_who2 D on M.SPID = D.BlkBySPID 
		where M.BlkBySPID = 0 or M.SPID = D.SPID;
	open SPIDList;

	while 1 = 1 begin

		fetch next from SPIDList into @SPID;
		if ( @@fetch_status <> 0 ) goto WhileEnd

		set @CommandText = null;
		set @Handle = (select top 1 sql_handle from master.dbo.sysprocesses where spid = @SPID);
		set @CommandText = (select top 1 convert(varchar(3000),[text]) from ::fn_get_sql(@Handle));
		update #sp_who2 set [BlkBy] = isnull(@CommandText,'') , [LastBatch] = 1 where [SPID] = @SPID;

	end

	WhileEnd:

	close SPIDList;
	deallocate SPIDList;

	select SPID, Status, Login , HostName , DBName , Command , CPUTime , DiskIO ,  ProgramName , BlkBySPID ,
		BlkBy [CommandText] , 
		LastBatch [IsLockSource]
	from #sp_who2
	where LastBatch = '1';

END


GO


--For SQL 2005~2008
USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetLockSource]    Script Date: 11/01/2012 18:51:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		rachen
-- Create date: 2012.11.01
-- Description:	取得資料鎖定的主要來源.
-- =============================================
Create PROCEDURE [dbo].[sp_GetLockSource]
AS
BEGIN

	set nocount on;

	create table #sp_who2 (
		[SPID] smallint null ,
		[Status] nvarchar(30) null ,
		[Login]	nvarchar(30) null ,
		[HostName] nvarchar(30) null ,	
		[BlkBy]	varchar(3000) null , /*CommandText*/
		[DBName] nvarchar(100) null ,	
		[Command] nvarchar(100) null ,
		[CPUTime] int null ,
		[DiskIO] int null ,
		[LastBatch]	varchar(30) null , /*IsLockSource*/
		[ProgramName] nvarchar(60) null ,
		[BlkBySPID] smallint null ,
		[request_id] int null
	);
	/*把 sp_who2 的結果塞入 temp 表單 */
	insert into #sp_who2 exec sp_who2;

	/*將 [BlkBy]處理成數字，以利後續處理 */
	update #sp_who2 set [BlkBySPID] = convert(smallint,Replace(Rtrim(Ltrim(BlkBy)),'.','')) , [BlkBy] = '' , [LastBatch] = '0';

	declare @SPID smallint;
	declare @CommandText varchar(3000);
	declare @Handle binary(20);

	declare SPIDList cursor  for
		select distinct M.SPID
		from #sp_who2 M 
		join #sp_who2 D on M.SPID = D.BlkBySPID 
		where M.BlkBySPID = 0 or M.SPID = D.SPID;
	open SPIDList;

	while 1 = 1 begin

		fetch next from SPIDList into @SPID;
		if ( @@fetch_status <> 0 ) goto WhileEnd

		set @CommandText = null;
		set @Handle = (select top 1 sql_handle from master.dbo.sysprocesses where spid = @SPID);
		set @CommandText = (select top 1 convert(varchar(3000),[text]) from ::fn_get_sql(@Handle));
		update #sp_who2 set [BlkBy] = isnull(@CommandText,'') , [LastBatch] = 1 where [SPID] = @SPID;

	end

	WhileEnd:	

	close SPIDList;
	deallocate SPIDList;

	select SPID, Status, Login , HostName , DBName , Command , CPUTime , DiskIO ,  ProgramName , BlkBySPID ,
		BlkBy [CommandText] , 
		LastBatch [IsLockSource]
	from #sp_who2
	where LastBatch = '1';

END


GO

回首頁