Nigdy nie kończące się wyszukiwanie w magazynie zapytań

Powiem od początku, że moje pytanie / problem, wygląda podobnie do tego poprzedniego, ale ponieważ nie jestem pewien, czy przyczyną lub informacji wyjściowy jest taki sam, postanowiłem odpowiedzieć na moje pytanie z trochę więcej szczegółów.

Poddany problem:

• o dziwnej godzinie (pod koniec dnia roboczego) instancja produkcyjna zaczyna zachowywać się niepoprawnie:
  • wysoki procesor dla instancji (w stosunku do wartości wyjściowej ~ 30% wzrósł do około dwukrotnie i wciąż rośnie)
  • zwiększona liczba transakcji / sekundę (chociaż obciążenie aplikacji nie zauważyło żadnych zmian)
  • zwiększona liczba bezczynnych sesji
  • dziwne zdarzenia blokujące między sesjami, które nigdy nie wyświetlały tego zachowania (nawet czytanie niezaangażowanych sesji powodowało blokowanie)
  • górne oczekiwania na interwał to brak blokady strony na 1. miejscu, a blokady zajmują 2. miejsce

Wstępne dochodzenie:

• za pomocą sp_whoIsActive widzieliśmy, że zapytanie wykonane przez nasze narzędzie monitorujące decyduje się na bardzo wolne działanie i pobranie dużej ilości procesora, co nie zdarzyło się wcześniej;
• jego poziom izolacji został odczytany jako niezaangażowany;
• przyjrzeliśmy się planowi, który widzieliśmy niepoprawne liczby: StatementEstRows = "3.86846e + 010" z około 150 TB danych szacunkowych do zwrócenia
• podejrzewaliśmy, że przyczyną była funkcja monitorowania zapytań w narzędziu do monitorowania, więc wyłączyliśmy tę funkcję (otworzyliśmy również bilet u naszego dostawcy, aby sprawdzić, czy jest świadomy jakiegokolwiek problemu)
• od pierwszego zdarzenia zdarzyło się to jeszcze kilka razy, za każdym razem, gdy zabijamy sesję, wszystko wraca do normy;
• zdajemy sobie sprawę, że zapytanie jest bardzo podobne do jednego z zapytań używanych przez MS w BOL do monitorowania magazynu zapytań - zapytania, które ostatnio uległy regresji pod względem wydajności (porównanie różnych punktów w czasie)
• uruchamiamy to samo zapytanie ręcznie i widzimy to samo zachowanie (procesor ciągle rośnie, rośnie czas oczekiwania na zatrzask, nieoczekiwane blokady itp.)

Winne zapytanie:

Select qt.query_sql_text, 
    q.query_id, 
    qt.query_text_id, 
    rs1.runtime_stats_id AS runtime_stats_id_1,
    interval_1 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi1.start_time), 
    p1.plan_id AS plan_1, 
    rs1.avg_duration AS avg_duration_1, 
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2, 
    interval_2 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi2.start_time), 
    rs2.runtime_stats_id AS runtime_stats_id_2
From sys.query_store_query_text AS qt 
Inner Join sys.query_store_query AS q 
    ON qt.query_text_id = q.query_text_id 
Inner Join sys.query_store_plan AS p1 
    ON q.query_id = p1.query_id 
Inner Join sys.query_store_runtime_stats AS rs1 
    ON p1.plan_id = rs1.plan_id 
Inner Join sys.query_store_runtime_stats_interval AS rsi1 
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id 
 Inner Join sys.query_store_plan AS p2 
    ON q.query_id = p2.query_id 
Inner Join sys.query_store_runtime_stats AS rs2 
    ON p2.plan_id = rs2.plan_id 
Inner Join sys.query_store_runtime_stats_interval AS rsi2 
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
Where rsi1.start_time > DATEADD(hour, -48, GETUTCDATE()) 
    AND rsi2.start_time > rsi1.start_time 
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > rs1.avg_duration * 2
Order By q.query_id, rsi1.start_time, rsi2.start_time

Ustawienia i informacje:

• SQL Server 2016 SP1 CU4 Enterprise w klastrze Windows Server 2012R2
• Magazyn zapytań włączony i skonfigurowany jako domyślny (bez zmiany ustawień)
• baza danych zaimportowana z instancji SQL 2005 (i nadal na poziomie zgodności 100)

Obserwacja empiryczna:

