Opcja (PRZEKOMPILOWANIE) jest zawsze szybsza; dlaczego?

Question

Opcja (PRZEKOMPILOWANIE) jest zawsze szybsza; dlaczego?

Napotkałem dziwną sytuację, w której dołączenie {[2] } do mojego zapytania powoduje, że uruchamia się w pół sekundy, podczas gdy pominięcie go powoduje, że zapytanie zajmuje znacznie ponad pięć minut.

Jest to przypadek, gdy zapytanie jest wykonywane z analizatora zapytań lub z mojego programu C# poprzez SqlCommand.ExecuteReader(). Wywołanie (lub nie wywołanie) DBCC FREEPROCCACHE lub DBCC dropcleanbuffers nie robi różnicy; wyniki zapytań są zawsze zwracane natychmiastowo z OPTION (RECOMPILE) i są większe niż pięć minut bez niego. Zapytanie jest zawsze wywoływane z tym samym parametry [na potrzeby tego testu].

Używam SQL Server 2008.

Jestem dość wygodny w pisaniu SQL, ale nigdy wcześniej nie używałem OPTION polecenia w zapytaniu i nie byłem zaznajomiony z całą koncepcją buforów planu, dopóki nie skanowałem postów na tym forum. Rozumiem z postów, że {[2] } to kosztowna operacja. Najwyraźniej tworzy nową strategię wyszukiwania dla zapytania. Dlaczego więc kolejne zapytania pomijające OPTION (RECOMPILE) są tak powolne? Czy kolejne zapytania nie powinny korzystać ze strategii wyszukiwania, która została obliczona na poprzednim wywołaniu, które zawierało podpowiedź rekompilacji?

Czy to bardzo niezwykłe mieć zapytanie, które wymaga podpowiedzi rekompilacji przy każdym pojedynczym wywołaniu?

Przepraszam za pytanie na poziomie podstawowym, ale nie mogę się z tym pogodzić.

Aktualizacja: zostałem poproszony o zamieszczenie zapytania...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

Podczas uruchamiania testu z analizatora zapytań, poprzedzam następujące wiersze:

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

Podczas wywoływania go z mojego programu C#, parametry są przekazywane przez właściwość SqlCommand.Parameters.

Na potrzeby tej dyskusji można założyć, że parametry nigdy się nie zmieniają, więc możemy wykluczyć suboptymalny parametr jako przyczynę.

134

sql sql-server compilation sql-server-2008 hint

Author: Abe Miessler, 2014-01-01

Source

4 answers

Często, gdy istnieje drastyczna różnica między uruchomieniem a uruchomieniem zapytania, stwierdzam, że często jest to jeden z 5 problemów.

