Musisz zrozumieć błąd wykonywania równoległego zapytania

Dzisiaj doświadczyliśmy obniżenia wydajności naszego produkcyjnego serwera SQL. W tym czasie zarejestrowaliśmy kilka "The query processor could not start the necessary thread resources for parallel query execution"błędów. Lektura, którą wykonałem, sugeruje, że ma to związek z liczbą procesorów używanych podczas wykonywania złożonego zapytania. Jednak kiedy sprawdziłem podczas przerwy nasze CPU Utilization was only at 7%. Czy jest jeszcze coś, co może odnosić się również do tego, czego jeszcze nie spotkałem? Czy to prawdopodobny winowajcą obniżenia wydajności, czy też ścigam czerwony śledź?

Moje wartości sp_configure są następujące:

name                                minimum maximum config_value run_value
cost threshold for parallelism      0       32767   5            5

Kilka miesięcy temu miałem do czynienia z podobną sytuacją, w której ustawienie MAXDOP było domyślne, a zapytanie ucieczki wyczerpało wszystkie wątki robocze.

Jak zauważył Remus, nazywa się to głodem nici robotniczej .

Po wystąpieniu tego warunku na serwerze zostanie utworzony zrzut pamięci.

Jeśli korzystasz z wersji 2008R2 + SP1 i nowszej, sys.dm_server_memory_dumpspoda również lokalizację pliku zrzutu.

Wróćmy do problemu:

Na jeden węzeł NUMA przypada 1 wątek monitorowania harmonogramu, a ponieważ masz 2 węzły NUMA, będą 2 wątki monitorowania harmonogramu, które są odpowiedzialne za sprawdzanie kondycji wszystkich programów planujących co 60 sekund dla tego konkretnego węzła NUMA, upewniając się, że harmonogram jest zablokowany lub nie.

Za każdym razem, gdy nowe żądanie pracy jest pobierane z kolejki roboczej programu planującego, licznik procesów roboczych jest zwiększany. Jeśli więc harmonogram ma w kolejce żądanie pracy i nie przetworzy jednego z żądań pracy w ciągu 60 sekund, harmonogram zostanie uznany za zablokowany.

Z powodu uciekającego zapytania lub rozległego paralelizmu powstaje stan wyczerpania wątków roboczych, ponieważ wszystkie wątki są zajęte przez to pojedyncze uciekające zapytanie lub nadmierne przedłużone blokowanie i nie można wykonać żadnej pracy, chyba że zginie proces obrażający.

Najlepszym rozwiązaniem jest dostrojenie ustawienia Max Degree of Parallelism . Domyślnie 0 oznacza, że ​​SQL Server może wykorzystywać wszystkie dostępne procesory do przetwarzania równoległego i tam, wyczerpując wszystkie wątki robocze.

Istnieje wiele przyczyn, które mogą prowadzić do wyczerpania wątków roboczych:

• Długie łańcuchy blokujące powodują, że SQL Serverowi brakuje wątków roboczych
• Szeroki paralelizm prowadzi również do wyczerpania wątków roboczych
• Długie oczekiwanie na każdy rodzaj „zamka” - spinlocki, zatrzaski. Przykładem jest osierocona blokada.

Zapoznaj się z moją odpowiedzią tutaj , która pokaże Ci, jak obliczyć wartość MAXDOP dla instancji serwera.

Ponadto zdecydowanie zalecamy rozpoczęcie zbierania informacji o statystykach Wait dotyczących instancji serwera bazy danych.

Może być kilka powodów. Najprawdopodobniej nie masz pracowników. Zobaczyć max_worker_threads. Stan ten nazywany jest „szarpaniem pracownika”. Pracownicy mogą zostać skradzeni na wiele sposobów (z których żaden nie spowodowałby dużego wykorzystania procesora, btw), takich jak blokowanie wielu żądań lub robienie głupich rzeczy w CLR (np. Żądania HTTP).

Objaw, który widzisz, jest ofiarą problemu, a nie przyczyną. Nie możemy polecić rozwiązania bez znajomości przyczyny. Musisz zebrać liczniki perf, DMV i sprawdzić ERRORLOG, aby uzyskać więcej informacji.