Como solucionar problemas de consultas de execução lenta no SQL Server 7.0 ou em versões posteriores

Este artigo descreve como manipular um problema de desempenho que aplicativos podem enfrentar em conjunto com o Microsoft SQL Server: diminuir o desempenho de uma consulta específica ou grupo de consultas. Se você estiver solucionando um problema de desempenho, mas você não tenha isolado o problema para uma consulta específica ou um grupo pequeno de consultas que executam mais lentamente do que o esperado, consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft antes de continuar: Este artigo se baseia no pressuposto de que você ter usado o artigo 224587 para restringir o escopo do problema e que você tenha capturado um rastreamento SQL Profiler com as colunas de eventos e dados específicos que são detalhados no artigo 224587.

Ajuste de consultas de banco de dados pode ser um esforço multifacetado. As seções a seguintes abordam itens comuns para examinar quando estiver investigando o desempenho da consulta.

Observação Se você estiver usando o SQL Server 2005, use o SQL Server Management Studio em vez de SQL Query Analyzer e use Orientador de otimização mecanismo de banco de dados em vez do Assistente para ajuste de índice.

Verificar a existência de índices corretas

Um dos primeiro verifica para executar quando você está enfrentando tempos de execução lenta consulta é uma análise de índice. Se você estiver investigando uma única consulta, você pode usar a opção Executar análise de índice no SQL Query Analyzer; se você tiver um rastreamento SQL Profiler de uma grande carga de trabalho, você pode usar o Assistente para ajuste de índice. Ambos os métodos usam o otimizador do SQL Server para determinar quais índices pode ser útil para as consultas especificadas. Esse é um método muito eficiente para determinar se existem índices corretos em seu banco de dados.

Para obter informações sobre como usar o Assistente para ajuste de índice, consulte o tópico "Assistente de ajuste de índice" nos manuais online do SQL Server 7.0.

Se você tiver atualizado seu aplicativo de uma versão anterior do SQL Server, índices diferentes podem ser mais eficientes no SQL Server 7.0 devido otimizador e alterações de mecanismo de armazenamento. O Assistente para ajuste de índice ajuda você a determinar se uma alteração na estratégia de indexação pode melhorar o desempenho.

Para obter mais informações sobre como usar Orientador de otimização mecanismo de banco de dados em vez do Assistente de ajuste de índice no SQL Server 2005, consulte os seguintes tópicos nos manuais online do SQL Server 2005:

• Diferenças entre o banco de dados mecanismo ajuste Supervisor e Assistente para ajuste de índice
• Tutorial do Orientador de otimização mecanismo de banco de dados

Remover todas as consultas, tabela e dicas de associação

Dicas substituem a otimização da consulta e podem impedir que o otimizador de consultas escolhendo o plano de execução mais rápido. Devido às alterações otimizador, dicas que melhor desempenho em versões anteriores do SQL Server pode ter efeito ou realmente negativamente pode afetam o desempenho no SQL Server 7.0. Além disso, as dicas de associação podem causar degradação do desempenho com base nas seguintes razões:

• Dicas de associação impede que uma consulta ad hoc sejam qualificados para parametrização automática e cache do plano de consulta.
• Quando você usa uma dica de associação, isso significa que você deseja forçar a ordem de associação para todas as tabelas a consulta, mesmo se as associações não usam explicitamente uma dica.

Se a consulta que você estiver analisando inclui qualquer dicas, removê-los e, em seguida, reavaliar o desempenho.

Examinar o plano de execução

Após você confirmar que os índices corretos existam e que não dicas são restringir capacidade do otimizador de gerar um plano eficiente, você pode examinar o plano de execução de consulta. Você pode usar qualquer um dos seguintes métodos para exibir o plano de execução para uma consulta:

• SQL Profiler
  
  Capturar o evento MISC:Execution plano no SQL Profiler, ocorrerá imediatamente antes do evento StmtCompleted para a consulta para o processo de sistema específico ID (SPID).
• SQL Query Analyzer: gráfico Showplan
  
  Com a consulta selecionada na janela consulta, clique no menu consulta e, em seguida, clique em Exibir execução estimado planejar .
  
  Observação : se o procedimento armazenado ou em lotes cria e faz referência a tabelas temporárias, você deve usar uma instrução SET STATISTICS PROFILE ON ou criar tabelas temporárias explicitamente antes de exibir o plano de execução.
• SHOWPLAN_ALL e SHOWPLAN_TEXT
  
  Para receber uma versão de texto do plano de execução estimado, você pode usar as opções SET SHOWPLAN_ALL e SET SHOWPLAN_TEXT. Consulte o "SET SHOWPLAN_ALL (T-SQL)" e "SET SHOWPLAN_TEXT (T-SQL)" tópicos nos manuais online do SQL Server 7.0 para obter mais detalhes.
  
  Observação : se o procedimento armazenado ou em lotes cria e faz referência a tabelas temporárias, você deve usar a opção SET STATISTICS PROFILE ON ou criar tabelas temporárias explicitamente antes de exibir o plano de execução.
• PROFILE STATISTICS
  
  Quando você está exibindo o plano de execução estimado, graficamente ou usando o SHOWPLAN, a consulta não é realmente executada. Portanto, se você criar tabelas temporárias em um lote ou um procedimento armazenado, você não pode exibir os planos de execução estimado porque as tabelas temporárias não existirá. STATISTICS PROFILE executa a consulta primeiro e, em seguida, exibe o plano de execução real. Consulte o tópico "SET STATISTICS PROFILE (T-SQL)" nos manuais online do SQL Server 7.0 para obter mais detalhes. Quando ele está sendo executado no SQL Query Analyzer, é exibida no formato do gráfica na guia Plano de execução no painel de resultados.

Para obter mais informações sobre como exibir o plano de execução estimado no SQL Server 2005, consulte o tópico "Como exibir o plano de execução estimado" nos manuais online do SQL Server 2005.

Examinar a saída do Showplan

Saída do Showplan fornece muitas informações sobre o plano de execução que o SQL Server está usando para uma consulta específica. Os detalhes de informações e eventos que são gerados são discutidos detalhadamente no capítulo "Otimizando o desempenho de banco de dados" SQL Server 7.0 Books Online. A seguir estão alguns aspectos básicos do plano de execução que você pode exibir para determinar se você estiver usando o melhor plano:

• Corrigir uso do índice
  
  A saída do showplan exibe cada tabela que está envolvida na consulta e o caminho de acesso que é usado para obter dados dele. Com showplan gráfica, mova o ponteiro sobre uma tabela para ver os detalhes para cada tabela. Se um índice está em uso, você verá "Índice atingir"; se um índice não estiver em uso, você verá uma "Tabela verificar" para uma pilha ou "Clustered Index Scan" para uma tabela que tem um índice de cluster. "Verificação de índice em cluster" indica que a tabela está sendo examinada pelo índice de cluster, não que o índice de cluster está sendo usado para diretamente acesso linhas individuais.
  
  Se você determinar que existe um índice útil e não está sendo usado para a consulta, você pode tentar forçar o índice usando uma dica de índice. Consulte o tópico "FROM (T-SQL)" nos manuais online do SQL Server para obter mais detalhes sobre dicas de índice.
• Ordem de associação correta
  
  A saída do showplan indica em que ordem tabelas envolvidas em uma consulta que está sendo associadas. Para associações de loop aninhadas, a tabela superior que está listada é a tabela externa e deve ser a menor das duas tabelas. Para junções de hash, a tabela superior torna-se a entrada de compilação e também deve ser a menor das duas tabelas. No entanto, observe que a ordem é menos importante porque o processador de consultas pode reverter a compilação e investigar entradas em tempo de execução se ele encontrar que o otimizador tomou uma decisão errada. Você pode determinar qual tabela retorna menos linhas verificando que estima que a contagem de linha na saída showplan.
  
  Se você determinar que a consulta pode se beneficiar de uma ordem de associação diferentes, você pode tentar forçar a ordem de associação com uma dica de associação. Consulte o tópico "FROM (T-SQL)" nos manuais online do SQL Server 7.0 para obter mais detalhes sobre dicas de associação.
  
  Observação : usar uma dica de associação em uma consulta grande implicitamente força a ordem de associação para a outras tabelas na consulta como se FORCEPLAN foi definida.
• Tipo de associação correta
  
  SQL Server usa aninhada loop, hash e associações de mesclagem. Se uma consulta de desempenho lento é usando uma técnica de associação por meio de outro, você pode tentar forçar um tipo de associação diferente. Por exemplo, se uma consulta estiver usando uma associação de hash, você pode forçar uma associação de loops aninhados usando a dica de associação do LOOP. Consulte o tópico "FROM (T-SQL)" nos manuais online do SQL Server 7.0 para obter mais detalhes sobre dicas de associação.
  
  Observação : usar uma dica de associação em uma consulta grande implicitamente força o tipo de associação para a outras tabelas na consulta como se FORCEPLAN foi definida.
• Execução paralela
  
  Se você estiver usando um computador com multiprocessador, você pode investigar se um plano paralelo está em uso. Se o paralelismo está em uso, você verá um evento de PARALELISMO (reunir fluxos). Se uma determinada consulta estiver lenta quando ele está usando um plano paralelo, você pode tentar forçar um plano paralelo não usando a dica OPTION (MAXDOP 1). Consulte o tópico "SELECT (T-SQL)" nos manuais online do SQL Server 7.0 para obter mais detalhes.

Para obter mais informações sobre como usar saída de plano de execução do Showplan no SQL Server 2005, consulte os seguintes tópicos nos manuais online do SQL Server 2005:

• Como salvar um plano de execução no formato XML
• XML Showplans
• Segurança do Showplan

cuidado : como o otimizador de consulta normalmente seleciona o melhor plano de execução para uma consulta, a Microsoft recomenda que você use dicas de associação, dicas de consulta e dicas de tabela somente como último recurso, e somente se você um administradores experientes do banco de dados.

Os tópicos a seguir nos manuais online do SQL Server 7.0 fornecem informações sobre otimização de consultas:

• "Otimizando o desempenho do aplicativo usando a recuperação de dados eficiente"
• "Ajuste de consulta"
• "Consulta recomendações ajuste"
• "Dicas de transact-SQL"