Clique aqui para ler esse artigo em PDF.
Profiler Parte I – Criando uma trace para análise de performance de um servidor SQL Server
Os sistemas normalmente não nascem lentos, mas tendem a ficar mais lentos com o tempo. O aumento do número de usuários, a existência de mais processos concorrentes, o crescimento do volume de informação armazenada, a falta (ou excesso) de índices e, por fim, a má qualidade do código T-SQL são alguns fatores que ocasionam o aparecimento de gargalos e, consequentemente, queda de performance.
Antes de pensar que o problema "vem de fora" e cogitar em aumentar o poder de fogo do processador, discos ou memória, cabe uma análise mais detalhada dos processos ativos no servidor de banco de dados. Muitas vezes todo o problema pode ser resolvido com a adição de um índice ou filtro num comando update. Mas como saber exatamente onde está o problema?
O SQL Server 2000 possui um utilitário chamado Profiler, indicado para rastreamento dos eventos processados numa base SQL Server 2000. O Profiler é uma ferramenta de diagnóstico, ou seja, ela nos fornece material para análise. Vale destacar que ela não se propõe por si só a efetuar correções ou qualquer espécie de tuning.
Nota
Considere um evento como qualquer ação gerada pelo engine do banco. A abertura de uma conexão, a execução de um comando select, a abertura de um cursor e a execução de uma procedure são alguns exemplos.
O Profiler é uma ferramenda que consegue ser simples e ao mesmo tempo complexa, devido ao universo de possibilidades para monitoramento. Se quisermos monitorar queries, devemos selecionar um tipo de evento. Para efetuar uma análise de deadlocks, existe outra classe de eventos que deve ser selecionada. Nessa primeira etapa aprenderemos alguns aspectos básicos do funcionamento do Profiler, navegando por suas telas para criar uma trace.
Criando uma trace passo-a-passo
O Profiler é uma ferramenta utilizada para criar traces. Como analogia, considere uma trace criada pelo Profiler como uma “fotografia” dos comandos executados pelo SQL Server 2000 em um determinado intervalo de tempo. Para criar uma trace, selecione Profiler no sub-menu do SQL Server (ver Figura 1).
Figura1. Selecionando o Profiler no sub-menu do Microsoft SQL Server.
Na tela principal do Profiler, selecione File / New / Trace (ver Figura 2).
Figura 2. Criando um trace no Profiler.
O próximo passo será fornecer uma conta com privilégios de system administrator (=SysAdmin) para realizar a trace (ver Figura 3). SysAdmin é um papel especial, que configura autoridade máxima ao proprietário da conta. Um cuidado especial deve ser tomado com relação à performance: um monitoramento prolongado em servidores já sobrecarregados pode comprometer seu desempenho.
Figura 3. Tela de logon no servidor onde será realizado a trace.
Se você não tem acesso à conta sa e está em dúvida com relação aos privilégios de sua conta, abra uma sessão no Query Analyzer e execute o comando .. sp_HelpSrvRoleMember SysAdmin para verificação dos usuários que possuem privilégio SysAdmin. Você deverá encontrar o usuário informado anteriormente (ou grupo do Windows NT, no caso de logon com segurança integrada) na listagem do comando. A Figura 4 ilustra a execução desse comando.
Figura 4. Comando sp_HelpSrvRoleMember
|
ServerRole |
MemberName |
MemberSid |
|
Sysadmin |
BUILTIN\Administrators |
0x01020000000000052000000020020000 |
|
Sysadmin |
sa |
0x01 |
|
Sysadmin |
PauloRibeiro |
0x7CBDA5F0F790C842BA0A0E80D784 |
Caso tente criar uma trace e não tenha o privilégio devido, a mensagem da Figura 5 será apresentada.
Figura 5. Usuário que não possui privilégio para execução da trace.
Considerando que você tenha os privilégios necessários e tendo efetuado a autenticação com sucesso, a tela para Configurações Gerais da trace é apresentada (ver Figura 6).
Figura 6. Configuração Geral da Trace.
As opções disponíveis são:
· Trace Name: nome da trace (=Trace_Exemplo);
· Trace SQL Server: identificação do servidor onde a trace está sendo executada. Corresponde ao servidor autenticado no passo anterior (\\SrvTeste) ;
· Template Name: nome do modelo da trace. Quando criamos uma trace, selecionamos determinados tipos de eventos que desejamos analisar. Para que não precisemos informar sempre os mesmos eventos ao criar uma nova trace, salvamos modelos chamados templates. Existem alguns templates pré-definidos, o SQLProfilerStandard é um deles. A Tabela 1 fornece uma descrição resumida dos templates existentes.
Tabela 1.
|
Nome do Template |
Para que serve |
|
SQLProfilerStandard |
Trace genérica; rastreia comandos executados com sucesso no servidor. |
|
SQLProfilerTSQL |
Utilize para visualizar os comandos T-SQL startados no servidor |
|
SQLProfilerTSQL_Duration |
Utilize para obter uma trace de comandos processados no servidor ordenados por tempo de execução. |
|
SQLProfilerTSQL_Group |
Lista os comandos startados com sucesso no servidor, ordenados por ApplicationName, NTUserName, LoginName e ClientProcessId |
|
SQLProfilerTSQL_Replay |
Utilizada para gravação de traces para posterior replay(1). |
|
SQLProfiler_SPs |
Utilizado para visualização dos comandos T-SQL executados internamente nas sp´s. |
|
SQLProfilerTuning |
Utilizado na geração de eventos para posterior análise pelo Index Tuning Wizard(2) |
(1) Replay é uma feature que permite simular a execução dos comandos gravados previamente por uma trace. O objetivo é comparar duas execuções – antes e após a implementação de correções.
(2) O Index Tuning Wizard é uma ferramenta disponível na opção Tools do Profiler e se propõe a analisar traces, recomendando a criação de índices e estatísticas para aumento de performance.
· Template file name: caminho do arquivo de template utilizado, cuja extensão é .TDF. Ao selecionar um template que você criou, Template Name é automaticamente alterado para Custom Template.
· Save to file: grava o resultado da trace num arquivo em disco com extensão .TRC.
o Set maximum file size (MB): informa o tamanho máximo do arquivo em disco gerado pela trace. Ao atingir esse limite a gravação em arquivo é suspensa mas o monitoramento continua ativo na tela do Profiler.
o Enable file rollover: se o rollover estiver habilitado e o arquivo atingir o limite definido em Set maximum file size (MB), o arquivo em disco será reinicializado. Neste caso, perde-se o que foi registrado em arquivo até esse momento.
o Server processes SQL Server trace data: se um dia você se deparar com a linha de texto em sua trace “ ... some events may have been lost ....”, isto quer dizer que o servidor está muito ocupado e optou por não enviar alguns comandos para sua trace para ganhar algum fôlego de processamento. Habilitando essa opção, você estará forçando o servidor a enviar todos os comandos processados para a trace, mesmo causando perda de performance. Recomendo não utilizar.
· Save to table: grava o resultado da trace numa tabela. Com o resultado da trace numa tabela, podemos estabelecer novos filtros ou ordenações que acharmos interessantes no processo de depuração de comandos. Podemos selecionar, por exemplo, todos os comandos emitidos por determinado usuário, ou ordenar os comandos por tempo de execução.
o Set maximum rows (in thousands): limita o número de linhas na tabela originada pela trace. Assim como acontece quando Set maximum file size está ativo, ao atingir a quantidade de linhas configuradas nesse parâmetro a trace deixará somente de gravar dados na tabela – os comandos continuarão sendo enviados para a console onde o Profiler estiver sendo executado.
· Enable trace stop time: estabelece prazo limite para término da trace.
As Opções Gerais serão mantidas sem alteração num primeiro momento. Seguimos agora para a guia Events (Ver Figura 7).
Figura 7. Configurando uma trace - Guia Events.
A guia Events apresenta uma relação de todas as classes de eventos que podem ser monitorados num servidor de banco de dados SQL Server 2000. Nesse contexto, classes são agrupamentos de eventos que possuem uma característica em comum: temos uma classe para controlar execução de procedures, outra para gerenciamento de locks, etc. O template SQLProfilerStandard, por exemplo, seleciona automaticamente alguns eventos vistos na Tabela 2.
Tabela 2.
|
Classe |
Evento |
Para que serve |
|
Security Audit |
Audit Logon |
Auditar abertura de sessões no banco. |
|
|
Audit Logoff |
Auditar encerramento de sessões no banco. |
|
Sessions |
Existing Connections |
Lista todas as conexões ativas no banco no momento em que a trace é iniciada. |
|
Stored Procedures |
RPC: Completed |
Lista a execução de sp´s originadas por conexões remotas (ADO, ODBC, OLEDB, etc). |
|
TSQL |
SQL:Batch Completed |
Lista as queries executadas fora do contexto de uma stored procedure |
A próxima etapa será definir que tipo de informação queremos visualizar na trace. O template SQLProfilerStandad já seleciona uma série de colunas, mas para deixar a tela do Profiler mais “enxuta”, excluiremos as colunas LoginName e, mais abaixo, ClienteProcessesId. Clique na coluna e depois em << Remove (ver Figura 8).
Figura 8. Seleção das colunas que serão visualizadas no Profiler.
Finalizamos a definição da trace na guia Filters (ver Figura 9), utilizada para refino da trace. A aplicação Profiler, para mostrar na tela os comandos processados pelo servidor SQL Server, é responsável por uma série de comandos que são também processados pelo engine do banco. Para evitar que esses comandos apareçam na trace, ligamos o filtro ApplicationName Not Like “Profile”. Filtros são utilizados para limitar os eventos rastreados na trace, reduzindo o número de linhas afetadas, facilitando nossa compreensão e melhorando o foco de nossa análise. Poderíamos, por exemplo, filtrar somente os comandos executados por um determinado spid. Se desejássemos analisar a execução de uma stored procedure em particular, poderíamos concentrar nossa análise somente nas execuções dessa sp, utilizando também os recursos de filtros (nesse caso, o filtro ObjectId armazenaria o Id da trace que queremos analisar).
Figura 9. Criando filtros para a trace
Concluído o processo de definição, clique em RUN (ver Figura 9) para iniciar a trace (ver Figura 10).
Figura 10. Tela para monitoramento do Profiler
Um resumo do significado das colunas apresentadas na Figura 10 é apresentado a seguir:
· EventClass: os eventos rastreados pelo Profiler são agrupados em classes. Assim, existem eventos que registram a execução batchs, outros que sinalizam o logon de usuários, etc. Na figura 10, o evento ExistingConnection serve para informar que, no momento em que a trace foi iniciada, existia somente uma conexão TCP-IP ativa no servidor, representada pelo spid 60;
· TextData: utilizada para visualização do dado coletado na trace. Essa coluna depende do tipo de evento capturado. Na Figura 10, a coluna TextData apresenta algumas informações relativas à conexão (protocolo utilizado, configurações atuais, etc);
· ApplicationName: nome da aplicação;
· LoginName: login do usuário responsável pela execução do comando;
· CPU: tempo consumido de CPU para execução do comando (em milissegundos);
· Reads: número de páginas lidas em memória para executar o comando;
· Writes: número de páginas gravadas pelo comando;
· Duration: duração do comando (em milissegundos);
· SPID: identificação da sessão no SQL Server;
· Start Time: horário de início da execução do comando.
Através desta interface é possível:
o Parar a trace. Para isto clique no botão
.
o Iniciar a trace novamente. Para isto clique em
.
o Iniciar uma nova trace, efetuando toda a parametrização novamente. Para isto clique em
.
o Trocar o template SQLProfilerStandard por outro. Para isto clique em
.
o Carregar uma trace previamente gravada em arquivo .TRC (opção Save To File na tela de Configurações Gerais da Trace, na Figura 6). Para isto clique no ícone
.
o Carregar uma trace gravada numa tabela no banco. Para isto clique em
.
o Acessar a tela de configurações gerais da trace – aquela da Figura 6. Para isto utilize o ícone de propriedades
.
o Procurar por uma determinada string na trace que você acabou de gerar. Para isto, utilize o binóculo
.
o Efetuar uma limpeza na tela. Para isto utilize a borracha
.
Agora um teste prático. Com o Profiler ativo, abra uma sessão no Query Analyzer e execute a seqüencia de comandos da Listagem 1.
Listagem 1. Comandos que serão visualizados no Profiler
use NorthWind
go
create procedure stp_Mostrar_Pedido (@OrderId int)
as
select o.OrderId, o.CustomerId,o.EmployeeId,d.ProductId,d.UnitPrice,d.Quantity
from Orders o
inner join
[Order Details] d
on o.OrderId = d.OrderId
where o.OrderId=@OrderId
RETURN
go
exec stp_Mostrar_Pedido 10249
go
Dirija-se agora ao Profiler e confirme o resultado (ver Figura 11).
Figura 11. Resultado da execução de comandos na console do Profile
Analisando os eventos gerados na tela do Profiler, verificamos que:
o Ao iniciar a trace, a única conexão existente no banco era do usuário sa, na conexão de spid igual a 60. O evento ExistingConnection tem por finalidade mostrar todas as conexões ativas no banco no início da trace. Vale uma nota aqui: o filtro ApplicationName not like SQL Profiler suprimiu a conexão do Profiler.
o O Evento ExistingConnection não popula as colunas CPU, Reads, Writes e Duration. Por isso, aparecem em branco na console. Para saber as colunas populadas por cada tipo de evento, procure por Events no Books On Line (=BOL) O BOL pode ser encontrado em Iniciar\Programas\Microsoft SQL Server\Books On Line.
o A parte inferior da console destaca o comando da linha TextData na linha do cursor ;
o Note que a linha de execução da stored procedure stp_Mostrar_Pedido aparece como SQL:Batch Completed e não como RPC:Completed por se tratar de uma conexão local e não remota ;
o A execução da procedure stp_Mostrar_Pedido consumiu 16 milissegundos de CPU, efetuou 100 leituras lógicas, não gravou nenhuma informação (Writes=0) e levou 16 milissegundos para ser executada.
Gravando a Trace
Porque gravaríamos uma trace? Traces normalmente são extensas; Não é difícil passarmos mais de um dia analisando suas queries. Só esse fator já justificaria sua gravação. Outras vezes gostaríamos de refinar um pouco mais o resultado do Profiler. Gravando a trace numa tabela, conseguimos facilmente ordenar, excluir ou mesmo estabelecer filtros adicionais e, depois então, recarregar a trace “enxuta” para a console. Por último, o Index Tuning Wizard, utilitário para efetuar tuning de uma base SQL Server, requer que o workload esteja previamente gravado em arquivo ou tabela.
Para gravar a trace, siga até a opção File da barra de menu e escolha Save As (ver Figura12).
Figura 12. Salvando a trace
As opções disponíveis para salvamento da trace são:
· Trace Template: utilize para gerar um template (arquivo com extensão .tdf, de Template Data File). Como vimos anteriormente nesse artigo, um template serve para armazenar a parametrização da trace, indicando quais eventos, colunas e filtros que foram aplicados. Se gravássemos um template com o nome SQLMag_Ed11.tdf em c:\Templates, poderíamos carregá-lo para geração de uma nova trace na guia Properties, selecionando o arquivo .tdf em Template File Name, sob a configuração Use the following trace template (ver Figura 6) . Note que essa opção NÃO grava o resultado da trace, apenas sua configuração.
· Trace File: salva um arquivo em disco com a extensão .trc com o resultado da trace. Nesse arquivo estarão gravados todos os comandos capturados pela trace, que poderão ser analisados na própria tela do Profiler, na opção File\Open\Trace File.
· Trace Table: armazena o resultado da trace numa tabela. Gravar o resultado da trace numa tabela é uma prática bastante útil pois viabiliza a utilização de comandos select para filtrar ainda mais a trace que estamos analisando. Como exemplo, se gravássemos uma trace na tabela de nome trace_sqlmag, poderíamos facilmente levantar os três batchs com maior tempo de execução com o comando apresentado na Listagem 2.
Listagem 2.
select top 3 Duration,LoginName,TextData
from trace_sqlmag
where eventclass=12 --SQL:BatchCompleted
and duration > 0
order by duration DESC
------------------------------------------------------------------------------------------------------------------------------------
|
Duration |
LoginName |
TextData |
|
1500 |
SQLMag\UserSQL |
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters |
|
300 |
Sa |
insert into groupContents select DISTINCT … |
|
250 |
Sa |
drop index groupcontents.PK_groupcontents |
· SQL Script : gera um arquivo-texto (extensão .sql) com o lote de comandos T-SQL necessários para criar e executar a trace. Através desse arquivo, poderíamos iniciar a trace fora dos limites do Profiler – por exemplo à partir do Query Analyser. A geração de scripts é utilizada quando existe a necessidade de executarmos uma trace automaticamente, ou em horários pouco convidativos. Na próxima edição, entraremos em mais detalhes sobre esse procedimento.
Conclusão
Quando o assunto é tuning, o Profiler é uma ferramenta indispensável. No próximo número continuaremos esse assunto e nos aprofundaremos nos principais eventos que devem ser analisados tendo em vista a otimização de processos. Até lá!
