Início / dba / Perguntas / 8885
Accepted
Ciaran Archer
Asked: 2011-12-08 07:25:14 +0800 CST

Plano de execução repentinamente lento para procedimentos armazenados

  • 772

Estou tentando entender um problema que estamos tendo com o SQL Server 2000. Somos um site moderadamente transacional e temos um procedimento armazenado chamado sp_GetCurrentTransactionsque aceita um customerID e duas datas.

Agora, dependendo das datas e do cliente, essa consulta pode retornar de zero a milhares de linhas.

O problema: o que experimentamos é que, de repente, obteremos vários erros (normalmente Execution Timeout Expiredou semelhantes) para um cliente específico enquanto ele tenta executar esse procedimento armazenado. Então, examinamos a consulta, executamos no SSMS e descobrimos que leva 30s. Então nós recompilamos o proc armazenado e -bang- ele roda agora em 300ms.

Falei com nosso DBA sobre isso. Ele me disse que o banco de dados criou um plano de consulta quando criamos o procedimento armazenado. Ele disse que era um bom plano para aquele conjunto de parâmetros, mas se você lançar um determinado conjunto de parâmetros nele, o plano não será o melhor plano para esses dados e, portanto, você o verá lento.

As opções apresentadas a mim são a movimentação dessa consulta de problema de um procedimento armazenado e de volta para o SQL dinâmico que tem seu plano de execução criado em cada execução.

Isso parece um retrocesso para mim e sinto que deve haver uma maneira de contornar isso. Existe alguma outra maneira de lidar com esse problema?

Todas e quaisquer respostas são apreciadas.

sql-server stored-procedures

6 respostas

  1. Best Answer

    Esse problema é chamado de detecção de parâmetro.

    Versões posteriores do SQL Server oferecem mais opções para lidar com isso, como OPTION (RECOMPILE)ou OPTIMIZE FORdicas.

    Você pode tentar declarar variáveis ​​no procedimento armazenado, atribuindo os valores de parâmetro às variáveis ​​e usando as variáveis ​​no lugar dos parâmetros, pois parece que na maioria das vezes você está obtendo um plano razoavelmente satisfatório.

    Normalmente, os planos mais catastroficamente ruins são aqueles compilados para parâmetros com seletividade muito alta, mas executados com parâmetros com seletividade baixa.

    Assumindo que o plano gerado é mais robusto com essa abordagem e satisfatório para todos os valores de parâmetros, a vantagem dessa abordagem sobre a sugerida pelo JNK é que ela não incorre em um custo de compilação para cada chamada.

    A desvantagem é que, para algumas execuções, o tempo de execução pode ser maior do que para um plano adaptado especificamente para esses valores de parâmetro, portanto, é uma compensação entre o tempo de compilação e o tempo de execução.

    • 14

  2. Em vez de usar SQL dinâmico, você sempre pode alterar suas chamadas de proc para:

    EXEC Database.dbo.usp_Myprocedure 'Parameter' WITH RECOMPILE

    As WITH RECOMPILEforças (você adivinhou!) Uma recompilação do plano de execução sempre que ele é executado.

    Você também pode incluir WITH RECOMPILEna definição do procedimento armazenado:

    CREATE PROCEDURE usp.MyProcedure (Parameters)
WITH RECOMPILE
AS
...
    • 6

  3. Você também pode tentar decidir pelo banco de dados qual plano usar, embora esteja lutando um pouco com o otimizador, por isso é mais frágil do que você esperaria.

    A técnica é esta - dividir o procedimento armazenado em 2, um destinado a um conjunto de parâmetros, um para o outro. Adicione cláusulas where a cada um de modo que entre eles cubram todos os casos possíveis. Observe os planos de consulta - um deve ser otimizado para um conjunto de parâmetros e o outro para o outro conjunto. Você pode ter que mexer na consulta para que isso aconteça, ou isso pode não ser possível para sua consulta, caso em que essa abordagem não funcionará.

    Agora, faça com que seu procedimento armazenado original verifique os valores dos parâmetros e envie para o apropriado dos dois procedimentos armazenados do parágrafo anterior.

    Isso pode funcionar, mas é uma espécie de hack para forçar o otimizador a trabalhar de forma mais eficaz para sua consulta. Como todos esses hacks, em versões futuras do banco de dados pode ser desnecessário ou até piorar as coisas. Portanto, mesmo que funcione, você deve decidir se vale a pena.

    • 2

  4. Você também pode tentar SET FORCEPLANindexar dicas.

    http://msdn.microsoft.com/en-us/library/ms188344.aspx
    Basicamente, permite que você escolha em que ordem a junção acontecerá.

    Você pode ter dicas de índice para garantir que o servidor SQL use os índices corretos.

    • 1

  5. Hmmm... se estivermos focados apenas neste procedimento armazenado, ficaria surpreso ao saber que o uso do plano de execução em cache estaria causando o problema que você está vendo. Eu pediria para ver o plano de execução do procedimento armazenado usando um conjunto de parâmetros para o cliente e as duas datas. Gostaria de saber se um índice mais específico seria útil -> como em customerId e apenas as duas datas?

    • 0

  6. A degradação repentina do desempenho soa como um plano de consulta ineficiente que é produzido, provavelmente como resultado de estatísticas ausentes. Execute um criador de perfil do SQL Server com as categorias de eventos "Erros e avisos" definidas e veja se há avisos sobre estatísticas ausentes.

    Você também pode estar perdendo um índice ou pode precisar desfragmentar os índices, pois eles podem estar muito fragmentados para o SQL Server usar, fazendo com que pense que uma varredura de tabela produzirá menos E/S.

    @JNK levanta um grande ponto sobre procedimentos armazenados - eles são compilados antecipadamente e o plano de consulta será armazenado junto com o procedimento armazenado.

    Não concordo necessariamente com o uso de WITH RECOMPILE , pois você perde o benefício do plano de consulta sendo armazenado e reutilizado. Existem alguns casos em que isso é necessário - ou seja, se suas estatísticas de distribuição nas tabelas subjacentes diferem muito entre as chamadas, mas geralmente, uma vez que os dados nas tabelas estejam maduros, a distribuição dos dados dentro das tabelas variará minimamente.

    Então, para resumir:

    1. Verifique se há estatísticas ausentes
    2. Verifique a fragmentação do índice
    3. Criar e usar um procedimento armazenado
    4. Renomeie o proc - sp_ é um namespace de prefixo reservado suavemente para procs do SQL Server do sistema interno - resultando no SQL Server sempre procurando no banco de dados mestre esses procedimentos armazenados primeiro. Renomear o proc usp_ em vez de sp_ resultará em um aumento de desempenho, mas duvido que seja seu problema neste caso.
    • 0

relate perguntas