February 13, 2003 at 1:30 pm
We installed SP4 on 7.0 and now an application came across this error:
Server: Msg 8650, Level 13, State 1, Procedure usp_build_report, Line 254
Intra-query parallelism caused your server command (process ID #25) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Now from looking at this KB article (http://support.microsoft.com/default.aspx?scid=kb;en-us;317821), looks like this problem was introduced with SP4 and the work around is to basically turn on parrellel query processing.
Here are my questions regarding this subject:
1) Have others experienced this problem?
2) What kind of code is actually causing this?
3) What methods besides turning of parallelism and query hints, did people use to get around this error?
4) Are the ways to identify which SQL commands might, or will recieve this error without actually running the command?
We want to allow parallel queries to run, would like to not have to put query hints on all application that have this problem.
Any help would be greatly appreciated.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 13, 2003 at 2:36 pm
I can't really answer all of the questions but I may be able to provide some insight. Using the MaxDop1 hint in your query forces SQL Server to use only one processor when your server is configured to use Parallelism. You can turn it off by right clicking your server and selecting properties in EM. In the environment I'm working in we were using parallelism on SQL 7.0 and I would recommend turning it of as did we. 7.0 had lots of problems merging the queries back together after splitting off on different processors. Hope that helps a little.
E...
February 13, 2003 at 2:54 pm
Turning parallelism off, surely solves the problem, but I am wondering did parallelism work in SP 3 and earlier? Or where we just not seeing the errors, it really was failing in SP 3 and earlier, and we just didn't know.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 13, 2003 at 3:26 pm
When we had it turned on we were on Sp3, It worked somtimes and then other times it didn't, I would guess it sounds like it may have got worse with SP4. All of my servers have it turned off right now.
February 14, 2003 at 8:02 am
According to the link you gave and to answer your question, "maybe". And I quote:
"If you configure SQL Server to use more than one processor on a multi-processor computer, and you run a query that uses intra-query parallelism, the query may stop responding on Microsoft SQL Server 7.0 "Golden",
Service Pack (SP1), Service Pack (SP2), and Service Pack (SP3) versions"
May stop.
It appears from my reading, SP4 made you aware that the query may stop and gave reasson why. I guess you can call that an improvement.
I remember running into issues with this on Data Generals SQL 7 SP2, running SAP R3 software. Setting the max degree of parallelism option to 1 corrected the problem of our querys just hanging. They wouldn't crash or anything they'd just hang until you kill them. In our case making the change didn't cause much of a performance lost in long running query processes. Not trying to sell anything, but I haven't seen the issue come up on our Compaq hardware.
Zach
quote:
Turning parallelism off, surely solves the problem, but I am wondering did parallelism work in SP 3 and earlier? Or where we just not seeing the errors, it really was failing in SP 3 and earlier, and we just didn't know.Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
John Zacharkan
John Zacharkan
February 14, 2003 at 9:25 am
Guess I was trying to assess how much of an impact turning off parallelism migh cause. I'm assuming with parallelism turned on not all query the queries would get locked, so some would take advantage of parallelism. Therefore turning it off would decrease performance for those queries that used parallelism. Since you can monitor execution plans using profiler, does anyone know how to filter on execution plans that use parallelism? Or at least a way to search through profiler output to find the queries that use parallelism?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply