December 11, 2007 at 7:57 am
I have several clients that are receiving this error, they are both utilizing the same function within our application. How can I determine the issues and where do I start? I'm not familiar enough with this sort of error, any help would be appreciated.
SQL Error 42000
EXEC cl_ansiclmedit1 'MAIN'
[Microsoft][ODBC SQL Server Driver][SQL Server]Intra-query parallelism caused your server command (process ID #120) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
December 11, 2007 at 9:28 am
It's basically telling you that the procedure had a high enough cost that it thought parallel execution would help. When executing in parallel, it caused a deadlock. First one I've heard of, but at least it presented you with the solution. It's suggesting that you set the max degree of parallelism for this procedure to 1 using the query hint MAXDOP = 1.
In the procedure, add this
OPTION (MAXDOP 1)
Or, if you don't want to modify the query, you can use a plan guide. That's a bit more complicated. Look it up in BOL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2007 at 10:10 am
Well I'm not sure if I can modify the query as it's hardcoded within our application....but I could be wrong. New to this but what is BOL?
December 11, 2007 at 10:51 am
Whoops. Assumptions will get you. Sorry.
BOL = Books Online. The documentation that comes with SQL Server.
If you can't modify the code directly, but you're getting this error, you may want to do a search on the topic "Plan Guides." It's a way of forcing an option, like MAXDOP, onto a query without modifying the code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2007 at 10:54 am
HA! That makes sense.... sometimes I'm slow....
thanks I'll take a look and see what I can find. much appriciated.
December 11, 2007 at 10:58 am
Oh, one other option, you could look at increasing the Parallelism Threshold for the server so that it's less likely to put this query into a parallel operation. That's another option.
Only thing is, you'd want to know for sure that you're not reaping benefits from the parallel operations prior to making them less likely.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2007 at 1:14 pm
BOL very helpful......
used
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 10;
GO
reconfigure;
GO
RECONFIGURE
-------
EXEC sp_configure 'recovery interval', 90
RECONFIGURE WITH OVERRIDE
GO
worked! THANKS
December 11, 2007 at 11:40 pm
Why the recovery interval?
Also what you can do is change the server-wide max degree of parallelism. I don't rcommend dropping it to 1. What I normally do is set it to a value between 1/2 and 1/3 of the number of processors in the server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2007 at 10:39 am
How can I do this server wide?
December 12, 2007 at 11:18 am
It's an advanced setting within the properties of the server. The default is usually zero.
This is the script from BOL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
You can set the value to what should be appropriate, say 1 to make it so it only ever uses a single processor. I still prefer setting the cost threshold to something higher, like 25, and leaving the MAXDOP alone.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2007 at 11:20 am
that is what I did...except I used 10...however I just received notice that are continuing to have this issue now in Outlook? Suggestions?
December 12, 2007 at 11:32 am
If it's that severe, you might want to try "turning off" parallelism by setting the MAXDOP to 1 for the server, but it's just a band-aid at this point.
If this is Outlook having the problem, you might want to do a search on a good Outlook forum and see if others have run into this issue. Also, you might try Microsoft's Connect site.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2007 at 11:44 am
I changed it back to 1 for now until I can figure out the underlying issue....hopefully that will at least buy me sometime.
April 5, 2009 at 11:32 am
Grant Fritchey
usually we use maxdop option to optimize the performance.
what will best solution without changing the maxdop option if we get this deadlock error.
thanks !!
April 5, 2009 at 3:02 pm
Limiting parallel execution only eliminates deadlocks if the deadlocks are being caused by parallel execution. There are plent of instance where it's not.
In addition to use MAXDOP on the query, you can set the server threshold higher, I usually do, or limit parallelism at the server level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply