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

ID: Q244934

The information in this article applies to:

Microsoft SQL Server version 7.0

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.

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.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Date        File name      Platform
   -------------------------------------

   7.00.753    s70753i.exe    x86
   7.00.753    s70753a.exe    Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

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 version 7.0.

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 : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug