Serious wait times with CXPACKET

[sqlsadie108]

Hi,

SQL Server 2005, SP2, 32 bit, 4 processors

Stored proc runs and never finishes...

I am seeing a lot of CXPACKET wait times on an UPDATE statement in a stored procedure. Research on the internet seems to point to poor usage of parallelism among the processors, or something to that effect.

Various "cures" include tuning the query, adding indexes, adding a hint to turn off the parallelism and redoing the statistics.

Has anyone here seen this problem, and how did you resolve it?

Thanks much

 

[ptheriault]

I have seen this problem, In fact I'm still seeing a lot of this problem. You can set the MAXDOP option as a query hint. But if you system is like mine that is a lot of queries to find and change.

If you run sp_configure you can change your server setting of max degree of parallelism.

This is a direct quote from Microsoft.

Running with a parallel plan is not inherently bad and should provide the fastest response time for that query. However, the response time for a given query must be weighed against the overall throughput and responsiveness of the rest of the queries on the system. Parallel queries are generally best suited to batch processing and decision support workloads and might not be desirable in a transaction processing environment.

This query will show you queries that are currenlty executing in parallel.

select  
    r.session_id, 
    r.request_id, 
    max(isnull(exec_context_id, 0)) as number_of_workers, 
    r.sql_handle, 
    r.statement_start_offset, 
    r.statement_end_offset, 
    r.plan_handle 
from  
    sys.dm_exec_requests r 
    join sys.dm_os_tasks t on r.session_id = t.session_id 
    join sys.dm_exec_sessions s on r.session_id = s.session_id 
where  
    s.is_user_process = 0x1 
group by  
    r.session_id, r.request_id,  
    r.sql_handle, r.plan_handle,  
    r.statement_start_offset, r.statement_end_offset 
having max(isnull(exec_context_id, 0)) > 0

This query will show you all your current cached query plans that could resultin a parallel query

-- 
-- Find query plans that may run in parallel 
-- 
select  
    p.*,  
    q.*, 
    cp.plan_handle 
from  
    sys.dm_exec_cached_plans cp 
    cross apply sys.dm_exec_query_plan(cp.plan_handle) p 
    cross apply sys.dm_exec_sql_text(cp.plan_handle) as q 
where  
    cp.cacheobjtype = 'Compiled Plan' and 
    p.query_plan.value('declare namespace  
p="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/07/showplan";[/URL] 
        max(//p:RelOp/@Parallel)', 'float') > 0

These 2 queries can show you the top 50 CPU pigs.

select top 50  
    sum(qs.total_worker_time) as total_cpu_time,  
    sum(qs.execution_count) as total_execution_count, 
    count(*) as  number_of_statements,  
    qs.plan_handle  
from  
    sys.dm_exec_query_stats qs 
group by qs.plan_handle 
order by sum(qs.total_worker_time) desc

--To find more details about plan run this query
-- takes either spid or plan_handle
SELECT *
FROM sys.dm_exec_query_plan(0x06000700185B93054043ED7F010000000000000000000000)

Also, these links may help.

http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlsadie108]

Thanks much

I used the OPTION (MAXDOP = 1) hint and it didn't do one iota of good. Yes, no more CXPACKETs, but the query still runs terribly... please see my other post under Programming

 

[ptheriault]

I ended up changing my server setting.

sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

I have seen much improved performance.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlsadie108]

Are you on 2005?

 

[ptheriault]

Yes

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[joseph77777]

My server started giving long delays overnight.

I'm still trying to find the root cause, but I had about 20 connections to the database, almost half with a Wait Type of CXPACKET.

After applying the configuration change (mas processors = 1), the connections faded to eight, with no wait types.

It really helped here.

Oh, my application is a checkbook. More reference than transactional, but highly used.