Rozwiązywanie problemów z ponowną kompilacją procedury przechowywania

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 243586 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

Streszczenie

W tym artykule omówiono konkretny rodzaj problemu z wydajnością, który może wystąpić w aplikacji korzystającej z programu Microsoft SQL Server: rekompilacja procedur przechowywanych w czasie wykonywania. W wypadku rozwiązywania problemów z wydajnością przy nieokreślonym dokładnie źródle problemu, przed kontynuowaniem należy zapoznać się z następującym artykułem z bazy wiedzy Microsoft Knowledge Base:

224587 JAK: Rozwiązywanie problemów z wydajnością programu SQL Server
W tym artykule przyjęto założenie, że użytkownik zapoznał się z powyższym artykułem w celu zawężenia zasięgu problemu i przechwycił śledzenie programu SQL Server Profiler zawierające konkretne zdarzenia oraz kolumny danych wskazane w powyższym artykule.

Więcej informacji

Gdy użytkownik wykonuje procedurę przechowywaną, która jeszcze nie jest obecna w pamięci podręcznej, program SQL Server ładuje tę procedurę i kompiluje plan kwerend. Skompilowany plan jest zapisywany w pamięci podręcznej i używany w kolejnych wywołaniach procedury przechowywanej do czasu wystąpienia akcji, która unieważni plan i wymusi ponowną kompilację. Następujące akcje mogą spowodować ponowną kompilację planu procedury przechowywanej:
  • Użycie klauzuli WITH RECOMPILE w instrukcji CREATE PROCEDURE lub EXECUTE.
  • Zmiany schematu w dowolnym z obiektów, do których istnieją odwołania, w tym dodanie lub usunięcie więzów, wartości domyślnych lub reguł.
  • Wywołanie procedury sp_recompile w odniesieniu do tabeli, do której odwołuje się procedura.
  • Przywrócenie bazy danych zawierającej procedurę lub dowolne obiekty, do których się odwołuje procedura (w wypadku wykonywania operacji między bazami danych).
  • Aktywność serwera, która spowoduje usunięcie planu z pamięci podręcznej.
Wszystkie te przyczyny ponownej kompilacji procedury przechowywanej występowały we wcześniejszych wersjach i powodowały ponowną kompilację planu przed rozpoczęciem wykonywania procedury. W programie SQL Server 7.0 wprowadzono nowe zachowanie mogące spowodować ponowną kompilację procedury przechowywanej w czasie wykonywania. To nowe zachowanie gwarantuje, że optymalizator zawsze dysponuje najlepszym możliwym planem dla każdej konkretnej instrukcji w procedurze. Następujące zdarzenia mogą spowodować ponowną kompilację procedury przechowywanej w czasie wykonywania:
  • Zmiana określonej części danych w tabeli, do której odwołuje się procedura przechowywana.
  • Procedura przeplata operacje DDL (Data Definition Language) i DML (Data Manipulation Language).
  • Procedura wykonuje pewne operacje na tabelach tymczasowych.
Każda z tych przyczyn jest omówiona szczegółowo w dalszej części tego artykułu.

W niektórych wypadkach koszt ponownej kompilacji procedur przechowywanych jest większy niż wynikająca z niej korzyść, zwłaszcza w wypadku bardzo dużych procedur. Bardzo ważne jest zwrócenie uwagi na fakt, że wyzwolenia ponownej kompilacji powoduje ponowną kompilację całego programu wsadowego lub procedury. To oznacza, że spadek wydajności jest wprost proporcjonalny do wielkości procedury lub programu wsadowego. Aby uzyskać więcej informacji na ten temat, zobacz temat „Transact-SQL Tips” (Porady dotyczące języka Transact-SQL) w dokumentacji SQL Server Books Online.


Następujące informacje w tym artykule służą przede wszystkim identyfikacji przyczyny ponownych kompilacji w czasie wykonywania procedury przechowywanej i stanowią omówienie metod, które pozwalają temu zapobiec.

Najważniejsza wskazówka

Podczas wykonywania procedury przechowywanej najlepiej określać właściciela w nazwie procedury. To zapewnia większą klarowność i ułatwia ponowne używanie istniejących planów wykonywania przez bieżącego użytkownika. Jeśli na przykład użytkownik niebędący właścicielem bazy danych (dbo) wykonuje procedurę przechowywaną (o nazwie myProc w tym przykładzie), której właścicielem jest użytkownik dbo, w bazie danych pubs, należy użyć następującej instrukcji:
exec dbo.myProc
				
zamiast:
exec myProc
				
Ta technika eliminuje pomyłki z innymi wersjami procedury innych właścicieli z punktu widzenia kodowania i konserwacji, a także umożliwia programowi SQL Server bardziej bezpośredni dostęp do planu wykonania konkretnej procedury.

Jeśli nie jest określona nazwa właściciela, program SQL Server uruchamia kod kompilacji i uzyskuje blokadę COMPILE na procedurze. Jednak w pewnym momencie stwierdzi, że nowy plan nie jest wymagany (przy założeniu, że nie ma innych powodów) i NIE kompiluje ponownie planu z powodu braku określenia właściciela. Mimo to dodatkowy krok uzyskania blokady COMPILE na procedurze może spowodować rywalizację o blokadę przy dużych obciążeniach. Zapoznaj się z artykułem Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks (INFORMACJE: Blokowanie w języku SQL spowodowane blokadami [[COMPILE]]), aby uzyskać więcej szczegółowych informacji na temat takiej sytuacji.

Jeśli w wywołaniu zostanie określony właściciel procedury (właściciel.procedura), nie ma potrzeby uzyskiwania blokady compile i prawdopodobieństwo wystąpienia rywalizacji zostanie zmniejszone.

Określanie i eliminowanie problemów

Należy się zapoznać z następującym artykułem z bazy wiedzy Microsoft Knowledge Base, aby uzyskać szczegółowe informacje na temat przechwytywania danych programu Profiler w celu lepszego przeanalizowania wydajności systemu:
224587 JAK: Rozwiązywanie problemów z wydajnością programu SQL Server

Wyświetlanie danych programu Profiler

