Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Intra-query parallelism deadlock?

Status
Not open for further replies.

ESquared

Programmer
Dec 23, 2003
6,129
US
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]
 
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]
 
> I don't even know where to begin.
It is very simple
Just add

OPTION (MAXDOP 1)

at the end of your query
 
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]
 
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

 
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.
Code:
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
Code:
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.
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top