Risoluzione dei problemi di ricompilazione delle stored procedure

Traduzione articoli Traduzione articoli
Identificativo articolo: 243586 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

In questo articolo si occupa un tipo specifico di problema di prestazioni che potrebbero verificarsi le applicazioni con Microsoft SQL Server: la ricompilazione della fase di esecuzione di stored procedure. Se si sta tentando di risolvere un problema di prestazioni, ma non si è ancora riusciti a determinare quale sia l'origine precisa del problema, vedere il seguente articolo della Microsoft Knowledge Base:

224587HOW TO: Troubleshoot prestazioni delle applicazioni con SQL Server
In questo articolo si presuppone che l'utente abbia utilizzato le informazioni fornite nell'articolo citato per restringere l'ambito del problema e abbia catturato una traccia Profiler di SQL Server contenente gli eventi e le colonne di dati indicati nell'articolo.

Informazioni

Quando un utente esegue una stored procedure, se non è già disponibile nella cache, SQL Server carica la procedura e compila un piano di query. Il piano compilato viene salvato nella cache e riutilizzato tutte le volte che viene richiamata la stored procedure finché non si verifica una qualche azione che invalida il piano e forza la ricompilazione. Di seguito sono elencate le azioni che potrebbero causare la ricompilazione del piano di una stored procedure:
  • Utilizzo di una clausola WITH RECOMPILE nell'istruzione CREATE PROCEDURE o EXECUTE.
  • Modifiche di schema a qualunque degli oggetti a cui viene fatto riferimento, tra cui aggiunta o eliminazione di vincoli, valori predefiniti o regole.
  • Esegue sp_recompile per una tabella a cui fa riferimento la procedura.
  • Ripristino del database che contenga la procedura o qualsiasi oggetto a cui si fa riferimento nella procedura, se si sta eseguendo operazioni tra più database.
  • Attività di server sufficiente per fare in modo che il piano non sia più contenuto nella cache.
Nelle versioni precedenti tutte queste ragioni erano sufficienti a determinare la ricompilazione del piano della stored procedure prima dell'inizio dell'esecuzione della procedura. In SQL Server 7.0, viene introdotto un nuovo comportamento che potrebbe causare una stored procedure ricompilare durante l'esecuzione. Questo comportamento assicura che l'utilità di ottimizzazione disponga sempre del piano migliore possibile per ciascuna specifica istruzione di una procedura. Di seguito sono riportati gli eventi che possono causare una ricompilazione a runtime di una stored procedure:
  • Percentuale sufficiente di modifiche ai dati di una tabella a cui si fa riferimento nella stored procedure.
  • La procedura sovrappone operazioni DDL (Data Definition Language) e operazioni DML (Data Manipulation Language).
  • La procedura esegue determinate operazioni con le tabelle temporanee.
Ciascuno di queste cause è illustrato in dettaglio in questo articolo.

In alcuni casi, è presente il costo della ricompilazione della stored procedure che è maggiore il vantaggio che derivato da ciò, soprattutto per le procedure di grandi dimensioni. È molto importante tenere presente che quando viene attivata una ricompilazione, del batch intero o procedure viene ricompilata. Questo significa che il degrado delle prestazioni è direttamente proporzionale alla dimensione della procedura o del batch. Per ulteriori informazioni su questo argomento, vedere l'argomento di "Suggerimenti a Transact-SQL" nella documentazione in linea di SQL Server.


Le informazioni contenute in questo articolo spiegano come identificare la causa della ricompilazione a runtime della stored procedura e illustrano i metodi disponibili per evitare tale ricompilazione.

Procedure consigliate

Si consiglia di proprietario qualificare i nomi di stored procedure quando si esegue una routine. Questo consente una maggiore chiarezza e un più facile riutilizzo del piano di esecuzione esistente da parte dell'utente corrente. Ad esempio, se un utente che non è il proprietario del database (dbo) esegue una stored procedure dbo di proprietà (denominata myProc in questo esempio) nel database pubs , utilizzare la seguente istruzione:
exec dbo.myProc
				
