Resolução de problemas recompilation do procedimento armazenado

Traduções de Artigos Traduções de Artigos
Artigo: 243586 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Nesta página

Sumário

Este artigo trata um determinado tipo de problema de desempenho que as aplicações podem ocorrer com o Microsoft SQL Server: recompilation o tempo de execução de procedimentos armazenados. Se estiver a resolver um problema de desempenho, mas não determinou que esta é a origem exacta do problema, consulte o seguinte artigo na Microsoft Knowledge Base antes de continuar:

224587COMO: Resolver problemas de desempenho de aplicações com o SQL Server
Este artigo pressupõe que tenha utilizado nesse artigo para restringir o âmbito do problema e que capturadas um rastreio de SQL Server Profiler com as colunas de eventos e dados específicas detalhadas nele.

Mais Informação

Quando um utilizador executa um procedimento armazenado, se ainda não estiver disponível na cache, o SQL Server carrega o procedimento e compila um plano de consulta. O plano compilado é guardado na cache e reutilizado por chamadas subsequentes do procedimento armazenado até que ocorra qualquer acção invalidar o plano e forçar uma recompilation. As seguintes acções podem causar recompilation de um plano de procedimento armazenado:
  • Utilização de uma cláusula WITH RECOMPILAR na instrução CREATE PROCEDURE ou executar.
  • Esquema é alterado para qualquer um dos objectos referenciados, incluindo adicionar ou ignorar restrições, as predefinições ou as regras.
  • Executar sp_recompile para uma tabela referenciada pelo procedimento.
  • O procedimento de restaurar a base de dados que contém o procedimento ou qualquer um dos objectos referencia (se estiver a efectuar operações entre bases de dados).
  • Actividade do servidor suficientes fazendo com que o plano para ser envelhecido fora da cache.
Estes motivos para ser um procedimento armazenado existissem em versões anteriores e causou o plano para recompilar antes de iniciar a execução do procedimento. No SQL Server 7.0, é introduzido um novo comportamento que podem causar um procedimento armazenado recompilar durante a execução. Este novo comportamento assegura que o optimizador tem sempre o melhor plano possível para cada instrução específico num procedimento. Os seguintes eventos poderão provocar um recompilation de tempo de execução de um procedimento armazenado:
  • Uma percentagem suficiente de alterações de dados numa tabela que é referenciado pelo procedimento armazenado.
  • O procedimento interleaves operações de linguagem DDL (Data Definition Language) e DML (Data Manipulation Language).
  • O procedimento efectua determinadas operações nos tabelas temporárias.
Cada uma destas causas é explicada em mais detalhe neste artigo.

Em alguns casos, o custo de recompilar o procedimento armazenado é mais do que a vantagem derivada fizer, especialmente para procedimentos de grandes dimensões. É muito importante observar novamente que quando é accionado um recompilation, a secção inteira ou o procedimento é compilado. Isto significa que uma diminuição no desempenho é directamente proporcional ao tamanho do procedimento de batch. Para mais informações sobre este tópico, consulte o tópico "Sugestões de Transact-SQL" no SQL Server Books Online.


As informações seguintes neste artigo foca a identificar a causa do procedimento armazenado run-time recompilações e descreve métodos que pode utilizar para evitar que.

Melhores práticas

É aconselhável proprietário qualificar nomes de procedimento armazenado quando executar um procedimento. Isto permite uma melhor clareza e mais fácil reutilização do plano de execução existente pelo utilizador actual. Por exemplo, se um utilizador que não seja o proprietário de base de dados (dbo) executa um procedimento armazenado de propriedade de dbo que (denominado myProc neste exemplo) na base de dados pubs , utilize a seguinte instrução:
exec dbo.myProc
				
em vez deste:
exec myProc
				
esta técnica elimina confusão sobre outras versões possíveis do procedimento por proprietários diferentes a partir de um ponto de vista codificação e manutenção e também permite SQL Server aceder a mais directamente o plano de execução para o procedimento específico.

Por não qualificar o nome do proprietário, o SQL Server introduz o código de compilação e adquire um bloqueio de COMPILAÇÃO no procedimento. No entanto, eventualmente-determina que um plano novo não é necessário (pressupondo que aplicam não outras razões), para que não recompilar o plano neste momento devido à falta de qualificação. No entanto, o passo adicional de obter um bloqueio de COMPILAÇÃO sobre o procedimento pode causar contenção de bloqueio situações graves. Consulte Q263889 INF: SQL bloqueio pagar para bloqueios [[COMPILAÇÃO]] para obter mais detalhes sobre esta situação.

Se o proprietário qualificar a chamada de procedimento com owner.procedure, não é necessário adquirir o bloqueio de compilação para a contenção é reduzida.

Identificar e resolver problemas

Se tiver não já feito, consulte o seguinte artigo na Microsoft Knowledge Base para obter detalhes sobre a capturar dados Profiler para ajudar a analisar o desempenho do sistema:
224587COMO: Resolver problemas de desempenho de aplicações com o SQL Server

Visualizar os dados do Profiler

SQL Server Profiler inclui um evento SP:Recompile que pode utilizar para monitorizar o número de recompilações ocorra. O evento SP:Recompile ocorre sempre que um procedimento armazenado recompila durante a execução.
  • grupo o rastreio Profiler por classe de evento

    1. No menu ficheiro , clique em Propriedades .
    2. No separador Colunas de dados , utilize botão para cima para mover a Classe de eventos e o texto no título de grupos , com a Classe de evento pela primeira vez. Utilize o botão para baixo para remover todas as outras colunas sob o título de grupos .
    3. Clique em OK .
    Verificar o número de eventos SP:Recompile .

    Pode expandir o grupo SP:Recompile para ver os detalhes de ocorrências individuais. A coluna de texto do evento indica o nome do procedimento armazenado que novamente compiladas. Se vários procedimentos estão a causar recompilações, estarão ordenadas por número de ocorrências. Se tiver um grande número de eventos SP:Recompile e detectar utilização elevada da CPU, focar a resolver os procedimentos que tem o número máximo de recompilações. Tome nota do processo do sistema ID (SPID) e a hora de início do evento SP:Recompile para uma instância dos procedimentos armazenados específicos e siga os passos abaixo.

    Se não vir quaisquer eventos SP:Recompile , mas continuar a verificar um problema de desempenho, consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
    224587COMO: Resolver problemas de desempenho de aplicações com o SQL Server
  • determinar a declaração que disparou o evento recompilação

    1. No menu ficheiro , clique em Propriedades .
    2. No separador Colunas de dados , utilize o botão para baixo para remover todas as outras colunas sob o título de grupos .
    3. No separador eventos , remova todos os eventos excepto SP: Iniciar , SP:StmtStarting , SP:Recompile , e SP: concluída . Se não capturar o evento SP:StmtStarting , pode substituir SP:StmtCompleted , mas não incluir ambos porque ao fazê-lo, duplica a quantidade de informações que necessárias para procure.
    4. Se identificou uma instância específica de um procedimento armazenado recompilation para examinar, pode limitar os dados que visualizar SPID e período de tempo da ocorrência específica utilizando o separador filtros .
    5. Clique em OK .

    O evento SP:Recompile irá ser aumentado directamente após o evento SP:StmtStarted da instrução do procedimento armazenado que causou o recompilation. Depois de concluído o evento recompilação, verá uma repetição de evento SP:StmtStarted , indicando que a instrução está em execução 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 executar este código no analisador de consultas e visualizar os eventos acima num rastreio do Profiler, verá a seguinte sequência:

    Reduzir esta tabelaExpandir esta tabela
    classe de evento texto
    SP: inícioRecompProc
    SP:StmtStartingCriar tabela # t (int)
    SP:StmtStartingSeleccionar * da t #
    SP:RecompileRecompProc
    SP:StmtStartingSeleccionar * da t #
    SP: concluídoRecompProc


    Pode saber imediatamente a instrução que causou o recompilation foi:
    select * from #t
    						
    Porque aparece antes e depois do evento SP:Recompile .

    Se tinha capturada apenas o evento SP:StmtCompleted , mas não o evento SP:StmtStarting , SP:Recompile mostrará imediatamente antes a instrução causado como abaixo:

    Reduzir esta tabelaExpandir esta tabela
    classe de evento texto
    SP: inícioRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedSeleccionar * da t #
    SP: concluídoRecompProc


    Pode ver que o evento SP:Recompile é desencadeado antes do evento SP:StmtCompleted o "seleccionar * da t #" instrução, que causou o recompilation. Isto faz sentido, como a instrução não pode ser concluída até depois do novo plano de consulta é gerado para a recompilação. Tudo o resto dos exemplos neste artigo utiliza o evento SP:StmtStarting . Lembre-se capturadas apenas o evento SP:StmtCompleted , apenas se ver a declaração depois SP:Recompile , como descrito acima.

    Tenha em atenção que, se executar este procedimento armazenado determinado várias vezes, SQL Server vai reutilizar o plano existente para este procedimento. Só verá o evento recompilação na primeira execução do procedimento ou se largar e recriar o procedimento sempre que executar o script. A razão para recompilation neste caso específico é discutida na secção "Recompilações por intercalada dados definição Language (DDL) e dados manipulação Language (DML) operações" deste artigo; este é apenas um exemplo para ilustrar a determinar facilmente que afirmação está a causar o recompilation.

