Kiedy poprzednio szybkie zapytanie SQL zaczyna działać wolno, gdzie szukać źródła problemu?

tło

Mam zapytanie działające przeciwko SQL Server 2008 R2, które łączy i / lub dołącza do lewej około 12 różnych „tabel”. Baza danych jest dość duża, z wieloma tabelami ponad 50 milionów wierszy i około 300 różnymi tabelami. To dla dużej firmy, która ma 10 magazynów w całym kraju. Wszystkie magazyny czytają i zapisują dane w bazie danych. Jest więc dość duży i dość zajęty.

Zapytanie, z którym mam problem, wygląda mniej więcej tak:

select t1.something, t2.something, etc.
from Table1 t1
    inner join Table2 t2 on t1.id = t2.t1id
    left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
    [etc]...
where t1.something = 123

Zauważ, że jedno ze złączeń dotyczy nieskorelowanego zapytania podrzędnego.

Problem polega na tym, że od dzisiejszego ranka, bez żadnych zmian (o których ja lub ktokolwiek z mojego zespołu wie), w systemie, zapytanie, które zwykle trwa około 2 minut, zaczęło zajmować półtorej godziny - w ogóle pobiegł. Reszta bazy danych wciąż nuci. Wyciągnąłem to zapytanie ze sproca, w którym zwykle działa, i uruchomiłem je w SSMS z zakodowanymi na stałe zmiennymi parametrycznymi z tą samą powolnością.

Dziwność polega na tym, że kiedy biorę nieskorelowane pod-zapytanie i wrzucam je do tabeli tymczasowej, a następnie używam tego zamiast pod-zapytania, zapytanie działa poprawnie. Również (i to jest dla mnie najdziwniejsze), jeśli dodam ten fragment kodu na końcu zapytania, zapytanie działa świetnie:

and t.name like '%'

Doszedłem (być może niepoprawnie) z tych małych eksperymentów, że przyczyną spowolnienia jest sposób, w jaki konfigurowany jest buforowany plan wykonania SQL - gdy zapytanie jest nieco inne, musi utworzyć nowy plan wykonania.

Moje pytanie brzmi: kiedy zapytanie, które kiedyś działało szybko, nagle zaczyna działać powoli w środku nocy i nie ma to wpływu na nic innego oprócz tego jednego zapytania, w jaki sposób go rozwiązać i jak uniknąć tego w przyszłości ? Skąd mam wiedzieć, co SQL robi wewnętrznie, aby spowolnić (jeśli uruchomione zostało niepoprawne zapytanie, mógłbym uzyskać jego plan wykonania, ale nie zadziała - może oczekiwany plan wykonania dałby mi coś?)? Jeśli ten problem dotyczy planu wykonania, jak mogę nie myśleć SQL, że naprawdę kiepskie plany wykonania są dobrym pomysłem?

Nie jest to również problem z wąchaniem parametrów. Widziałem to wcześniej, i to nie wszystko, ponieważ nawet kiedy koduję zmienne w SSMS, wciąż mam niską wydajność.

Kiedy zapytanie, które kiedyś działało szybko, nagle zaczyna działać powoli w środku nocy i nie ma to wpływu na nic innego poza tym jednym zapytaniem, jak mogę go rozwiązać ...?

Możesz rozpocząć od sprawdzenia, czy plan wykonania jest nadal w pamięci podręcznej. Sprawdzić sys.dm_exec_query_stats, sys.dm_exec_procedure_statsi sys.dm_exec_cached_plans. Jeśli zły plan wykonania jest nadal buforowany, możesz go przeanalizować, a także sprawdzić statystyki wykonania. Statystyki wykonania będą zawierać informacje w postaci odczytów logicznych, czasu procesora i czasu wykonania. Mogą one dawać wyraźne wskazówki na temat problemu (np. Duży skan vs. blokowanie). Zobacz Identyfikowanie zapytań problemowych, aby uzyskać wyjaśnienie, jak interpretować dane.

Nie jest to również problem z wąchaniem parametrów. Widziałem to wcześniej, i to nie wszystko, ponieważ nawet kiedy koduję zmienne w SSMS, wciąż mam niską wydajność.