invece di:
exec myProc
				
questa tecnica consente di evitare confusione sulle altre possibili versioni della routine da proprietari diversi dal punto di vista di codifica e la manutenzione e consente inoltre a SQL Server accedere più direttamente il piano di esecuzione per la procedura specifica.

Non qualificando il nome del proprietario SQL Server viene inserito il codice di compilazione e acquisisce un blocco di compilazione per la procedura. Tuttavia, infine determina che un nuovo piano non è necessario (supponendo che non altri motivi applicano), in modo che non ricompilare il piano a questo punto a causa della mancanza di qualificazione. Tuttavia il passaggio aggiuntivo di ottenere un blocco di compilazione per la procedura può causare un conflitto di blocco nelle situazioni gravi. Fare riferimento a Q263889 INF: SQL blocco dovuti a blocchi [[compilazione]] per ulteriori informazioni su questa situazione.

Se è proprietario specificare completo la chiamata di routine con owner.procedure, non è necessario acquisire il blocco di compilazione, in modo si riduce la contesa.

Identificazione e risoluzione dei problemi

Se non è ancora stato fatto, vedere il seguente articolo della Microsoft Knowledge Base per informazioni su come catturare i dati di Profiler al fine di aiutare l'analisi delle prestazioni del sistema:
224587HOW TO: Troubleshoot prestazioni delle applicazioni con SQL Server

Visualizzazione dei dati di Profiler

SQL Server Profiler include un evento SP: Recompile che è possibile utilizzare per monitorare il numero di ricompilazioni che si verificano. L'evento di SP: Recompile si verifica ogni volta che una stored procedure viene ricompilata durante l'esecuzione.
  • gruppo traccia di Profiler dalla classe di evento

    1. Nel menu file , fare clic su Proprietà .
    2. Nella scheda Colonne di dati , utilizzare il pulsante UP per spostare prima il Event Class e Text nella sezione gruppi con Classe di evento . Utilizzare il pulsante DOWN per rimuovere tutte le altre colonne sotto l'intestazione gruppi .
    3. Fare clic su OK .
    Controllare il numero degli eventi SP: Recompile .

    È possibile espandere il gruppo SP:Recompile per vedere i dettagli delle singole occorrenze. La colonna Text dell'evento indica il nome della stored procedure che viene ricompilato. Se più procedure causano delle ricompilazioni, vengono ordinate in base al numero di occorrenze. Se si dispone di un numero elevato di evento SP: Recompile e si verificano utilizzo elevato della CPU, concentrare sulla risoluzione di procedure con il numero massimo di ricompilazioni. Prendere nota del sistema ID di processo (SPID) e l'ora di inizio dell'evento SP: Recompile per un'istanza della stored procedure particolare e seguire i passaggi riportati di seguito.

    Se non viene visualizzato alcun evento SP: Recompile , ma si verificano ancora problemi di prestazioni, vedere il seguente articolo della Microsoft Knowledge Base riportato di seguito:
    224587HOW TO: Troubleshoot prestazioni delle applicazioni con SQL Server
  • determinare l'istruzione che avviata l'evento ricompilazione

    1. Nel menu file , fare clic su Proprietà .
    2. Nella scheda Colonne di dati , è necessario utilizzare il pulsante DOWN per rimuovere tutte le altre colonne sotto l'intestazione gruppi .
    3. Nella scheda di eventi rimuovere tutti gli eventi tranne SP: avvio , SP: StmtStarting , SP: Recompile e SP: completata . Se non si acquisire l'evento SP: StmtStarting , è possibile sostituire SP: StmtCompleted ma non entrambi includono perché ciò raddoppia così la quantità di informazioni che è necessario esaminare.
    4. Se è stato identificato una particolare istanza di una ricompilazione di stored procedure per esaminare, è possibile limitare i dati che utilizzando la scheda filtri è visualizzare specifico SPID e intervallo di tempo dell'occorrenza.
    5. Fare clic su OK .

    Verrà generato l'evento SP: Recompile subito dopo l'evento SP:StmtStarted dell'istruzione stored procedure che ha causato la ricompilazione. Dopo avere completato l'evento ricompilazione, verrà visualizzata una ripetizione dell'evento SP:StmtStarted , che indica che l'istruzione è in esecuzione con il piano appena generato.

    Si consideri l'esempio riportato di seguito:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    se si esegue questo codice in Query Analyzer e visualizzare gli eventi sopra di una traccia di Profiler, si noterà che la sequenza riportata di seguito:

    Riduci questa tabellaEspandi questa tabella
    classe di evento testo
    SP: StartingRecompProc
    SP: StmtStartingcreare la tabella # t (int)
    SP: StmtStartingSelezionare * from # t
    SP: RecompileRecompProc
    SP: StmtStartingSelezionare * from # t
    SP: CompletedRecompProc


    da cui è possibile dedurre immediatamente che l'istruzione che ha causato la ricompilazione è stata l'istruzione:
    select * from #t
    						
    Perché viene visualizzata prima e dopo l'evento SP: Recompile .

    Se è stato catturato solo l'evento SP: StmtCompleted , ma non l'evento SP: StmtStarting , lo SP: Recompile verrà visualizzato direttamente prima dell'istruzione che ha causato come riportato di seguito:

    Riduci questa tabellaEspandi questa tabella
    classe di evento testo
    SP: StartingRecompProc
    SP: RecompileRecompProc
    SP: StmtCompletedSelezionare * from # t
    SP: CompletedRecompProc


    È possibile vedere che l'evento di SP: Recompile viene generato prima dell'evento SP: StmtCompleted per il "selezionare * from # t" istruzione, che ha causato la ricompilazione. Questo ha senso in quanto l'istruzione non può essere completata finché non viene generato il nuovo piano della query per la ricompilazione. Tutto il resto degli esempi in questo articolo è utilizzare l'evento SP: StmtStarting . Se è stato catturato solo l'evento SP: StmtCompleted , è sufficiente ricordarsi di visualizzare l'istruzione dopo lo SP: Recompile , come spiegato in precedenza.

    Se si esegue questa particolare stored procedure più volte, SQL Server riutilizzerà il piano esistente per la procedura. Si verrà vedere solo l'evento ricompilazione alla prima esecuzione della procedura o se eliminare e ricreare la procedura ogni volta che è eseguire lo script. Il motivo per la ricompilazione in questo caso specifico è descritto nella sezione "Ricompilazioni dovute alla sovrapposizione Data Definition Language (DDL) e dati Modifica Language di operazioni (DML) operazioni" di questo articolo; questa è semplicemente un esempio in cui viene illustrato come determinare facilmente l'istruzione ha causato la ricompilazione.

Ricompilazioni dovute alle modifiche di riga

Se una percentuale sufficiente di dati è stato modificato in una tabella fa riferimento una stored procedure dal momento che è stato generato il piano della query originale, SQL Server ricompilerà la stored procedure per verificare che contenga un piano basato sui dati statistici più aggiornati. Come esempio, si consideri la seguente stored procedure:
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
				
per la seconda esecuzione della procedura RowModifications , si saranno presenti i seguenti eventi in Profiler:

Riduci questa tabellaEspandi questa tabella
classe di evento testo
SP: StartingRowModifications
SP: StmtStartingcreare la tabella # t (un int, char(10)) b
SP: StmtStartingSelezionare * from # t
SP: StmtStartingInserire # t selezionare * da SomeTable
SP: StmtStartingSelezionare count(*) # t in cui un = 37
SP: RecompileRowModifications
Auto-UpdateStatsun
SP: StmtStartingSelezionare count(*) # t in cui un = 37
SP: CompletedRowModifications

Nota: La prima esecuzione verrà inoltre visualizzati un evento SP: Recompile per il "selezionare * from # t" istruzione. La ragione della ricompilazione in questo specifico caso è discussa nella sezione "Ricompilazioni dovute alla sovrapposizione di operazioni DDL (Data Definition Language) e di operazioni DML (Data Manipulation Language)" di questo articolo. In questo esempio, concentrarsi sullo SP: Recompile indicato poiché si verifica ogni volta che viene eseguita la routine.

In questo esempio l'istruzione "select count(*) from #t where a = 37" determina la ricompilazione della procedura a causa del fatto che il numero di righe della tabella è cambiato da quando questa è stata creata. La presenza dell'evento Auto-UpdateStats conferma che la ricompilazione è stato a causa di modifiche di riga. La colonna di testo indica la colonna per il quale le statistiche sono state modificate.

Quando è stata creata la tabella #t il numero delle righe era uguale a zero. Il piano per l'istruzione "select * from #t" originale è stato sviluppato con tale numero di righe e lo stesso vale per il piano della query "select count (*)". Tuttavia, prima dell'esecuzione "selezionare count(*)", la voce 1000 nuove righe vengono inserite nella tabella # t. Vista la quantità sufficiente di dati modificati, l'utilità di ottimizzazione ha ricompilato la procedura per assicurare che disponesse del piano più efficiente per l'istruzione. Questa ricompilazione si verificherà per ogni esecuzione della stored procedure poiché l'inserimento di 1000 righe verrà sempre essere visualizzato come significativi abbastanza da giustificare la ricompilazione.

L'algoritmo utilizzato da SQL Server per determinare se un piano debba essere ricompilato è lo stesso utilizzato per l'aggiornamento automatico delle statistiche, descritto nel seguente articolo della Microsoft Knowledge Base:
195565INF: SQL Server 7.0 e SQL Server 2000 Autostats funzionamento
Nell'esempio riportato sopra, la stored procedure è sufficientemente piccola perché la ricompilazione non determini un impatto negativo sulle prestazioni. Tuttavia nel caso di una stored procedure con attività simili che causi più ricompilazioni, si potrà notare un degrado delle prestazioni.

I metodi descritti di seguito consentono di impedire la ricompilazione dettata dalla modifica delle righe:
  • Eseguire l'istruzione utilizzando sp_executesql .
    Questo è il metodo preferito. Le istruzioni eseguite utilizzando la stored procedure sp_executesql stored procedure non vengono compilate come parte del piano di stored procedure. Pertanto, quando si esegue un'istruzione, SQL Server sarà libero di utilizzare un piano esistente nella cache per l'istruzione oppure di creare un nuovo piano a runtime. In qualsiasi caso, il piano per la stored procedure che esegue la chiamata non verrà influenzato e non dovrà essere ricompilato.

    L'istruzione EXECUTE produce lo stesso effetto, sebbene non sia particolarmente consigliabile. Utilizzo di EXECUTE istruzione non è efficiente come l'utilizzo di sp_executesql poiché non consente di parametrizzazione della query.

    È possibile scrivere la procedura RowModifications indicata in precedenza utilizzare sp_executesql come indicato di seguito:

    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
    						

    Per la seconda esecuzione della procedura RowModifications2 , si saranno presenti i seguenti eventi in Profiler:

    Riduci questa tabellaEspandi questa tabella
    classe di evento testo
    SP: StartingRowModifications2
    SP: StmtStartingcreare la tabella # t (un int, char(10)) b
    SP: StmtStartingSelezionare * from # t
    SP: StmtStartingInserire # t selezionare * da SomeTable
    SP: StmtStartingExec sp_executesql N'select count(*) from # t in cui un = @ un ', n'@a int ', @ un = 37
    SP: Starting
    SP: StmtStartingSelezionare count(*) # t in cui un = @ un
    Auto-UpdateStatsun
    SP: StmtStartingSelezionare count(*) # t in cui un = @ un
    SP: Completed
    SP: CompletedRowModifications2


    Si noti che non vi sono Nessun evento SP: Recompile per la procedura RowModifications2 . Esistono completo SP: avvio per SP: completata eventi per la stored procedure sp_executesql chiamare il contesto e un evento Auto-UpdateStats per una colonna. Tuttavia, poiché questa chiamata è fuori del contesto della stored procedure, non è necessario la procedura RowModifications2 ricompilazione in questo caso.

    Per ulteriori informazioni sull'utilizzo della stored procedure sp_executesql stored procedure, vedere "sp_executesql (T-SQL)" e "Using sp_executesql" argomenti nella documentazione in linea di SQL Server.
  • Utilizzare sottoprocedure per eseguire le istruzioni che causano la ricompilazione.
    In questo caso l'istruzione causerà una ricompilazione, ma anziché ricompilare l'intera stored procedure, verrà ricompilata solo la sottoprocedura di dimensioni ridotte.
  • Utilizzare l'opzione KEEP PLAN.
    Tabelle temporanee contengono regole speciali relative al ricompilazioni che, in alcuni casi, possono essere più restrittivi rispetto l'algoritmo di ricompilazione predefinito. È possibile utilizzare l'opzione KEEP PLAN per reimpostare la soglia della tabella temporanea in base all'algoritmo predefinito. Per ulteriori informazioni in proposito, vedere la sezione "Utilizzo dell'opzione KEEP PLAN per evitare la ricompilazione" di questo articolo.
Nota: La procedura RowModifications è un esempio molto semplificato di una routine che viene ricompilata a causa di modifiche di riga. Si considerino i seguenti aspetti relativi a tale esempio:

  • Sebbene nell'esempio sia utilizzata una tabella temporanea, questa situazione si applica a stored procedure che fanno riferimento anche a tabelle permanenti. Se nella tabella è stata modificata una quantità di dati sufficiente da quando il piano della query è stato generato, la stored procedure verrà ricompilata. Le differenze in base a cui le tabelle temporanee vengono prese in considerazione per motivi di ricompilazione sono descritte nella sezione "Utilizzo dell'opzione KEEP PLAN per evitare la ricompilazione" di questo articolo.
  • Le prime esecuzioni delle due procedure riportate sopra causano una ricompilazione anche per la prima istruzione SELECT dalla tabella #t. Le ragioni di tale ricompilazione sono discusse nella sezione "Ricompilazioni dovute alla sovrapposizione di operazioni DDL (Data Definition Language) e operazioni DML (Data Manipulation Language)" di questo articolo.
  • In questo esempio è stata utilizzata un'istruzione "select count(*) from #t" anziché una semplice istruzione "select * from #t". Per evitare ricompilazioni eccessive, SQL Server non prende in considerazione la ricompilazione di piani di importanza relativa, quale ad esempio un'istruzione "select * from" da una tabella, a seguito della modifica delle righe.

Ricompilazioni dovute alla sovrapposizione di operazioni DDL (Data Definition Language) e operazioni DML (Data Manipulation Language)

Se in una procedura o in un batch vengono eseguite operazioni DDL, il batch o la procedura verrà ricompilata al verificarsi della prima operazione DML successiva che influisce sulla tabella coinvolta nell'operazione DDL.

Si consideri la seguente stored procedure di esempio:
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 si esegue questo codice in Query Analyzer e visualizzare gli eventi sopra di una traccia di Profiler, si noterà che la sequenza riportata di seguito:

Riduci questa tabellaEspandi questa tabella
classe di evento testo
SP: StartingInterfoliazione
SP: StmtStartingcreare la tabella t1 (int)
SP: StmtStartingSelezionare * from t1
SP: RecompileInterfoliazione
SP: StmtStartingSelezionare * from t1
SP: StmtStartingcreare indice idx_t1 su t1(a)
SP: StmtStartingSelezionare * from t1
SP: RecompileInterfoliazione
SP: StmtStartingSelezionare * from t1
SP: StmtStartingcreare la tabella t2 (int)
SP: StmtStartingSelezionare * da t2
SP: RecompileInterfoliazione
SP: StmtStartingSelezionare * da t2
SP: CompletedInterfoliazione