Program SQL Server Profiler zawiera zdarzenie SP:Recompile, którego można użyć do monitorowania liczby ponownych kompilacji. Zdarzenie SP:Recompile występuje za każdym razem, gdy podczas wykonywania miała miejsce ponowna kompilacja procedury przechowywanej.
  • Grupowanie śledzenia programu Profiler według klas zdarzeń:

    1. W menu File (Plik) kliknij polecenie Properties (Właściwości).
    2. Na karcie Data Columns (Kolumny danych) użyj przycisku DO GÓRY, aby przenieść kolumnę Event Class (Klasa zdarzeń) oraz Text (Tekst) poniżej nagłówka Groups (Grupy), umieszczając kolumnę Event Class jako pierwszą. Użyj przycisku W DÓŁ, aby usunąć wszystkie pozostałe kolumny poniżej nagłówka Groups.
    3. Kliknij przycisk OK.
    Sprawdź liczbę zdarzeń SP:Recompile.

    Można rozwinąć grupę SP:Recompile, aby wyświetlić szczegóły poszczególnych wystąpień. Kolumna Text zdarzenia określa nazwę ponownie kompilowanej procedury przechowywanej. Jeśli wiele procedur powoduje ponowną kompilację, są one posortowane według liczby wystąpień. Jeśli występuje bardzo dużo zdarzeń SP:Recompile i procesor jest obciążony w znacznym stopniu, należy się skoncentrować na procedurach, dla których liczba ponownych kompilacji jest największa. Należy zwrócić uwagę na identyfikator procesu systemowego (SPID) oraz godzinę rozpoczęcia zdarzenia SP:Recompile dla wystąpienia konkretnych procedur przechowywanych i wykonać następujące kroki.

    Jeśli nie widzisz zdarzeń SP:Recompile, ale wciąż występują problemy z wydajnością, zajrzyj do następującego artykułu z bazy wiedzy Microsoft Knowledge Base:
    224587 JAK: Rozwiązywanie problemów z wydajnością programu SQL Server
  • Określanie instrukcji powodującej zdarzenie ponownej kompilacji

    1. W menu File (Plik) kliknij polecenie Properties (Właściwości).
    2. Na karcie Data Columns (Kolumny danych) użyj przycisku W DÓŁ, aby usunąć wszystkie pozostałe kolumny poniżej nagłówka Groups (Grupy).
    3. Na karcie Events (Zdarzenia) usuń wszystkie zdarzenia z wyjątkiem zdarzeń SP:Starting, SP:StmtStarting, SP:Recompile i SP:Completed. Jeśli nie było przechwytywane zdarzenie SP:StmtStarting, możesz je zastąpić zdarzeniem SP:StmtCompleted, ale nie dołączaj obu, ponieważ podwoi to ilość informacji, które trzeba będzie przejrzeć.
    4. Jeśli zostało zlokalizowane konkretne wystąpienie ponownej kompilacji procedury przechowywanej, które należy zbadać, można ograniczyć ilość danych do przeglądania do konkretnego identyfikatora SPID i przedziału czasu wystąpienia przy użyciu karty Filters (Filtry).
    5. Kliknij przycisk OK.

    Zdarzenie SP:Recompile wystąpi bezpośrednio po zdarzeniu SP:StmtStarted instrukcji procedury przechowywanej, która spowodowała ponowną kompilację. Po zakończeniu zdarzenia ponownej kompilacji ponownie pojawi się zdarzenie SP:StmtStarted oznaczające wykonanie instrukcji z nowo wygenerowanym planem.

    Rozważmy następujący przykład:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Jeśli ten kod zostanie wykonany w programie Query Analyzer i zostaną wyświetlone powyższe zdarzenia w śledzeniu programu Profiler, będzie widoczna następująca sekwencja:

    Zwiń tę tabelęRozwiń tę tabelę
    Event ClassText
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RecompileRecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    Natychmiast można się zorientować, że instrukcja, która spowodowała ponowną kompilację, to:
    select * from #t
    						
    Można to wywnioskować z faktu, że pojawia się zarówno przed, jak i po zdarzeniu SP:Recompile.

    Gdyby było przechwytywane jedynie zdarzenie SP:StmtCompleted, ale nie zdarzenie SP:StmtStarting, zdarzenie SP:Recompile pojawiłoby się bezpośrednio przed instrukcją, która je spowodowała:

    Zwiń tę tabelęRozwiń tę tabelę
    Event Class Text
    SP:StartingRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedselect * from #t
    SP:CompletedRecompProc


    Jak widać zdarzenie SP:Recompile występuje przed zdarzeniem SP:StmtCompleted dla instrukcji „select * from #t” , która spowodowała ponowną kompilację. Jest to logiczne, gdyż instrukcja nie może być ukończona do czasu wygenerowania nowego planu zapytań dla ponownej kompilacji. We wszystkich pozostałych przykładach w tym artykule jest używane zdarzenie SP:StmtStarting. Jeśli było przechwytywane jedynie zdarzenie SP:StmtCompleted, należy pamiętać, że należy wyświetlać instrukcję po zdarzeniu SP:Recompile, jak to wyjaśniono powyżej.

    Należy zwrócić uwagę, że jeśli ta konkretna procedura przechowywana będzie uruchomiona wiele razy, program SQL Server będzie używać istniejącego planu dla tej procedury. Zdarzenie ponownej kompilacji będzie widoczne jedynie przy pierwszym wykonaniu procedury lub gdy procedura będzie odrzucana i ponownie tworzona przy każdym wykonaniu skryptu. Przyczyna ponownej kompilacji w tym konkretnym przypadku jest omówiona w sekcji „Ponowne kompilacje spowodowane przeplataniem operacji DDL (Data Definition Language) z operacjami DML (Data Manipulation Language)” tego artykułu. Jest to po prostu przykład ilustrujący, jak łatwo określić instrukcję powodującą ponowną kompilację.

