FIX: Killing Running Query May Result in a SPID in Constant ROLLBACK State and AV

Article translations Article translations
Article ID: 244934 - View products that this article applies to.
This article was previously published under Q244934
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 56539 (SQLBUG_70)
Expand all | Collapse all

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.

Properties

Article ID: 244934 - Last Review: October 22, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB244934

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com