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
Even if you are Running Service Pack 2 you can get caught out.
And finally, here is one specific to OLAP Services
Most of these problems can be worked around by disabling parallel execution in one of two ways:
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.
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.