SQL Server - Monitora Lock - Cria Procedure, Tabela E Job - Inclui A Consulta

Esse script cria uma tabela de monitoração de locks e de waits bem como uma stored procedure para coletar esses dados no SQL Server e um job schedulado para captura de histórico da consulta.

É excelente para voce entender a causa raiz do problema de performance.

-- SCRIPT PARA CRIAÇÃO DE PROCEDURE, TABELA E JOB PARA MONITORAMENTO DE BLOQUEIOS

/****** CRIAÇÃO DE PROCEDURE SP MONITORAMENTO LOCKS ******/

use msdb

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_MonitoramentoLocks]

as

DECLARE @tempo INT = 1000 --miliseg

declare @idlog varchar(128) = newid()

declare @tab table (

[session_id] [smallint] NOT NULL,

[blocked_by] [smallint] NOT NULL,

[login] [nchar](128) NOT NULL,

[host_name] [nchar](128) NOT NULL,

[program_name] [nchar](128) NOT NULL,

[Query] [nvarchar](max) NULL,

[Command] [nchar](16) NOT NULL,

[database] [sysname] NOT NULL,

[last_wait_type] [nchar](32) NOT NULL,

[wait_time_sec] [bigint] NULL,

[last_batch] [datetime] NOT NULL,

[login_time] [datetime] NOT NULL,

[status] [nchar](30) NOT NULL,

[cpu] [int] NULL,

[capture_time] [datetime] NOT NULL,

[ID_log] [uniqueidentifier] NULL)

insert into @tab

Select

SPRO.spid AS [session_id]

,SPRO.blocked AS [blocked_by]

,SPRO.loginame AS [login]

,SPRO.hostname AS [host_name]

--,SPRO.program_name AS [program_name1]

,CASE WHEN LTRIM(RTRIM(SPRO.[Program_name])) LIKE '%TSQL Job%' THEN B.NAME

ELSE LTRIM(RTRIM(SPRO.[Program_name])) END AS [Program_name]

,CASE WHEN Text LIKE 'FETCH API_CURSOR%'

THEN (SELECT TOP 1 c.properties +' | '+ t.text

FROM sys.dm_exec_cursors (SPRO.spid) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t )

ELSE Text END AS [Query]

--,Text AS [Query]

,SPRO.cmd AS [Command]

,DTBS.name AS [database]

,SPRO.lastwaittype AS [last_wait_type]

,SPRO.waittime/1000 AS [wait_time]

,SPRO.last_batch AS [last_batch]

,SPRO.login_time AS [login_time]

,SPRO.status AS [status]

,(SPRO.cpu/1000) AS [cpu]

,getdate() AS [capture_time]

,@idlog AS [ID_log]

FROM sys.sysprocesses AS SPRO

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

OUTER APPLY (SELECT NAME FROM MSDB..SYSJOBS (NOLOCK)

WHERE '0x'+CONVERT(char(32),CAST(job_id AS binary(16)),2) = SUBSTRING(SPRO.[Program_name],30,34)) B

INNER JOIN sys.databases AS DTBS

ON SPRO.dbid = DTBS.database_id

where lastwaittype <> 'miscellaneous'

and SPRO.spid >0

and SPRO.blocked <> 0

and SPRO.waittime >@tempo

/****** INSERE OS DADOS NA TABELA LOG_LOCKS ******/

INSERT INTO MSDB.dbo.log_locks

select * from @tab

union

Select

SPRO.spid AS [session_id]

,SPRO.blocked AS [blocked_by]

,SPRO.loginame AS [login]

,SPRO.hostname AS [host_name]

--,SPRO.program_name AS [program_name]

,CASE WHEN LTRIM(RTRIM(SPRO.[Program_name])) LIKE '%TSQL Job%' THEN B.NAME

ELSE LTRIM(RTRIM(SPRO.[Program_name])) END AS [Program_name]

,CASE WHEN Text LIKE 'FETCH API_CURSOR%'

THEN (SELECT TOP 1 c.properties +' | '+ t.text

FROM sys.dm_exec_cursors (SPRO.spid) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t )

ELSE Text END AS [Query]

--,Text AS [Query]

,SPRO.cmd AS [Command]

,DTBS.name AS [database]

,SPRO.lastwaittype AS [last_wait_type]

,SPRO.waittime/1000 AS [wait_time]

,SPRO.last_batch AS [last_batch]

,SPRO.login_time AS [login_time]

,SPRO.status AS [status]

,(SPRO.cpu/1000) AS [cpu]

,getdate() AS [capture_time]

,@idlog AS [ID_log]

FROM sys.sysprocesses AS SPRO

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

OUTER APPLY (SELECT NAME FROM MSDB..SYSJOBS (NOLOCK)

WHERE '0x'+CONVERT(char(32),CAST(job_id AS binary(16)),2) = SUBSTRING(SPRO.[Program_name],30,34)) B

INNER JOIN sys.databases AS DTBS

ON SPRO.dbid = DTBS.database_id

where SPRO.spid in (select blocked_by from @tab)

go

/****** CRIA A TABELA LOG_LOCKS ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[log_locks](

[session_id] [smallint] NOT NULL,

[blocked_by] [smallint] NOT NULL,

[login] [nchar](128) NOT NULL,

[host_name] [nchar](128) NOT NULL,

[program_name] [nchar](128) NOT NULL,

[Query] [nvarchar](max) NULL,

[Command] [nchar](16) NOT NULL,

[database] [sysname] NOT NULL,

[last_wait_type] [nchar](32) NOT NULL,

[wait_time_sec] [bigint] NULL,

[last_batch] [datetime] NOT NULL,

[login_time] [datetime] NOT NULL,

[status] [nchar](30) NOT NULL,

[cpu] [int] NULL,

[capture_time] [datetime] NOT NULL,

[ID_log] [uniqueidentifier] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** CRIACAO DE JOB DBA - MONITORA LOCK ******/

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Monitora Lock',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'.',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXEC [dbo].[SP_MonitoramentoLocks]

',

@database_name=N'msdb',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'.',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=2,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20200108,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'34a970a7-8ad8-4aef-8919-11020b25815d'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

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

-- APÓS TER TUDO CRIADO, DEVE-SE USAR O SCRIPT ABAIXO PARA FAZER A CONSULTA DE BLOQUEIOS

/****** SCRIPT PARA CONSULTA NA TABELA LOG_LOCKS ******/

SELECT TOP (1000) [session_id] "ID Bloqueado"

,[blocked_by] "ID Bloqueando"

,[login] "Login"

,[host_name] "Host"

,[wait_time_sec] "Tempo de espera"

,[program_name] "Programa"

,[Query] "Query"

,[Command] "Comando"

,[database] "Banco"

,[capture_time] "Data e hora de captura"

,[last_batch] "Data e hora que rodou por último"

,[login_time] "Data e hora de login"

,[status] "Status"

,[cpu] "CPU"

,[last_wait_type] "Último tipo de espera"

,[ID_log] "ID Log"

FROM msdb.[dbo].[log_locks]

-- where [wait_time_sec] > 10

order by capture_time desc