Recomendações de ajuste de consulta

Algumas consultas consomem mais recursos do que outras. Por exemplo, consultas que retornam grandes conjuntos de resultados e as que contêm cláusulas WHERE não exclusivas sempre consomem recursos de forma intensiva. Nenhum grau de inteligência de otimizador de consulta pode eliminar o custo de recursos dessas construções quando comparado a uma consulta menos complexa. O SQL Server usa o plano de acesso ideal, mas a otimização da consulta limita-se ao que é possível.

No entanto, para aprimorar o desempenho de consulta, você pode:

• Adicionar mais memória. Essa solução poderá ser especialmente útil se o servidor executar muitas consultas complexas e várias das consultas tiverem execução lenta.

• Usar mais de um processador. Vários processadores permitem que o Mecanismo de Banco de Dados use consultas paralelas. Para obter mais informações, consulte Processamento paralelo de consultas.

• Regravar a consulta. Considere os seguintes problemas:

  • Se a consulta usar cursores, determine se a consulta de cursor pode ser gravada com um tipo mais eficiente de cursor (como cursor somente de avanço rápido) ou uma única consulta. Consultas únicas geralmente superam em desempenho as operações de cursor. Como um conjunto de instruções de cursor é geralmente uma operação de loop externo, na qual cada linha de loop externo é processada uma vez usando uma instrução interna, considere usar uma instrução GROUP BY ou CASE ou uma subconsulta. Para obter mais informações, consulte Tipos de cursor (Mecanismo de Banco de Dados) e Conceitos básicos sobre consultas.

  • Se um aplicativo usar um loop, considere colocar o loop dentro da consulta. Freqüentemente um aplicativo contém um loop que contém uma consulta com parâmetros que é executada muitas vezes e requer uma viagem de ida e volta da rede entre o computador que executa o aplicativo e o SQL Server. Em vez disso, crie uma consulta única, mais complexa, usando uma tabela temporária. Apenas uma viagem de ida e volta da rede é necessária e o otimizador de consulta poderá otimizar melhor a consulta única. Para obter mais informações, consulte Transact-SQL de procedimento e Variáveis Transact-SQL.

  • Não use diversos aliases para uma única tabela na mesma consulta para simular interseção de índice. Isso não é mais necessário porque o SQL Server considera a interseção de índice automaticamente e pode utilizar vários índices na mesma tabela e na mesma consulta. Considere a consulta de exemplo:

    SELECT * FROM lineitem 
    WHERE partkey BETWEEN 17000 AND 17100 AND
        shipdate BETWEEN '1/1/1994' AND '1/31/1994'
    

    O SQL Server pode explorar índices nas colunas partkey e shipdate e executar uma correspondência hash entre os dois subconjuntos para obter a interseção do índice.

  • Use a parametrização de consulta para permitir a reutilização de planos de execução de consulta armazenados em cache. Se um conjunto de consultas tiver os mesmos hashes de consulta e de plano de consulta, você poderá melhorar o desempenho criando uma consulta parametrizada. Chamar uma consulta com parâmetros em vez de várias consultas com valores literais permite a reutilização do plano de execução de consulta armazenado em cache. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta e Reutilização e armazenamento em cache do plano de execução.

    Se não for possível modificar o aplicativo, você poderá usar guias de plano de modelo com parametrização forçada para obter um resultado semelhante. Para obter mais informações, consulte Especificando comportamento de parametrização de consulta usando guias de plano.

  • Só utilize dicas de consulta se necessário. As consultas que usam dicas executadas em versões anteriores do SQL Server devem ser testadas sem as dicas especificadas. As dicas podem impedir o otimizador de consulta de escolher um plano de execução melhor. Para obter mais informações, consulte SELECT (Transact-SQL).

• Use query_plan_hash para capturar, armazenar e comparar os planos de execução de consultas ao longo do tempo. Por exemplo, depois de alterar a configuração do sistema, é possível comparar os valores de hash de plano de consulta relativos a consultas essenciais com os valores de hash de plano de consulta originais. As diferenças nos valores de hash de plano de consulta podem indicar se a alteração na configuração do sistema resultou em planos de execução de consulta atualizados para consultas importantes. Você também poderá optar por interromper a execução de uma consulta atual que está sendo executada há bastante tempo caso o hash do respectivo plano de consulta em sys.dm_exec_requests seja diferente do hash do plano de consulta de linha de base, conhecido por ter bom desempenho. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta.

• Use a opção de configuração query governor. A opção de configuração query governor pode ser usada para evitar que os recursos do sistema sejam consumidos por consultas longas. Por padrão, a opção é definida para permitir a execução de todas as consultas, independentemente do tempo. No entanto, você pode definir o administrador de consultas para limitar o número máximo de segundos permitido para execução de todas as consultas em todas as conexões, ou apenas as consultas de uma conexão específica. Como o administrador de consultas tem base no custo estimado da consulta, em vez de no tempo real decorrido, não tem sobrecarga de tempo de execução. Ele também interrompe consultas longas antes de serem iniciadas, em vez de executá-las até que algum limite predefinido seja alcançado. Para obter mais informações, consulte Opção query governor cost limit e SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

• Otimize a reutilização de planos de consulta a partir do cache do plano. O Mecanismo de Banco de Dados armazena em cache planos de consulta para possível reutilização. Se um plano de consulta não estiver armazenado em cache, nunca poderá ser reutilizado. Ao contrário, planos de consulta não armazenados em cache devem ser compilados cada vez que são executados, o que resulta em desempenho insatisfatório. As opções da instrução SET do Transact-SQL a seguir evitam que planos de consulta armazenados em cache sejam reutilizados. Um lote do Transact-SQL que contém essas opções SET ativadas não pode compartilhar seus planos de consulta com o mesmo lote compilado com as opções SET desativadas:

  SET ANSI_NULL_DFLT_OFF	SET ANSI_NULL_DFLT_ON
  SET ANSI_NULLS	SET ANSI_PADDING
  SET ANSI_WARNINGS	SET ARITHABORT
  SET CONCAT_NULL_YIELDS_NULL	SET DATEFIRST
  SET DATEFORMAT	SET FORCEPLAN
  SET LANGUAGE	SET NO_BROWSETABLE
  SET NUMERIC_ROUNDABORT	SET QUOTED_IDENTIFIER
  SET TEXTSIZE

  Além disso, a opção SET ANSI_DEFAULTS afeta a reutilização de planos de consulta armazenados em cache pois pode ser usada para alterar as opções SET ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS e QUOTED_IDENTIFIER. Observe que a maioria das opções SET que podem ser alteradas com SET ANSI_DEFAULTS são listadas como opções SET que podem afetar a reutilização de planos de consulta.

  Você pode alterar algumas dessas opções SET com os seguintes métodos:

  • Use o procedimento armazenado sp_configure para alterações em todo o servidor. Para obter mais informações, consulte sp_configure (Transact-SQL).

  • Use a cláusula SET da instrução ALTER DATABASE. Para obter mais informações, consulte ALTER DATABASE (Transact-SQL)

  • Altere as configurações de conexão OLE DB e ODBC. Para obter mais informações, consulte Configuração de rede de cliente.

Observação
Para evitar recompilações de planos de consulta causadas por opções SET, defina as opções SET no momento da conexão e verifique se elas não se alteram durante a conexão. Algumas opções SET devem ser definidas como valores específicos para usar exibições indexadas ou índices em colunas computadas. Para obter mais informações, consulte Opções SET que afetam os resultados.

Consulte também

Referência

CASE (Transact-SQL)

Conceitos

Noções básicas sobre subconsultas