Solucionando problemas de recompilação de procedimento armazenado

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

Neste artigo

Sumário

Este artigo aborda um tipo específico de problema de desempenho que os aplicativos podem enfrentar com o Microsoft SQL Server: a recompilação de tempo de execução de procedimentos armazenados. Se você estiver solucionando um problema de desempenho, mas não determinou se essa é a fonte precisa do seu problema, consulte o seguinte artigo na Base de Dados de Conhecimento Microsoft:

224587 Como solucionar problemas de desempenho de aplicativo com o SQL Server
Este artigo presume que você usou aquele artigo para solucionar o escopo do problema e que você capturou um rastreamento do SQL Server Profiler com os eventos e as colunas de dados específicos detalhados nele.

Mais Informações

Quando um usuário executa um procedimento armazenado, se ele já não estiver disponível no cache, o SQL Server carrega o procedimento e compila um plano de consulta. O plano compilado é salvo no cache e é reutilizado por chamadores subseqüentes do procedimento armazenado até que alguma ação ocorra para invalidar o plano e forçar a recompilação. As seguintes ações podem causar a recompilação de um plano de procedimento armazenado:
  • O uso de uma cláusula WITH RECOMPILE na instrução CREATE PROCEDURE ou EXECUTE.
  • O esquema é alterado por qualquer um dos objetos referenciados, inclusive ao adicionar ou ignorar restrições, padrões ou regras.
  • Executar sp_recompile para uma tabela referenciada pelo procedimento.
  • Restaurar o banco de dados que contém o procedimento ou qualquer um dos objetos referenciados pelo procedimento (se você estiver executando operações de banco de dados cruzado).
  • Atividade do servidor suficiente para fazer com que o plano fique sem espaço no cache
Todas essas razões para recompilar um procedimento armazenado existiram em versões anteriores e faziam com que o plano fosse recompilado antes do início da execução do procedimento. No SQL Server 7.0, um novo comportamento é introduzido para recompilar o procedimento armazenado durante a execução. Esse novo comportamento garante que o otimizador sempre terá o melhor plano possível para cada instrução específica em um procedimento. Os seguintes eventos podem causar uma recompilação de tempo de execução de um procedimento armazenado:
  • Uma porcentagem suficiente de dados é alterada em uma tabela referenciada pelo procedimento armazenado.
  • O procedimento intercala as operações DDL (Linguagem de definição de dados) e DML (Linguagem de manipulação de dados).
  • O procedimento executa determinadas operações em tabelas temporárias.
Cada uma dessas causas é discutida em mais detalhes neste artigo.

Em alguns casos, o custo para recompilar o procedimento armazenado é maior que o benefício, especialmente para grandes procedimentos. É importante observar que quando uma recompilação é acionada, todo o lote ou procedimento é recompilado. Isso significa que a degradação do desempenho é diretamente proporcional ao tamanho do procedimento ou lote. Para obter mais informações sobre esse tópico, consulte o tópico "Dicas do Transact-SQL" nos Books online do SQL Server.


A seguinte informação neste artigo concentra-se na identificação da causa das recompilações de tempo de execução do procedimento armazenado e aborda métodos que podem ser usados para preveni-las.

Melhor prática

É melhor se qualificar como proprietário dos nomes do procedimento armazenado ao executar um procedimento. Isso permite uma maior clareza e facilita a reutilização do plano de execução existente pelo usuário atual. Por exemplo, se um usuário que não é o proprietário do banco de dados (dbo) executa um procedimento armazenado de propriedade de um dbo (chamado myProc neste exemplo) no banco de dados pubs, use a seguinte instrução:
exec dbo.myProc
				
Em vez desta:
exec myProc
				
Essa técnica elimina a confusão sobre outras possíveis versões do procedimento por diferentes proprietários de um ponto de vista de manutenção e codificação, e também permite que o SQL Server acesse o plano de execução para o procedimento específico mais diretamente.