Statystyki - Statystyki są nieaktualne. Baza danych przechowuje statystyki dotyczące zakresu i rozkładu typów wartości w różnych kolumnach tabel i indeksów. Pomaga to silnikowi zapytań opracować "Plan" ataku na sposób wykonania zapytania, na przykład rodzaj metody, której użyje do dopasowania kluczy między tabelami za pomocą skrótu lub szukania przez cały zestaw. Możesz wywołać aktualizacje statystyk całej bazy danych lub tylko niektórych tabel lub indeksów. To spowalnia zapytanie od jednego uruchomienia do drugiego, ponieważ gdy Statystyki są nieaktualne, prawdopodobnie plan zapytań nie jest optymalny dla nowo wstawionych lub zmienionych danych dla tego samego zapytania(wyjaśnione więcej poniżej). Może nie być właściwe Natychmiastowe aktualizowanie statystyk w bazie danych produkcyjnych, ponieważ pojawią się pewne koszty ogólne, spowolnienie i opóźnienie w zależności od ilości danych do próbka. Możesz również użyć pełnego skanowania lub pobierania próbek do aktualizacji statystyk. Jeśli spojrzysz na plan zapytań, możesz również wyświetlić statystyki dotyczące indeksów w użyciu za pomocą polecenia DBCC SHOW_STATISTICS (tablename, indexname) . To pokaże dystrybucję i zakresy kluczy, których plan zapytań używa do oparcia swojego podejścia.
Parameter SNIFFING - Plan zapytań, który jest buforowany, nie jest optymalny dla określonych parametrów przechodzą, mimo że samo zapytanie nie uległo zmianie. Na przykład, jeśli przekazujesz parametr, który pobiera tylko 10 z 1 000 000 wierszy, utworzony plan zapytań może używać połączenia Hashowego, jednak jeśli przekazany parametr użyje 750 000 z 1 000 000 wierszy, utworzony plan może być skanowaniem indeksu lub skanowaniem tabeli. W takiej sytuacji można polecić poleceniu SQL użycie opcji (RECOMPILE) lub SP do użycia z RECOMPILE. Powiedzieć silnikowi, że jest to " singiel Użyj planu " i nie używać planu buforowanego, który prawdopodobnie nie ma zastosowania. Nie ma reguły, jak podjąć tę decyzję, zależy to od znajomości sposobu, w jaki zapytanie będzie używane przez użytkowników.
INDEXES - możliwe, że zapytanie nie uległo zmianie, ale zmiana w innym miejscu, taka jak usunięcie bardzo użytecznego indeksu spowolniła zapytanie.
ROWS CHANGED - wiersze, które pytasz, drastycznie zmieniają się z wywołania na wywołanie. Zazwyczaj Statystyki są automatycznie aktualizowane w tych przypadkach. Jeśli jednak budujesz dynamiczny SQL lub wywołujesz SQL w ciasnej pętli, istnieje możliwość, że używasz przestarzałego planu zapytań opartego na niewłaściwej drastycznej liczbie wierszy lub statystyk. Ponownie w tym przypadku przydatna jest opcja (przekompiluj).
Logika to logika, Twoje zapytanie nie jest już wydajne, było w porządku dla małej liczby wierszy, ale już nie skaluje. Zwykle wiąże się to z bardziej dogłębną analizą Plan Zapytań. Na przykład, nie można już robić rzeczy luzem, ale trzeba kawałki rzeczy i zrobić mniejsze commity, lub twój produkt krzyżowy był w porządku dla mniejszego zestawu, ale teraz zajmuje CPU i pamięci, ponieważ skaluje się większe, może to być również prawdziwe dla korzystania z różnych, wywołujesz funkcję dla każdego wiersza, Twoje kluczowe mecze nie używają indeksu z powodu konwersji typu odlewania lub NULL lub funkcji... Zbyt wiele możliwości.

Ogólnie pisząc zapytanie, powinieneś mieć jakiś obraz umysłowy z grubsza jak pewne dane są dystrybuowane w tabeli. Na przykład kolumna może mieć równomiernie rozłożoną liczbę różnych wartości lub może być przekrzywiona, 80% czasu ma określony zestaw wartości, niezależnie od tego, czy rozkład będzie się często zmieniał w czasie, czy będzie dość statyczny. To da Ci lepsze wyobrażenie o tym, jak zbudować efektywne zapytanie. Ale także podczas debugowania wydajności zapytań mają podstawę do budowania hipotezy, dlaczego jest powolny lub nieefektywne.

119

Author: CodeCowboyOrg,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-02-12 21:32:50

Aby dodać do doskonałej listy (podanej przez @CodeCowboyOrg) sytuacji, w których opcja (PRZEKOMPILOWANIE) może być bardzo pomocna,

