Riešenie problémov s uloženej procedúry recompilation

Preklady článku Preklady článku
ID článku: 243586 - Zobraziť produkty, ktorých sa tento článok týka.
Rozbaliť všetko | Zbaliť všetko

Na tejto stránke

SUHRN

Tento článok sa zaoberá iba určitý typ výkon vydanie, ktoré aplikácie môžu vyskytnúť s Microsoft SQL Server: run-time recompilation uložené procedúry. Ak riešite výkonom problém, ale môžete mať nie stanoviť, že to je presný zdroj vášho problém, označovať nasledujúci článok v databáze Microsoft Knowledge Base pred konanie:

224587 AKO: Riešenie uplatňovanie výkonu s SQL Server
Tento článok predpokladá, že ste použili že článok na zúžiť rozsah problému, a že ste zachytili SQL Server Profiler stopových s špecifických udalostí a údaje stĺpcov podrobné v rámci nej.

DALSIE INFORMACIE

Keď používateľ spustí uloženej procedúry, ak nie je už je k dispozícii v cache, SQL Server načíta postupom a zostavuje dotaz plán. Kompilovaný plán je uložená vo vyrovnávacej pamäti a je opätovne následné volajúcich uložené postupu, kým nejakú akciu vyskytne narúšali plánu a platnosť recompilation. Tieto akcie môžu spôsobiť recompilation z uložená procedúra plán:
  • Použitie S REKOMPILOVAT doložky v postupe vytvoriť alebo VYKONAŤ vyhlásenie.
  • Schéma zmien odkazovaný predmety, vrátane pridaním alebo kvapkadlová obmedzenia, predvolené nastavenia alebo pravidlami.
  • Spustené sp_recompile pre tabuľku uvádzaný postupom.
  • Obnoviť databázu, ktorá obsahuje postup alebo akýmkoľvek objekty postup odkazy (Ak vykonávate kríž-databáza operácie).
  • Dostatočné aktivite servera, ktoré spôsobuje plánu, ktoré majú dozrievať vyrovnávacej pamäte.
Zo všetkých týchto dôvodov pre prelinkovania uloženej procedúry urobil existujú v starších verziách, a spôsobil plánu prekompilovať pred začiatkom vykonanie postupu. V SQL Server 7.0, je zavedené nové správanie môžu spôsobiť uloženej procedúry prekompilovať počas realizácie. Toto nové správanie zabezpečuje, že optimalizácia má vždy najlepšie možné plánu pre každého osobitné vyhlásenie v rámci postupu. Nasledujúcich udalostí môže spôsobiť run-time recompilation uloženej procedúry:
  • Dostatočné percento zmeny údajov v tabuľke, ktorá je odkazuje uloženej procedúry.
  • Postup interleaves údajov Definition Language (DDL) a údaje Manipulation Language (DML) operácie.
  • Postup vykonáva určité operácie o dočasnom tabuľky.
Každý z týchto príčin je podrobne diskutovalo ďalej v tomto článok.

V niektorých prípadoch náklady na prelinkovania uloženej procedúry je viac než odvodeného z pritom najmä pre veľké postupy. Je veľmi dôležité, že keď recompilation spustí, celý šarže alebo postup je prekompilovali. To znamená, že výkon degradácie je priamo úmerné veľkosti postup alebo dávky. Pre Ďalšie informácie o tejto téme, pozrite si tému "Transact-SQL Tipy" v SQL Server Books Online.


Tieto informácie v tomto článku sa zameriava na identifikujúci príčinu run-time uložené postup recompilations a rozoberá metódy môžete používať na zabránenie ich.

Osvedčených postupov

Je to najlepšie vlastník kvalifikovať uloženej procedúry názvy, keď ste vykonať postup. To umožňuje lepšiu prehľadnosť a ľahšie opätovné využitie existujúce vykonávania plánu aktuálnym používateľom. Napríklad, ak používateľ, ktorý nie je vlastník databázy (dbo) popravených dbo-vlastnené uloženej procedúry (nazývané myProc v tomto príklade) v krčmy databázy, použite nasledujúce vyhlásenie:
exec dbo.myProc
				
Namiesto toho:
exec myProc
				
Táto technika eliminuje zámeny o iných možných verziách postup rôznych vlastníkov z hľadiska kódovanie a údržby a tiež umožňuje SQL Server prístup vykonávania plánu pre konkrétny postup viac priamo.

Autor: nekvalifikovali meno majiteľa, vstúpi do SQL Server skompilovať kód a získava SKOMPILOVAŤ zámok na postup. Avšak, Nakoniec to určuje, že nový plán sa nevyžaduje (za predpokladu žiadne iné dôvody neplatia), takže nie rekompilovat plánu na tomto mieste kvôli nedostatku kvalifikácie. Avšak ďalší krok dostať SKOMPILOVAŤ zámok na postup môže spôsobiť blokovanie tvrdenie v ťažkej situácie. Byť Q263889 INF: SQL blokovanie kvôli zámky [[SKOMPILOVAŤ]] Ďalšie podrobnosti o tejto situácii.

Ak ste vlastníkom kvalifikovať volanie procedúry s owner.procedure, môžete urobiť nie je potrebné získať skompilovať zámku, takže tvrdenie je znížená.

Identifikácia a riešenie problémov

Ak ste tak ešte neurobili, pozrite článok v databáze Microsoft Knowledge Base podrobnosti o zachytenie Profiler údajov pomôcť analyzovať výkon vášho systému:
224587 AKO: Riešenie uplatňovanie výkonu s SQL Server

Zobraziť údaje Profiler

SQL Server Profiler zahŕňa SP:Recompile udalostí, ktoré môžete použiť na sledovanie počtu recompiles vyskytujúce sa. V SP:Recompile udalosť nastane kedykoľvek uloženej procedúry recompiles počas vykonanie.
  • Skupina Profiler stopových udalosť triedy

    1. Na Súbor ponuky, kliknite na tlačidlo Vlastnosti.
    2. Na Stĺpce údajov TAB, použite tlačidlo UP presunúť Trieda udalostí a Text podľa Groups položky, s Trieda udalostí vytlačíte. Pomocou tlačidla dole odstrániť všetky ostatné stĺpce podľa Groups položka.
    3. Kliknite na položku ok.
    Skontrolujte si ?íslo SP:Recompile udalosti.

    Môžete rozšíriť skupine SP:Recompile vidieť Podrobnosti o jednotlivých výskytov. V Text stĺpec udalosti udáva názov uloženej procedúry že prekompilovali. Ak viaceré postupy spôsobujú recompiles, sú zoradené podľa počtu výskytov. Ak máte veľký počet SP:Recompile udalosti a budete sa stretli zaťažuje Procesor, zameranie sa na riešenie postupov, ktoré majú najvyšší počet recompiles. Poznámka systém proces ID (SPID) a začať čas SP:Recompile udalosť pre jednu inštanciu najmä uložené postupy a postupujte podľa nižšie uvedených krokov.

    Ak nevidíte žiadne SP:Recompile udalostí, ale sú stále zažívajú problému s výkonom, pozri nasledujúci článok v databáze Microsoft Knowledge Base:
    224587 AKO: Riešenie uplatňovanie výkonu s SQL Server
  • Určenie vyhlásenie, že spustí Recompile udalosť

    1. Na Súbor ponuky, kliknite na tlačidlo Vlastnosti.
    2. Na Stĺpce údajov TAB, použite tlačidlo nadol odstrániť všetky ostatné stĺpce podľa Groups položka.
    3. Na Udalosti kartu, odstrániť všetky udalosti s výnimkou PS: spustenie, SP:StmtStarting, SP:Recompile, a PS: dokončené. Ak ste urobil nie zachytiť SP:StmtStarting udalosti, ktorou môžete nahradiť SP:StmtCompleted, ale nezahŕňajú oboje, pretože robí tak štvorhra na výšku informácie, ktoré budete musieť pozrieť.
    4. Ak ste zistili inštancia uložené postupom recompilation preskúmať, môžete obmedziť údaje môžete zobraziť na osobitné číslo SPID a časový rámec na výskyt pomocou Filtre kartu.
    5. Kliknite na položku ok.

    V SP:Recompile udalosť sa bude riešiť priamo po SP:StmtStarted udalosť výkazu uložená procedúra, ktorá spôsobila recompilation. Po dokončení je recompile udalosti, uvidíte opakovaním The SP:StmtStarted udalosti naznačujúce, že vyhlásenie je vykonávajúci s novo generované plánu.

    Zvážte nasledovný príklad:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Ak ste spustiť tento kód v analyzátor dotazu a zobraziť uvedené udalosti v Profiler stopy, uvidíte v tomto poradí:

    Zbaliť túto tabuľkuRozbaliť túto tabuľku
    Trieda udalostíText
    PS: spustenieRecompProc
    SP:StmtStartingvytvorenie tabuľky # t (int)
    SP:StmtStartingvyberte * z # t
    SP:RecompileRecompProc
    SP:StmtStartingvyberte * z # t
    PS: dokončenéRecompProc


    Môžete povedať hneď, že vyhlásenie, spôsobené recompilation bol:
    select * from #t
    						
    Pretože sa javí ako pred a po SP:Recompile udalosť.

    Ak ste mal zachytené iba SP:StmtCompleted udalosť, ale nie SP:StmtStarting udalosti, SP:Recompile bude zobrazovať priamo pred výkaz, ktorý spôsobil ho ako nižšie:

    Zbaliť túto tabuľkuRozbaliť túto tabuľku
    Trieda udalostíText
    PS: spustenieRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedvyberte * z # t
    PS: dokončenéRecompProc


    Môžete vidieť, že SP:Recompile udalosť pripomienka pred SP:StmtCompleted udalosť pre "vyberte * z # t" vyhlásenie, ktoré spôsobil recompilation. To dáva zmysel, ako výkaz nemôžu byť ukončené do po nový dotaz plán je generovaný pre recompile. Všetky ostatné Príklady v tomto článku použitie SP:StmtStarting udalosť. Ak ste zachytili iba SP:StmtCompleted udalosť, len nezabudnite zobraziť vyhlásenie po SP:Recompile, ako je vysvetlené vyššie.

    Všimnite si, že ak spustíte tento najmä uložená procedúra viackrát, SQL Server budú opätovné použitie existujúcich plán pre tento postup. Recompile podujatie bude vidieť iba na prvý vykonanie konania, alebo ak ste kvapka a znova vytvoriť postup každej čas môžete spustiť skript. Dôvodom pre recompilation v tomto konkrétnom prípad sa diskutuje v "Recompilations dôsledku prekladania údajov definícii Jazyk (DDL) a údaje manipulácia Language (DML) operácie"časti tejto článku; to je jednoducho príklad pre ilustráciu, ako ľahko určiť, ktoré vyhlásenie spôsobuje recompilation.

Recompilations kvôli riadok modifikácie

Ak dostatočné percentuálne údaje zmenila v tabuľke odkazuje uloženej procedúry od doby pôvodný plán dotaz bol generuje, server SQL Server bude rekompilovat uloženej procedúry na zabezpečenie toho, že má plán na základe najnovších štatistických údajov. Ako príklad, zvážiť nasledujúce uložená procedúra:
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
				
Na druhom vykonanie RowModifications postup, uvidíte nasledujúcich udalostí v Profiler:

Zbaliť túto tabuľkuRozbaliť túto tabuľku
Trieda udalostíText
PS: spustenieRowModifications
SP:StmtStartingvytvorenie tabuľky # t (int, b char(10))
SP:StmtStartingvyberte * z # t
SP:StmtStartingVložiť # t vyberte * z SomeTable
SP:StmtStartingVyberte count(*) z # t ak = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingVyberte count(*) z # t ak = 37
PS: dokončenéRowModifications

POZNÁMKA: Prvú popravu zobrazí aj SP:Recompile udalosť pre "vyberte * z # t" vyhlásenie. Dôvodom pre recompilation v tomto špecifickom prípade sa diskutuje v "Recompilations náležitým do Prekladania údajov Definition Language (DDL) a údaje manipulácia jazyk (DML) operácie"časti tohto článku. Pre tento príklad zamerať na SP:Recompile uvedené vyššie, pretože sa vyskytne zakaždým, keď sa postup popravený.

