Dealing with CXPACKET waits - setting cost threshold for parallelism

Question

As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.

I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a bad idea. But another idea is to increase the cost threshold before parallelism kicks in. The default of 5 for the cost of an execution plan is fairly low.

So I was wondering if there's a query out there already written that would find me the queries with the highest execution plan cost (I know you can find those with the highest duration of execution and so on - but is the execution plan cost retrievable somewhere, too?) and that would also tell me if such a query has been executed in parallel.

Does anyone have such a script at hand, or can point me in the direction of the relevant DMV, DMF or other system catalog views to find this out?

Answer 1

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of work, which is why it is such a prevalent "fix."

If you can get an actual plan for a query that seems to be responsible for high CXPACKET waits, load it into SQL Sentry Plan Explorer. There's usually a reason behind this; we show which parallel operations led to thread skew, and you can easily correlate that to estimates that are off (we highlight operations with estimates that are off by a at least certain threshold). Usually the underlying problem is really bad/out-of-date (or unavailable) statistics.

Unfortunately what you'll find in sys.dm_exec_cached_plans are estimated plans. They won't tell you whether the plan went parallel when it was actually used, because the actual plan is not what's cached. In some cases you expect to see both a serial and parallel plan for the same query; this is not how SQL Server deals with the situation for parallel plans that might be parallel at runtime. (Lots of information about that here.)

Answer 2

If you wish to see the actual execution plan of a query that is running.

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

First then enter the result into this query.

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

That will show you actual execution plan that sql used for that query. You could use that execution plan to see which thread you are waiting on.

I have also found that turning off hyper threading drastically reduced my CXpacket wait times.

Hope that helps.

Answer 3

The above answer by Aaron is correct.

I'd just like to add that, if you're not already using SQL Performance Dashboard Reports and the built-in Data Collector, you should start.

You could also take the following query, and modify it as you see fit:

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

Answer 4

In my previous experience Cost Threshold For Parallelism didn't help to reduce CXPACKET.

High CXPACKET wait can happen due to incorrect statistics resulting in Skewed Parallellism.

1. More on CXPACKET Waits: Skewed Parallelism
2. Microsoft Connect Item
3. My Query is (Not) Waiting Because of Parallelism? - Tim Ford

Following is SQL I used to find sessions that has both CXPacket and "other waits" in it (please see the dagram below).

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]

Large Scans can also be part of the root cause. When I checked the execution plan from the above query, I found one such scan in my database. There was also a missing index suggestion in the execution plan.

---