Zmienne Tabeli . Gdy używasz zmiennych tabelkowych, nie będzie żadnych wstępnie zbudowanych statystyk dla zmiennej tabelkowej, co często prowadzi do dużych różnic między szacowanymi i rzeczywistymi wierszami w planie zapytań. Użycie opcji (przekompiluj) na zapytaniach ze zmiennymi tabeli pozwala na generowanie planu zapytań, który ma znacznie lepsze oszacowanie wiersza liczby. Miałem szczególnie krytyczne użycie zmiennej tabeli, która była bezużyteczna i którą miałem zamiar porzucić, dopóki nie dodałem opcji (przekompiluj). Czas biegu poszedł z godzin do zaledwie kilku minut. Jest to prawdopodobnie nietypowe, ale w każdym razie, jeśli używasz zmiennych tabel i pracujesz nad optymalizacją, warto sprawdzić, czy opcja (REKOMPILE) robi różnicę.

22

Author: DWright,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-11-20 20:20:35

Pierwsze działania przed tunowaniem zapytań to defragmentacja / odbudowa indeksów i statystyk, w przeciwnym razie marnujesz czas.

Musisz sprawdzić plan wykonania, aby sprawdzić, czy jest stabilny (jest taki sam, gdy zmieniasz parametry), jeśli nie, być może będziesz musiał utworzyć indeks okładki (w tym przypadku dla każdej tabeli) (znając ten system możesz utworzyć taki, który będzie przydatny również dla innych zapytań).

Jako przykład : Utwórz indeks idx01_datafeed_trans On datafeed_trans ( feedid, feedDate) INCLUDE (acctNo, tradeDate)

Jeśli plan jest stabilny lub możesz go ustabilizować, możesz wykonać zdanie za pomocą sp_executesql ('zdanie sql'), aby zapisać i użyć stałego planu wykonania.

Jeśli plan jest niestabilny, musisz użyć instrukcji ad-hoc lub EXEC('zdanie sql'), aby ocenić i utworzyć plan wykonania za każdym razem. (lub procedura składowana "z rekompilacją").

Mam nadzieję, że to pomoże.

0

Author: Cristian Solervicéns,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-23 12:49:54

score 125 · Accepted Answer

Są chwile, kiedy używanie OPTION(RECOMPILE) ma sens. Z mojego doświadczenia wynika, że jedyną realną opcją jest użycie dynamicznego SQL. Zanim zbadasz, czy ma to sens w twojej sytuacji, polecam przebudowanie statystyk. Można to zrobić uruchamiając:

EXEC sp_updatestats

A potem odtworzyć twój plan egzekucji. Zapewni to, że po utworzeniu planu realizacji będzie on korzystał z najnowszych informacji.

Dodanie OPTION(RECOMPILE) przebudowuje plan wykonania za każdym razem, gdy twoje zapytanie zostanie wykonane. Nigdy nie słyszałem tego opisanego jako creates a new lookup strategy, ale może po prostu używamy różnych terminów dla tej samej rzeczy.

Po utworzeniu procedury składowanej (podejrzewam, że wywołujesz ad-hoc sql z. NET, ale jeśli używasz sparametryzowanego zapytania, to kończy się to zapisanym wywołaniem proc) SQL Server próbuje określić najbardziej efektywny plan wykonania tego zapytania na podstawie danych w Twojej bazie danych i parametrów przekazywanych w bazie danych. (parametr sniffing ), a następnie buforuje ten plan. Oznacza to, że jeśli utworzysz zapytanie, w którym jest 10 rekordów w bazie danych, a następnie wykonasz je, gdy jest 100,000,000 rekordów, buforowany plan realizacji może nie być już najbardziej efektywny.

Podsumowując - nie widzę żadnego powodu, dla którego OPTION(RECOMPILE) byłoby to korzyścią. Podejrzewam, że musisz zaktualizować swoje statystyki i plan egzekucji. Przebudowa statystyk może być istotną częścią pracy DBA w zależności od Twoja sytuacja. Jeśli nadal masz problemy po aktualizacji statystyk, proponuję zamieścić oba plany realizacji.

I odpowiadając na twoje pytanie-tak, powiedziałbym, że jest to bardzo nietypowe dla twojej najlepszej opcji, aby rekompilować plan wykonania za każdym razem, gdy wykonujesz zapytanie.