In questo caso la stored procedure viene ricompilata tre volte durante l'esecuzione. Per capire perché accade questo, si consideri in che modo l'utilità di ottimizzazione sviluppa un piano per questa stored procedure:
  1. Durante la compilazione iniziale della procedura, le tabelle t1 e t2 non esistono. pertanto non può essere creato alcun piano per le query che fanno riferimento a tali tabelle. Deve essere generati in fase di esecuzione.
  2. Come la routine viene eseguita per la prima volta, il primo passaggio consiste nel creare la tabella t1. Il passaggio successivo è una selezione dalla tabella t1, che non vi è alcun piano per. A questo punto pertanto la procedura viene ricompilata per sviluppare un piano per l'istruzione SELECT. Viene generato un piano per la selezione corrente dalla tabella t1, come la selezione dalla tabella t1 dopo la creazione dell'indice. È possibile generare alcun piano per la selezione da t2 perché t2 ancora a non esiste ancora.
  3. Il passaggio successivo consiste nel creare un indice nella tabella t1. Seguente, selezionare un altro viene eseguita su t1, che ora dispone di un piano della ricompilazione prima. Tuttavia, perché lo schema di t1 è stato modificato da quando tale piano è stato generato, la procedura necessario ricompilare nuovamente per generare un nuovo piano per la selezione da t1. E perché t2 non è presente, alcun piano non può essere generato per la selezione da t.
  4. Successivamente, viene creato il t2 tabella e viene eseguita la selezione da t2. Poiché non esiste alcun piano per l'istruzione, la procedura viene ricompilata un'ultima volta.
Queste ricompilazioni si verificano per ogni esecuzione della stored procedure. Per ridurre il numero delle ricompilazioni, modificare la procedura in modo che tutte le operazioni DDL vengano eseguite per prime e in seguito vengano eseguite tutte le operazioni DML, come illustrato di seguito:
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
				
La prima esecuzione della procedura NoInterleave visualizzerà i seguenti eventi di Profiler:

Riduci questa tabellaEspandi questa tabella
classe di evento testo
SP: StartingNoInterleave
SP: StmtStartingcreare la tabella t1 (int)
SP: StmtStartingcreare indice idx_t1 su t1(a)
SP: StmtStartingcreare la tabella t2 (int)
SP: StmtStartingSelezionare * from t1
SP: RecompileNoInterleave
SP: StmtStartingSelezionare * from t1
SP: StmtStartingSelezionare * from t1
SP: StmtStartingSelezionare * da t2
SP: CompletedNoInterleave


In questo caso tutte le istruzioni DDL vengono eseguite per prime. L'utilità di ottimizzazione compila la procedura nel modo seguente:
  1. Durante la compilazione iniziale della procedura, le tabelle t1 e t2 non esistono. pertanto non può essere creato alcun piano per le query che fanno riferimento a tali tabelle. Deve essere generati in fase di esecuzione.
  2. I passaggi primo che esegue la procedura sono il DDL operazioni, la creazione di tabelle t1 e t2, così come l'indice in t1.
  3. Il passaggio successivo è il primo selezionare t1. Poiché è non disponibile alcun piano per l'istruzione SELECT seguente, la procedura viene ricompilata. Poiché esistono tutti gli oggetti, i piani vengono generati per tutte le istruzioni SELECT presenti nella procedura in questo momento.
  4. Il resto della routine viene eseguita utilizzando i piani generati. Poiché non vengono apportate modifiche agli oggetti a cui si fa riferimento, la procedura non verrà ricompilata ulteriormente.
