Wyświetlenie szacowanego planu wykonania generuje CXPACKET, PAGELATCH_SH i LATCH_EX [ACCESS_METHODS_DATASET_PARENT] czeka

Używam Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) na maszynie wirtualnej 4 vCPU z max degree of parallelismustawioną 2i cost threshold for parallelismustawioną na 50.

Rano, gdy próbuję wyświetlić szacunkowy plan wykonania dla zapytania SELECT TOP 100 , napotykam ogromne oczekiwania, a operacja renderowania szacowanego planu zajmuje minuty, często w zakresie 5-7 minut. Ponownie, to nie jest faktyczne wykonanie zapytania, to tylko proces wyświetlania Szacowanego Planu Wykonania .

sp_WhoIsActivepokaże albo PAGEIOLATCH_SHczeka albo LATCH_EX [ACCESS_METHODS_DATASET_PARENT]czeka, a kiedy uruchomię skrypt WaitingTasks.sql Paula Randala podczas operacji, wyświetla CXPACKEToczekiwania z wątkami roboczymi pokazującymi PAGEIOLATCH_SHoczekiwania:

* pole opisu zasobu = exchangeEvent id=Port5f6069e600 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=1 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen

Wątki robocze wyglądają statsna zapamiętywanie całej tabeli (ponieważ te numery stron, a także kolejne numery stron pokazane w zapytaniu Paula Randala z powrotem do klucza klastrowego statstabeli). Po powrocie planu jest on w zasadzie natychmiastowy przez resztę dnia, nawet po tym, jak widzę większość statsścierania tabeli z pamięci podręcznej i pozostały tylko różne rekordy (które, jak zakładam, zostały pobrane z powodu operacji wyszukiwania z podobnych zapytań).

Oczekiwałbym tego początkowego zachowania, gdyby zapytanie faktycznie było wykonywane przy użyciu planu korzystającego z operatorów SCAN, ale dlaczego robi to, oceniając plany wykonania, aby dotrzeć do operatora SEEK, jak pokazano w powyższym planie? Co mogę zrobić (oprócz uruchamiania tego wyciągu przed godzinami pracy, aby moje dane były odpowiednio buforowane), aby poprawić wydajność tutaj? Zakładam, że para indeksów obejmujących byłaby korzystna, ale czy naprawdę gwarantowałyby jakiekolwiek zmiany w zachowaniu? Muszę tu pracować w ramach pewnych ograniczeń okna przechowywania i konserwacji, a samo zapytanie jest generowane z rozwiązania dostawcy, więc wszelkie inne sugestie (oprócz lepszego indeksowania) byłyby w tym momencie mile widziane.

Wygląda na to, że Twoje żądanie rzeczywistego planu wykonania uruchomiło aktualizacje statystyk. Ponieważ wspominasz, że dzieje się to rano, wyobrażam sobie, że istnieje proces z dnia na dzień, który wprowadza wiele modyfikacji w zaangażowanych tabelach?

W związku z tym SQL Server używa statystyk do utworzenia planu, osiągnął próg modyfikacji i wykonuje automatyczne aktualizacje statystyk w ramach operacji.

W pliku XML szacowanego planu, który udostępniłeś, widzę te zbliżone daty aktualizacji statystyk z dzisiejszego ranka:

LastUpdate="2019-05-06T09:12:49.92"
LastUpdate="2019-05-06T09:12:58.3"
LastUpdate="2019-05-06T09:13:20.33"
LastUpdate="2019-05-06T09:13:09.67"
LastUpdate="2019-05-06T09:12:59.05"
LastUpdate="2019-05-06T09:12:39.56"

Jeśli są to bardzo duże, zajęte tabele (wydaje się prawdopodobne na podstawie odsetków próbkowania), to nie jest zaskakujące, że aktualizacje statystyk zabierają dużo mocy.

Kiedy widzę długi szacowany czas planu w SSMS, jest to jeden z następujących porządków prawdopodobieństwa:

1. Optymalizator zapytań zdecydował, że musi utworzyć lub zaktualizować statystyki.
2. Rozmiar szacowanego planu jest bardzo duży (powiedzmy> 10 MB) i jego wyświetlenie zajmuje po prostu SSMS.
3. Sama kompilacja zapytań zajęła dużo czasu ze względu na użycie procesora w poszukiwaniu wystarczająco dobrego planu.
4. Serwer jest pod ekstremalnym przymusem. Na przykład być może będę musiał czekać na dostępność bramy .
5. Różne błędy, które prowadzą do bardzo długich czasów kompilacji.

W twojej sytuacji odpowiedź jest prawie na pewno, że SQL Server aktualizuje lub tworzy statystyki. Jest kilka wskazówek: rozmiar planu zapytań jest niewielki, plan zapytań jest stosunkowo prosty i tani, a procesor kompilacji jest znacznie krótszy niż czas kompilacji:

Nowy współpracownik, Josh Darnell, wskazał również dobrą wskazówkę dotyczącą ostatniego zaktualizowanego czasu na statystyki w XML.

SQL Server 2019 wprowadza nowy typ oczekiwania, WAIT_ON_SYNC_STATISTICS_REFRESH , na czas oczekiwania na zapytania dotyczące aktualizacji statystyk. O wiele łatwiej jest zdiagnozować ten problem w tej wersji. Do tego czasu będziesz musiał polegać na technikach pośrednich.

Obejścia obejmują aktualizację statystyk w okresie konserwacji lub włączenie automatycznej aktualizacji statystyk Async dla bazy danych. Przed zmianą zapoznaj się z pełnymi konsekwencjami tej opcji. Plany zapytań zostaną utworzone przed aktualizacją statystyk zamiast po aktualizacji statystyk. W przypadku niektórych obciążeń może to być ogromna wygrana. Dla innych może przynieść więcej szkody niż pożytku.