SQL 7 Bugs On Multi-Processor Machines

March 18, 2001

If you are running SQL 7 on a server with more than one CPU then a single query can take advantage of multiple CPUs by splitting its workload across more than one processor.

This is a real bonus in most cases, but in rare instances queries can run appallingly slowly when the query optimizer chooses a parallel plan. The situation is all the more confusing because the Query Analyzer can show you an execution plan for the query that looks really efficient.

And in rare cases, SPIDS can run forever, phantom errors can be generated, incorrect results may be produced, and in extreme cases SQL Server may keel over.

Because the problems listed here are specific to machines running SQL 7 on multi-processor boxes, you can encounter them at unexpected times, such as when you upgrade a perfectly functioning database to a higher specification server, or upgrading a database from SQL 6.5 to 7.

After finding out about one of these problems the "hard way", I decided to spend some time looking into the subject at the Microsoft support home page. Here is what I found:

If you are not running Service Pack 2 or later, then you can be hit by

• This bug causes slow query execution
• This one can cause killed queries to be left in a rollback state until you cycle SQL Server.
• Update statements don't seem to be working? Check out this bug
• This one causes complex Inserts, Updates and Deletes to fail

Even if you are Running Service Pack 2 you can get caught out.

• Bug Q254903 can cause queries to run very slowly. It is listed as fixed in Service pack 3 by Microsoft, but at the time of writing the bug has not been documented.
• Bug Q266234 can cause access violations in SQL Server.
• Bug Q266368 can cause update queries to never complete, and applies only to SP2 servers.
• This one causes never-ending processes when you cancel a SELECT statement.
• This lock escalation problem can cause the server to shut down in rare circumstances.
• This one can generate phantom error messages when no rows are returned.
• Your tempdb space can get eaten up by this bug
• Phantom rows may be returned by this bug
• Delete statements can cause phantom database corruption warnings
• Four CPUs are better than two? Not in this case
• This one causes Access Violations

And finally, here is one specific to OLAP Services

• Update: A reader kindly sent me this description of a bug that affects SMS users.

I have seen SMS server systems that use the smsprov.dll that translates WMI statements to SQL statement, but assumes case sensitivity of the SQL server. You understand with multiprocessors enabled or parallelism. YOUR SMS SERVER WILL HANG if it is a heavily utilized big one. With one processor active or a hot-fixes smsprov32.dll that is case insensitive all issues disappeared.

Most of these problems can be worked around by disabling parallel execution in one of two ways:

1. The Optimizer hint OPTION (MAXDOP 1) can be used with individual select statements that are suffering from either of these bugs. Just to add insult to injury, this option sometimes fails for update statements prior to SP3.
2. Parallel execution can be disabled server-wide with the configuration option (sp_configure 'max degree of parallelism', 1)

Which solution you use depends on your requirements--do you want to take the safe option and disable parallel execution server-wide, or do you want to cure just the queries you know you are having problems with, and let the rest take advantage of parallel execution.

Which solution you choose if up to you, but the second option might be a good bet if you are worried about upgrading mission critical systems.

It's worth noting that SQL Server can still take advantage of multiple processors to run multiple queries in parallel f you choose either of these options, but it will not try to split a single query across multiple CPUs when these options are set.

Wrapping Up

While the list of bugs above may look pretty scary, I was using SQL 7 on SMP machines for quite some time before coming across even one, and that is not a unique experience. Most of these bugs seem to happen in rare circumstances.

I hope I have missed nothing, but there may well be other funnies, undocumented or otherwise. If you have any further information, then feedback is always appreciated.

Further reading

• SQL 7 Service pack 3 fix list
• SQL 7 Service pack 2 fix list
• An in-depth look at query processors
• This MSDN article and the links at the bottom of it are updated for SQL 2000, but they are still a good introduction to parallel queries.