Recompilações devido a modificações de linha

Se tiver alterado uma percentagem suficiente de dados numa tabela referenciada por um procedimento armazenado desde que foi criado o plano de consulta original, SQL Server irá recompilar o procedimento armazenado para garantir que tem um plano baseado nos dados estatísticos mais recentes. 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 execução do procedimento RowModifications segunda, verá os seguintes eventos no Profiler:

Reduzir esta tabelaExpandir esta tabela
classe de evento texto
SP: inícioRowModifications
SP:StmtStartingCriar tabela # t (uma int, char(10)) b
SP:StmtStartingSeleccionar * da t #
SP:StmtStartingInserir n.º t seleccionar * da SomeTable
SP:StmtStartingSelecione count(*) t # onde um = 37
SP:RecompileRowModifications
UpdateStats automáticaum
SP:StmtStartingSelecione count(*) t # onde um = 37
SP: concluídoRowModifications

NOTA: A primeira execução também mostrará um evento SP:Recompile para o "seleccionar * da t #" instrução. A razão para recompilation neste caso específico é discutida na secção "Recompilações por intercalada dados definição Language (DDL) e dados manipulação Language (DML) operações" deste artigo. Neste exemplo, focar SP:Recompile acima porque ocorre sempre que o procedimento é executado.

Neste exemplo, "Seleccione count(*) t # onde um = 37" faz com que um recompilation do procedimento devido a alteração no número de linhas desde que a tabela foi criada. A presença do evento Automaticamente UpdateStats confirma que o recompilation foi devido a modificações de linha. A coluna de texto indica a coluna para o qual as estatísticas foram modificadas.

Quando a tabela de t # foi criada, o número de linhas é zero. O plano para o original "seleccionar * da t #" desenvolvido com esse número de linhas, bem como o plano de consulta "select contagem (*)". No entanto, antes do "count(*) seleccionar" é executada, são inseridas 1.000 novas linhas na tabela de t #. Porque um número suficiente de dados foi alterado, o optimizador recompila o procedimento para se certificar de que escolhe o plano mais eficaz para a instrução. Este recompilation ocorrerá em cada execução do procedimento armazenado uma vez que a inserção de 1.000 linhas irá sempre ser visualizada como importantes suficiente para justificar a recompilation.

