LAB: SQL Blocker Script 2000 (Performance)
Blocker Script é uma stored procedure escrita por um engenheiro de suporte (MikeZ), sendo muito utilizado para coletar dados relacionados a bloqueios. Esse script está disponível através do artigo KB 271509.
How to monitor blocking in SQL Server 2005 and in SQL Server 2000
https://support.microsoft.com/kb/271509/en-us
Esse script tem anos de existência e seu código continua praticamente o mesmo. Um dos (poucos) problemas é a impossibilidade de identificar queries causando alto consumo de CPU, sendo necessário adotar uma ferramenta adicional como o SQL Profiler. Pensando nisso, fiz algumas alterações que auxiliam no diagnóstico de performance coletando menos informação. Já faz 5 anos que tenho usado essa versão modificada do Blocker script para avaliar a performance de servidores SQL Server.
Como coletar informações de performance?
1) Crie a stored procedure sp_blocker_pfe_auto usando o script abaixo.
USE MASTER
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.fn_blocker_pfe_sql_text'))
DROP FUNCTION dbo.fn_blocker_pfe_sql_text
GO
---------------------------------------------------------------------------------------
-- Procedure: dbo.fn_blocker_pfe_sql_text
---------------------------------------------------------------------------------------
CREATE FUNCTION dbo.fn_blocker_pfe_sql_text(@sql_handle BINARY(20), @stmt_start INT, @stmt_end INT, @length INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @text VARCHAR(8000)
DECLARE @len INT
IF @stmt_end > 0
BEGIN
SET @len = (@stmt_end - @stmt_start)/2
IF @len < @length
SET @length = @len
END
SELECT @text = SUBSTRING(text, @stmt_start/2, @length) FROM ::fn_get_sql(@sql_handle)
RETURN @text
end
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.sp_blocker_pfe80') )
DROP PROCEDURE dbo.sp_blocker_pfe80
GO
---------------------------------------------------------------------------------------
-- Procedure: dbo.sp_blocker_pfe
---------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_blocker_pfe80 (
@info bit = 0,
@process bit = 0,
@process2 bit = 0,
@lock bit = 0,
@waitstat bit = 0,
@inputbuffer bit = 0,
@sqlhandle bit = 0,
@sqlhandle_collect bit = 0,
@sqlhandle_flush bit = 0,
@opentran bit = 0,
@logspace bit = 0,
@memstatus bit = 0,
@perfinfo bit = 0,
@trace bit = 0,
@textsize int = 256
)
AS
SET NOCOUNT ON
SET LANGUAGE 'us_english'
DECLARE @spid VARCHAR(6)
DECLARE @blocked VARCHAR(6)
DECLARE @time DATETIME
DECLARE @time2 DATETIME
DECLARE @dbname nVARCHAR(128)
DECLARE @status SQL_VARIANT
DECLARE @useraccess SQL_VARIANT
DECLARE @perfobjname NVARCHAR(256)
IF is_member('sysadmin')=0
BEGIN
PRINT 'Must be a member of the sysadmin group in order to run this procedure'
return
END
SET @time = getdate()
DECLARE @probclients
TABLE (
spid SMALLINT,
ecid SMALLINT,
blocked SMALLINT,
waittype BINARY(2),
dbid SMALLINT,
sql_handle BINARY(20),
stmt_start INT,
stmt_end INT,
PRIMARY KEY (spid, ecid))
INSERT @probclients
SELECT spid, ecid, blocked, waittype, dbid, sql_handle, stmt_start, stmt_end
FROM master.dbo.sysprocesses
WHERE
(
kpid<>0 OR
waittype<>0x0000 OR
open_tran<>0 OR
spid IN (SELECT blocked FROM master.dbo.sysprocesses)
) AND spid>50
---------------------------------------------------------------------------------------
-- 8.2 Start time:
---------------------------------------------------------------------------------------
SET @time2 = GETDATE()
PRINT ''
PRINT '8.3 Start time: ' + CONVERT(VARCHAR(26), @time, 121) + ' ' + CONVERT(VARCHAR(12), datediff(ms,@time,@time2))
---------------------------------------------------------------------------------------
-- Static Configuration
---------------------------------------------------------------------------------------
IF @info = 1
BEGIN
SET @time2 = GETDATE()
PRINT ''
PRINT 'MACHINE INFORMATION'
PRINT ''
PRINT 'ServerName: ' + @@SERVERNAME
PRINT 'PhysicalName: ' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)
PRINT 'ProductVersion: ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
PRINT 'ProductLevel: ' + CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR)
PRINT 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS VARCHAR)
PRINT 'ProcessId: ' + CAST(SERVERPROPERTY('ProcessId') AS VARCHAR)
PRINT 'SessionId: ' + CAST(@@SPID AS VARCHAR)
PRINT ''
PRINT @@version
PRINT ''
PRINT 'EXEC xp_msver'
PRINT ''
EXEC xp_msver
PRINT 'SELECT sysconfigures'
PRINT ''
SELECT value, comment FROM sysconfigures
PRINT 'INFO ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- Connections
---------------------------------------------------------------------------------------
IF @process = 1
BEGIN
SET @time2 = GETDATE()
PRINT ''
PRINT 'SYSPROCESSES'
SELECT spid, status, blocked, open_tran,
waitresource, waittype, waittime, cmd, lastwaittype,
cpu, physical_io,memusage,
last_batch=convert(VARCHAR(26), last_batch,121),
login_time=convert(VARCHAR(26), login_time,121),
net_address,net_library,
dbid, ecid, kpid, hostname, hostprocess, loginame, program_name,
nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
FROM master.dbo.sysprocesses
WHERE
spid IN (SELECT spid FROM @probclients)
PRINT 'ESP ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- Connections2
---------------------------------------------------------------------------------------
IF @process2 = 1
BEGIN
SET @time2 = GETDATE()
PRINT ''
PRINT 'SYSPROCESSES2'
SELECT spid, status, blocked, open_tran,
waitresource, waittype, waittime, cmd, lastwaittype,
cpu, physical_io,memusage,
last_batch=convert(VARCHAR(26), last_batch,121),
login_time=convert(VARCHAR(26), login_time,121),
net_address,net_library,
dbid, ecid, kpid, hostname, hostprocess, loginame, program_name,
nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
FROM master.dbo.sysprocesses
WHERE
(kpid<>0 OR waittype<>0x0000 OR open_tran<>0) AND (spid>50)
PRINT 'ESP2 ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- SYSLOCKINFO
---------------------------------------------------------------------------------------
IF @lock = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'SYSLOCKINFO'
SELECT CONVERT (smallint, req_spid) AS spid,
rsc_dbid AS dbid,
rsc_objid AS ObjId,
rsc_indid AS IndId,
SUBSTRING (v.name, 1, 4) AS Type,
SUBSTRING (rsc_text, 1, 32) as Resource,
SUBSTRING (u.name, 1, 8) AS Mode,
SUBSTRING (x.name, 1, 5) AS Status
FROM
master.dbo.syslockinfo l inner join master.dbo.spt_values v on (l.rsc_type = v.number and v.type = 'LR')
inner join master.dbo.spt_values x on (l.req_status = x.number and x.type = 'LS')
inner join master.dbo.spt_values u on (l.req_mode + 1 = u.number and u.type = 'L')
WHERE l.rsc_type = 5
PRINT 'ESL ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- DBCC SQLPERF(WAITSTATS)
---------------------------------------------------------------------------------------
IF @waitstat = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'DBCC SQLPERF(WAITSTATS)'
DBCC SQLPERF(WAITSTATS)
PRINT 'DBCCWAIT ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- DBCC INPUTBUFFER
---------------------------------------------------------------------------------------
IF @inputbuffer = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'DBCC INPUTBUFFER(*)'
DECLARE ibuffer CURSOR FAST_FORWARD FOR
SELECT DISTINCT CAST (spid AS VARCHAR(6)) AS spid
FROM @probclients
WHERE (spid <> @@spid)
OPEN ibuffer
FETCH NEXT FROM ibuffer INTO @spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
PRINT ''
PRINT 'DBCC INPUTBUFFER FOR SPID ' + @spid
EXEC ('DBCC INPUTBUFFER (' + @spid + ')')
FETCH NEXT FROM ibuffer INTO @spid
END
DEALLOCATE IBUFFER
PRINT 'DBCCINPUTBUFFER(*) END ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- SQLHANDLE
---------------------------------------------------------------------------------------
IF @sqlhandle = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'SQLHANDLE'
SELECT
sql_handle, stmt_start, stmt_end,
total = count(*),
text = cast(dbo.fn_blocker_pfe_sql_text(sql_handle, stmt_start, stmt_end, @textsize) as VARCHAR(1024))
FROM @probclients WHERE ecid = 0 and spid<>@@spid
GROUP BY sql_handle, stmt_start, stmt_end
ORDER BY count(*) DESC
PRINT 'ESH HANDLE 0x0000000000000000000000000000000000000000 ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- DBCC OPENTRAN
---------------------------------------------------------------------------------------
IF @opentran = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'DBCC OPENTRAN(*)'
DECLARE ibuffer CURSOR FAST_FORWARD FOR
SELECT DISTINCT CAST (dbid AS VARCHAR(6)) FROM @probclients
WHERE dbid <> 0
UNION SELECT '2'
OPEN ibuffer
FETCH NEXT FROM ibuffer INTO @spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
PRINT ''
SET @dbname = DB_NAME(@spid)
SET @status = DATABASEPROPERTYEX(@dbname,'Status')
SET @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
PRINT 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
IF @status = N'ONLINE' and @useraccess = N'SINGLE_USER'
PRINT 'Skipped: Status=ONLINE UserAccess=SINGLE_USER'
ELSE
DBCC OPENTRAN(@dbname)
FETCH NEXT FROM ibuffer INTO @spid
END
DEALLOCATE ibuffer
PRINT 'DBCCOPENTRAN(*) END ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- DBCC MEMORYSTATUS
---------------------------------------------------------------------------------------
IF @memstatus = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'DBCC MEMORYSTATUS'
DBCC MEMORYSTATUS
PRINT 'MEMSTATUS ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- DBCC SQLPERF(LOGSPACE)
---------------------------------------------------------------------------------------
IF @logspace = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'DBCC SQLPERF(LOGSPACE)'
DBCC SQLPERF(LOGSPACE)
PRINT 'DBCCLOG ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- Sysperfinfo
---------------------------------------------------------------------------------------
IF @perfinfo = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'SYSPERFINFO'
SELECT TOP 1 @perfobjname=LEFT(object_name, CHARINDEX(N':',object_name)) FROM sysperfinfo
SELECT object_name, counter_name, cntr_value FROM sysperfinfo
WHERE
object_name IN (@perfobjname + N'Buffer Manager',
@perfobjname + N'Databases',
@perfobjname + N'General Statistics',
@perfobjname + N'Memory Manager',
@perfobjname + N'SQL Statistics')
PRINT 'PERFINFO ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- ::fn_trace_getinfo
---------------------------------------------------------------------------------------
IF @trace = 1
BEGIN
SELECT @time2 = GETDATE()
PRINT ''
PRINT 'TRACE_GETINFO'
SELECT * FROM ::fn_trace_getinfo(0)
PRINT 'TRCINF ' + convert(VARCHAR(12), datediff(ms,@time2,getdate()))
END
---------------------------------------------------------------------------------------
-- End time
---------------------------------------------------------------------------------------
PRINT ''
PRINT 'End time: ' + convert(varchar(26), getdate(), 121)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.sp_blocker_pfe_auto') )
DROP PROCEDURE dbo.sp_blocker_pfe_auto
GO
---------------------------------------------------------------------------------------
-- Procedure: dbo.sp_blocker_pfe_auto
---------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_blocker_pfe_auto
AS
SET NOCOUNT ON
EXEC dbo.sp_blocker_pfe80 @info = 1, @trace = 1
WHILE 1=1
BEGIN
EXEC dbo.sp_blocker_pfe80 @process2 = 1, @inputbuffer = 1, @sqlhandle = 1
RAISERROR('',0,1) WITH NOWAIT
WAITFOR DELAY '0:0:15'
EXEC dbo.sp_blocker_pfe80 @process2 = 1, @inputbuffer = 1, @sqlhandle = 1
RAISERROR('',0,1) WITH NOWAIT
WAITFOR DELAY '0:0:15'
EXEC dbo.sp_blocker_pfe80 @process2 = 1, @inputbuffer = 1, @sqlhandle = 1
RAISERROR('',0,1) WITH NOWAIT
WAITFOR DELAY '0:0:15'
EXEC dbo.sp_blocker_pfe80
@process2 = 1, @inputbuffer = 1, @sqlhandle = 1,
@waitstat = 1, @lock = 1, @opentran = 1
RAISERROR('',0,1) WITH NOWAIT
WAITFOR DELAY '0:0:15'
END
Esse script cria os seguintes scripts no banco de dados MASTER: fn_blocker_pfe_sql_text, sp_blocker_pfe80, sp_blocker_pfe_auto. Após criado esses objetos, não é necessário rodar o script novamente.
2) Execute a stored procedure sp_blocker_pfe_auto usando um usuário com privilégios de SysAdmin para coletar informações do servidor SQL. Esse script fica executando infinitamente e pode ser cancelado após obter as informações necessárias.
3) É possível coletar as informações do Blocker Script usando a linha de comando
osql -S SERVIDOR\INSTANCIA -o arquivo_saida.out -E -Q "sp_blocker_pfe_auto" -w2000
Atenção: Não esqueça de alterar o nome do servidor instância SQL Server (SERVIDOR\INSTANCIA) e do arquivo de saída.
No meu caso, a linha de comando ficou assim:
osql -S fcatae-11\katmai –o blocker2010-10-07.out -E -Q "sp_blocker_pfe_auto" -w2000
Após 30 minutos de coleta de dados, apertei Control-C para finalizar o script. Na minha máquina, foi gerado o arquivo blocker2010-10-07.out com 5MB. O tamanho do arquivo varia conforme a carga no servidor, mas é razoável estimar arquivos de 300MB para uma monitoração de 8 horas.
Exemplo
Em uma situação de bloqueio, identificamos que o SPID 56 está bloqueando a sessão 57. O recurso envolvido é a tabela identificada por 5:2105058535 (dbid:objid).
Próximos Passos
- Esse script funciona desde o SQL Server 2000. Já está na hora de atualizar para SQL 2008, usando as novas Views de sistema.
- As técnicas para analisar o resultado precisam ser documentadas. Quase todos os problemas (alta CPU, contenção de disco, TempDb, bloqueios) podem ser identificados com o auxílio desse script.
Essa semana estou reescrevendo a stored procedure para usar as novas funcionalidades do SQL 2008 e, em breve, disponibilizarei um tutorial sobre como analisar o arquivo de saída (falta tempo para fazer tudo isso). Essa é uma das principais ferramentas para diagnóstico de performance.
Links Relacionados
- Coleta de dados no SQL 2008–Script 1
- Coleta de dados no SQL 2008–Script 2
- Coleta de dados no SQL 2008–Script 3
- Versão final: Monitor SQL (Versão atualizada do Blocker Script)
Gostaria de convidar a todos que deixem seus comentários sobre o assunto – sugestões, idéias, dicas, etc.