Obsługa CXPACKET czeka - ustawienie progu kosztu dla równoległości

W odpowiedzi na moje poprzednie pytanie dotyczące rozwiązywania problemów z witryną Sharepoint zastanawiałem się, czy mogę coś zrobić z czekaniem na CXPACKET.

Wiem, że szarpnięciem kolana jest wyłączenie wszelkiej równoległości poprzez ustawienie MAXDOP na 1 - brzmi jak zły pomysł. Ale innym pomysłem jest zwiększenie progu kosztów, zanim rozpocznie się równoległość. Domyślna wartość 5 dla planu wykonania jest dość niska.

Zastanawiałem się więc, czy jest już napisane zapytanie, w którym znalazłyby mnie zapytania o najwyższym koszcie wykonania planu (wiem, że możesz znaleźć te, które mają najwyższy czas wykonania itd. - ale czy gdzieś można go uzyskać, też?), a to także powiedziałoby mi, czy takie zapytanie zostało wykonane równolegle.

Czy ktoś ma taki skrypt pod ręką, czy może skierować mnie w stronę odpowiednich widoków DMV, DMF lub innych katalogów systemowych, aby się tego dowiedzieć?

CXPACKETnigdy nie jest przyczyną; ponosi całą winę, ale zawsze jest to objaw czegoś innego. Musisz złapać te zapytania w akcie i dowiedzieć się, czym jest „coś innego”. Może się różnić od zapytania do zapytania, a całkowite wyłączenie paralelizmu jest - jak sugerujesz - w większości przypadków niepotrzebnym przesadą. Ale często jest to najmniej pracy, dlatego jest tak powszechną „poprawką”.

Jeśli możesz uzyskać rzeczywisty plan zapytania, które wydaje się odpowiadać za wysokie oczekiwania CXPACKET, załaduj go do Eksploratora planów SQL Sentry . Zwykle jest tego powód; pokazujemy, które operacje równoległe doprowadziły do ​​wypaczenia wątku, i możesz łatwo skorelować to z szacunkami, które są wyłączone (zaznaczamy operacje z szacunkami, które są wyłączone o co najmniej pewien próg). Zwykle podstawowym problemem są naprawdę złe / nieaktualne (lub niedostępne) statystyki.

Niestety w sys.dm_exec_cached_plans znajdują się szacunkowe plany. Nie powiedzą ci, czy plan był równoległy, gdy był faktycznie używany, ponieważ rzeczywisty plan nie jest buforowany. W niektórych przypadkach oczekujesz, że zobaczysz plan szeregowy i równoległy dla tego samego zapytania; nie w ten sposób SQL Server radzi sobie z sytuacją dla planów równoległych, które mogą być równoległe w czasie wykonywania. ( Wiele informacji na ten temat tutaj .)

Jeśli chcesz zobaczyć aktualny plan wykonania uruchomionego zapytania.

SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = [YourSPID]

Najpierw wprowadź wynik do tego zapytania.

SELECT query_plan FROM sys.dm_exec_query_plan (Enter the result here.)

To pokaże ci aktualny plan wykonania użyty przez SQL dla tego zapytania. Możesz użyć tego planu wykonania, aby zobaczyć, na który wątek czekasz.

Odkryłem również, że wyłączenie hiperwątkowości drastycznie skróciło czas oczekiwania na pakiet CX.

Mam nadzieję, że to pomaga.

Powyższa odpowiedź Aarona jest poprawna.

Chciałbym tylko dodać, że jeśli nie używasz jeszcze raportów SQL Performance Dashboard Reports i wbudowanego modułu gromadzącego dane , powinieneś zacząć.

Możesz również wziąć następujące zapytanie i zmodyfikować je według własnego uznania:

DECLARE @MinExecutions int; 
SET @MinExecutions = 5 

SELECT EQS.total_worker_time AS TotalWorkerTime 
      ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO 
      ,EQS.execution_count As ExeCnt 
      ,EQS.last_execution_time AS LastUsage 
      ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS 
      ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count  
       AS AvgLogicalIO 
      ,DB.name AS DatabaseName 
      ,SUBSTRING(EST.text 
                ,1 + EQS.statement_start_offset / 2 
                ,(CASE WHEN EQS.statement_end_offset = -1  
                       THEN LEN(convert(nvarchar(max), EST.text)) * 2  
                       ELSE EQS.statement_end_offset END  
                 - EQS.statement_start_offset) / 2 
                ) AS SqlStatement 
      -- Optional with Query plan; remove comment to show, but then the query takes !!much longer!! 
      --,EQP.[query_plan] AS [QueryPlan] 
FROM sys.dm_exec_query_stats AS EQS 
     CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP 
     LEFT JOIN sys.databases AS DB 
         ON EST.dbid = DB.database_id      
WHERE EQS.execution_count > @MinExecutions 
      AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) 
ORDER BY AvgLogicalIo DESC 
        ,AvgCPUTimeMiS DESC

W moim poprzednim doświadczeniu Próg kosztów dla równoległości nie pomógł w zmniejszeniu CXPACKET.

Wysokie CXPACKEToczekiwanie może się zdarzyć z powodu niepoprawnych statystyk skutkujących wypaczeniem równoległości.

1. Więcej na temat CXPACKET Waits: Skewed Parallelism
2. Element Microsoft Connect
3. Moje zapytanie czeka (nie) z powodu równoległości? - Tim Ford

Poniżej znajduje się SQL, którego użyłem do znalezienia sesji, które zawierają zarówno pakiet CXPacket, jak i „ inne oczekiwania ” (zobacz poniższy schemat).

SQL

DECLARE @RawResult TABLE ([database_id] INT,[session_id] INT,exec_context_id INT, [blocking_session_id] INT,task_state VARCHAR(20),
                          [cpu_time] BIGINT,[wait_duration_ms] BIGINT, [wait_type] VARCHAR(100),[resource_description] nvarchar(3072),
                          [sql_handle] varbinary(64),[plan_handle] varbinary(64)
                          )
INSERT INTO @RawResult
SELECT 
    [R].[database_id],
    [S].[session_id],
    [W].exec_context_id,
    [W].blocking_session_id,
    [T].task_state,
    [R].[cpu_time],
    [W].[wait_duration_ms],
    [W].[wait_type],
    [W].[resource_description],
    [R].[sql_handle],
    [R].[plan_handle]
FROM sys.dm_os_waiting_tasks [W]
INNER JOIN sys.dm_os_tasks [T] ON
    [W].[waiting_task_address] = [T].[task_address]
INNER JOIN sys.dm_exec_sessions [S] ON
    [W].[session_id] = [S].[session_id]
INNER JOIN sys.dm_exec_requests [R] ON
    [S].[session_id] = [R].[session_id]
WHERE [S].[is_user_process] = 1
--AND S.session_id <> @@SPID--???
--ORDER BY [W].[session_id],[W].[exec_context_id];


SELECT  
    DB_NAME(C.database_id) AS database_name,
    C.[database_id],
    C.[session_id],
    C.exec_context_id,
    C.blocking_session_id,
    C.task_state,
    C.[cpu_time],
    C.[wait_duration_ms],
    C.[wait_type],
    C.[sql_handle],
    C.[plan_handle],
    [H].text,
    [P].[query_plan],
    C.[resource_description]
FROM @RawResult C
OUTER APPLY sys.dm_exec_sql_text (C.[sql_handle]) [H]
OUTER APPLY sys.dm_exec_query_plan (C.[plan_handle]) [P]
WHERE C.[session_id] IN
                    (
                        SELECT A.[session_id]
                        FROM @RawResult A
                        INNER JOIN @RawResult B
                            ON A.[session_id] = B.[session_id]
                            AND A.wait_type='CXPACKET'
                            AND B.wait_type <> 'CXPACKET'
                    )
ORDER BY C.[session_id],C.[exec_context_id]

Duże skany mogą być również częścią głównej przyczyny. Kiedy sprawdziłem plan wykonania z powyższego zapytania, znalazłem jeden taki skan w mojej bazie danych. Brakowało również sugestii dotyczącej indeksu w planie wykonania.