O algoritmo de que SQL Server utiliza para determinar se um plano deve ser novamente compilado é o mesmo algoritmo utilizado para actualização automática estatísticas descrito no seguinte artigo na base de dados de conhecimento da Microsoft:
195565INF: Como SQL Server 7.0 e SQL Server 2000 Autostats trabalho
No exemplo acima, o procedimento armazenado é suficientemente pequeno que o recompilation não teria um efeito perceptível no desempenho. No entanto, se tiver um procedimento armazenado grande que executa actividades semelhantes resultando recompilações vários, poderá notar uma degradação do desempenho.

Existem os seguintes métodos para contrariar recompilações devido a modificações de linha:
  • Execute a instrução utilizando sp_executesql .
    Este é o método preferencial. Não são compiladas instruções executadas utilizando o procedimento sp_executesql armazenados como parte do plano de procedimento armazenado. Assim, quando executar a instrução, SQL Server serão livre para utilizar um plano existente na cache para a instrução ou criar um novo em tempo de execução. Em qualquer dos casos, o plano para o procedimento armazenado chamado não é afectado e não tiver a ser novamente compiladas.

    A instrução EXECUTE terá o mesmo efeito; no entanto, não é aconselhável. Utilizar o executar instrução não é tão eficaz como utilizar sp_executesql porque não permite para parameterization da consulta.

    O procedimento RowModifications indicado acima pode ser escrito utilizar 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 execução do procedimento RowModifications2 segunda, verá os seguintes eventos no Profiler:

    Reduzir esta tabelaExpandir esta tabela
    classe de evento texto
    SP: inícioRowModifications2
    SP:StmtStartingCriar tabela # t (uma int, char(10)) b
    SP:StmtStartingSeleccionar * da t #
    SP:StmtStartingInserir n.º t seleccionar * da SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) de t # onde um = @ um ', n'@a int ', @ um = 37
    SP: início
    SP:StmtStartingSelecione count(*) t # onde um = @ um
    UpdateStats automáticaum
    SP:StmtStartingSelecione count(*) t # onde um = @ um
    SP: concluído
    SP: concluídoRowModifications2


    Repare que não existem eventos SP:Recompile para o procedimento RowModifications2 . Existem concluída SP: Iniciar para SP: concluída eventos sp_executesql chamar contexto e um evento UpdateStats automaticamente para uma coluna. No entanto, uma vez que esta chamada é fora do contexto do procedimento armazenado, o procedimento RowModifications2 não é necessário ser novamente compiladas neste caso.

    Para mais informações sobre como utilizar o procedimento sp_executesql armazenadas, consulte o "sp_executesql (T-SQL)" e "Utilizar sp_executesql" tópicos no SQL Server Books Online.
  • Utilize sub-procedures para executar as instruções que estão a causar as recompilações.
    Neste caso, a instrução ainda pode causar um recompilation, mas em vez de recompilar o procedimento armazenado chamado grande, irá recompilar apenas sub-procedure pequeno.
  • Utilize a opção manter planear.
    As tabelas temporárias têm regras especiais sobre recompilações que, em alguns casos, podem ser mais restrita do que o algoritmo de recompilation predefinido. Pode utilizar a planear manter opção para diminuir o limite da tabela temporária para o algoritmo predefinido. Para mais informações, consulte a secção "Evitar Recompilation por utilizar a manter planear opção" deste artigo.