V tomto príklade „vyberte count(*) z # t ak = 37 " spôsobuje recompilation konania kvôli zmene počtu riadkov Vzhľadom k tomu, že tabuľka bola vytvorená. Prítomnosť Auto-UpdateStats udalosť potvrdzuje, že recompilation bol kvôli riadok modifikácie. V Text stĺpec označuje stĺpec, pre ktoré boli štatistiky modifikované.

Pri vytvorení tabuľky # t počet riadkov je nula. Plán pre pôvodné "vyberte * z # t" je vyvíjaný s riadok počítať, ako aj plán na dotaz "vyberte hrabě (*)". Avšak, pred "vyberte count(*)" je popravený, 1000 nových riadkov sa vkladajú do tabuľky # t. Pretože dostatočné množstvo údajov bol zmenený, optimalizácia recompiles postupu na zabezpečenie, že rozhodne čo najefektívnejšie plán vyhlásenie. Táto recompilation bude uskutočňovať na každý výkon uložené postup pretože vloženia 1000 riadkov bude vždy vnímať ako dostatočne významný rozkaz recompilation.

Algoritmus SQL Server používa na určenie, či by rekompilovaný plán je rovnaký algoritmus používané na auto aktualizácia štatistiky, ako je popísané v nasledujúcom článku v Microsoft Knowledge Base:
195565 INF: Ako SQL Server 7.0 a SQL Server 2000 Autostats prácu
Vo vyššie uvedenom príklade je malý uloženej procedúry natoľko, že recompilation by nemali mať výrazný vplyv na výkon. Avšak, ak máte veľké uloženej procedúry, ktorá vykonáva podobné činnosti výsledkom viac recompilations, môžete si všimnúť výkon degradácia.

Tieto metódy existujú umožňujúce čeliť recompilations kvôli riadok modifikácie:
  • Spustiť pomocou vyhlásenie sp_executesql.
    Toto je uprednostňovanou metódou. Výkazy programovateľnostisp_executesql uložená procedúra sa nezostavujú ako súčasť uloženej procedúry plán. Preto, keď vykonávajúci vyhlásenie, server SQL Server bude môcť použiť existujúci plán vo vyrovnávacej pamäti pre výkaz alebo vytvoriť nový na Doba spracovania. V každom prípade je plán pre volanie uloženej procedúry nedotknuté a nemá prekompilovať.

    VYKONAŤ vyhlásenie bude mať rovnaký účinok; však neodporúča. Pomocou spustiť vyhlásenie nie je rovnako efektívne ako pomocou sp_executesql pretože to neumožňuje pre parametrizáciou dotaz.

    V RowModifications postup uvedených vyššie môže byť napísaný na použitie sp_executesql takto:

    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
    						

    Na druhom vykonanie RowModifications2 postup, uvidíte nasledujúcich udalostí v Profiler:

    Zbaliť túto tabuľkuRozbaliť túto tabuľku
    Trieda udalostíText
    PS: spustenieRowModifications2
    SP:StmtStartingvytvorenie tabuľky # t (int, b char(10))
    SP:StmtStartingvyberte * z # t
    SP:StmtStartingVložiť # t vyberte * z SomeTable
    SP:StmtStartingExec sp_executesql N'select Count(*) z # t ak = @ ", N'@a int", @ = 37
    PS: spustenie
    SP:StmtStartingVyberte count(*) z # t ak = @
    Auto-UpdateStatsa
    SP:StmtStartingVyberte count(*) z # t ak = @
    PS: dokončené
    PS: dokončenéRowModifications2


    Oznámenie, že neexistujú žiadne SP:Recompile udalosti pre RowModifications2 . Nie sú úplné PS: spustenie na PS: dokončené udalosti pre sp_executesql volať kontext, a Auto-UpdateStats udalosť pre stĺpec a. Avšak, pretože táto výzva je mimo kontexte uložené postup, RowModifications2 postup nie je potrebné prekompilovať v tomto prípade.

    Ďalšie informácie o používaní sp_executesql uložená procedúra, pozri „sp_executesql (T-SQL)"a"Using sp_executesql"témy v zdroji SQL Server Books Online.
  • Použitie sub-procedures na vykonanie vyhlásenia, ktoré sú spôsobuje recompilations.
    V tomto prípade stále môže spôsobiť vyhlásenie recompilation, ale namiesto toho prelinkovania veľké volanie uloženej procedúry, to bude len rekompilovat malé sub-procedure.
  • Použite možnosť zachovať plánu.
    Dočasné tabuľky osobitné pravidlá týkajúce sa recompilations, čo v niektorých prípadoch môže byť viac prísne ako predvolené recompilation algoritmus. Môžete použiť udržať plánu možnosť odpočinúť dočasná tabuľka prah späť na predvolené algoritmus. Ďalšie informácie nájdete "vyhnúť Recompilation pomocou udržať plánu Možnosť"časť tohto článku.
POZNÁMKA: V RowModifications postup je veľmi zjednodušený príklad postupu, ktorý je prekompilovali kvôli riadok modifikácie. Prečítajte nasledujúce upozornenia pokiaľ ide o tento príklad:

  • Hoci tento príklad používa dočasná tabuľka, táto situácia vzťahuje na uložené procedúry, ktoré odkazujú na trvalé tabuliek rovnako. Ak dostatočné množstvo údajov v odkazovanej tabuľka zmenila od dotaz plán bol vygenerovaný, bude rekompilovaný uloženej procedúry. V rozdiely v ako dočasným tabuľkám považujú na účely recompilation sú opísané v "vyhnúť sa Recompilation prostredníctvom pomocou udržať plán alternatívu" časti v tomto článku.
  • Prvý popravy vyššie uvedených dvoch postupov tiež spôsobiť recompilation na prvý vybrať z # t dočasná tabuľka. Dôvody pre tento recompilation sú diskutované v "Recompilations dôsledku prekladania Údaje Definition Language (DDL) a údaje manipulácia Language (DML) operácie" časti v tomto článku.
  • "Vyberte count(*) z # t" vyhlásenie bolo použité v tomto napríklad, skôr ako jednoduchý "vyberte * z # t" vyhlásenie. Aby sa zabránilo nadmernému recompilations, SQL Server nepovažuje za prelinkovania "triviálne plány" (napríklad ako vyberte * z tabuľky) kvôli riadok modifikácie.

Recompilations kvôli prekladania údajov Definition Language (DDL) a manipuláciu dát Language (DML) operácie

Ak DDL operácie sa vykonávajú v rámci postupu alebo dávky, postup alebo dávky je prekompilovali pri vyskytnutí prvom následné DML operáciám, ktoré ovplyvňujú zapojených do DDL tabuľky.

Zvážiť nasledujúceho príkladu uložená procedúra:
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
				
Ak ste spustiť tento kód v analyzátor dotazu a zobraziť uvedené udalosti v Profiler stopy, uvidíte v tomto poradí:

Zbaliť túto tabuľkuRozbaliť túto tabuľku
Trieda udalostíText
PS: spusteniePrekladať
SP:StmtStartingvytvorenie tabuľky t1 (int)
SP:StmtStartingvyberte * z t1
SP:RecompilePrekladať
SP:StmtStartingvyberte * z t1
SP:StmtStartingvytvoriť index idx_t1 na t1(a)
SP:StmtStartingvyberte * z t1
SP:RecompilePrekladať
SP:StmtStartingvyberte * z t1
SP:StmtStartingvytvorenie tabuľky t2 (int)
SP:StmtStartingvyberte * z t2
SP:RecompilePrekladať
SP:StmtStartingvyberte * z t2
PS: dokončenéPrekladať


V tomto prípade je prekompilovali uloženej procedúry trikrát počas realizácie. Pochopiť, prečo sa to stane, zvážiť, ako sa Optimalizácia rozvíja plán pre tento uloženej procedúry:
  1. Počas počiatočného zostavenie postupu t1 tabuliek a t2 neexistujú. Preto žiadny plán pre dotazy týchto odkazov tabuľky je možné vytvoriť. Musia vzniknúť v čase vykonania.
  2. Ako postup popravených prvýkrát, prvý krok je vytvoriť tabuľku t1. Ďalším krokom je vybrať z tabuľky t1--ktoré Neexistuje plán pre. Preto sa postup prekompilovali v tejto chvíli sa vypracovať plán na príkaze SELECT. Plán je generovaný súčasnej Vyberte t1, rovnako ako výber z t1 po vytvorení registra. Žiadny plán môže byť vytvorené pre vyberte zo t2, pretože t2 stále neexistuje ešte.
  3. Ďalší krok je vytvoriť index t1. Po že iný výber je vykonávaná na t1, ktoré má v súčasnosti plán od prvého Rekompilovat. Avšak, pretože schémy t1 sa zmenilo od tohto plánu bol vytvorený, postup musí rekompilovaný znova generovať nový plán pre výber z t1. A pretože t2 stále neexistuje, môže byť žiadny plán generované pre výber z t.
  4. Ďalej sa vytvorí tabuľka t2 a vyberte zo t2 je popravený. Pretože neexistuje žiadny plán pre vyhlásenie, postup je prekompilovali konečné čas.
Tieto recompilations vyskytujú na každý výkon k ulo enému . Znížiť recompilations, modifikovať postup robiť všetky DDL operácie po prvé, nasleduje DML operácií, ako je to znázornené na nasledujúcom:
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
				
Prvý vykonanie NoInterleave postup bude zobrazovať nasledujúcich udalostí v Profiler:

Zbaliť túto tabuľkuRozbaliť túto tabuľku
Trieda udalostíText
PS: spustenieNoInterleave
SP:StmtStartingvytvorenie tabuľky t1 (int)
SP:StmtStartingvytvoriť index idx_t1 na t1(a)
SP:StmtStartingvytvorenie tabuľky t2 (int)
SP:StmtStartingvyberte * z t1
SP:RecompileNoInterleave
SP:StmtStartingvyberte * z t1
SP:StmtStartingvyberte * z t1
SP:StmtStartingvyberte * z t2
PS: dokončenéNoInterleave


V tomto prípade všetko DDL závierky urobiť až vpredu. Optimalizácia zostavuje tento postup takto:
  1. Počas počiatočného zostavenie postupu t1 tabuliek a t2 neexistujú. Preto žiadny plán pre dotazy týchto odkazov tabuľky je možné vytvoriť. Musia vzniknúť v čase vykonania.
  2. Prvé kroky postupu vykonáva sú DDL operácií, vytváranie tabuliek t1 a t2, rovnako ako index na t1.
  3. Ďalším krokom je prvý vyberte zo t1. Pretože existuje žiadny plán k dispozícii pre tento Select, postupom, ktorý je prekompilovali. Pretože všetky objekty existujú, plány sú generované pre všetky vyberte vyhlásenia v postupe v tejto dobe.
  4. Zvyšku pracovného postupu popravených pomocou plány generované. Pretože neexistujú žiadne zmeny na odkazovanú objekty, nie je žiadny musieť prekompilovať ďalšieho postupu.
POZNÁMKA: Druhej a nasledujúcich popravy urobiť použitie existujúcich dotaz plán a cache a nemajú za následok akékoľvek recompilations vôbec. Postupy, ktoré vytvoriť, zmeniť alebo kvapka tabuliek by sa mali upraviť tak, aby všetky DDL výkazy sú umiestnené na začiatku postupu.

Recompilations kvôli určité dočasné tabuľky operácie

K dočasným tabuľkám v uloženej procedúre môžu spôsobiť uložená procedúra prekompilovať zakaždým, keď sa postup popravený.

Ak tomu chcete zabrániť, zmeniť uloženej procedúry tak, aby spĺňal nasledujúce požiadavky:
  • Všetky vyhlásenia, ktoré obsahujú názov dočasná tabuľka byť dočasná tabuľka vytvorené rovnaké uloženej procedúry, a nie v volanie alebo nazýva uloženej procedúry alebo v reťazci programovateľnosti spustiť vyhlásenie alebo sp_executesql uložená procedúra.
  • Všetky vyhlásenia, ktoré obsahujú názov dočasná tabuľka zobrazí syntakticky po dočasná tabuľka vo uloženej procedúry alebo spúšťač.
  • Neexistujú žiadne VYHLÁSIŤ kurzor vyhlásenia, ktorého vyberte výkazy referenčné dočasná tabuľka.
  • Všetky vyhlásenia, ktoré obsahujú názov akékoľvek dočasné tabuľky predchádzať akejkoľvek DROP TABLE vyhlásenie, že odkazy dočasná tabuľka.

    DROP TABLE závierky nie sú potrebné pre dočasné tabuľky vytvorené v uložené . V tabuľkách sú automaticky spadol, keď postup má dokončiť.
  • Žiadne vyhlásenie vytvára dočasná tabuľka (napríklad vytvoriť Tabuľka alebo vyberte... DO) sa zobrazia v toku riadenia vyhlásenie ako IF... INÉHO alebo KÝM.

Vyhnúť Recompilation pomocou možnosti PONECHAŤ plán

Dočasná tabuľka použitie v rámci uložené procedúry zavádza určité komplexnosť pre optimalizáciu dotazu. Riadku a štatistické informácie tabuľky môže výrazne líšiť počas celého života k ulo enému postup vykonania. Aby sa zabezpečilo, že optimalizácia používa najlepšie plán vo všetkých prípady týkajúce sa dočasným tabuľkám, špeciálne algoritmus bol vyvinutý viac agresívny s recompilations. Algoritmus, ktorý uvádza, že ak dočasná tabuľka vytvorené s uloženej procedúry sa zmenil viac ako šesťkrát, postup bude rekompilovaný pri ďalšom vyhlásenie odkazov dočasná tabuľka.

Zvážte nasledovný príklad:
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
				
V tomto prípade uvidíte nasledujúcich udalostí v Profiler pre druhý vykonávanie:

Zbaliť túto tabuľkuRozbaliť túto tabuľku
Trieda udalostíText
PS: spustenieuseKeepPlan
SP:StmtStartingvytvorenie tabuľky # t (int)
SP:StmtStarting -Sedem vložiť vyhlásenia-
SP:StmtStartingVyberte count(*) z # t1
SP:RecompileuseKeepPlan
SP:StmtStartingVyberte count(*) z # t1
PS: dokončenéuseKeepPlan

Postup je prekompilovali na vybrať, ktorý sa vyskytuje po siedmich zmeny # t dočasná tabuľka.

Tomto agresívne recompilation je užitočná v prípadoch, kde sa zmení na rozdelenie údajov dočasnej tabuľky môže výrazne ovplyvniť optimálne dotaz plán pre výkaz odkazujúcich na to. Avšak z veľkých postupy, ktoré upravovať dočasné tabuľky často, ale nie v významným spôsobom, recompilations môže mať za následok pomalší celkový výkon. Možnosť zachovať plán v príkaze SELECT bola zavedená v tejto situácii.

MAJTE PLÁN eliminuje uloženej procedúry recompilations spôsobené viac ako šesť zmeny dočasné tabuľky v rámci postupu a vráti späť k štandardu algoritmus na recompilation kvôli riadok modifikácie hovorilo v "Recompilations kvôli riadok úpravy" časť tohto článku. MAJTE PLÁN nebráni recompilations úplne, jednoducho zabraňuje spôsobených viac ako šesť zmeny k dočasným tabuľkám uvádzaný v poriadku. V príklad vyššie, ak ste odstrániť komentár z riadka "možnosť (PONECHAŤ plán)" v uložená procedúra SP:Recompile udalosť sa negeneruje.

Ak odstránite komentár z "možnosť (PONECHAŤ plán)" linka v horeuvedenom kóde a spustite ho, budete pozri nasledujúcich udalostí v Profiler:

Zbaliť túto tabuľkuRozbaliť túto tabuľku
Trieda udalostíText
PS: spustenieuseKeepPlan
SP:StmtStartingvytvorenie tabuľky # t (int)
SP:StmtStarting -Sedem vložiť vyhlásenia-
SP:StmtStartingVyberte count(*) z # t1 možnosť (PONECHAŤ PLÁN)
PS: dokončenéuseKeepPlan