• ze względu na bardzo zwariowane statystyki, wzięliśmy wszystkie * plan_persist ** obiekty użyte w złym oszacowanym planie (brak rzeczywistego planu, ponieważ zapytanie nigdy się nie zakończyło) i sprawdziliśmy statystyki, niektóre indeksy użyte w planie nie miały żadnych statystyk (DBCC SHOWSTATISTICS nic nie zwrócił, wybierz z sys.stats pokazał NULL funkcja stats_date () dla niektórych indeksów

Szybkie i brudne rozwiązanie:

• ręcznie utwórz brakujące statystyki dotyczące obiektów systemowych związanych ze sklepem zapytań lub
• wymusza uruchomienie zapytania przy użyciu nowego CE (traceflag) - który również utworzy / zaktualizuje niezbędne statystyki lub
• zmień poziom kompatybilności bazy danych na 130 (aby domyślnie używał nowego CE)

Tak więc moim prawdziwym pytaniem byłoby:

Dlaczego zapytanie w magazynie zapytań powoduje problemy z wydajnością w całej instancji? Czy jesteśmy na terytorium błędów w Query Store?

PS: Prześlę kilka plików (ekrany drukowania, statystyki IO i plany) w krótkim czasie.

Pliki dodane do Dropbox .

Plan 1 - początkowy nieprecyzyjny plan produkcji

Plan 2 - aktualny plan, stary CE, w środowisku testowym (to samo zachowanie, te same zwariowane statystyki)

Plan 3 - aktualny plan, nowy CE, w środowisku testowym

Jak powiedziałem w odpowiedzi, test empiryczny wykazał, że na obiektach systemowych sys.plan_persisted * istniały indeksy bez żadnych (żadnych) statystyk nad nimi. Podejrzewam, że dzieje się tak, ponieważ baza danych jest migrowana z instancji SQL 2005 i przez pewien czas utrzymywana na poziomie zgodności 100, dlatego nowy CE nie mógł zostać użyty.

Sprawdź liczbę wierszy:

Select count(1) from NoNameDB.sys.plan_persist_runtime_stats with (nolock) --60362   
Select count(1) from NoNameDB.sys.plan_persist_plan with (nolock) --1853    
Select count(1) from NoNameDB.sys.plan_persist_runtime_stats_interval with (nolock) --671    
Select count(1) from NoNameDB.sys.plan_persist_query with (nolock) --1091    
Select count(1) from NoNameDB.sys.plan_persist_query_text with (nolock) --911

To pokazało, że początkowe szacunki były błędne. Wykonano z połączeniem DAC, w przeciwnym razie tabele nie będą dostępne do zapytania.

Sprawdzanie statystyk:

DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats_interval', plan_persist_runtime_stats_interval_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats', plan_persist_runtime_stats_idx1);    
DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats', plan_persist_runtime_stats_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_plan', plan_persist_plan_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_plan', plan_persist_plan_idx1);    
DBCC SHOW_STATISTICS ('sys.plan_persist_query', plan_persist_query_cidx)    
DBCC SHOW_STATISTICS ('sys.plan_persist_query_text', plan_persist_query_text_cidx);

To pokazało, że niektóre indeksy miały puste statystyki (brak, brak, zero).

Wstępna poprawka:

UPDATE STATISTICS sys.plan_persist_runtime_stats WITH fullscan;
UPDATE STATISTICS sys.plan_persist_plan WITH fullscan;
UPDATE STATISTICS sys.plan_persist_runtime_stats_interval WITH fullscan;
UPDATE STATISTICS sys.plan_persist_query WITH fullscan;
UPDATE STATISTICS sys.plan_persist_query_text WITH fullscan;

Ten rodzaj poprawił statystyki i sprawił, że zapytanie zakończyło się w 10-12 sekund.

Druga poprawka :

(zweryfikowane tylko w środowisku testowym) i najprawdopodobniej właściwym, ponieważ pokazuje najlepsze statystyki dla zapytania, była zmiana poziomu kompatybilności bazy danych na 130. W rezultacie zapytanie zakończyło się za około 10-12 sekund z statystyki normalnej liczby (10 000 wierszy).

Poprawka pośrednia :

DBCC TRACEON (2312) -- new CE

Powiązana pomoc dotycząca statystyk ukrytych tabel systemowych.

Podstawowym problemem, który jest widoczny, jeśli otworzysz rzeczywisty plan w SSMS i spojrzysz na użycie procesora (lub sprawdzisz XML), jest węzeł 32, TVF. Winowajcą powolnych zapytań ze Sklepu Zapytań jest wielokrotny dostęp do TVF w pamięci .

Nie ma znaczenia, ile wierszy jest zwracanych z tych TVF, tylko liczba razy, gdy są one dostępne. Naprawą będzie wszystko, co możesz zrobić, aby oderwać swoje plany od wielokrotnego ich czytania.

Opierając się na moim ograniczonym debugowaniu (zarówno pod względem umiejętności, jak i spędzonego czasu), moja hipoteza jest taka, że ​​cała pamięć przypisana do konkretnego komponentu danych w magazynie zapytań jest skanowana przy każdym wykonaniu TVF. I nie były w stanie wpłynąć na to z alokacji pamięci albo sp_query_store_flush_dbalbo DBCC FREESYSTEMCACHE.

Dotychczasowe skuteczne obejścia obejmują przewodniki po planach, podpowiedzi (do tej OPTION(HASH JOIN, LOOP JOIN)pory działały dla mnie wystarczająco dobrze) i uruchamianie zapytań w magazynie zapytań w węźle tylko do odczytu AG.