SQL SERVER 2008 – Server Side Tracing

Publicado 17 Mar 2011 - 15:41 by cardoso.alisson

Técnicas e ferramentas de monitorização para servidores são assuntos sempre em alta, seja no mundo real ou acadêmico. Afinal, todos querem o ambiente de produção funcionando.

O SQL Server tem uma ferramenta muito popular chamada Profiler. O Profiler é distribuído junto com o SQL Server 2008 e pode encontrado no Microsoft SQL Server 2008, nas ferramentas de Performance Tools. Ele possui uma interface gráfica, fácil de usar, onde é possível visualizar instruções que são enviadas e alguns eventos que ocorrem no servidor.

Contudo, a interface gráfica é apenas uma máscara. Nos bastidores, o Profiler faz uso de um conjunto de stored procedures para realizar a monitorização. É possível usar somente as stored procedures e dispensar a interface gráfica. A monitorização ou tracing que faz uso somente de stored procedures é chamado de Server Side Tracing. Ele não tem interface gráfica, é feito por script e os eventos e instruções enviados ao servidor são salvos em arquivos de extensão “trc”.

Então, surge a pergunta: Porquê trocar a interface gráfica com todas as suas facilidades por uma coleção de arquivos?

Em geral, o Server Side Tracing é mais leve do que o Profiler. O MVP  Linchi Shea  publicou um artigo muito bom comparando a performance entre eles.

As stored procedures que compõe este conjunto são:

sp_trace_create: Cria e configura algumas propriedades do trace. A seguir alguns exemplos das propriedades: local onde os arquivos serão salvos; tamanho de cada arquivo em MB; números de arquivos que serão criados pelo trace. Essa stored procedure retorna um identificador para o trace. Para maiores informações consulte o MSDN.

sp_trace_setevent: Adiciona ou remove eventos e suas respectivas colunas ao trace. Não é possível adicionar ou remover colunas ou eventos em um trace em execução. Para saber mais sobre os eventos e colunas consulte o MSDN.

sp_trace_setstatus: Altera o status do trace. Cada status tem um valor correspondente: 0 parado; 1 executar; 2 fechar e liberar arquivo. Antes de fechar o é necessário parar (status 0) o trace. Para maiores informações consulte o MSDN.

sp_trace_setfilter: Adiciona filtros ao trace limitando a quantidade de informações nos arquivos. O trace deve estar parado (status 0) para adicionar os filtros. Os operadores lógicos AND e OR são representados por números. O valor 0 (zero) para AND e   1 (um) para OR. Seguido a mesma regra os operadores de comparação são representados pelos seguintes valores:  0 (zero) para = (igual);   1 (um) para <> (diferente);    2(dois) > (maior do que );    3(três) para <( menor do que);    4(quatro) >= (maior ou igual);    5(cinco) <= (menor ou igual);    6 (seis) like;    7 (sete) not like. Para maiores informações consulte o MSDN.

A seguir, um exemplo de uso. Atenção para a ordem dos passos e aos comentários no código.

Passo 1 – Criar definições do Trace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare @trace_id int
declare @tamanho_max_arquivo bigint
declare @trace_status bit
set @tamanho_max_arquivo=10
set @trace_status=1
exec sp_trace_create
-- identificador do trace
@traceid=@trace_id OUTPUT,
@options=2,
-- local e nome do arquivo.
--NAO USAR '_' UNDERSCORE E EXTENSÃO TRC NO NOME DO ARQUIVO
@tracefile=N'C:\TRACE_EXEMPLO',
-- o tamanho de cada arquivo sera de 10MB
@maxfilesize=@tamanho_max_arquivo 

Passo 2 – Adicionar Eventos e Colunas

O evento SQL:BatchCompleted que é representado pelo valor 12 será adicionado.

1
2
3
4
5
6
7
8
9
10
11
12
13
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=01, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=03, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=08, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=09, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=10, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=11, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=12, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=13, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=14, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=15, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=16, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=17, @on=@trace_status
exec sp_trace_setevent @traceid=@trace_id, @eventid=12, @columnid=18, @on=@trace_status

 Passo 3 – Executar Trace

1
exec sp_trace_setstatus @traceid=@trace_id , @status=1

 Identificar Trace

Para encerrar um trace é necessário saber seu código de identificação. A função fn_trace_getinfo  retorna a lista de traces ativos do servidor com suas propriedades. As propriedades são as mesmas definidas na stored procedure sp_trace_create. Veja o exemplo abaixo:

1
select * from::fn_trace_getinfo(default)

 A coluna traceid armazena o código identificador de cada trace. A coluna property as seguintes propriedades:

  1. Mostra as opções do parâmetro @options
  2. Local e nome do arquivo, parâmetro @tracefile
  3. Tamanho máximo de cada arquivo, parâmetro @maxfilesize
  4. A data e hora a qual o trace ira parar automaticamente, parâmetro @stoptime. Neste exemplo o parâmetro foi ignorado, por isso o valor é NULL.
  5. Status atual do trace

Todos os parâmetros listados acima são da stored procedure sp_trace_create.
A coluna value o valor para cada propriedade. O código de identificador para o trace do exemplo é 2.

Parar Trace

Com a stored procedure abaixo é possível pausar o trace. O primeiro parâmetro é o código identificador do trace, o segundo o status.

1
exec sp_trace_setstatus 2, 0

 Encerrar Trace

A stored procedure abaixo fecha definitivamente o trace e o remove da lista de traces da instância. A ordem de parâmetros é idêntica ao tópico “Parar Trace” acima. Após fechá-lo é possível mover ou excluir o arquivo.

1
exec sp_trace_setstatus 2, 2

 Ler os arquivos gerados pelo Trace Server Side

Os arquivos podem ser lidos de duas maneiras: a primeira pelo SQL Server Profiler; segunda pela função fn_trace_gettable.

Caso sua escolha seja no pelo Profiler, siga os passos:

  1. Inicie o Profiler
  2. Clique em File -> Open -> Trace File

Se sua escolha for pela segunda opção, a função fn_trace_gettable necessita de 2 parâmetros. O primeiro é a path seguida pelo nome do arquivo. O segundo é o numero de arquivos que você deseja ler, caso o seu trace tenha gerado mais de um arquivo. Se quiser ler apenas 1 (um) arquivo passe a default.

1
select * from ::fn_trace_gettable('C:\TRACE_EXEMPLO.trc', default)

 Leia o arquivo pelas duas opções e veja qual lhe agrada mais.

Espero que tenha gostado do post.

Até ao próximo.