Olá amigos!
Esse post é bem simples. Uma receita de
bolo para voce agendar a execução do seu SQL Server Profiler e retornar isso
depois em uma consulta T-SQL. Ótimo para relatórios de Performance e analise de
problemas.
Então mãos a obra!
-- ## PASSO 1 -
Criar o Script Tracer Definition.
O Script Tracer Definition é um arquivo .SQL que contém todas as regras sobre o que será capturado, quanto tempo e quais critérios de filtro. Como ele é bem extenso e pouco intuitivo para ser feito na mão, o primeiro passo é utilizar o SQL Profiler para fazer o trabalho para você.
1 - Abra o SQL Profiler, crie um novo
Trace.
2 - Selecione a checkbox SaveToFile e
escolha um diretório na sua máquina para armazenarmos o resultado.
Obs: Esse diretório na verdade deve ser um
diretório do servidor onde voce executará o Job. Mas é possivel alterarmos essa
configuração depois.
Obs2: A opção SaveToTable não é possivel
no job agendado (Checar)
3 - Selecione a checkbox "Enable Trace Stop Time". Isso dirá ao Profiler que hora ele deve parar de processar o trace.
No meu exemplo, quero que o profiler rode
das 09:00 até as 11:00.
Obs: A configuração de Start fica por
conta do schedule do job como veremos adiante.
4 - Configure os eventos que voce deseja.
Em geral eu capturo as StoreProcedures e Batchs T-SQL que finalizaram.
5 - Clique em Run e na sequencia para a
captura. Vá File/Export/Script
Trace Definition/ For SQL Server 2005-2008
6 - Abra o Script no SQL Management
Studio. Abaixo o script que foi gerado. Fiz comentários grifados em cada ponto
importante.
*/
/****************************************************/
/* Created by:
SQL Server 2008 Profiler */
/* Date: 16/09/2011
17:52:36 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare
@maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2011-09-19 11:00:00.000'
--<< Aqui é o Stop Time que definimos.
set
@maxfilesize = 5 --<<---- Aqui é o tamanho maximo do arquivo TRC
em Mb.
-- Please replace the text InsertFileNameHere, with an
appropriate
-- filename prefixed by a path, e.g.,
c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If
you are writing from
-- remote server to local drive, please use UNC path
and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'F:\Traces\Trace_Agendado', @maxfilesize,
@Datetime
/*<<--
Onde está em amarelo voce coloca o diretório do servidor e nome do arquivo
desejado (sem o .trc) .>> */
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter
int
declare
@bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c16c42f1-c66c-4152-8fc1-d92a44fef880'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - fd8af8d3-5b18-49b2-93d4-048a74902059'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID-- Loop para aguardar a conclusão do job
DECLARE @Running int = 1
WHILE @Running = 1
BEGIN
WAITFOR DELAY '00:01:00'
IF EXISTS(SELECT * FROM sys.traces WHERE ID = @TraceID)
SET @Running = 1
else SET @Running = 0
END
goto finish
error:
select ErrorCode=@rc
finish:
go
--################
FIM DO SCRIPT ##############################
-- OBSERVAÇAO:
Voce pode fazer um teste do script no próprio SSMS. É só executar -- o script, se
ele foi bem sucessido ele retorna um TraceID que pode ser
-- consultado na tabela sys.trace
/*
-- ## PASSO 2 -
Criação de Job.
Vamos criar um
Job com dois passos
1 - No primeiro será o da execução do
trace.
2 - Vamos acrescentar um pequeno loop
T-SQL que checa se o Trace conclui logo depois do "select
TraceID=@TraceID". Veja abaixo.
*/
(…)
select TraceID=@TraceID
DECLARE @Running int = 1
WHILE @Running = 1
BEGIN
WAITFOR DELAY '00:01:00'
IF EXISTS(SELECT * FROM sys.traces WHERE ID = @TraceID)
SET @Running = 1
else
SET @Running = 0
END
(…)
/*
3 - O segundo passo é colocar o conteudo
do trace em uma tabela usando a function fn_trace_gettable
*/
SELECT TextData, CPU,Reads,Writes,Duration
into
SuaBaseDeDados.dbo.SuaTabela
-- Coloque acima a base e tabela de sua referencia
FROM fn_trace_gettable ( N'F:\Traces\Trace_Agendado.trc' , DEFAULT )
-- 4 - E pronto só agendar o Job e brincar com
sua tabelinha.
/*
## BEGIN BONUS
##
Se
voce chegou até aqui é porque o assunto realmente te interessou. Existe um
pequeno truque para você tornar o Stop Time dinamico.
É
bem simples e torna o job ainda mais versátil. Se quiser saber como faz, ou se
voce descobriu como fazer me mande um email.
## END BONUS ##
Gostou da dica? Achou algum bug? Tem
alguma pergunta?? Fique a vontade para me contactar e comentar abaixo!
email: fjantunes@gmail.com
*/
