Article ID: 244934
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q244934
BUG #: 56539 (SQLBUG_70)
SYMPTOMS
Killing a query that is taking a parallel plan may cause the spid to stop in ROLLBACK until the server is stopped and restarted.
Random disconnects while running queries may also cause a spid to be trapped in rollback.
Another symptom of the spid in rollback are possible access violations (AVs) in the SQL Server Errorlog. When the access violation occurs dumps of other spids in the errorlog may be seen as well.
CAUSE
The problem revolves around the fact that SQL Server is doing a parallel plan for the query. Refer to the "More Information" section for further details.
WORKAROUND
Preventing a query from having a parallel plan can be accomplished in one of two ways. First, at the system level by setting the sp_configure value of max degree of parallellism to one.
For example:
sp_configure 'max degree of parallellism', '1'
reconfigure with override
Secondly, on a per query basis, by using the OPTION MAXDOP 1 option.
For example, on the pubs database:
select * from authors OPTION (MAXDOP 1)
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
Parallel queries are only considered when there are more than one idle processor available to SQL Server. On a heavily used system, the optimizer should not choose a parallel plan for any queries.
Setting the "max degree of parallelism" to force a serial plan may cause some performance degradation, but only during those periods of time when the number of simultaneous connections running queries is less than the number of available processors, and those queries would otherwise be eligible for a parallel plan.
The SQL Profiler can be used to determine what queries are using a parallel plan. The "Event Sub Class" column for the DELETE, INSERT, SELECT and UPDATE operators shows the degree of parallelism used. To monitor lock escalation, add the Lock Escalation event to your trace. Note that this event is only listed when you go to the Tools menu, choose Options, and then select All event classes.
Additional query words: DOP, rollback, AV
Keywords: kbbug kbfix KB244934
