Program błędnych zapytań SQL Server 2016 blokuje DB raz w tygodniu

Raz w tygodniu, przez ostatnie 5 tygodni, mniej więcej o tej samej porze dnia (wczesny poranek, może być oparty na aktywności użytkownika, gdy ludzie zaczynają go używać), SQL Server 2016 (AWS RDS, kopia lustrzana) zaczyna przekraczać limit czasu zapytania.

AKTUALIZACJA STATYSTYKI we wszystkich tabelach zawsze naprawia to natychmiast.

Po raz pierwszy zmusiłem go do aktualizowania wszystkich statystyk we wszystkich tabelach co noc (zamiast co tydzień), ale nadal tak się działo (około 8 godzin po uruchomieniu statystyk aktualizacji, ale nie codziennie, gdy się uruchamia).

Ostatnim razem włączyłem Query Store, aby sprawdzić, czy mogę znaleźć konkretne zapytanie / plan zapytań. Myślę, że udało mi się zawęzić to do jednego:

Po znalezieniu tego zapytania dodałem zalecany indeks, którego brakowało w tym rzadko używanym zapytaniu (ale które dotyka wielu często używanych tabel).

Zły plan zapytań polegał na skanowaniu indeksu (na stole zawierającym tylko 10 000 wierszy). Jednak inne plany zapytań, które zwróciły się w milisekundach, używały tego samego skanowania. Najnowszy plan zapytań, po utworzeniu nowego indeksu, ma jedynie na celu. Ale nawet bez tego wskaźnika, 99% czasu, wracał w ciągu kilku milisekund, ale potem co tydzień zajmowałoby> 40 sekund.

• Zły, który wygasa : http://brentozar.com/pastetheplan/?id=rymaWt56e
• Poprzednie plany, które nie przekraczają limitu czasu: http://brentozar.com/pastetheplan/?id=HyN7ftcpe
• Najnowszy plan z nowym indeksem: http://brentozar.com/pastetheplan/?id=ryLuGKcag

To zaczęło się dziać po przejściu na SQL Server 2016 od 2012 roku.

DBCC CHECKDB nie zwraca żadnych błędów.

1. Czy nowy indeks rozwiąże problem, sprawiając, że już nigdy nie wybierze złego planu?
2. Czy powinienem „wymusić” plan, który teraz działa dobrze?
3. Jak się upewnić, że tak się nie stanie z innym zapytaniem / planem?
4. Czy to objaw większego problemu?

Indeksy, które właśnie dodałem:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Pełny tekst zapytania:

https://pastebin.com/Z5szPBfu (wygenerowane przez LINQ, mogę / powinienem być w stanie zoptymalizować wybrane kolumny, ale powinno to być nieistotne dla tego problemu)

Odpowiem na twoje pytania w innej kolejności niż zadałeś.

4. Czy jest to objaw większego problemu?

Nowy liczność Estymator w SQL Server 2016 może być przyczynienie się do tego problemu. SQL Server 2012 korzysta ze starszej wersji CE i nie wystąpił problem z tą wersją. Nowy estymator liczności przyjmuje różne założenia dotyczące danych i może generować różne plany zapytań dla tego samego kodu SQL. W przypadku niektórych zapytań ze starszą wersją CE możesz uzyskać lepszą wydajność, w zależności od zapytania i danych. Dlatego niektóre części modelu danych mogą nie być najlepiej dopasowane do nowego CE. W porządku, ale na razie może być konieczne obejście nowego CE.

Byłbym również zaniepokojony niespójną wydajnością zapytań, nawet przy codziennych aktualizacjach statystyk. Jedną ważną rzeczą do zapamiętania jest to, że zbieranie statystyk dla wszystkich tabel skutecznie usunie wszystkie plany zapytań z pamięci podręcznej, więc możesz mieć problem ze statystykami lub może to mieć związek z wąchaniem parametrów. Trudno jest ustalić bez dużej ilości informacji o modelu danych, szybkości zmiany danych, zasadach aktualizacji statystyk, sposobie wywoływania kodu itp. SQL Server 2016 oferuje pewne ustawienia na poziomie bazy danych dla wykrywania parametrów, które mogą być pomocne , ale może to wpłynąć na całą aplikację zamiast tylko jednego problematycznego zapytania.

Przedstawię przykładowy scenariusz, który może prowadzić do takiego zachowania. Powiedziałeś:

Niektórzy użytkownicy mogą mieć 1 rekord uprawnień, niektórzy nawet do 20 tys.

Załóżmy, że zbierasz statystyki na wszystkich tabelach, co usuwa wszystkie plany zapytań. W zależności od czynników wymienionych powyżej, jeśli pierwsze zapytanie dnia jest skierowane do użytkownika z tylko 1 rekordem uprawnień, wówczas SQL Server może buforować plan, który działa dobrze dla użytkowników z 1 rekordem, ale strasznie działa z użytkownikami z 20 000 rekordów. Jeśli pierwsze zapytanie tego dnia dotyczy użytkownika z 20 000 rekordów, możesz uzyskać dobry plan na 20 000 rekordów. Gdy kod jest uruchamiany dla użytkownika z 1 rekordem, może nie być najbardziej optymalnym zapytaniem, ale może nadal kończyć się w ms. To naprawdę brzmi jak wąchanie parametrów. Wyjaśnia, dlaczego nie zawsze widzisz problem lub dlaczego czasami potrzeba wielu godzin.

