Como solucionar problemas de desempenho de aplicativo com o SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 224587 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

Este artigo passo-a-passo descreve como solucionar os problemas de desempenho do SQL Server. Solucionar problemas de desempenho envolve o uso de uma série de etapas para isolar e determinar a causa do retardamento de um aplicativo. As possíveis causas incluem:
  • Bloqueio.
  • Contenção de recurso do sistema.
  • Problemas de design do aplicativo.
  • Um conjunto específico de consultas ou procedimentos armazenados com longos períodos de execução.
Esse artigo descreve como determinar a origem de um problema de desempenho. Ele também se refere a outros artigos na Base de Dados de Conhecimento Microsoft que abordam os detalhes dos problemas de desempenho específicos para obter soluções de problemas adicionais.

SQL Profiler


O SQL Profiler é uma ferramenta poderosa para solucionar problemas de desempenho do aplicativo do seu SQL Server 7.0 ou posterior. O SQL Profiler permite que você capture facilmente todos os eventos que estão ocorrendo no servidor sob um carregamento típico e fornece informações sobre esse eventos. Usar o SQL Profiler em conjunto com o Monitor de Desempenho do Microsoft Windows NT e algumas consultas para identificar se o bloqueio está ocorrendo fornecerá a você as informações necessárias para resolver a grande maioria dos problemas de desempenho.

O que monitorar

1. Configure o SQL Profiler para capturar um rastreamento. Para fazer isto, execute as seguintes etapas:
  1. Abra o SQL Profiler.
  2. No menu Tools, clique em Options.
  3. Verifique se as opções All Event Classes e All Data Columns estão selecionadas.
  4. Clique em OK.
  5. Crie um novo rastreamento.
  6. No menu File, aponte para New e clique em Trace.
  7. Na guia General, especifique um nome de rastreamento e um arquivo para o qual capturar os dados.
  8. Na guia Events, adicione os seguintes tipos de evento para o seu rastreamento:

    Recolher esta tabelaExpandir esta tabela
    CabeçalhoEvento a adicionarDescrição
    CursorsCursorPrepareEsse evento indica que um cursor em uma instrução SQL foi preparado usando ODBC, OLEDB ou a biblioteca do banco de dados.
    Error and WarningMissing Column StatisticsEsse evento indica que as estatísticas de coluna que podem ser úteis para o Optimizer não estavam disponíveis. A coluna Text mostra a lista de colunas com estatísticas ausentes. Esse evento, em conjunto com um evento Misc: Auto-UpdateStats, indicam que a opção Auto Create Statistics foi disparada.
    Misc.AttentionEsse evento indica que um sinal de atenção foi enviado por um cliente.
    Misc.Auto-UpdateStatsEsse evento indica que a opção Auto Update Statistics foi disparada.
    Misc.Exec Prepared SQLEsse evento indica que o ODBC, o OLE DB ou a biblioteca do banco de dados executou uma instrução ou instruções Transact-SQL preparada(s).
    Misc.Execution PlanEsse evento mostra o plano de execução da instrução Transact-SQL que foi executada.
    Misc.Prepare SQLEsse evento indica que um aplicativo ODBC, OLE DB ou uma biblioteca do banco de dados preparou uma instrução ou instruções Transact-SQL para uso.
    Misc.Unprepare SQLEsse evento indica que um aplicativo ODBC, OLE DB ou uma biblioteca do banco de dados não preparou uma instrução ou instruções Transact-SQL para uso.
    SessionsConnectEsse evento indica que uma nova conexão foi feita.
    SessionsDisconnectEsse evento indica que um cliente se desconectou.
    SessionsExisting ConnectionEsse evento indica que uma conexão existiu quando o rastreamento do SQL Profiler foi iniciado.
    Stored ProceduresSP: CompletedEsse evento indica que um procedimento armazenado completou a execução.
    Stored ProceduresSP: RecompileEsse evento indica que um procedimento armazenado foi recompilado durante a execução.
    Stored ProceduresSP: StartingEsse evento indica que um procedimento armazenado iniciou a execução.
    Stored ProceduresSP: StmtCompletedEsse evento indica que uma instrução em um procedimento armazenado completou a execução.
    TSQL:SQL:BatchCompletedEsse evento indica que um lote Transact-SQL foi concluído. A coluna Text mostra a instrução que foi executada.
    TSQL:SQL:StmtCompletedEsse evento indica uma instrução Transact-SQL que foi concluída. A coluna Text mostra a instrução que foi executada.
    TSQL:RPC:CompletedEsse evento indica que uma chamada de procedimento remoto (RPC) foi concluída.
  9. Se o seu aplicativo estiver recebendo erros de tempo limite, parar de responder (travar) ou enfrentar outros eventos que fazem com que as instruções com problema nunca sejam concluídas, inclua também os seguintes eventos:

    Recolher esta tabelaExpandir esta tabela
    TSQL:SQL:BatchStartingEsse evento indica o início de um lote Transact-SQL. A coluna Text mostra a instrução que está sendo executada.
    TSQL:SQL:StmtStartingEsse evento indica o início de uma instrução Transact-SQL. A coluna Text mostra a instrução que está sendo executada.
    TSQL:RPC:StartingEsse evento indica o início de uma chamada de procedimento remoto (RPC).
    Stored ProceduresSP: StmtStartingEsse evento indica que uma instrução em um procedimento armazenado está iniciando a execução.


    Isso ajudará você a verificar se é possível ver a instrução que estava sendo executada quando o tempo limite ocorreu.
  10. Na guia Data Columns, verifique se as seguintes colunas estão incluídas:

    Para o SQL Server 2000

    Start Time

    End Time

    LoginSid

    SPID

    Event Class

    TextData

    IntegerData

    BinaryData

    Duration

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    DBUserName


    Para o SQL Server 7.0

    Start Time

    End Time

    Connection ID

    SPID

    Event Class

    Text

    Integer Data

    Binary Data

    Duration

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    SQL User Name

Para obter informações sobre o uso do SQL Profiler, consulte os Books online do SQL Server 7.0 e do SQL Server 2000.


2. Use o Monitor de Desempenho para capturar os contadores do Windows NT e do SQL Server. Para fazer isto, execute as seguintes etapas:
  1. Inicie o Monitor de Desempenho do Windows NT.
  2. No menu Exibir, clique em Log.
  3. No menu Opções, clique em Log.
  4. Especifique um nome de arquivo e um local para registrar os contadores de desempenho. É possível ajustar o intervalo de atualização, conforme apropriado.
  5. No menu Editar, clique em Adicionar ao log.
  6. Adicione todos os objetos. Ambos os objetos do Windows NT e do SQL Server.
  7. Para iniciar o log, no menu Opções, clique em Log e no botão Iniciar log.

Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
150934 Como criar um Log do Monitor de Desempenho para a solução de problemas do NT

3. Verifique os bloqueios.

Para ver se o bloqueio está ocorrendo, execute o procedimento armazenado do sistema sp_who:
exec sp_who
Essa saída conterá uma coluna blk. Examine a saída para qualquer entrada diferente de zero que indica que o bloqueio está ocorrendo. Execute esse procedimento periodicamente durante o período no qual ocorre a retardamento de desempenho.

Observação Executar o procedimento armazenado de sistema sp_who é uma verificação para ver se existe bloqueio. Normalmente, não é informação suficiente para solucionar o problema de bloqueio completamente. Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
251004 INF: Como monitorar o bloqueio do SQL Server 7.0

Executar o aplicativo sob um carregamento típico

O ideal é capturar o Monitor de Desempenho do SQL Profiler e bloquear a saída durante o mesmo período. Esse período deve abranger um período no qual o desempenho do aplicativo muda de bom para ruim. A combinação dessas informações ajudará você a obter uma idéia mais clara de onde o retardamento do desempenho está ocorrendo.