Nie jestem przekonana. Zmienne kodowane na stałe w SSMS nie dowodzą, że wcześniejszy zły plan wykonania nie został skompilowany na podstawie wypaczonych danych wejściowych. Zapoznaj się z opcją wąchania parametrów, osadzania i opcji RECOMPILE, aby znaleźć bardzo dobry artykuł na ten temat. Powolny w aplikacji, szybki w SSMS? Zrozumienie Performance Mysteries to kolejne doskonałe odniesienie.

Doszedłem (być może niepoprawnie) z tych małych eksperymentów, że przyczyną spowolnienia jest sposób, w jaki konfigurowany jest buforowany plan wykonania SQL - gdy zapytanie jest nieco inne, musi utworzyć nowy plan wykonania.

Można to łatwo przetestować. SET STATISTICS TIME ONpokaże Ci czas kompilacji vs. SQL Server: Liczniki wydajności statystyk ujawnią również, czy kompilacja jest problemem (szczerze mówiąc, uważam, że jest mało prawdopodobna).

Jest jednak coś podobnego, co możesz trafić: bramka przyznania zapytania. Czytaj Zrozumienie serwera SQL grant pamięci szczegóły. Jeśli twoje zapytanie w tej chwili żąda dużej granty, nie ma dostępnej pamięci, będzie musiało poczekać i wszystko będzie wyglądało jak „powolne wykonanie” dla aplikacji. Analiza statystyk informacji o oczekiwaniu ujawni, jeśli tak jest.

Aby uzyskać bardziej ogólną dyskusję na temat tego, co mierzyć i czego szukać, zobacz Jak analizować wydajność programu SQL Server

Jest to zmora do uruchamiania złożonych zapytań w SQL Server. Na szczęście nie zdarza się to często.

Sprawdź plan zapytania dla zapytania (gdy działa ono wolno). Zgaduję, że znajdziesz zagnieżdżone sprzężenie pętli występujące raz lub więcej razy w tabelach bez indeksów dla sprzężenia. To naprawdę spowalnia rzeczy. Aby przewinąć do przodu, sposobem na rozwiązanie tego jest podpowiedź. Dodaj następujące na końcu zapytania:

OPTION (MERGE JOIN, HASH JOIN)

To zazwyczaj naprawiło dla mnie ten problem w przeszłości.

Może się zdarzyć, że subtelne zmiany w tabeli (lub dostępności tymczasowej przestrzeni) powodują, że SQL optymalizuje preferowanie wolniejszego algorytmu łączenia. Może to być dość subtelne i dość nagłe. Podczas tworzenia tabeli tymczasowej optymalizator ma więcej informacji na temat tabeli (takich jak jej rozmiar), dzięki czemu może wygenerować lepszy plan.

Zwykle jest to brakujący indeks powodujący tego rodzaju problem.

To, co zwykle robię, to uruchomienie zapytania za pomocą SQL Management Studio i włączenie opcji „Uwzględnij rzeczywisty plan wykonania (CTRL + M)” i dowiedz się, które łączenie ma największy procent.

Aplikacja nie koncentruje się na wąskim gardle, ale można go znaleźć „szybko”, patrząc tylko na wynik.

przykład tutaj:

Ostatnio doświadczyłem tego samego problemu, który doprowadził mnie do tej strony.

@MartinSmith był przy czymś, kiedy zalecił aktualizację statystyk i wyjaśnienie planu. Chciałbym dodać, że powinieneś także spróbować rzucić okiem na uruchomione zadania / zapytania, które mogą tworzyć blokady, a tym samym spowalniać czas odpowiedzi.

W moim przypadku winowajcą były statystyki dotyczące gromadzenia pracy. Z jakiegoś powodu nie zakończyło się to w oknie, które powinno być, i działało po wznowieniu pracy użytkowników. Znalazłem proces, zabiłem go, a zapytania ponownie zaczęły odpowiadać.

Mam nadzieję, że to pomaga komuś innemu

Musisz także sprawdzić, czy działa jakieś zadanie tworzenia kopii zapasowej serwera lub archiwizacji \ indeksowania, gdy widzisz problem z wydajnością w T-SQL \ Procedura.