SqlServer性能调优之SQL语句阻塞查询

SqlServer查看Sql阻塞,定位表压力问题。

CREATE DATABASE [DBA_DB]
GO
 
USE [DBA_DB]
GO
 
CREATE TABLE Blocking_sysprocesses(
      [spid] smallint,
      [kpid] smallint,
      [blocked] smallint,
      [waitType] binary(2),
      [waitTime] bigInt,
      [lastWaitType] nchar(32),
      [waitResource] nchar(256),
      [dbID] smallint,
      [uid] smallint,
      [cpu] int,
      [physical_IO] int,
      [memusage] int,
      [login_Time] datetime,
      [last_Batch] datetime,
      [open_Tran] smallint,
      [status] nchar(30),
      [sid] binary(86),
      [hostName] nchar(128),
      [program_Name] nchar(128),
      [hostProcess] nchar(10),
      [cmd] nchar(16),
      [nt_Domain] nchar(128),
      [nt_UserName] nchar(128),
      [net_Library] nchar(12),
      [loginName] nchar(128),
      [context_Info] binary(128),
      [sqlHandle] binary(20),
      [CapturedTimeStamp] datetime
)
GO
CREATE TABLE [dbo].[Blocking_SqlText](
      [spid] [smallint],
      [sql_text] [nvarchar](2000),
      [Capture_Timestamp] [datetime] 
) 
GO
 
CREATE PROCEDURE [dbo].[checkBlocking] 
AS
BEGIN
 
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 
declare @Duration   int -- in milliseconds, 1000 = 1 sec
declare @now            datetime
declare @Processes  int
 
select  @Duration = 100  -- in milliseconds, 1000 = 1 sec
select  @Processes = 0
 
select @now = getdate()
 
CREATE TABLE #Blocks_rg(
      [spid] smallint,
      [kpid] smallint,
      [blocked] smallint,
      [waitType] binary(2),
      [waitTime] bigInt,
      [lastWaitType] nchar(32),
      [waitResource] nchar(256),
      [dbID] smallint,
      [uid] smallint,
      [cpu] int,
      [physical_IO] int,
      [memusage] int,
      [login_Time] datetime,
      [last_Batch] datetime,
      [open_Tran] smallint,
      [status] nchar(30),
      [sid] binary(86),
      [hostName] nchar(128),
      [program_Name] nchar(128),
      [hostProcess] nchar(10),
      [cmd] nchar(16),
      [nt_Domain] nchar(128),
      [nt_UserName] nchar(128),
      [net_Library] nchar(12),
      [loginName] nchar(128),
      [context_Info] binary(128),
      [sqlHandle] binary(20),
      [CapturedTimeStamp] datetime
)     
      
INSERT INTO #Blocks_rg  
SELECT 
      [spid],
      [kpid],
      [blocked],
      [waitType],
      [waitTime],
      [lastWaitType],
      [waitResource],
      [dbID],
      [uid],
      [cpu],
      [physical_IO],
      [memusage],
      [login_Time],
      [last_Batch],
      [open_Tran],
      [status],
      [sid],
      [hostName],
      [program_name],
      [hostProcess],
      [cmd],
      [nt_Domain],
      [nt_UserName],
      [net_Library],
      [loginame],
      [context_Info],
      [sql_Handle],
      @now as [Capture_Timestamp]
FROM master..sysprocesses where blocked <> 0 
AND waitTime > @Duration      
      
SET @Processes = @@rowcount
 
INSERT into #Blocks_rg
SELECT 
 
      src.[spid],
      src.[kpid],
      src.[blocked],
      src.[waitType],
      src.[waitTime],
      src.[lastWaitType],
      src.[waitResource],
      src.[dbID],
      src.[uid],
      src.[cpu],
      src.[physical_IO],
      src.[memusage],
      src.[login_Time],
      src.[last_Batch],
      src.[open_Tran],
      src.[status],
      src.[sid],
      src.[hostName],
      src.[program_name],
      src.[hostProcess],
      src.[cmd],
      src.[nt_Domain],
      src.[nt_UserName],
      src.[net_Library],
      src.[loginame],
      src.[context_Info],
      src.[sql_Handle]
      ,@now as [Capture_Timestamp]
FROM  master..sysprocesses src inner join #Blocks_rg trgt
       on trgt.blocked = src.[spid]
 
if @Processes > 0
BEGIN
      INSERT [dbo].[Blocking_sysprocesses] 
      SELECT * from #Blocks_rg
      
DECLARE @SQL_Handle binary(20), @SPID smallInt;
DECLARE cur_handle CURSOR FOR SELECT sqlHandle, spid FROM #Blocks_rg;
OPEN cur_Handle
FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
INSERT [dbo].[Blocking_SqlText]
SELECT      @SPID, CONVERT(nvarchar(4000), [text]) ,@now as [Capture_Timestamp] from ::fn_get_sql(@SQL_Handle)
 
FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID
END
CLOSE cur_Handle
DEALLOCATE cur_Handle
 
END
 
DROP table #Blocks_rg
 
END
 
GO

---------------------------------------------------------------
USE msdb;
GO
 
EXEC dbo.sp_add_job
      @job_name = N'DBA_DB_MonitorBlocking',
      @owner_login_name=N'sa';
GO
EXEC sp_add_jobstep
      @job_name = N'DBA_DB_MonitorBlocking',
      @step_name = N'execute blocking script',  
      @subsystem = N'TSQL',
      @command = N'exec checkBlocking',
	  @database_name=N'DBA_DB';
GO    
EXEC sp_add_jobSchedule
      @name = N'ScheduleBlockingCheck',
      @job_name = N'DBA_DB_MonitorBlocking',
      @freq_type = 4, -- daily
      @freq_interval = 1, 
      @freq_subday_type = 4,
      @freq_subday_interval = 1
EXEC sp_add_jobserver @job_name = N'DBA_DB_MonitorBlocking', @server_name = N'(local)'

--每天在 0:00:00 和 23:59:59 之间、每 1 分钟 执行
---------------------------------------------------------------
use DBA_DB
GO    
exec checkBlocking

use DBA_DB
GO    
SELECT * from Blocking_sqlText with(nolock) order by Capture_Timestamp desc
SELECT * FROM Blocking_sysprocesses with(nolock) order by CapturedTimestamp desc

truncate table Blocking_sqlText;
truncate table Blocking_sysprocesses;

---------------------------------------------------------------