Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL 7 Bugs on SMP Servers

By Neil Boyle,

SQL 7 bugs to watch for on multi-processor machines


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 it’s 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.

In and 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.

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 & 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

About the author

Neil Boyle is an independant SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk

Total article views: 3311 | Views in the last 30 days: 1
 
Related Articles
FORUM

parallelism

parallelism

FORUM

Parallelism in Simple Queries

Parallelism in Simple Queries

FORUM

Intra-query parallelism

Intra-query parallelism - Can someone please help?

ARTICLE

SQL Server 2012’s Information on Parallel Thread Usage

In the SQL Server 2012 execution plan we can see better the true thread reservations for a query and...

FORUM

Sql server performance issues

Parallelism

Tags
bugs    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones