Dica rápida, 2 de fevereiro de 2005

Dica rápida: Executando o Profiler em um SQL Server ocupado
por Brian Moran savvy@sqlmag.com

P: Eu tentei usar o SQL Server Profiler para ajustar um banco de dados ocupado do SQL Server para um de meus clientes. O banco de dados 8GB estava sendo executado em um servidor com 8 CPUs com 70 a 80% de utilização da CPU. Imediatamente, o servidor parou de responder e eu tive que reiniciá-lo. O suporte do Microsoft Developer Network (MSDN) me informou que o Profiler pode colocar uma carga pesada em SQL Servers ocupados. Para ajustar o banco de dados, eu precisei do texto do SQL e ler o resultado das consultas. Como eu coleto essas informações de um SQL Server multiprocessado ocupado sem executar o servidor na base?

R: Eu considero o Profiler a ferramenta mais importante no meu arsenal de ajuste do SQL Server e o uso construtivamente quase todos os dias. Nos últimos anos, eu usei o Profiler em mais de 100 sites de clientes, incluindo servidores que realizam dezenas de milhares de transações por segundo. Enquanto era cuidadoso, eu nunca estive em uma situação na qual não pudesse usar o Profiler. Mas como qualquer ferramenta avançada, o Profiler pode causar problemas.

Primeiro, o Profiler não é nada mais que um front-end de GUI que chama uma série de funções e procedimentos coletivamente denominados Rastreamento SQL. Na maioria dos casos, o uso do Profiler para capturar informações do sistema é mais caro do que chamar procedimentos do Rastreamento SQL diretamente. Para verificar se você não perdeu nenhum evento em um servidor ocupado, selecione a opção de dados de rastreamento dos processos do servidor a partir da GUI do Profiler. No entanto, esta opção inicia dois rastreamentos separados que capturam o mesmo conjunto de eventos. O SQL Server envia um fluxo de evento para a GUI do Profiler e o outro fluxo de evento para um arquivo local no servidor. Está preocupado com o efeito de executar um rastreamento? Se estiver, certamente você não deseja executar dois rastreamentos!

Outra opção do Profiler permite você gravar diretamente em uma tabela do SQL Server. Mas não use esta opção se você não estiver preocupado com o desempenho. O SQL Server gravará na tabela cada evento que o Profiler captura e também registrará cada evento no log de transações, criando uma carga enorme em um servidor ocupado. Em vez disso, você deve capturar os dados de evento em um arquivo local e usar a função fn_trace_gettable() para carregar os dados rastreados em uma tabela para análise. De forma alternativa, você pode gravar os dados do rastreamento em uma unidade de rede, mas eu sempre posso gravar os dados do rastreamento em um arquivo local sem criar gargalos de E/S adicionais.

Raramente, eu executo o Profiler em um servidor de produção ocupado devido aos efeitos de desempenho. Em vez disso, eu uso uma série de procedimentos personalizados que chamam diretamente os procedimentos do Rastreamento SQL para interromper, iniciar e controlar rastreamentos. Embora eu não tenha espaço para explicar meus procedimentos personalizados nesta resposta, aprender como escrever seus próprios procedimentos para controlar rastreamentos é bem simples. Você pode usar o Profiler para entender como o rastreamento funciona. Primeiro, inicie uma instância do Profiler para remover o filtro padrão que impede a captura da atividade do Profiler. Em seguida, inicie outra instância do Profiler e crie o rastreamento que você deseja executar manualmente. A primeira instância do Profiler capturará os procedimentos que a segunda instância usa para realizar o rastreamento e você terá um bom modelo para criar seus próprios procedimentos de rastreamento. Você também pode usar a opção Rastreamento de Script diretamente do Profiler para usar scripts executando rastreamentos.

Os rastreamentos, se forem executados através do Profiler ou através de chamadas diretas para o Rastreamento SQL, raramente degradam o desempenho a menos que cresçam muito e rapidamente. Eu vi arquivos de rastreamento que cresceram 20 MB por segundo, o que provavelmente danificará o desempenho. Entretanto, muitas vezes, eu executo rastreamentos em servidores extremamente ocupados sem uma penalidade de desempenho significativa, pois mantenho a taxa de crescimento dos meus arquivos de rastreamento em um nível gerenciável. Você terá que experimentar para determinar quando os rastreamentos estão crescendo muito e muito rápido para o seu hardware específico. Eu sempre especifico o tamanho máximo do arquivo ao rastrear em um servidor ocupado. Se um rastreamento começar a crescer muito de forma incontrolável e ultrapassar o limite do tamanho máximo do arquivo definido, o rastreamento será interrompido. Eu acho que 50 MB é um tamanho máximo seguro; de acordo com a minha experiência, se um rastreamento for caro o suficiente para degradar o desempenho, ele atingirá o limite de 50 MB dentro de alguns segundos. O tamanho máximo adequado para sua situação dependerá do hardware e do volume da transação.

Outra forma de controlar o tamanho do arquivo de rastreamento e reduzir o potencial de um problema de desempenho induzido pelo rastreamento é incluir somente as colunas de eventos e dados necessárias. Encontrar a mistura adequada de colunas de eventos e dados é um assunto complexo; você pode ler mais sobre como selecionar as melhores colunas de eventos e dados para suas necessidades em "Tracking Down Event Clues", maio de 2001, InstantDoc ID 20159 de Kalen Delaney, bem como no meu artigo "Working with Trace Filters", abril de 2003, InstantDoc ID 38040. Você também pode controlar o tamanho do rastreamento especificando a duração mínima da CPU para transações que deseja capturar. A maioria das suas transações serão rápidas, sendo executadas em menos de 20 ms. Portanto, se você definir um filtro com duração mínima de 20 ms, poderá cortar muitos dados que você está gravando no arquivo de rastreamento sem perder muitos eventos significativos.

Meu conselho final sobre o gerenciamento do tamanho do arquivo de rastreamento é observar as funções definidas pelo usuário (UDFs), que podem criar uma grande quantidade de dados de rastreamento. Se você tem uma instrução SELECT que usa uma UDF para retornar 10.000 linhas e a UDF contém 10 instruções, você gerou apenas 100.000 eventos de rastreamento. Se 10 pessoas executarem a instrução SELECT ao mesmo tempo, a UDF irá gerar 1 milhão de eventos de rastreamento. Muitas vezes, você desejará incluir filtros de rastreamento para eliminar a atividade da UDF quando tiver certeza de que uma UDF específica não está contribuindo para o problema de desempenho.

Esta dica do SQL Server foi trazida para você pela SQL Server Magazine, o guia inteligente para criar aplicativos de classe mundial.

Peça a SQL Server Magazine hoje e obtenha acesso online GRATUITO para cada artigo publicado na revista desde 1999! Isso inclui cinco anos de dicas, truques e instruções. Saiba mais .