Interpretar os resultados

  1. Verifique os bloqueios.

    Se a coluna blk na saída sp_who for diferente de zero, isso indicará que o bloqueio está ocorrendo no seu sistema. Se os processos estiverem bloqueando uns aos outros, os processos que estão sendo bloqueados poderão enfrentar períodos de execução mais longos. Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
    224453 INF: Entendendo e resolvendo problemas de bloqueio do SQL Server 7.0 ou 2000
  2. Examine a saída do SQL Profiler.

    Exibir os dados do SQL Profiler de forma eficiente é extremamente valioso para resolver os problemas de desempenho. O mais importante a se perceber é que não é preciso ver tudo que você capturou. Seja seletivo. O SQL Profiler fornece recursos para ajudá-lo a exibir os dados capturados de forma eficiente. Nas guias Properties (clique em Properties no menu File), o SQL Profiler permite que você limite os dados exibidos removendo as colunas de dados ou os eventos, agrupando (classificando) por colunas de dados e aplicando filtros. É possível pesquisar todo o rastreamento ou uma coluna específica para obter valores específicos (no menu Edit, clique em Find). Também é possível salvar os dados do SQL Profiler em uma tabela do SQL Server (no menu File, aponte para Save As, clique em Trace Table) e execute as consultas do SQL nela.

    Atenção para executar a filtragem apenas em um arquivo de rastreamento salvo anteriormente. Se você executar essas etapas em um rastreamento ativo, haverá o risco de perder os dados capturados desde que o rastreamento começou. Salve um rastreamento ativo em um arquivo ou tabela primeiro (no menu File, clique em Save As) e abra novamente (no menu File, clique em Open) antes de continuar. Ao trabalhar em um arquivo de rastreamento salvo, a filtragem não remove permanentemente os dados que estão sendo filtrados; ela apenas não os exibe. É possível adicionar e remover os eventos e as colunas de dados conforme necessário para ajudá-lo a concentrar-se em suas pesquisas.

    A primeira etapa ao examinar arquivos de rastreamento do SQL Profiler para casos de desempenho é determinar onde os tipos diferentes de eventos estão ocorrendo no servidor.

    Agrupar o rastreamento por classe de evento:

    a. No menu File, clique em Properties.

    b. Na guia Data Columns, use o botão PARA CIMA para mover a Event Class no cabeçalho Groups e o botão PARA BAIXO para remover todas as outras colunas no cabeçalho Groups.

    c. Clique em OK.

    Agrupar por coluna de classe de evento mostra qual tipo de eventos está ocorrendo no SQL Server e com que freqüência. Pesquise essa coluna para os seguintes eventos:

    SP:RECOMPILE

    Esse evento indica que um procedimento armazenado foi recompilado durante a execução. Vários eventos de recompilação indicam que o SQL Server está usando recursos na compilação de consulta em vez de na execução de consulta.

    Para obter informações adicionais sobre a solução de problemas de recompilações de procedimento armazenado, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
    243586 INF: Solucionando problemas de recompilação de procedimento armazenado


    Attention

    Um sinal de atenção indica que uma consulta foi cancelada pelo cliente. Isso ocorre geralmente devido a uma das duas causas:

    O usuário cancelou explicitamente a consulta ou encerrou o aplicativo.

    -ou-

    O tempo limite de uma consulta foi excedido.

    Se você visualizar sinais de atenção, isso poderá indicar que determinadas consultas estão sendo executadas lentamente.

    Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
    243589 Como solucionar problemas de execução lenta de consultas no SQL Server 7.0 ou posterior
    Para ajudar a identificar a consulta que recebeu o sinal de atenção, revise o rastreamento de modo que não esteja agrupado por nenhuma coluna de dados e filtre de acordo com o SPID que o recebeu (na guia Filters, defina SPID = x). O evento SQL:StmtStarting, SQL:BatchStarting ou SP:StmtStarting imediatamente anterior ao sinal de atenção é a consulta que recebeu o tempo limite ou foi cancelada. É possível pesquisar a coluna Event Class para o evento de Atenção a fim de facilmente localizá-lo (no menu Edit, clique em Find).

    PREPARE SQL e EXEC PREPARED SQL

    O evento Prepare SQL indica que um aplicativo ODBC, OLE DB ou uma biblioteca do banco de dados preparou uma instrução ou instruções Transact-SQL para uso. O evento Exec Prepared SQL indica que o aplicativo usou uma instrução preparada existente para executar um comando.

    Compare o número de vezes que esses dois eventos ocorreram. O ideal é que um aplicativo prepare uma instrução SQL uma vez e a execute várias vezes. Para o Optimizer, isso economiza o custo de compilar um novo plano cada vez que a instrução é executada. Por isso, o número de eventos Exec Prepared SQL deve ser maior que o número de eventos Prepare SQL. Se o número de eventos Prepare SQL for aproximadamente equivalente ao número de eventos Exec Prepared SQL, isso poderá indicar que o aplicativo não está fazendo bom uso do modelo preparar/executar. É melhor não preparar uma instrução que irá ser executada uma única vez. Para obter mais informações sobre a preparação de instruções SQL, consulte o tópico "Preparing SQL Statements" nos Books online do SQL Server.

    Se o número de eventos Exec Prepared SQL não for de três a cinco vezes maior que o número de eventos Prepare SQL, pode ser que o aplicativo não esteja fazendo uso eficiente do modelo preparar/executar. Para obter mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
    243588 Como solucionar problemas de desempenho de consultas ad-hoc

    No SQL Server 2000, as excessivas viagens de ida e volta por preparar/executar serão eliminadas, de modo que a razão 3-5 não seja tão limitada. No entanto, ainda pode ser uma boa regra tentar e reutilizar o plano preparado mais de uma vez.

    Missing Column Statistics

    Esse evento indica que as informações estatísticas que o Optimizer poderia ter usado para gerar um melhor plano de consulta não estavam disponíveis. Isso indica que a consulta não tem índices úteis em pelo menos uma tabela envolvida. Além de não ter um índice útil, o SQL Server não tem dados estatísticos sobre as colunas envolvidas para tomar uma decisão informada para um plano de consulta. O resultado é que o plano de consulta gerado pode não ser o melhor. Se você visualizar esses eventos, veja o plano de consulta e o plano de execução gerados e leia o seguinte artigo na Base de Dados de Conhecimento Microsoft para obter as etapas para melhorar o desempenho dessa consulta:
    243589 Como solucionar problemas de execução lenta de consultas no SQL Server 7.0 ou posterior

    Ao visualizar os eventos Missing Column Statistics, concentre-se primeiro naqueles que ocorrem em associação às consultas demoradas. Alguns eventos podem ser gerados e resolvidos automaticamente pelo SQL Server com as estatísticas automáticas e podem não solicitar a intervenção do usuário. Por isso, a melhor estratégia é concentrar-se primeiro nas consultas demoradas, como exibido mais adiante nesse artigo, e notar se há eventos Missing Column Statistics associados.

    Se você não estiver visualizando as instâncias dessas classes de evento, a próxima etapa será determinar onde o tempo está sendo gasto.

    Agrupar a saída de rastreamento por duração (Group the trace output by Duration):

    a. No menu File, clique em Properties.

    b. Na guia Data Columns, use o botão PARA CIMA para mover a Duration no cabeçalho Groups e o botão PARA BAIXO para remover todas as outras colunas no cabeçalho Groups.

    c. Na guia Events, remova todos os grupos, com exceção do TSQL e dos Stored Procedures.

    d. Clique em OK.

    Ao agrupar por duração, você pode facilmente ver quais instruções SQL, lotes ou procedimentos estão sendo executados mais lentamente. É muito importante verificar não apenas o momento no qual o problema está ocorrendo, mas também obter uma linha de base de quando o desempenho é bom para fazer a comparação. É possível filtrar de acordo com a hora de início para separar o rastreamento em seções onde o desempenho foi bom e uma seção separada para quando o desempenho foi fraco. Procure as consultas com a duração mais demorada em que o desempenho foi bom. Elas podem ser a raiz do problema. Se o desempenho geral do sistema foi reduzido, mesmo as consultas boas poderão mostrar longas durações enquanto estiverem aguardando nos recursos do sistema.

    Se você visualizar um pequeno número de consultas demoradas, consulte o seguinte artigo na Base de Dados de Conhecimento Microsoft:
    243589 Como solucionar problemas de execução lenta de consultas no SQL Server 7.0 ou posterior
    Se você perceber que a duração de consultas individuais está baixa, mas há várias delas, e o contador de SQL Compilations/sec na saída do Monitor de Desempenho (descrita mais adiante) é alto, consulte o seguinte artigo da Base de Dados de Conhecimento Microsoft:
    243588 Como solucionar problemas de desempenho de consultas ad-hoc
    Examine as colunas de dados restantes:

    Um esclarecimento adicional sobre a natureza de um problema de desempenho pode ser obtido ao visualizar as outras colunas de dados nos dados de rastreamento. Esses são alguns pontos a considerar:

    Se o uso da CPU for alto, agrupe por CPU para ver quais consultas são os maiores usuários de tempo da CPU. Pesquise a coluna Text para "hash" ou "mesclagem" para encontrar qual plano de execução de consulta está usando os tipos de junção. Eles demandam mais CPU e memória do que uma junção de loops aninhados, que geralmente demanda E/S.

    Se o E/S de disco for o afunilamento, agrupe por leituras e gravações. Exiba os campos Application Name, NT User Name e SQL User Name para ajudar a isolar a origem de uma consulta demorada.

    A coluna de dados inteiros da exceção do evento indicará quaisquer erros que foram retornados para o cliente. É possível encontrar o texto da mensagem de erro procurando o número nos Books online do SQL Server 7.0.

    O campo ID de conexão é útil para verificar se você está procurando nas mesmas sessões para um cliente específico. Um SPID não pode garantir isso, enquanto que um usuário pode ter desconectado e um novo usuário conectado e recebido o mesmo SPID.

    O benefício derivado desses campos pode variar dependendo do cenário, mas eles deverão ser examinados se os campos óbvios mencionados anteriormente nesse artigo não fornecerem uma resposta.
  3. Examine a saída do Monitor de Desempenho.

    O Monitor de Desempenho exibirá os afunilamentos gerais do sistema. Pode ser que o SQL Server e o aplicativo estejam sendo executados conforme o esperado, mas o computador está sem energia suficiente, com falta de memória ou de outros recursos. Ou determinados contadores podem indicar problemas na forma em que o aplicativo e o SQL Server estão sendo executados. No mínimo, verifique os seguintes contadores:

  • Objeto: Processo

    Contador: Processador

    Instância: SQL Server

  • Objeto: Processador

    Contador: %Tempo do processador

    Instância: Verifique cada instância do processador

  • Objeto: Physical Disk

    Contador: Avg. Disk Queue Length

    Instância: Verifique cada instância de disco físico

  • Objeto: SQL Server:SQL Statistics

    Contador: SQL Compilations/sec
Procure uma tendência durante o período no qual o desempenho mudou de bom para ruim: o que aumentou primeiro? O computador é vinculado à CPU ou ao E/S de disco? Essa informação, juntamente com a do Profiler mencionada anteriormente nesse artigo, ajudarão você a solucionar as áreas problemáticas. Os problemas de CPU podem indicar grande número de recompilações de procedimento armazenado, compilações de consulta ad-hoc ou uso intensivo de junções hash ou de mesclagem. Os artigos mencionados anteriormente neste artigo devem ser seguidos para determinar o curso correto da ação. Os altos comprimentos da fila de disco podem indicar a necessidade de mais memória do sistema ou um subsistema de disco aprimorado.

Propriedades

ID do artigo: 224587 - Última revisão: sábado, 29 de dezembro de 2007 - Revisão: 4.1
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 7.0 Standard Edition
Palavras-chave: 
kbhowto kbhowtomaster kbinfo kbproductlink KB224587

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com