Intra-query parallelism deadlock?

[ESquared]

A process that's been running hundreds of times a day for almost two years gave this error message last night:

---------------------------
Crystal Reports: Database Error
---------------------------
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Intra-query parallelism caused your server command (process ID #56) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
---------------------------
OK
---------------------------

Nice, eh? I don't even know where to begin. I read what Microsoft has to say about it so I can only conclude that it's a matter of out-of-date statistics or a fragmented index, since nothing has changed on that server as far as I know.

Anyway, a new one for me.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[JackVam]

Hi
Do you have a single processor machine?
has your data changed recently?

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[ESquared]

No, it's a multiple processor machine. I can look up how many when I get back there.

The data has not had any reason to change any more than at any other time. I suspect it's just the condition of the indexes and statistics. It IS a 150 Gb database and growing...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[evilDBA]

> I don't even know where to begin.
It is very simple
Just add

OPTION (MAXDOP 1)

at the end of your query

 

[ESquared]

evilDBA,

Thank you for being evil! I, too, can read the article I linked.

For one thing, there are many queries involved in the two stored procedures called, so I don't know which one actually caused the problem. Adding maxdop 1 to all of them is certainly doable, but before I do that I'd like to know more.

And for another, I wasn't really looking for fixes (the microsoft article gives those) but was interested in speculation on causes.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[evilDBA]

You can trace individual statements in stored procs using SQL profiler. Or, if you isolated you query, you can add debug prints

On OLTP systems, where the number of simultaneous active connections >> N of processors, I recommend to disable parralelism completely.

On OLAP/Reporting systems it is another story, but I guess it is not so difficult to find where exactly is your bad query

 

[ptheriault]

E2,
I had a very similar problem. After working with MS it was decided to just turn off paraellism for the whole server.

Here are some queries that will help you.

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

-- 
-- 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

To turn off parallelism

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

parallelism can cause problems in an OLTP environment. Basically one thread on scheduler(CPU) is deadlocking with another thread an a different schedular. I spent a good month working with MS before we decided to turn it off. Since then I haven't had a problem and I've noticed an increase in performance for queries that used to run in parallel.




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

 

[ESquared]

Thanks for the input. I'll pursue this with the DBA for that system. Any more input is welcome.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]