Ponowne kompilacje w wyniku modyfikacji wierszy

Jeśli od momentu wygenerowania oryginalnego planu kwerend zmieni się wystarczająca część danych tabeli, do której odwołuje się procedura przechowywana, program SQL Server ponownie skompiluje procedurę przechowywaną, aby zagwarantować, że ma ona plan oparty na aktualnych danych statystycznych. Jako przykład rozważmy następującą procedurę przechowywaną:
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
				
Przy drugim wykonaniu procedury RowModifications w programie Profiler pojawią się następujące zdarzenia:

Zwiń tę tabelęRozwiń tę tabelę
Event ClassText
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

Uwaga: Przy pierwszym wykonaniu pojawi się także zdarzenie SP:Recompile dla instrukcji „select * from #t” . Przyczyna ponownej kompilacji w tym konkretnym przypadku jest omówiona w sekcji „Ponowne kompilacje spowodowane przeplataniem operacji DDL (Data Definition Language) z operacjami DML (Data Manipulation Language)” tego artykułu. Skoncentrujmy się na przykład na pokazanym powyżej zdarzeniu SP:Recompile, ponieważ występuje ono przy każdym wykonaniu procedury.

W tym przykładzie instrukcja „select count(*) from #t where a = 37” powoduje ponowną kompilację procedury ze względu na zmianę liczby wierszy od czasu utworzenia tabeli. Obecność zdarzenia Auto-UpdateStats potwierdza, że ponowna kompilacja była spowodowana modyfikacjami wierszy. Kolumna Text wskazuje kolumnę, dla której były zmodyfikowane statystyki.

Gdy została utworzona tabela #t, liczba wierszy wynosiła zero. Został opracowany plan dla oryginalnej instrukcji „select * from #t” z określoną liczbą wierszy, a także plan dla kwerendy „select count (*)” . Jednak przed wykonaniem instrukcji „select count(*)” nastąpiło wstawienie 1000 nowych wierszy do tabeli #t. Ponieważ zmieniła się wystarczająca ilość danych, optymalizator ponownie skompilował procedurę, aby zagwarantować wybór najefektywniejszego planu dla instrukcji. Ponowna kompilacja będzie miała miejsce przy każdym wykonaniu procedury przechowywanej, ponieważ wstawienie 1000 wierszy zawsze będzie uznawane za zmianę wystarczającą do ponownej kompilacji.

Algorytm używany przez program SQL Server do określenia, czy plan powinien zostać ponownie skompilowany, jest identyczny jak algorytm używany do automatycznej aktualizacji statystyk, co zostało opisane w następującym artykule z bazy wiedzy Microsoft Knowledge Base:
195565 INF: Sposób działania statystyk automatycznych programu INF Server SQL i 7.0 Server 2000
W powyższym przykładzie procedura przechowywana jest na tyle mała, że jej ponowna kompilacja nie miałaby znaczącego wpływu na wydajność. Jeśli jednak występują bardzo duże procedury przechowywane wykonujące podobne działania powodujące wiele ponownych kompilacji, można zaobserwować obniżenie wydajności.

