Troubleshooting
Problem
Customer upgrades to new high-specification SQL server. End users experience slower performance, and may have intermittent issues and perhaps system crashes.
Symptom
In other words, users are experiencing poor performance and error messages, with a high-specification SQL 2000 server.
There may be various different error messages, but in particular there will be events recorded inside the 'application log' of the Application and SQL server, referring to deadlocks.
Cause
Available evidence suggests that the cause is a problem in a third party (Microsoft) product.
There seems to be a Microsoft issue with their SQL 2000 Service Pack 4 software patch, since there have been reports of other 3rd party (non-Cognos) software experiencing issues after upgrading from SP3a to SP4. For example, see:
- http://www.sqlnewsgroups.net/sqlserver/t1883-sp4-reports-queries-blocking-themselves.aspx
- http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=52230
More information:
SQL 2000 allows two types of optimisations for multi-CPU servers:
- Processor Control - Also known as 'affinity'
- Parallelism - Also known as 'MAXDOP'
The bad performance (referred to in this Technote) is caused by a problem with 'parallelism'. This relates to 'parallel execution plans'. The concept of running 'parallel plans' for SQL queries is that (in theory) this should speed up queries by using more than one CPU. However, it is possible that some queries may run slower with parallel plans.
For example, the following scenario is possible:
- SQL Server attempts to run the Controller query in parallel, but finds some deadlocks
- MS SQL incorrectly decides to use all of its CPU processing power attempting to solve all the database locks *instead of* running the application query
- For example, the query 'select into' may be a good example. This may cause the locking of the entire TEMPDB
- By locking the TEMPDB, you end up causing all client actions to 'lock up' for some time (perhaps even 30 minutes or so) whilst MS SQL resolves all the locks
Microsoft have acknowledged that their SQL 2000 product's parallelism (MAXDOP) settings may need to be reconfigured for some environments. For more information, see their official website such as:
- http://support.microsoft.com/kb/837983 - You may receive error message 8650 when you run a query that uses intra-query parallelism
- http://support.microsoft.com/kb/329204 - General guidelines to use to configure the MAXDOP option
More Information:
If parallelism is turned on, as it is by default if you have multiple CPUs, then the query optimizer will evaluate each query for the possibility of using parallelism, which takes a little overhead. On many OLTP servers, the nature of the queries being run often do not lend itself to using parallelism for running queries. Examples of this include standard SELECT, INSERT, UPDATE and DELETE statements. Because of this, the query optimizer is wasting its time evaluating each query to see if it can take advantage of parallelism.
Environment
Customer feedback suggests that this exact problem is overwhelmingly mainly seen in SQL 2000 SP4.
However, it is possible that similar problems may also affect customers using SQL 2005. For example, there is a known Microsoft bug in their SQL 2005 SP2 product, which is explained here: http://support.microsoft.com/kb/968449
The problem will more likely affect SQL 2000 servers which:
- have CPUs with Intel's Hyperthreading feature enabled
- and/or have many/multiple separate physical CPU (for example 4), each with multi-core (for example 2 inside each CPU).
- In this example, the SQL server (by default) would have access to 8 CPU cores in total.
Diagnosing The Problem
Inside the Event Viewer on the SQL server, there may be multiple error messages inside the 'Application Log' which refer to deadlocks.
Resolving The Problem
The problem is normally fixed by lowering the 'parallelism' setting of SQL 2000. In other words, reduce the 'parallel execution' value of SQL 2000 from the default (use all cores) to a much lower value. Typically the best values to choose are either 2 or 1.
Steps:
- Obtain a short amount of downtime (for all systems which use the SQL server)
- Launch SQL Enterprise Manager
- Right-click on <SQLSERVERNAME> and choose 'properties'
- Click on 'Processor' tab
- Locate section 'Parallelism'
- Under the section 'Specify the number of processors to use for parallel execution of queries' change it from the default ('Use All available processors') to 'Use'
- Change the current number (for example '8') of processors to 2 (two)
- Click OK
- Reboot SQL server
- Test
- TIP: If Testing shows no improvement, repeat the above but instead change the current number of processors to 1 (one).
Additional Recommendations:
In addition to the above, the following may help:
- Ensure that you are running a regular (e.g. weekly) database maintenance plan to optimise indexes etc.
- Ensure that you are regularly (every night) updating database statistics. TIP: For details on how to achieve (1) and (2), see separate IBM Technote #1347569.
- Disable (in the SQL server's BIOS) the use of Intel's first generation Hyperthreading (found in early Intel Xeon chips).
Historical Number
1038843
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21371449