OPCJA FORCE ORDER poprawia wydajność, dopóki wiersze nie zostaną usunięte

Mam dość złożone zapytanie SQL Server 2008 (około 200 wierszy dość gęstego SQL), które nie działało tak, jak tego potrzebowałem. Z czasem wydajność spadła z około 0,5 sekundy do około 2 sekund.

Patrząc na plan wykonania, było dość oczywiste, że zmiana kolejności połączeń może poprawić wydajność. Zrobiłem to i to ... aż do około 0,3 sekundy. Teraz zapytanie ma podpowiedź „OPCJA SIŁA ZAMÓWIENIA” , a życie jest dobre.

Nadchodzi dzisiaj, sprzątanie bazy danych. Archiwizuję około 20% wierszy, nie podejmując żadnych działań w odpowiedniej bazie danych, z wyjątkiem usuwania wierszy ... plan wykonania zostaje całkowicie zamknięty . Całkowicie błędnie ocenia, ile wierszy zostaną zwrócone określone poddrzewa i (na przykład) zastępuje:

<Hash>

z

<NestedLoops Optimized='false' WithUnorderedPrefetch='true'>

Teraz czas kwerendy skraca się z około 0,3 s do około 18 s. (!) Tylko dlatego, że usunąłem wiersze. Jeśli usunę wskazówkę dotyczącą zapytania, wrócę do około 2 sekund czasu zapytania. Lepiej, ale gorzej.

Problem odtworzyłem po przywróceniu bazy danych do wielu lokalizacji i serwerów. Po prostu usunięcie około 20% wierszy z każdej tabeli zawsze powoduje ten problem.

1. Czy jest to normalne w przypadku wymuszonego przyłączenia, aby prognozy zapytań były całkowicie niedokładne (a tym samym czasy zapytań były nieprzewidywalne)?
2. Czy powinienem oczekiwać, że będę musiał zaakceptować nieoptymalną wydajność zapytań, czy też obserwować ją jak jastrząb i często ręcznie edytować wskazówki dotyczące zapytań? A może wskazówka dla każdego dołączenia? .3s do 2s to duży hit.
3. Czy jest oczywiste, dlaczego optymalizator wybuchł po usunięciu wierszy? Na przykład „tak, wzięto przykładowy skan, a ponieważ zarchiwizowałem większość wierszy wcześniej w historii danych, próbka dała rzadkie wyniki, więc nie doceniono potrzeby posortowanej operacji mieszania”?

Jeśli chcesz zobaczyć plany wykonania, sugeruj lokalizację, w której mogę je opublikować. W przeciwnym razie próbowałem najbardziej oszałamiającego kawałka. Oto podstawowe błędne oszacowanie, liczby w parens są (szacowane: rzeczywiste) wierszami.

                             /  Clustered Index Scan (908:7229)
Nested Loops (Inner Join) --<
                             \  NonClustered Index Seek (1:7229)

Uwaga: oczekuje się, że wewnętrzna pętla skanuje 908 wierszy, ale zamiast tego skanuje 52 258 441. Gdyby było dokładne, gałąź ta działałaby około 2 ms, a nie 12 sekund. Przed usunięciem wierszy to oszacowanie złączenia wewnętrznego zostało wyłączone tylko 2 razy i zostało wykonane jako dopasowanie mieszające dla dwóch indeksów klastrowych.

Czy jest to normalne w przypadku wymuszonego przyłączenia, aby prognozy zapytań były całkowicie niedokładne (a tym samym czasy zapytań były nieprzewidywalne)?

Zastosowanie FORCE ORDER nie powoduje, że oszacowania są niedokładne, podobnie jak usuwanie wierszy. Wymuszenie aktualizacji statystyk w tabeli może poprawić dokładność oszacowania.

Czy powinienem oczekiwać, że będę musiał zaakceptować nieoptymalną wydajność zapytań, czy też obserwować ją jak jastrząb i często ręcznie edytować wskazówki dotyczące zapytań? A może wskazówka dla każdego dołączenia? .3s do 2s to duży hit.

Najlepiej byłoby zapewnić optymalizatorowi informacje potrzebne do wygenerowania najlepszego planu, bez korzystania z podpowiedzi FORCE ORDER. W ten sposób powinien lepiej radzić sobie ze zmianami w podstawowej dystrybucji danych bez konieczności ręcznej interwencji. To powiedziawszy, jeśli charakter danych jest taki, że liczność może zmieniać się znacznie z godziny na godzinę lub z dnia na godzinę, rozważ skorzystanie z przewodnika po planie, aby upewnić się, że plan został ustalony.

Czy jest oczywiste, dlaczego optymalizator wybuchł po usunięciu wierszy? Na przykład „tak, wzięto przykładowy skan, a ponieważ zarchiwizowałem większość wierszy wcześniej w historii danych, próbka dała rzadkie wyniki, więc nie doceniono potrzeby posortowanej operacji mieszania”?

Nie wymieniłeś liczby wierszy w tabelach problemów, ale prawdopodobne jest, że usunięcia:

• nie usunął wystarczającej liczby wierszy, aby uruchomić aktualizację statystyk. Powinno to nastąpić, gdy zmodyfikowano 20% wierszy, ale istnieje opcja użycia flagi śledzenia 2371 w celu włączenia progu dynamicznego.
• uruchomił aktualizację statystyk, ale zebrana próbka nie była reprezentatywna. Popraw to, uruchamiając ręczną aktualizację Z PEŁNYM SKANOWANIEM .

Możesz również napotkać stare, dobre problemy z wąchaniem parametrów , w przypadku których istnieje wiele opcji do obejścia. Z RECOMPILE może być kosztowną opcją określenie tak dużego zapytania, ale warto to zbadać zarówno na poziomie procedury, jak i instrukcji.