Ao não qualificar o nome do proprietário, o SQL Server digita o código de compilação e adquire um bloqueio de COMPILE no procedimento. Entretanto, eventualmente ele determina que um novo plano não é solicitado (presumindo que nenhuma outra razão se aplica), então ele NÃO recompila o plano devido à falta de qualificação. Porém, a etapa extra para obter um bloqueio COMPILE no procedimento pode bloquear a contenção em situações graves. Consulte Q263889 INF: Bloqueio de SQL devido aos bloqueios [[COMPILE]], para obter mais detalhes sobre esta situação.

Se você se qualificar como proprietário da chamada de procedimento com proprietário.procedimento, não será preciso adquirir o bloqueio de compilação, de modo que a contenção será reduzida.

Identificando e resolvendo os problemas

Se você ainda não fez isso, consulte o seguinte artigo na Base de Dados de Conhecimento Microsoft para obter detalhes sobre a captura de dados do Profiler para ajudar na análise de desempenho do seu sistema:
224587 Como solucionar problemas de desempenho de aplicativo com o SQL Server

Exibir os dados do Profiler

O SQL Server Profiler inclui um evento SP:Recompile que pode ser usado para monitorar o número de recompilações que estão ocorrendo. O evento SP:Recompile ocorre sempre que um procedimento armazenado é recompilado durante a execução.
  • Agrupar o rastreamento do Profiler por classe de evento

    1. No menu File, clique em Properties.
    2. Na guia Data Columns, use o botão PARA CIMA para mover a Event Class e o Text no cabeçalho Groups, com Data Columns em primeiro. Use o botão PARA BAIXO para remover todas as outras colunas no cabeçalho Groups.
    3. Clique em OK.
    Verifique o número dos eventos SP:Recompile.

    É possível expandir o grupo SP:Recompile para ver os detalhes das ocorrências individuais. A coluna Text do evento indica o nome do procedimento armazenado que foi recompilado. Se vários procedimentos estiverem causando as recompilações, eles serão classificados pelo número de ocorrências. Se você tiver uma grande número de eventos SP:Recompile e estiver enfrentando um alto uso de CPU, concentre em resolver os procedimentos que têm mais números de recompilações. Observe o SPID e a hora de início do evento SP:Recompile para uma instância do(s) procedimento(s) armazenado(s) específico(s) e execute as etapas abaixo.

    Se você não visualizar nenhum evento SP:Recompile, mas ainda estiver enfrentando um problema de desempenho, consulte o seguinte artigo na Base de Dados de Conhecimento Microsoft:
    224587 Como solucionar problemas de desempenho de aplicativo com o SQL Server
  • Determinar a instrução que disparou o evento de recompilação

    1. No menu File, clique em Properties.
    2. Na guia Data Columns, use o botão PARA BAIXO para remover todas as outras colunas no cabeçalho Groups.
    3. Na guia Events, remova todos os eventos, com exceção de SP:Starting, SP:StmtStarting, SP:Recompile e SP:Completed. Se você não capturar o evento SP:StmtStarting, poderá substituir SP:StmtCompleted, mas não inclua ambos, pois isso duplicará a quantidade de informação que você precisa examinar.
    4. Se você identificou uma instância específica de uma recompilação de procedimento armazenado para ser examinada, será possível limitar os dados exibidos para o SPID e o período específicos da ocorrência usando a guia Filters.
    5. Clique em OK.

    O evento SP:Recompile aparecerá diretamente após o evento SP:StmtStarted da instrução de procedimento armazenado que causou a recompilação. Após a conclusão do evento de recompilação, você verá uma repetição do evento SP:StmtStarted, indicando que a instrução está sendo executada com o plano recém gerado.

    Considere o seguinte exemplo:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Se você executar esse código no Query Analyzer e exibir os eventos acima em um rastreamento do Profiler, você verá a seguinte seqüência:

    Recolher esta tabelaExpandir esta tabela
    Classe de eventoTexto
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    É possível informar imediatamente que a instrução que causou a recompilação foi:
    select * from #t
    						
    Porque ela aparece antes e depois do evento SP:Recompile.

    Se você capturou somente o evento SP:StmtCompleted, mas não o evento SP:StmtStarting, o SP:Recompile será exibido diretamente antes da instrução que o causou, como segue:

    Recolher esta tabelaExpandir esta tabela
    Classe de eventoTexto
    SP:StartingRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedselect * from #t
    SP:CompletedRecompProc


    Você pode ver que o evento SP:Recompile aparece antes do evento SP:StmtCompleted para a instrução "select * from #t", que causou a recompilação. Isso faz sentido, já que a instrução não pode ser concluída até que o novo plano de consulta seja gerado para a recompilação. Todos os outros exemplos nesse artigo usam o evento SP:StmtStarting. Se você capturou somente o evento SP:StmtCompleted, lembre-se apenas de exibir a instrução antes de SP:Recompile, como explicado acima.

    Observe que se você executar este procedimento armazenado específico diversas vezes, o SQL Server reutilizará o plano existente para esse procedimento. Você somente verá o evento de recompilação na primeira execução do procedimento, ou se você ignorar e recriar o procedimento toda vez que executar o script. A razão para a recompilação neste caso específico é discutida na seção "Recompilações causadas pela intercalação das operações DDL e DML" deste artigo; isto é apenas um exemplo para ilustrar como determinar facilmente qual instrução está causando a recompilação.

Recompilações causadas por modificações de linha

Se uma porcentagem suficiente de dados foi alterada na tabela por um procedimento armazenado desde a hora em que o plano de consulta original foi gerado, o SQL Server recompilará o procedimento armazenado para garantir um plano com base nos dados estatísticos mais atualizados. Por exemplo, considere o seguinte procedimento armazenado:
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
Para a segunda execução do procedimento RowModifications, você verá os seguintes eventos no Profiler:

Recolher esta tabelaExpandir esta tabela
Classe de eventoTexto
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

OBSERVAÇÃO: A primeira execução também mostrará um evento SP:Recompile para a instrução "select * from #t". A razão para recompilação nesse caso específico é discutida na seção "Recompilações causadas pela intercalação das operações DDL e DML" neste artigo. Para esse exemplo, concentre-se no SP:Recompile exibido acima, pois ele ocorre sempre que o procedimento é executado.

Nesse exemplo, "select count(*) from #t where a = 37" causa uma recompilação do procedimento devido à alteração no número de linhas desde o momento em que a tabela foi criada. A presença do evento Auto-UpdateStats confirma que a recompilação foi causada por modificações de linha. A coluna Text indica a coluna para a qual as estatísticas foram modificadas.

Quando a #t table foi criada, o número de linhas era zero. O plano para "select * from #t" original é desenvolvido com aquela contagem de linha, bem como o plano para a consulta "select count (*)". Entretanto, antes de executar "select count(*)", 1.000 novas linhas são inseridas na #t table. Como uma quantidade de dados suficiente foi modificada, o otimizador recompila o procedimento para garantir que ele escolha o plano mais eficiente para a instrução. Essa recompilação ocorrerá em cada execução do procedimento armazenado pois a inserção de 1.000 linhas será sempre exibida como significativa o suficiente para garantir a recompilação.

O algoritmo que o SQL Server usa para determinar se um plano deve ser recompilado é o mesmo usado para as estatísticas de atualização automática conforme descrito no seguinte artigo da Base de Dados de Conhecimento Microsoft:
195565 INF: Como o Autostats do SQL Server 7.0 e do SQL Server 2000 funcionam
No exemplo acima, o procedimento armazenado é tão pequeno que a recompilação não tem um efeito notável no desempenho. Entretanto, se você tiver uma procedimento armazenado grande que executa atividades semelhantes que resultam em várias recompilações, será possível perceber uma degradação de desempenho.

Os seguintes métodos existem para anular as recompilações causadas por modificações de linha:
  • Execute a instrução usando sp_executesql.
    Este é o método preferido. As instruções executadas usando o procedimento armazenado sp_executesql não são compiladas como parte do plano de procedimento armazenado. Portanto, ao executar a instrução, o SQL Server será liberado para usar um plano existente no cache para a instrução ou para criar um novo plano no momento da execução. Em ambos os casos, o plano para o chamado procedimento armazenado não é afetado e não precisa ser recompilado.

    A instrução EXECUTE terá o mesmo efeito, entretanto, não é recomendável. Usar a instrução EXECUTE não é tão eficiente quanto usar sp_executesql, pois ela não permite a parametrização da consulta.

    O procedimento RowModifications dado acima pode ser gravado para usar sp_executesql da seguinte forma:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    Para a segunda execução do procedimento RowModifications2, você verá os seguintes eventos no Profiler:

    Recolher esta tabelaExpandir esta tabela
    Classe de eventoTexto
    SP:StartingRowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    Observe que não há eventos SP:Recompile para o procedimento RowModifications2. Existe SP:Starting completo para eventos SP:Completed para o contexto de chamada sp_executesql e um eventoAuto-UpdateStats para a coluna a. Entretanto, como essa chamada está fora do contexto do procedimento armazenado, o procedimento RowModifications2 não precisa ser recompilado nesse caso.

    Para obter mais informações sobre o uso do procedimento armazenado sp_grantdbaccess, consulte os tópicos "sp_executesql (T-SQL)" e "Usando sp_executesql" nos Books online do SQL Server.
  • Use subprocedimentos para executar as instruções que estão causando as recompilações.
    Nesse caso, a instrução ainda pode causar uma recompilação, mas em vez de recompilar um grande procedimento armazenado de chamada, ela irá recompilar somente o pequeno subprocedimento.
  • Use a opção KEEP PLAN.
    As tabelas temporárias têm regras especiais referentes a recompilações que, em alguns casos, podem ser mais rigorosas do que o algoritmo de recompilação padrão. É possível usar a opção KEEP PLAN para reduzir o limite da tabela temporária ao algoritmo padrão. Para obter informações adicionais, consulte a seção "Evitando recompilação ao usar a opção KEEP PLAN" desse artigo.
OBSERVAÇÃO: O procedimento RowModifications é um exemplo muito simplificado de um procedimento recompilado devido às modificações de linha. Revise os seguintes avisos referentes a esse exemplo:

  • Embora o exemplo use uma tabela temporária, essa situação também se aplica aos procedimentos armazenados referentes às tabelas permanentes. Se uma quantidade suficiente de dados em uma tabela referenciada tiver sido alterada desde a geração do plano, o procedimento armazenado será recompilado. As diferenças em como as tabelas temporárias são consideradas para os fins de recompilação são descritas na seção "Evitando recompilação ao usar a opção KEEP PLAN" desse artigo.
  • As primeiras execuções do dois procedimentos acima também causam uma recompilação na primeira seleção da table #t temporária. As razões para essa recompilação são discutidas na seção "Recompilações causadas pela intercalação das operações DDL e DML" neste artigo.
  • Uma instrução "select count(*) from #t" foi usada nesse exemplo, em vez de uma simples instrução "select * from #t". Para evitar recompilações excessivas, o SQL Server não considera a recompilação de "planos triviais" (como um select * from a table) causada por modificações de linha.

Recompilações causadas pela intercalação das operações DDL e DML

Se as operações DDL forem executadas sem um procedimento ou lote, o procedimento ou lote será recompilado ao encontrar a primeira operação DML subseqüente que está afetando a tabela envolvida na DDL.

Considere o seguinte procedimento armazenado de exemplo:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
Se você executar esse código no Query Analyzer e exibir os eventos acima em um rastreamento do Profiler, você verá a seguinte seqüência:

Recolher esta tabelaExpandir esta tabela
Classe de eventoTexto
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP:RecompileInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


Nesse caso, o procedimento armazenado é recompilado três vezes durante a execução. Para entender por que isso acontece, considere como o otimizador desenvolve um plano para o procedimento armazenado:
  1. Durante a compilação inicial do procedimento, as tabelas t1 e t2 não existem. Portanto, nenhum plano para as consultas referentes a essas tabelas pode ser criado. Elas devem ser geradas durante o período da execução.
  2. Como o procedimento é executado pela primeira vez, a primeira etapa é criar a tabela t1. A próxima etapa é um "select from table t1" -- para a qual não existe nenhum plano. Portanto, o procedimento é recompilado para desenvolver um plano para a instrução SELECT. Um plano é gerado para "select from t1" atual, bem como para o "select from t1" após a criação do índice. Nenhum plano pode ser gerado para "select from t2" porque t2 ainda não existe.
  3. A próxima etapa é criar um índice em t1. A partir disso, outro SELECT é executado em t1, que agora tem um plano da primeira recompilação. Entretanto, como o esquema de t1 foi alterado desde a geração do plano, o procedimento deve ser recompilado novamente para gerar um novo plano para o "select from t1". E como t2 não existe, nenhum plano pode ser gerado para "select from t".
  4. A seguir, a tabela t2 é criada e "select from t2" é executado. Como nenhum plano existe para a instrução, o procedimento é recompilado pela última vez.
Essas recompilações ocorrem em cada execução do procedimento armazenado. Para reduzir as recompilações, modifique o procedimento para fazer todas as operações DDL primeiro, seguido das operações DML, conforme mostrado em:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
				
Para a primeira execução do procedimento NoInterleave, os seguintes eventos serão exibidos no Profiler:

Recolher esta tabelaExpandir esta tabela
Classe de eventoTexto
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP:RecompileNoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


Nesse caso, todas as instruções DDL são concluídas primeiro. O otimizador compila esse procedimento da seguinte maneira:
  1. Durante a compilação inicial do procedimento, as tabelas t1 e t2 não existem. Portanto, nenhum plano para as consultas referentes a essas tabelas pode ser criado. Elas devem ser geradas durante o período da execução.
  2. As primeiras etapas que o procedimento executa são as operações DDL, criando as tabelas t1 e t2, bem como o índice em t1.
  3. A próxima etapa é o primeiro "select from t1". Como não existe plano disponível para essa instrução SELECT, o procedimento é recompilado. Como todos os objetos existem, os planos são gerados para todas as instruções SELECT no procedimento neste momento.
  4. O restante do procedimento é executado usando os planos gerados. Como não há alterações nos objetos referenciados, não há mais a necessidade de recompilar o procedimento.
OBSERVAÇÃO: A segunda e a subseqüente execuções usam o plano de consulta e o cache existentes, e não resultam em nenhuma recompilação. Os procedimentos que criam, alteram ou ignoram tabelas devem ser modificados para garantir que todas as instruções DDL estejam localizadas no início do procedimento.

Recompilações causadas por determinadas operações de tabela temporária

O uso de tabelas temporárias em um procedimento armazenado pode fazer com que o procedimento seja recompilado sempre que for executado.

Para evitar isso, altere o procedimento armazenado de modo que ele atenda os seguintes requisitos:
  • Todas as instruções que contêm o nome de uma tabela temporária referem-se a uma tabela temporária criada no mesmo procedimento armazenado, e não em um procedimento armazenado de chamada ou chamado ou em uma seqüência executada usando a instrução EXECUTE ou o procedimento armazenado sp_executesql.
  • Todas as instruções que contêm o nome de uma tabela temporária aparecem sintaticamente após a tabela temporária no procedimento armazenado ou acionador.
  • Não há instruções DECLARE CURSOR cujas instruções SELECT referem-se a uma tabela temporária.
  • Todas as instruções que contêm o nome de uma tabela temporária precedem qualquer instrução DROP TABLE referente a uma tabela temporária.

    As instruções DROP TABLE não são necessárias para as tabelas temporárias criadas em um procedimento armazenado. As tabelas são automaticamente ignoradas ao concluir o procedimento.
  • Nenhuma instrução que cria um a tabela temporária (como CREATE TABLE ou SELECT... INTO) aparece em uma instrução de controle de fluxo como IF... ELSE ou WHILE.

Evitando recompilação ao usar a opção KEEP PLAN

O uso de tabela temporária em procedimentos armazenados introduz determinadas complexidades para o otimizador de consulta. A contagem de linha e a informação estatística das tabelas podem variar significativamente durante a vida útil da execução do procedimento armazenado. Para garantir que o otimizador usará o melhor plano em todos os casos referentes às tabelas temporárias, um algoritmo especial foi desenvolvido para ser mais agressivo com as recompilações. O algoritmo informa que se uma tabela temporária criada com um procedimento armazenado for alterada mais de seis vezes, o procedimento será recompilado quando a próxima instrução referenciar a tabela temporária.