Nota: Il seconda e le successive esecuzioni rendere utilizzare il piano di query esistente e la cache e non si produrre le ricompilazioni affatto. Procedure per creare, modificano o eliminare tabelle devono essere modificate per garantire che tutte le istruzioni DDL collocate all'inizio della procedura.

Ricompilazioni dovute a operazioni con le tabelle temporanee

L'impiego di tabelle temporanee in una stored procedure può causare la ricompilazione della stored procedure ogni volta che questa viene eseguita.

Per evitare questo problema, modificare la stored procedure, in modo che soddisfi i requisiti seguenti:
  • Tutte le istruzioni che contengono il nome di una tabella temporanea fare riferimento a una tabella di temporanea creata nella stessa stored procedure e non in una stored procedure chiamante o chiamata o una stringa eseguita utilizzando l'istruzione EXECUTE stored procedure di istruzione o stored procedure sp_executesql .
  • Tutte le istruzioni che contengono il nome di una tabella temporanea devono essere riportate subito dopo la tabella temporanea nella stored procedure o nel trigger.
  • Non devono essere presenti istruzioni DECLARE CURSOR le cui istruzioni SELECT facciano riferimento a una tabella temporanea.
  • Tutte le istruzioni che contengono il nome di una tabella temporanea precedere qualsiasi istruzione DROP TABLE che faccia riferimento a una tabella temporanea.

    Le istruzioni DROP TABLE non sono necessarie per le tabelle temporanee create in una stored procedure. Le tabelle vengono eliminate automaticamente quando la procedura è completata.
  • Nessuna istruzione di creazione di una tabella temporanea (ad esempio CREATE TABLE o SELECT... INTO) presente in un'istruzione di controllo di flusso, ad esempio se... ELSE o WHILE.

Utilizzo dell'opzione KEEP PLAN per evitare le ricompilazioni

Le tabelle temporanee in una stored procedure comportano un certo grado di complessità per l'utilità di ottimizzazione delle query. Il conteggio delle righe e le informazioni statistiche possono variare notevolmente nel corso di vita della stored procedure. Per assicurare che l'utilità di ottimizzazione utilizzi il piano migliore in tutti i casi riguardanti tabelle temporanee, è stato sviluppato uno speciale algoritmo che impone limiti più rigidi alle ricompilazioni. Questo algoritmo specifica che se una tabella temporanea creata con una stored procedure viene modificata più di sei volte, la procedura verrà ricompilata la prima volta che l'istruzione successiva fa riferimento alla tabella temporanea.

Si consideri l'esempio riportato di seguito:
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
				
in questo caso, si saranno presenti i seguenti eventi in Profiler per la seconda esecuzione:

Riduci questa tabellaEspandi questa tabella
classe di evento testo
SP: StartinguseKeepPlan
SP: StmtStartingcreare la tabella # t (int)
SP: StmtStarting -I sette inserire istruzioni-
SP: StmtStartingSelezionare count(*) dalla tabella # t1
SP: RecompileuseKeepPlan
SP: StmtStartingSelezionare count(*) dalla tabella # t1
SP: CompleteduseKeepPlan

La procedura viene ricompilata in corrispondenza della prima istruzione SELECT che si verifica dopo che la tabella temporanea #t è stata modificata sette volte.

Questo criterio di ricompilazione risulta utile nei casi in cui le modifiche alla distribuzione dei dati della tabella temporanea possono influenzare drasticamente il piano della query ottimale per l'istruzione che vi fa riferimento. Tuttavia, nel caso di procedure di grandi dimensioni che modificano le tabelle temporanee molto frequentemente ma non in maniera significativa, la ricompilazione può determinare un degrado delle prestazioni. L'opzione KEEP PLAN dell'istruzione SELECT è stata introdotta proprio per queste situazioni.