Istnieją następujące metody przeciwdziałania ponownym kompilacjom spowodowanym modyfikacjami wierszy:
  • Wykonywanie instrukcji przy użyciu procedury sp_executesql.
    Jest to preferowana metoda. Instrukcje wykonywane przy użyciu procedury przechowywanej sp_executesql nie są kompilowane jako część planu procedury przechowywanej. Dlatego, gdy jest wykonywana instrukcja, program SQL Server będzie mógł użyć planu istniejącego w pamięci podręcznej lub utworzyć nowy w czasie wykonywania. W obu wypadkach nic nie będzie rzutowało na plan wywołania procedury przechowywanej i nie będzie konieczne ponowne kompilowanie go.

    Instrukcja EXECUTE będzie miała identyczny skutek. Jednak nie zaleca się stosowania jej. Użycie instrukcji EXECUTE nie jest tak efektywne jak użycie procedury sp_executesql, ponieważ nie umożliwia parametryzacji kwerendy.

    Powyższą procedurę RowModifications można napisać z wykorzystaniem procedury sp_executesql w następujący sposób:

    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
    						

    Przy drugim wykonaniu procedury RowModifications2 w programie Profiler pojawią się następujące zdarzenia:

    Zwiń tę tabelęRozwiń tę tabelę
    Event ClassText
    SP:StartingRowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    Należy zauważyć, że zdarzenia SP:Recompile nie występują dla procedury RowModifications2. Występują pełne zdarzenia od SP:Starting do SP:Completed dla kontekstu wywołania sp_executesql i zdarzenie Auto-UpdateStats dla kolumny a. Jednak ponieważ to wywołanie jest poza kontekstem procedury przechowywanej, procedura RowModifications2 nie musi być ponownie kompilowana w tym wypadku.

    Aby uzyskać więcej informacji na temat używania procedury przechowywanej sp_executesql, zobacz tematy „sp_executesql (T-SQL)” i „Using sp_executesql” (Używanie procedury sp_executesql) w dokumentacji SQL Server Books Online.
  • Użycie podprocedur do wykonywania instrukcji powodujących ponowne kompilacje.
    W tym wypadku instrukcja może wciąż powodować ponowną kompilację, ale zamiast ponownej kompilacji bardzo dużej procedury przechowywanej zostanie skompilowana jedynie niewielka podprocedura.
  • Użycie opcji KEEP PLAN (zachowaj plan).
    Tymczasowe tablice mają specjalne reguły dotyczące ponownych kompilacji, które w pewnych wypadkach mogą być bardziej rygorystyczne niż domyślny algorytm ponownej kompilacji. Można użyć opcji KEEP PLAN, aby przywrócić tablicy tymczasowej domyślny algorytm. Aby uzyskać więcej informacji, zobacz sekcję „Unikanie ponownej kompilacji przy użyciu opcji KEEP PLAN” w dalszej części tego artykułu.
Uwaga: Procedura RowModifications to bardzo uproszczony przykład procedury kompilowanej ponownie z powodu modyfikacji wierszy. Należy przejrzeć następujące zastrzeżenia dotyczące tego przykładu:

  • Wprawdzie w przykładzie użyto tablicy tymczasowej, ale ta sytuacja dotyczy także procedur przechowywanych odwołujących się do tablic stałych. Jeśli w tablicy, do której odwołuje się procedura, zostanie zmieniona wystarczająca ilość danych od czasu wygenerowania planu, procedura przechowywana zostanie ponownie skompilowana. Różnice w sposobie kwalifikowania tymczasowych tablic do ponownej kompilacji są opisane w sekcji „Unikanie ponownej kompilacji przy użyciu opcji KEEP PLAN” w dalszej części tego artykułu.
  • Pierwsze wykonania dwóch powyższych procedur także powodują ponowną kompilację przy pierwszej instrukcji „select” na tymczasowej tablicy #t. Przyczyny tej ponownej kompilacji są omówione w sekcji „Ponowne kompilacje spowodowane przeplataniem operacji DDL (Data Definition Language) z operacjami DML (Data Manipulation Language)” tego artykułu.
  • W tym przykładzie użyto instrukcji „select count(*) from #t” zamiast prostej instrukcji „select * from #t” . Aby uniknąć nadmiarowych ponownych kompilacji, program SQL Server nie bierze pod uwagę ponownego kompilowania „prostych planów” (takich jak select * from table) na skutek modyfikacji wierszy.

Ponowne kompilacje spowodowane przeplataniem operacji DDL (Data Definition Language) z operacjami DML (Data Manipulation Language)

Jeśli operacje DDL są wykonywane wewnątrz procedury lub programu wsadowego, procedura lub program wsadowy są ponownie kompilowane, gdy napotkają następną operację DML mającą wpływ na tabelę zaangażowaną w operację DDL.

Rozważmy następującą przykładową procedurę przechowywaną:
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
				