1. Czy nowy indeks rozwiąże problem, sprawiając, że już nigdy nie wybierze złego planu?

Myślę, że jeden z dodanych indeksów zapobiegnie problemowi, ponieważ uzyskanie dostępu do wymaganych danych za pośrednictwem indeksu będzie tańsze niż skanowanie klastrowanego indeksu względem tabeli, zwłaszcza gdy skanowanie nie może zostać wcześniej zakończone. Powiększmy złą część planu zapytań:

SQL Server szacuje, że tylko jeden wiersz zostanie zwrócony z przyłączenia na [Permission]i [Project]. Dla każdego wiersza na wejściu zewnętrznym wykona skanowanie indeksu klastrowego [Appointment]. Wszystkie wiersze zostaną zeskanowane z tej tabeli, ale tylko te pasujące do filtrowania [Start]zostaną zwrócone do operatora łączenia. W obrębie operatora łączenia wyniki są jeszcze bardziej zmniejszone.

Opisany powyżej plan zapytań może być poprawny, jeśli naprawdę jest tylko jeden wiersz wysłany do zewnętrznego wejścia sprzężenia. Jeśli jednak oszacowanie liczności na podstawie sprzężenia jest nieprawidłowe i otrzymamy, powiedzmy, 1000 wierszy, wówczas SQL Server wykona 1000 skanowań indeksu klastrowego [Appointment]. Wydajność planu zapytań jest bardzo wrażliwa na problemy z szacowaniem.

Najbardziej bezpośrednim sposobem, aby nigdy więcej nie uzyskać tego planu zapytań, byłoby utworzenie indeksu obejmującego [Appointment]tabelę. Coś jak indeks na [ProjectId]i [Start]powinien to zrobić. Wygląda na to, że jest to dokładnie [idx_appointment_start]indeks utworzony w celu rozwiązania problemu. Innym sposobem, aby zniechęcić serwer SQL do wybierania planu zapytań, jest poprawienie oszacowania liczności na podstawie połączenia [Permission]i [Project]. Typowe sposoby, aby to zrobić, to zmiana kodu, aktualizacja statystyk, użycie starszej wersji CE, tworzenie statystyk wielokolumnowych, przekazywanie SQL Serverowi więcej informacji o zmiennych lokalnych, takich jak RECOMPILEpodpowiedź, lub materializowanie tych wierszy w tabeli tymczasowej. Wiele z tych technik nie jest dobrym podejściem, gdy potrzebujesz czasu odpowiedzi na poziomie ms lub musisz napisać kod przez ORM.

Utworzony indeks [AppointmentAttendee]nie jest bezpośrednim sposobem rozwiązania problemu. Otrzymasz jednak wielokolumnowe statystyki dotyczące indeksu, które mogą zniechęcić do złego planu zapytań. Indeks może zapewniać bardziej wydajny sposób dostępu do danych, co może również zniechęcać do złego planu zapytań, ale nie sądzę, aby istniała jakakolwiek gwarancja, że ​​to się nie powtórzy po włączeniu indeksu [AppointmentAttendee].

3. Jak mogę się upewnić, że tak się nie stanie z innym zapytaniem / planem?

Rozumiem, dlaczego zadajesz to pytanie, ale jest ono bardzo szerokie. Moja jedyna rada to próba lepszego zrozumienia pierwotnej przyczyny niestabilności planu zapytań, sprawdzenie, czy masz odpowiednie indeksy utworzone dla obciążenia, oraz dokładne przetestowanie i monitorowanie obciążenia. Microsoft ma kilka ogólnych porad dotyczących radzenia sobie z regresjami planu zapytań spowodowanymi przez nowy CE w SQL Server 2016:

Zalecany przepływ pracy w celu uaktualnienia procesora zapytań do najnowszej wersji kodu to:

1. Zaktualizuj bazę danych do SQL Server 2016 bez zmiany poziomu kompatybilności bazy danych (zachowaj ją na wcześniejszym poziomie)

2. Włącz magazyn zapytań w bazie danych. Aby uzyskać więcej informacji o włączaniu i korzystaniu z magazynu zapytań, zobacz Monitorowanie wydajności za pomocą magazynu zapytań.

3. Poczekaj wystarczającą ilość czasu, aby zebrać reprezentatywne dane dotyczące obciążenia.

4. Zmień poziom zgodności bazy danych na 130

5. Za pomocą SQL Server Management Studio oceń, czy po zmianie poziomu zgodności występują regresje wydajności dla określonych zapytań

6. W przypadku regresji wymuś wcześniejszy plan w magazynie zapytań.

7. Jeśli istnieją plany zapytań, które nie wymuszają lub wydajność nadal jest niewystarczająca, rozważ przywrócenie wcześniejszego poziomu zgodności, a następnie skorzystanie z obsługi klienta Microsoft.

Nie twierdzę, że musisz przejść na SQL Server 2012 i zacząć od nowa, ale opisana ogólna technika może być dla Ciebie przydatna.

2. Czy powinienem „wymusić” plan, który działa teraz dobrze?

To zależy wyłącznie od Ciebie. Jeśli uważasz, że masz plan zapytań, który działa dobrze dla wszystkich możliwych parametrów wejściowych, czujesz się dobrze z funkcjonalnością magazynu zapytań i chcesz mieć spokój ducha związany z narzucaniem planu zapytań, skorzystaj z niego. Wymuszanie planów zapytań, które miały regresje, jest jednak częścią zalecanej przez Microsoft zasady aktualizacji do SQL Server 2016.