L'opzione KEEP PLAN elimina le ricompilazioni della stored procedure causate da oltre 6 modifiche alle tabelle temporanee della procedura e ripristina i criteri dell'algoritmo predefinito per la ricompilazione dettata dalla modifica delle righe, descritto nella sezione precedente "Ricompilazioni dovute a modifiche di riga" di questo articolo. KEEP PLAN non impedisce ricompilazioni completamente, quelli causati da più di sei modifiche alle tabelle temporanee a cui fa riferimento la procedura impedisce semplicemente. Nell'esempio precedente, se si rimuove il commento dalla riga "option (KEEP PLAN)" nella stored procedure, l'evento SP: Recompile non verrà generato.

Rimuovendo il commento dalla riga "option (KEEP PLAN)" ed eseguendo il codice, verranno registrati i seguenti eventi in Profiler:

Riduci questa tabellaEspandi questa tabella
classe di evento testo
SP: StartinguseKeepPlan
SP: StmtStartingcreare la tabella # t (int)
SP: StmtStarting -I sette inserire istruzioni-
SP: StmtStartingSelezionare count(*) dall'opzione di tabella # t1 (KEEP PLAN)
SP: CompleteduseKeepPlan


Nota non che non esiste nessun evento SP: Recompile .

Ricompilazione a causa di alcuni SET di istruzioni eseguite in stored procedure

Le seguenti cinque opzioni SET sono impostate su ON per impostazione predefinita:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Se si esegue l'istruzione SET per una di queste opzioni è impostata su OFF, la stored procedure verrà ricompilata ogni volta che viene eseguito. Il motivo è che la modifica di queste opzioni potrebbe influenzare il risultato della query che ha attivato la ricompilazione.

Si consideri il codice di esempio riportato di seguito:
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
				
in questo caso, si saranno presenti i seguenti eventi in SQL Profiler per ogni esecuzione di stored procedure:
+---------------------------------------------------+
| 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                  | 
+---------------------------------------------------+
				
sostituzione l'opzione SET con uno qualsiasi dei cinque opzioni elencate visualizzeranno i risultati stessi. Utilizzando l'opzione del piano di mantenere qui non consentono inoltre di evitare la ricompilazione in quanto la causa della ricompilazione è dall'istruzione SET.

Di per evitare la ricompilazione è consigliabile non utilizzare uno dei seguenti cinque istruzione SET in una stored procedure. Per ulteriori informazioni, vedere il seguente articolo della Microsoft Knowledge Base riportato di seguito:
294942PRB: SET CONCAT_NULL_YIELDS_NULL causati stored procedure per la ricompilazione
Tuttavia, non consigliabile, in esecuzione il SET istruzione per reimpostare l'opzione di connessione lo stesso valore della stored procedure, inoltre possibile evitare la ricompilazione, farlo come:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
traccia di SQL Profiler non verrà visualizzato nessun altro evento SP: Recompile.

Nella tabella seguente sono elencate alcune comuni istruzioni SET e o meno il modifica l'istruzione SET in una stored procedure provoca la ricompilazione:
Riduci questa tabellaEspandi questa tabella
Istruzione setRicompilare
Set quoted_identifierNo
Set arithabort
Set ansi_null_dflt_on
Set ansi_defaults
Set ansi_warnings
Set ansi_padding
Set concat_null_yields_null
Set numeric_roundabortNo
Set nocountNo
Set rowcountNo
Set xact_abortNo
Set implicit_transactionsNo
Set arithignoreNo
Set lock_timeoutNo
Set fmtonlyNo

Riferimenti

308737INF: How identificare la causa della ricompilazione in un evento SP: Recompile

Per informazioni sull'utilizzo di SQL Server Profiler, vedere la documentazione in linea di SQL Server.

Proprietà

Identificativo articolo: 243586 - Ultima modifica: venerdì 2 novembre 2007 - Revisione: 2.5
Le informazioni in questo articolo si applicano 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
Chiavi: 
kbmt kbinfo KB243586 KbMtit
Traduzione automatica articoli
Il presente articolo è stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non è sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, più o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non è la sua. Microsoft non è responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 243586
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

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