Gerando traces através de SPs
Hoje eu fui fazer uma análise de performance em um cliente e foi uma experiência muito legal, pois tive a oportunidade de trabalhar em um ambiente grande, sem muito stress, aprender um pouco mais com outra pessoa e aplicar algumas das ferramentas que tenho conhecido ultimamente.
Obs: Prometo colocar um post sobre as ferramentas, que estão disponíveis publicamente, mas a maioria das pessoas não conhece (inclusive eu até pouco tempo atrás).
Para fazer um serviço pró-ativo de análise de performance, o primeiro passo é coletar os dados que servirão de base para sua análise e a partir deles distinguir pontos interessantes para serem atacados. Depois de colocar o performance monitor e o blocker script rodando foi a vez do Profiler, configurei dois traces e os deixei rodando.
Tudo estava correndo bem e aproveitei para pensar um pouco no processo de levantamento, já que irei repetir isso milhares de vezes em outros clientes. O Profiler é a ferramenta mais intrusiva na coleta de dados e para automatizar o procedimento e diminuir o impacto da interface gráfica jogando aquele mundo de informação na tela, resolvi deixar pronto alguns scripts para gerar os traces através do Query Analyzer. Para o propósito deste artigo, não irei abordar outras ferramentas de coleta e possíveis melhoras no processo (ainda mais porque não sei o que fazer com algumas ;-)).
Para gerar um script de configuração e ativação do trace, você pode utilizar o Profiler para determinar exatamente os eventos e colunas necessários e depois utilizar a opção de menu “File > Script trace > For SQL Server 2000” para gerar o seu “.sql”.
O script conterá as seguintes informações (aproximadamente, claro)
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 400 -- Em MB
/*
Cria o objeto de trace no SQL Server apontando para o caminho especificado
*/
exec @rc = sp_trace_create @TraceID output, 0, N'C:\temp\Profiler', @maxfilesize, NULL
if (@rc != 0) goto error
O valor da variável @TraceID irá conter o inteiro criado pelo SQL Server para que você possa referenciar o trace. Neste caso está sendo criado um trace chamado profiler.trc no caminho “C:\temp” com um tamanho máximo de 400 MB. Se o retorno foi igual a zero significa que o trace foi criado com sucesso.
Depois de criado o trace, chega a hora de configurar os eventos e as colunas que devem ser capturadas. Isso é feito através da stored procedure sp_trace_setevent, como no exemplo abaixo:
declare @on bit
set @on = 1
-- Evento: RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @on -- Captura a coluna TextData
exec sp_trace_setevent @TraceID, 10, 3, @on -- Captura a coluna DatabaseID
....
Sempre coloco nos meus traces todas as colunas de eventos especificados, para garantir que nenhuma informação importante irá faltar. O script em anexo mostra a criação de um trace padrão que eu utilizo para monitorar as instruções e SPs que estão sendo executadas.
Para evitarmos que o arquivo cresça exageradamente, podemos aplicar alguns filtros. O profiler gera por padrão um filtro para não capturar eventos onde o ApplicationName seja “SQL Profiler”. Eu sempre aplico mais dois filtros, um deles é para somente pegar eventos com duração superior a X ms e ignorar o SPID da conexão onde o blocker script está sendo executado.
O valor da duração “X” eu determino através de testes antes de começar a monitoração, coloco 1000ms e vejo o resultado, se somente algumas consultas estiverem passando eu diminuo esse valor para 100, se mesmo assim poucas consultas passam disso, eu baixo o valor para 10 milisegundos e me dou por satisfeito.
-- Cria as variáveis de filtro
declare @intfilter int
declare @bigintfilter bigint
-- Filtro: ApplicationName not like 'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Filtro: SPID not equal to 144 (Retirar o SPID do blocker script)
set @intfilter = 144
exec sp_trace_setfilter @TraceID, 12, 0, 1, @intfilter
-- Filtro: Duration greater than or equal to 100 (ms)
set @bigintfilter = 100
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
Depois que tudo estiver configurado, ative o trace utilizando a instrução abaixo. Note que @TraceID foi gerado pelo sp_trace_create e para que ele esteja visível neste ponto, tudo precisa ser executado em um mesmo batch. Senão basta substituir a variável pelo identificador do trace.
exec sp_trace_setstatus @TraceID, 1
Neste ponto o @TraceID já está fora de escopo e para finalizar a execução do trace é necessário utilizar o comando a seguir, colocando o traceId correto. Se desejar parar o trace e iniciá-lo novamente, veja a utilização do parâmetro 0 ao invés do 2.
exec sp_trace_setstatus @TraceID, 2
Para conseguir informações sobre todos os traces configurados no SQL Server, execute “SELECT * FROM ::fn_trace_getinfo(default) ”.
[]s
Luti