Jeśli ten kod zostanie wykonany w programie Query Analyzer i zostaną wyświetlone powyższe zdarzenia w śledzeniu programu Profiler, będzie widoczna następująca sekwencja:

Zwiń tę tabelęRozwiń tę tabelę
Event ClassText
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP:RecompileInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP:RecompileInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


W tym wypadku procedura przechowywana jest ponownie kompilowana trzykrotnie podczas wykonywania. Aby zrozumieć, dlaczego to ma miejsce, rozważmy, jak optymalizator opracowuje plan dla tej procedury przechowywanej:
  1. Podczas początkowej kompilacji procedury nie istnieją tabele t1 i t2. Dlatego nie może być utworzony żaden plan dla kwerend odwołujących się do tych tabel. Plany muszą być tworzone w czasie wykonywania.
  2. Gdy procedura jest wykonywana po raz pierwszy, pierwszym krokiem jest utworzenie tabeli t1. Następny krok to wykonanie instrukcji „select” na tabeli t1 — dla której nie ma planu. Dlatego w tym momencie procedura jest ponownie kompilowana, aby opracować plan dla instrukcji SELECT. Plan jest generowany dla bieżącej instrukcji „select” na tabeli t1, a także dla instrukcji „select” na tabeli t1 po utworzeniu indeksu. Nie jest generowany plan dla instrukcji „select” na tabeli t2, ponieważ tabela t2 jeszcze nie istnieje.
  3. Następnym krokiem jest utworzenie indeksu na tabeli t1. Następnie jest wykonywana kolejna instrukcja „select” na tabeli t1, dla której istnieje plan z pierwszej kompilacji. Jednak z powodu zmiany schematu tabeli t1 od czasu wygenerowania planu procedura musi być ponownie skompilowana, aby wygenerować nowy plan dla instrukcji „select” na tabeli t1. Ponieważ tabela t2 wciąż nie istnieje, nie może być wygenerowany plan dla instrukcji „select” na tabeli t2.
  4. Następnie jest tworzona tabela t2 i jest wykonywana na niej instrukcja „select” . Ponieważ nie istnieje plan dla instrukcji, procedura jest ponownie kompilowana ostatni raz.
Te ponowne kompilacje mają miejsce przy każdym wykonaniu procedury przechowywanej. Aby ograniczyć te ponowne kompilacje, należy zmodyfikować procedurę, tak aby wykonywała w pierwszej kolejności wszystkie operacje DDL, a następnie operacje DML, jak to pokazano poniżej:
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
				
Przy pierwszym wykonaniu procedury NoInterleave w programie Profiler pojawią się następujące zdarzenia:

Zwiń tę tabelęRozwiń tę tabelę
Event ClassText
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP:RecompileNoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


W tym wypadku wszystkie instrukcje DDL są wykonywane na początku. Optymalizator skompiluje tę procedurę w następujący sposób:
  1. Podczas początkowej kompilacji procedury nie istnieją tabele t1 i t2. Dlatego nie może być utworzony żaden plan dla kwerend odwołujących się do tych tabel. Plany muszą być tworzone w czasie wykonywania.
  2. Pierwsze kroki wykonywane przez procedurę to przeprowadzenie operacji DDL, utworzenie tabel t1 i t2, a także indeksu na tabeli t1.
  3. Następnym krokiem jest wykonanie instrukcji „select” na tabeli t1. Ponieważ nie jest dostępny plan dla instrukcji SELECT, procedura jest ponownie kompilowana. Ponieważ istnieją wszystkie obiekty, w tym momencie są generowane plany dla wszystkich instrukcji SELECT w procedurze.
  4. Pozostała część procedury jest wykonywana przy użyciu wygenerowanych planów. Ponieważ w obiektach, do których odwołuje się procedura, nie ma zmian, nie ma potrzeby dalszych ponownych kompilacji procedury.
