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

Trace_AppPerformance.sql

Comments (0)

Skip to main content