Wysokie przydziały procesora CPU lub pamięci mogą wystąpić w przypadku zapytań korzystających z zoptymalizowanej pętli zagnieżdżonej lub sortowania wsadowego

Ten artykuł pomaga rozwiązać problem polegający na tym, że wysokie użycie procesora CPU występuje podczas uruchamiania zapytań w programie SQL Server.

Dotyczy: SQL Server
Oryginalny numer KB: 2009160

Symptomy

Jeśli używasz programu Microsoft SQL Server, który ma wysoce współbieżne obciążenie, możesz zauważyć pewne problemy z wydajnością zapytań. To zachowanie może być widoczne jako średnie lub wysokie użycie procesora CPU lub żądania udzielenia pamięci ekstremalnej.

Mogą również wystąpić inne skutki uboczne, takie jak warunki OOM, ciśnienie pamięci na potrzeby eksmisji pamięci podręcznej planu lub nieoczekiwane RESOURCE_SEMAPHORE oczekiwania.

Ponadto można zauważyć, że plany zapytań dla zapytań korzystających z wielu procesorów CPU lub pamięci mają atrybut OPTIMIZED dla operatora sprzężenia zagnieżdżonego ustawionego na wartość True.

Przyczyna

Ten problem może wystąpić w niektórych przypadkach, gdy procesor zapytań programu SQL Server wprowadza opcjonalną operację sortowania w celu zwiększenia wydajności. Ta operacja jest znana jako "Zoptymalizowana pętla zagnieżdżona" lub "Sortowanie wsadowe", a optymalizator zapytań określa, kiedy najlepiej wprowadzić te operatory. W rzadkich przypadkach zapytanie dotyka tylko kilku wierszy, ale koszt konfiguracji operacji sortowania jest tak znaczący, że koszt zoptymalizowanej zagnieżdżonej pętli przewyższa korzyści. W związku z tym w tych przypadkach można obserwować wolniejsze wyniki w porównaniu z oczekiwanymi wartościami.

Rozwiązanie

Flaga śledzenia 2340

Aby rozwiązać ten problem, użyj flagi śledzenia 2340, aby wyłączyć optymalizację. Flaga śledzenia 2340 instruuje procesor zapytań, aby nie używał operacji sortowania (sortowania wsadowego) do zoptymalizowanych sprzężeń zagnieżdżonych pętli podczas generowania planu zapytania. Ma to wpływ na całe wystąpienie.

Przed włączeniem tej flagi śledzenia możesz dokładnie przetestować aplikacje, aby upewnić się, że podczas wyłączania tej optymalizacji uzyskasz oczekiwane korzyści z wydajności. Jest to spowodowane tym, że optymalizacja sortowania może być przydatna, gdy istnieje duży wzrost liczby wierszy, które są dotykane przez plan.

Aby uzyskać więcej informacji, zobacz DBCC TRACEON — Trace Flags (Transact-SQL).

Modyfikowanie kodu w celu użycia wskazówki DISABLE_OPTIMIZED_NESTED_LOOP

Alternatywnie zastosuj następującą DISABLE_OPTIMIZED_NESTED_LOOP wskazówkę dotyczącą zapytania, aby wyłączyć optymalizację na poziomie zapytania.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT (DISABLE_OPTIMIZED_NESTED_LOOP)); 

Aby uzyskać więcej informacji, zobacz DISABLE_OPTIMIZED_NESTED_LOOP.

Więcej informacji

Opcje uruchamiania usługi aparatu bazy danych

Dotyczy

• SQL Server 2005 do programu SQL Server 2019