Uwaga: Drugie i następne wykonania używają istniejącego planu kwerend i pamięci podręcznej i nie powodują żadnych ponownych kompilacji. Procedury tworzące, zmieniające lub usuwające tabele powinny być zmodyfikowane w sposób zapewniający zlokalizowanie wszystkich instrukcji DDL na początku procedur.

Ponowne kompilacje spowodowane pewnymi operacjami na tabelach tymczasowych

Używanie tabel tymczasowych w procedurze przechowywanej może powodować ponowne kompilacje przy każdym wykonaniu procedury.

Aby tego uniknąć, należy zmienić procedurę przechowywaną w taki sposób, aby spełniała następujące wymagania:
  • Wszystkie instrukcje zawierające nazwę tabeli tymczasowej odwołują się do tabeli tymczasowej utworzonej w tej samej procedurze przechowywanej, a nie w procedurze wywołującej lub wywoływanej, lub w ciągu wykonywanym przy użyciu instrukcji EXECUTE bądź procedury przechowywanej sp_executesql.
  • Wszystkie instrukcje zawierające nazwę tabeli tymczasowej pojawiają się syntaktycznie po nazwie tej tabeli w procedurze lub wyzwalaczu.
  • Nie ma instrukcji DECLARE CURSOR, których instrukcje SELECT odwołują się do tabeli tymczasowej.
  • Wszystkie instrukcje zawierające nazwę dowolnej tabeli tymczasowej poprzedzają wszelkie instrukcje DROP TABLE odwołujące się do tabeli tymczasowej.

    Instrukcje DROP TABLE są zbędne w odniesieniu do tabel tymczasowych tworzonych w procedurze przechowywanej. Te tabele są automatycznie usuwane, gdy procedura zakończy działanie.
  • Nie występują instrukcje tworzące tabele tymczasowe (takie jak CREATE TABLE lub SELECT... INTO) w instrukcjach sterujących przepływem, takich jak IF... ELSE lub WHILE.

Unikanie ponownej kompilacji przy użyciu opcji KEEP PLAN

Używanie tabel tymczasowych w procedurach przechowywanych w pewnym stopniu komplikuje pracę optymalizatora kwerend. Liczba wierszy i informacje statystyczne takich tabel mogą się drastycznie zmieniać w czasie wykonywania procedury przechowywanej. Aby zagwarantować, że optymalizator używa najlepszego planu we wszystkich wypadkach dotyczących tabel tymczasowych, opracowano specjalny algorytm, który intensywniej stosuje ponowne kompilacje. Ten algorytm stwierdza, że jeśli tabela tymczasowa utworzona przez procedurę przechowywaną zmieniła się ponad sześć razy, procedura będzie ponownie skompilowana, gdy następna instrukcja odwoła się do tabeli tymczasowej.

Rozważmy następujący przykład:
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
				
W tym wypadku dla drugiego wykonania w programie Profiler pojawią się następujące zdarzenia:

Zwiń tę tabelęRozwiń tę tabelę
Event ClassText
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements - (siedem instrukcji wstawienia)
SP:StmtStartingselect count(*) from #t1
SP:RecompileuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

Procedura jest ponownie kompilowana po napotkaniu instrukcji „select” po siódmej zmianie tabeli tymczasowej #t.

Ta agresywna ponowna kompilacja jest pomocna w sytuacjach, gdy zmiany rozkładu danych tabeli tymczasowej mogą drastycznie wpłynąć na optymalny plan kwerend dla instrukcji, która się do niej odwołuje. Jednak w wypadku bardzo dużych procedur, które często modyfikują tabele tymczasowe, ale w niewielkim stopniu, ponowne kompilacje mogą spowodować ogólne pogorszenie wydajności. Opcja KEEP PLAN instrukcji SELECT została wprowadzona na potrzeby takiej sytuacji.

Opcja KEEP PLAN eliminuje ponowne kompilacje procedury przechowywanej powodowane przez więcej niż sześć zmian tabeli tymczasowej w procedurze i przywraca standardowy algorytm ponownej kompilacji spowodowanej modyfikacjami wierszy opisany w sekcji „Ponowne kompilacje w wyniku modyfikacji wierszy” tego artykułu. Opcja KEEP PLAN nie zapobiega wszystkim ponownym kompilacjom — po prostu eliminuje te, które są powodowane przez więcej niż sześć zmian tabel tymczasowych w procedurze. Jeśli w powyższym przykładzie zostanie usunięty komentarz z wiersza „option (KEEP PLAN)” procedury przechowywanej, zdarzenie SP:Recompile nie będzie wygenerowane.

Jeśli w powyższym kodzie zostanie usunięty komentarz z wiersza „option (KEEP PLAN)” i ten kod zostanie wykonany, w programie Profiler będą widoczne następujące zdarzenia:

Zwiń tę tabelęRozwiń tę tabelę
Event ClassText
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - The seven insert statements - (siedem instrukcji wstawienia)
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


Należy zwrócić uwagę, że nie ma zdarzenia SP:Recompile.

Ponowne kompilacje powodowane pewnymi instrukcjami SET wykonywanymi w procedurze przechowywanej

Pięć następujących opcji instrukcji SET jest domyślnie włączonych:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Jeśli zostanie wykonana instrukcja SET wyłączająca dowolną z tych opcji, procedura przechowywana będzie ponownie kompilowana przy każdym wykonaniu. Taka sytuacja jest spowodowana przez to, że zmiana tych opcji może wpłynąć na wynik kwerendy, który wyzwolił ponowną kompilację.

Rozważmy następujący przykładowy kod:
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
				
W tym wypadku w programie SQL Profiler pojawią się następujące zdarzenia dla każdego wykonania procedury przechowywanej:
+---------------------------------------------------+
| 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                  | 
+---------------------------------------------------+
				
Zastąpienie opcji SET dowolną z pięciu opcji wymienionych powyżej da identyczne wyniki. Także użycie tutaj opcji „keep plan” nie pozwoli uniknąć ponownej kompilacji, ponieważ przyczyną kompilacji jest instrukcja SET.

Zalecany sposób uniknięcia ponownej kompilacji to nieużywanie żadnej z tych pięciu instrukcji SET w procedurze przechowywanej. Aby uzyskać dodatkowe informacje, zobacz następujący artykuł z bazy wiedzy Microsoft Knowledge Base:
294942 PRB: Instrukcja SET CONCAT_NULL_YIELDS_NULL może powodować ponowną kompilację procedur przechowywania
Jednak, choć nie jest to zalecana metoda, wykonanie instrukcji SET w celu zresetowania opcji połączenia do wartości identycznych jak w procedurze przechowywanej także pozwala uniknąć ponownej kompilacji, na przykład:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
Śledzenie programu SQL Profiler nie będzie zawierało zdarzeń SP:Recompile.

W poniższej tabeli podano niektóre typowe instrukcje SET oraz informacje, czy zmiana instrukcji SET w procedurze przechowywanej powoduje ponowną kompilację:
Zwiń tę tabelęRozwiń tę tabelę
Instrukcja SetPonowna kompilacja
Set quoted_identifierNie
Set arithabortTak
Set ansi_null_dflt_onTak
Set ansi_defaultsTak
Set ansi_warningsTak
Set ansi_paddingTak
Set concat_null_yields_nullTak
Set numeric_roundabortNie
Set nocountNie
Set rowcountNie
Set xact_abortNie
Set implicit_transactionsNie
Set arithignoreNie
Set lock_timeoutNie
Set fmtonlyNie

Materiały referencyjne

308737 INF: Identyfikowanie przyczyny ponownej kompilacji w zdarzeniu SP:Recompile

Informacje na temat używania programu SQL Profiler można znaleźć w dokumentacji SQL Server Books Online.

Właściwości

Numer ID artykułu: 243586 - Ostatnia weryfikacja: 3 stycznia 2008 - Weryfikacja: 2.3
Informacje zawarte w tym artykule dotyczą:
  • 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
Słowa kluczowe: 
kbinfo KB243586

Przekaż opinię

 

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