Considere o seguinte exemplo:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
Nesse caso, você verá os seguintes exemplos no Profiler para a segunda execução:

Recolher esta tabelaExpandir esta tabela
Classe de eventoTexto
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStartingselect count(*) from #t1
SP:RecompileuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

O procedimento é recompilado no SELECT que ocorre após as sete alterações na tabela temporária #t.

Essa recompilação agressiva é útil em casos onde as alterações na distribuição de dados da tabela temporária podem afetar significativamente o melhor plano de consulta para a instrução referente à ela. Porém, no caso de grandes procedimentos que modificam freqüentemente as tabelas temporárias, mas não de uma forma significativa, as recompilações podem resultar em um desempenho geral mais lento. A opção KEEP PLAN da instrução SELECT foi introduzida para essa situação.

KEEP PLAN elimina as recompilações de procedimento armazenado causadas por mais de seis alterações nas tabelas temporárias dentro do procedimento e retorna ao algoritmo padrão para recompilação causada modificações de linha, já discutidas na seção "Recompilações causadas por modificações de linha" desse artigo. A opção KEEP PLAN não evita todas as recompilações, ela simplesmente previne aquelas causadas por mais de seis alterações nas tabelas temporárias referentes ao procedimento. No exemplo acima, se você remover o comentário da linha "option (KEEP PLAN)" no procedimento armazenado, o evento SP:Recompile não será gerado.

Se você remover o comentário da linha "option (KEEP PLAN)" no código acima e executá-lo, você verá os seguintes eventos no Profiler:

Recolher esta tabelaExpandir esta tabela
Classe de eventoTexto
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements -
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


Observe que não há evento SP:Recompile.

Recompilações causadas por determinadas instruções SET executadas no procedimento armazenado

As cinco opções SET a seguir são definidas como ON por padrão:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Se você executar a instrução SET para definir qualquer uma dessas opções como OFF, o procedimento armazenado será recompilado sempre que for executado. A razão para isso é que alterar essas opções pode afetar o resultado da consulta que acionou a recompilação.

Considere o seguinte código de exemplo:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
Nesse caso, você verá os seguintes eventos no SQL Profiler para cada execução do procedimento armazenado:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
Substituir a opção SET por qualquer uma das cinco opções listadas acima mostrará os mesmos resultados. Ainda, usar a opção para manter o plano aqui não ajudará a evitar a recompilação, pois a causa da recompilação parte da instrução SET.

O modo recomendável de evitar a recompilação é não usar qualquer uma dessas cinco instruções SET em um procedimento armazenado. Para obter informações adicionais, consulte o seguinte artigo na Base de Dados de Conhecimento Microsoft:
294942 PRB: SET CONCAT_NULL_YIELDS_NULL pode causar a recompilação de procedimentos armazenados
Entretanto, como não recomendável, executar a instrução SET para redefinir a opção de conexão com o mesmo valor do procedimento armazenado, também pode evitar a recompilação, fazendo da seguinte forma:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
O rastreamento do SQL Profiler não mais mostrará os eventos SP:Recompile.

A seguinte tabela lista algumas instruções SET comuns, e se alterar ou não a instrução SET em um procedimento armazenado causa uma recompilação:
Recolher esta tabelaExpandir esta tabela
Instrução SETRecompile
Set quoted_identifierNão
Set arithabortSim
Set ansi_null_dflt_onSim
Set ansi_defaultsSim
Set ansi_warningsSim
Set ansi_paddingSim
Set concat_null_yields_nullSim
Set numeric_roundabortNão
Set nocountNão
Set rowcountNão
Set xact_abortNão
Set implicit_transactionsNão
Set arithignoreNão
Set lock_timeoutNão
Set fmtonlyNão

Referências

308737 INF: Como identificar a causa da recompilação em um evento SP:Recompile

Para obter informações sobre o uso do SQL Server Profiler, consulte os Manuais online do SQL Server.

Propriedades

ID do artigo: 243586 - Última revisão: sábado, 29 de dezembro de 2007 - Revisão: 2.3
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Palavras-chave: 
kbinfo KB243586

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