NOTA: O procedimento RowModifications é um exemplo de um procedimento que é novamente compilado devido a modificações de linha muito simplificado. Reveja as seguintes advertências relativas a este exemplo:

  • Ainda que o exemplo que utilize uma tabela temporária, esta situação se aplica a procedimentos armazenados que referenciam bem a tabelas permanentes. Se uma quantidade suficiente de dados numa tabela referenciada foi alterada desde que foi criado o plano de consulta, o procedimento armazenado vai ser novamente compilado. As diferenças nas tabelas como temporárias são consideradas para fins de recompilation são descritos na "Evitar Recompilation por utilizar a manter planear opção" secção deste artigo.
  • Execuções primeira dos dois procedimentos acima também causar um recompilation no ' primeiro ', seleccione a partir de t o n.º tabela temporária. As razões para esta recompilation abordadas o "recompilações por intercalada dados definição Language (DDL) e dados manipulação Language (DML) operações" secção deste artigo.
  • Foi utilizada uma instrução "select count(*) de t #" neste exemplo em vez de um simples "seleccionar * da t #" instrução. Para evitar recompilações excessivas, SQL Server não considera ser "trivial planos" (tal como uma selecção * a partir de uma tabela) devido a modificações de linha.

Recompilações devido à intercalada Data Definition Language (DDL) e Data Manipulation Language (DML) operações

Novamente se DDL operações são efectuadas dentro de um procedimento ou batch, procedimento ou da secção é compilada quando encontra a primeira DML subsequente operação afectar a tabela envolvida na DDL.

Considere o seguinte procedimento armazenado 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 executar este código no analisador de consultas e visualizar os eventos acima num rastreio do Profiler, verá a seguinte sequência:

Reduzir esta tabelaExpandir esta tabela
classe de evento texto
SP: inícioIntercalar
SP:StmtStartingCriar tabela t1 (int)
SP:StmtStartingSeleccionar * da t1
SP:RecompileIntercalar
SP:StmtStartingSeleccionar * da t1
SP:StmtStartingCriar índice idx_t1 t1(a)
SP:StmtStartingSeleccionar * da t1
SP:RecompileIntercalar
SP:StmtStartingSeleccionar * da t1
SP:StmtStartingCriar tabela t2 (int)
SP:StmtStartingSeleccionar * da t2
SP:RecompileIntercalar
SP:StmtStartingSeleccionar * da t2
SP: concluídoIntercalar


Novamente neste caso, o procedimento armazenado é compilado três vezes durante a execução. Para compreender porque é que isto acontece, considere a forma como o optimizador desenvolve-se um plano para este procedimento armazenado:
  1. Durante a compilação inicial do procedimento, as tabelas t1 e t2 não existem. Por conseguinte, pode ser criado não planear as consultas estas tabelas de referência. Devem ser gerados ao tempo de execução.
  2. Como o procedimento for executado pela primeira vez, o primeiro passo é criar tabela t1. O passo seguinte é uma selecção a partir da tabela t1--que não existe nenhum plano para. Novamente por este motivo, o procedimento é compilado neste momento para desenvolver um plano para a instrução SELECT. Um plano é gerado para seleccione actual de t1, bem como a selecção de t1 após a criação de índices. Plano de não pode ser gerado para a selecção de t2 porque t2 ainda ainda não existe.
  3. O passo seguinte consiste em criar um índice no t1. A seguir, seleccione outro é executada no t1, que já tem um plano de recompilação primeiro. Novamente no entanto, porque o esquema de t1 foi alterado desde que esse plano foi gerado, o procedimento deve ser compilado novamente para gerar uma nova planta para a seleccionar a partir de t1. E porque t2 ainda não existir, não plano pode ser gerado para seleccionar a partir de t.
  4. Em seguida, a tabela t2 é criada e a selecção de t2 é executada. Dado que não plano para instrução, o procedimento é novamente compilado uma última vez.
Estes recompilações ocorrem em cada execução do procedimento armazenado. Para reduzir as recompilações, modificar o procedimento para fazer DDL todas as operações em primeiro lugar, seguido DML operações, tal como mostrado na seguinte:
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
				
A primeira execução do procedimento NoInterleave mostrará os seguintes eventos Profiler:

Reduzir esta tabelaExpandir esta tabela
classe de evento texto
SP: inícioNoInterleave
SP:StmtStartingCriar tabela t1 (int)
SP:StmtStartingCriar índice idx_t1 t1(a)
SP:StmtStartingCriar tabela t2 (int)
SP:StmtStartingSeleccionar * da t1
SP:RecompileNoInterleave
SP:StmtStartingSeleccionar * da t1
SP:StmtStartingSeleccionar * da t1
SP:StmtStartingSeleccionar * da t2
SP: concluídoNoInterleave


Neste caso todas as instruções de DDL terminar até à frente. O optimizador compila este procedimento da seguinte forma:
  1. Durante a compilação inicial do procedimento, as tabelas t1 e t2 não existem. Por conseguinte, pode ser criado não planear as consultas estas tabelas de referência. Devem ser gerados ao tempo de execução.
  2. Os passos primeiro executa o procedimento são a DLL de operações, criar tabelas t1 e t2, bem como o índice no t1.
  3. O próximo passo é seleccionar primeiro a partir do t1. Porque não existe nenhum plano disponíveis para esta instrução SELECT, o procedimento é novamente compilado. Uma vez que todos os objectos existirem, planos são gerados para todas as instruções SELECT no procedimento neste momento.
  4. Os restantes passos do procedimento executa utilizando os planos gerados. Porque não existem nenhumas alterações a objectos referenciados, não é necessário para recompilar o procedimento mais.
NOTA: Execuções segunda e subsequentes tornam o plano de consulta e cache existente e execute não resultar em qualquer recompilações sequer. Procedimentos que criar, alterar ou Largue tabelas devem ser modificados para garantir que todas as instruções de DDL estão localizadas no início do procedimento.

Recompilações devido a determinadas operações de tabela temporária

Utilização de tabelas temporárias num procedimento armazenado pode causar o procedimento armazenado ser novamente compiladas sempre que o procedimento é executado.

Para evitar esta situação, altere o procedimento armazenado, de modo a que cumpre os seguintes requisitos:
  • Todas as afirmações que contêm o nome de uma tabela temporária fazer referência a uma tabela temporária criada no mesmo procedimento armazenado e não numa chamada ou chamado procedimento armazenado ou numa cadeia executada utilizando o executar instrução ou sp_executesql procedimento armazenado.
  • Todas as afirmações que contêm o nome de uma tabela temporária sintacticamente aparecem após a tabela temporária no accionador ou procedimento armazenado.
  • Existem não instruções DECLARE CURSOR cujas instruções SELECT fazer referência a uma tabela temporária.
  • Todas as instruções que contêm o nome de qualquer tabela temporária de preceder qualquer instrução DROP TABLE que referencia uma tabela temporária.

    Instruções DROP TABLE não são necessários para tabelas temporárias criadas num procedimento armazenado. As tabelas automaticamente são ignoradas quando tiver concluído o procedimento.
  • Não criar uma tabela temporária de instruções (como, por exemplo, CREATE TABLE ou SELECT... INTO) aparece numa instrução de controlo de fluxo, tais como IF... ELSE ou WHILE.

Evitar Recompilation utilizando a opção de PLAN manter

Tabela temporária utilização de procedimentos armazenados apresenta determinadas complexidades para o Optimizador de consultas. O número de linhas e informações estatísticas das tabelas podem variar drasticamente durante a duração da execução da procedimento armazenado. Para garantir que o Optimizador utiliza o plano melhor em todos os casos relacionados com tabelas temporárias, um algoritmo especial foi desenvolvido para ser mais agressivo com recompilações. O algoritmo indica novamente que se uma tabela temporária criada com um procedimento armazenado tiver sido alterada mais de seis vezes, o procedimento vai ser compilado quando a instrução seguinte faz referência 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
				
neste caso, verá os seguintes eventos no Profiler para a execução segundo:

Reduzir esta tabelaExpandir esta tabela
classe de evento texto
SP: iníciouseKeepPlan
SP:StmtStartingCriar tabela # t (int)
SP:StmtStarting -A sete insere instruções-
SP:StmtStartingSelecione count(*) # t1
SP:RecompileuseKeepPlan
SP:StmtStartingSelecione count(*) # t1
SP: concluídouseKeepPlan

O procedimento é novamente compilado no seleccione que ocorre depois das alterações de sete para t o n.º tabela temporária.

Este modo agressivo recompilation é útil nos casos em que as alterações da distribuição de dados da tabela temporária drasticamente podem afectam o plano de consulta ideal para a declaração de referência. No entanto, no caso de grandes procedimentos que modificar tabelas temporárias frequentemente, mas não de forma significativa, de recompilações podem resultar em mais lento o desempenho global. A opção manter planear da instrução SELECT foi introduzida para esta situação.

MANTER planear elimina causadas por mais de seis alterações a tabelas temporárias no procedimento de recompilações de procedimento armazenado e reverte para o algoritmo padrão para recompilation devido a modificações de linhas apresentadas acima na secção "Recompilações por linha modificações" deste artigo. MANTER planear não impede que recompilações completamente, simplesmente impede os causado por mais de seis alterações a tabelas temporárias referenciadas no procedimento. No exemplo acima, se remover o comentário da linha "opção (mantenha PLAN)" no procedimento armazenado, o evento SP:Recompile não será gerado.

Se remover o comentário da linha "opção (mantenha PLAN)" no código acima e executá-la, verá os seguintes eventos no Profiler:

Reduzir esta tabelaExpandir esta tabela
classe de evento texto
SP: iníciouseKeepPlan
SP:StmtStartingCriar tabela # t (int)
SP:StmtStarting -A sete insere instruções-
SP:StmtStartingSelecione count(*) a opção # t1 (manter planear)
SP: concluídouseKeepPlan


Tenha em atenção que nenhum evento SP:Recompile não existe.

Recompilações devido a certas SET instruções executadas no procedimento armazenado

As seguintes cinco opções de SET estão definidas para ON por predefinição:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Novamente se executar a instrução SET para definir qualquer uma destas opções para OFF, o procedimento armazenado vai ser compilado sempre que é executado. O motivo para isto é que a alteração destas opções pode afectar o resultado da consulta que accionou o recompilation.

Tenha em consideração 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
				
neste caso, 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 de SET com qualquer uma das cinco opções acima listada mostrará os mesmos resultados. Além disso, utilizar a opção de manter plano aqui não ajudar a evitar a recompilation pois a causa do recompilation da instrução SET.

A forma recomendada para evitar o recompilation é não utilizar qualquer um destas cinco instrução SET num procedimento armazenado. Para obter informações adicionais, consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
294942PROBLEMA: SET CONCAT_NULL_YIELDS_NULL pode causar a procedimentos para recompilação armazenados
No entanto, como não recomendado, executar o SET instrução para repor a opção de ligação para o mesmo valor que o procedimento armazenado, também pode evitar a recompilação fazê-lo como:
Set ANSI_DEFAULTS OFF

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

A tabela seguinte lista algumas instruções SET comuns e se é ou não a alterar a instrução SET num procedimento armazenado de uma recompilação faz com que:
Reduzir esta tabelaExpandir esta tabela
Definir declaraçãoRecompilar
Conjunto quoted_identifierNão
Conjunto arithabortSim
Conjunto ansi_null_dflt_onSim
Conjunto ansi_defaultsSim
Conjunto ansi_warningsSim
Conjunto ansi_paddingSim
Conjunto concat_null_yields_nullSim
Conjunto numeric_roundabortNão
Definir nocountNão
Conjunto rowcountNão
Conjunto xact_abortNão
Conjunto implicit_transactionsNão
Conjunto arithignoreNão
Conjunto lock_timeoutNão
Conjunto fmtonlyNão

Referências

308737INF: Como identificar a causa do Recompilation de um evento SP:Recompile

Para obter informações sobre a utilização do SQL Server Profiler, consulte SQL Server Books Online.

Propriedades

Artigo: 243586 - Última revisão: 2 de novembro de 2007 - Revisão: 2.5
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: 
kbmt kbinfo KB243586 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 243586

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