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.

Back to the top

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.

Back to the top

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)
				

Back to the top

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:For more information, contact your primary support provider.

Back to the top

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.

Back to the top