Poznámka: nie je žiadny SP:Recompile udalosť.

Recompilations kvôli určitých nastaviť závierky popravený v uloženej procedúry

Nasledujúcich päť SET možností sú nastavené na predvolené:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Ak spustíte SET vyhlásenie nastaviť tieto možnosti na OFF, uložená procedúra bude prekompilovali zakaždým, keď beží. Dôvodom pre to je, že meniace sa tieto možnosti môžu ovplyvniť výsledok dotazu, ktorá vyvolala recompilation.

Zvážte nasledujúce vzorového kódu:
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
				
V tomto prípade uvidíte nasledujúcich udalostí v SQL Profiler pre každý realizáciu uloženej procedúry:
+---------------------------------------------------+
| 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                  | 
+---------------------------------------------------+
				
Nahradenie nastaviť možnosť jeden z piatich možností uvedených vyššie vám ukáže rovnaké výsledky. Tiež pomocou možnosť ponechať plán tu nebude pomôcť vyhnúť sa recompilation, pretože je príčinou recompilation z SET vyhlásenia.

Odporúčaný spôsob, ako zabrániť recompilation nie je použitie ktorejkoľvek z týchto piatich SET vyhlásenie v uložené . Pre ďalšie informácie pozri článok v Microsoft Knowledge Base:
294942 PRB: SET CONCAT_NULL_YIELDS_NULL môže spôsobiť uložené postupy na Recompile
Avšak, ako neodporúča, spustený súbor vyhlásenie na obnovenie pripojenia možnosť na rovnakú hodnotu ako je uložená postup, to môže tiež vyhnúť recompile robí to ako:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
SQL Profiler stopa sa zobrazia ďalšie SP:Recompile udalosti.

Nasledujúca tabuľka uvádza niektoré spoločné SET výkazy a či alebo nie zmenou SET vyhlásenie v uloženej procedúry spôsobuje recompile:
Zbaliť túto tabuľkuRozbaliť túto tabuľku
Nastaviť vyhlásenieRekompilovat
Set quoted_identifierNie
Set arithabortYes
Set ansi_null_dflt_onYes
Set ansi_defaultsYes
Set ansi_warningsYes
Set ansi_paddingYes
Set concat_null_yields_nullYes
Set numeric_roundabortNie
Set nocountNie
Set rowcountNie
Set xact_abortNie
Set implicit_transactionsNie
Set arithignoreNie
Set lock_timeoutNie
Set fmtonlyNie

Odkazy

308737 INF: Ako identifikovať príčinu Recompilation v SP:Recompile prípade

Ďalšie informácie o používaní servera SQL Server Profiler, pozri SQL Server Books Online.

Vlastnosti

ID článku: 243586 - Posledná kontrola: 23. októbra 2011 - Revízia: 2.0
Informácie v tomto článku sa týkajú nasledujúcich produktov:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Kľúčové slová: 
kbinfo kbmt KB243586 KbMtsk
Strojovo preložené
DÔLEŽITÉ: Tento článok bol preložený pomocou softvéru na strojový preklad od spoločnosti Microsoft, nie prekladateľom. Spoločnosť Microsoft ponúka články preložené prekladateľmi aj strojovo preložené články, vďaka čomu máte možnosť prístupu ku všetkým článkom databázy Knowledge Base vo svojom jazyku. Strojovo preložený článok však nie je vždy perfektný. Môže obsahovať chyby týkajúce sa slovnej zásoby, syntaxe alebo gramatiky, podobne ako cudzinec môže robiť chyby, keď rozpráva vašim jazykom. Spoločnosť Microsoft nenesie zodpovednosť za akékoľvek nepresnosti, chyby alebo škody spôsobené akýmkoľvek nepresným prekladom obsahu alebo jeho použitím zo strany zákazníkov. Spoločnosť Microsoft softvér na strojový preklad pravidelne aktualizuje.
Pokiaľ chcete vidieť anglickú verziu článku, kliknite sem:243586

Odošlite odozvu

 

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