FIX: Some complex queries are slower after you install SQL Server 2000 Service Pack 2 or SQL Server 2000 Service Pack 3

View products that this article applies to.

Bug #: 473314 (SQL Server 8.0)

SYMPTOMS

After you install Microsoft SQL Server 2000 Service Pack 2 (SP2) or SQL Server 2000 Service Pack 3 (SP3), you may notice that complex queries that contain many join clauses and sub-queries are slower than before.

For a list of all publicly released SQL Server 2000 Post-Service Pack 3a hotfixes, see the following article in the Microsoft Knowledge Base:

810185 (http://support.microsoft.com/kb/810185/ ) SQL Server 2000 hotfix update for SQL Server 2000 Service Pack 3 and 3a

Back to the top

CAUSE

The SQL Server optimizer has built-in thresholds that determine when to stop or to continue optimizing a query after a plan has been obtained. These thresholds exist to make sure that a balance exists between compilation time and execution time.

In the original released version of SQL Server 2000, a problem with the thresholds was found that caused SQL Server to spend a long time optimizing certain queries. In SQL Server 2000 SP2, a change to the thresholds was made to fix the problem. The new thresholds work well most of the time. However, there are always queries that are right at the boundary of the thresholds that may receive a better execution plan with more optimization.

Back to the top

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack

Back to the top

Hotfix information

Prerequisites

Microsoft SQL Server 2000 Service Pack 3 (SP3).

For additional information about how to obtain SQL Server 2000 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:
290211 (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack
You must also have SQL Server 2000 Hotfix build 2000.80.977 installed.

For additional information about error messages that you receive when the SQL Server 2000 Post SP3a hotfix build 200.80.977 is not installed, click the following article number to view the article in the Microsoft Knowledge Base:
888007 (http://support.microsoft.com/kb/888007/ ) You receive a "The product does not have a prerequisite update installed" error message when you try to install a SQL Server 2000 post-Service Pack 3 hotfix
For additional information about the hotfix installer for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
842960 (http://support.microsoft.com/kb/842960/ ) Description of the 32-bit SQL Server 7.0 and SQL Server 2000 hotfix installer

Restart information

You must restart your computer after you apply this hotfix.

Hotfix file information

This hotfix contains only those files that are required to correct the issues that this article lists. This hotfix may not contain of all the files that you must have to fully update a product to the latest build.

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

SQL Server 2000 32-bit versions

   Date         Time   Version        Size       File name
   ------------------------------------------------------------------
   03-Dec-2004  17:49  2000.80.994.0    664,128  Autoplt.dll      
   03-Dec-2004  17:49  2000.80.994.0     78,400  Console.exe      
   03-Dec-2004  17:49  2000.80.994.0    332,352  Ctasksui.dll     
   03-Dec-2004  17:49  2000.80.994.0    315,968  Custtask.dll     
   03-Dec-2004  17:49  2000.80.994.0     33,340  Dbmslpcn.dll     
   08-Apr-2004  12:42                   786,432  Distmdl.ldf
   08-Apr-2004  12:42                 2,359,296  Distmdl.mdf
   30-Sep-2003  13:55                       180  Drop_repl_hotfix.sql
   03-Dec-2004  17:49  2000.80.994.0  1,905,216  Dtspkg.dll       
   03-Dec-2004  17:49  2000.80.994.0    528,960  Dtspump.dll      
   03-Dec-2004  17:49  2000.80.994.0  1,557,052  Dtsui.dll        
   03-Dec-2004  17:49  2000.80.994.0    639,552  Dtswiz.dll       
   30-Sep-2003  13:55                   747,927  Instdist.sql
   30-Sep-2003  13:55                     1,581  Inst_repl_hotfix.sql
   03-Dec-2004  17:49  2000.80.994.0    352,828  Isqlw.exe        
   03-Dec-2004  17:49  2000.80.994.0     82,492  Itwiz.exe        
   03-Dec-2004  17:49  2000.80.994.0     90,692  Msgprox.dll      
   28-Oct-2004  23:16  8.11.41022.0     226,304  Mssdi98.dll      
   03-Dec-2004  17:49  2000.80.994.0     62,024  Odsole70.dll     
   03-Dec-2004  17:50  2000.80.994.0     25,144  Opends60.dll     
   03-Dec-2004  17:49  2000.80.994.0     57,904  Osql.exe         
   03-Dec-2004  17:49  2000.80.994.0    279,104  Pfutil80.dll     
   30-Sep-2003  13:48                   550,780  Procsyst.sql
   08-Apr-2004  12:42                    12,305  Qfe469315.sql
   08-Apr-2004  12:42                    19,195  Qfe469571.sql
   26-Aug-2004  15:14                     5,157  Qfe472197.sql
   03-Dec-2004  17:49  2000.80.994.0    143,940  Qrdrsvc.exe      
   03-Dec-2004  11:25                 1,136,072  Replmerg.sql
   03-Dec-2004  17:49  2000.80.994.0    221,768  Replprov.dll     
   03-Dec-2004  17:49  2000.80.994.0    307,784  Replrec.dll      
   03-Dec-2004  11:38  2000.80.994.0    159,813  Replres.rll
   02-Dec-2004  17:53                 1,088,240  Replsys.sql
   30-Sep-2003  13:55                   986,603  Repltran.sql
   24-Nov-2004  15:04                        55  Repl_uninstall.sql
   03-Dec-2004  17:49  2000.80.994.0    287,304  Rinitcom.dll     
   03-Dec-2004  17:49  2000.80.994.0     78,416  Sdiclnt.dll      
   03-Dec-2004  17:49  2000.80.994.0     66,112  Semmap.dll       
   03-Dec-2004  17:49  2000.80.994.0     57,916  Semnt.dll        
   03-Dec-2004  17:49  2000.80.994.0    492,096  Semobj.dll       
   03-Dec-2004  12:08  2000.80.994.0    172,032  Semobj.rll
   03-Dec-2004  17:49  2000.80.994.0     53,832  Snapshot.exe     
   30-Sep-2003  13:48                   117,834  Sp3_serv_uni.sql
   03-Dec-2004  17:48  2000.80.994.0     28,672  Sqlagent.dll     
   03-Dec-2004  17:49  2000.80.994.0    311,872  Sqlagent.exe     
   03-Dec-2004  17:48  2000.80.994.0    168,001  Sqlakw32.dll     
   03-Dec-2004  17:49  2000.80.994.0     33,344  Sqlctr80.dll     
   03-Dec-2004  17:49  2000.80.994.0  4,215,360  Sqldmo.dll       
   03-Dec-2004  17:49                    25,172  Sqldumper.exe    
   03-Dec-2004  11:23  2000.80.994.0     28,672  Sqlevn70.rll
   03-Dec-2004  17:49  2000.80.994.0    156,224  Sqlmaint.exe     
   03-Dec-2004  17:49  2000.80.994.0    180,792  Sqlmap70.dll     
   03-Dec-2004  17:49  2000.80.994.0    188,992  Sqlmmc.dll       
   03-Dec-2004  12:16  2000.80.994.0    479,232  Sqlmmc.rll
   03-Dec-2004  17:49  2000.80.994.0    401,984  Sqlqry.dll       
   03-Dec-2004  17:49  2000.80.994.0     57,920  Sqlrepss.dll     
   03-Dec-2004  17:49  2000.80.994.0  7,716,945  Sqlservr.exe     
   03-Dec-2004  17:49  2000.80.994.0    590,396  Sqlsort.dll      
   03-Dec-2004  17:49  2000.80.994.0     45,644  Sqlvdi.dll       
   03-Dec-2004  17:49  2000.80.994.0    106,588  Sqsrvres.dll     
   03-Dec-2004  17:49  2000.80.994.0     33,340  Ssmsgnet.dll     
   03-Dec-2004  17:49  2000.80.994.0     33,340  Ssmslpcn.dll     
   03-Dec-2004  17:49  2000.80.994.0     33,340  Ssmsqlgc.dll     
   03-Dec-2004  17:49  2000.80.994.0     82,492  Ssnetlib.dll     
   03-Dec-2004  17:49  2000.80.994.0     25,148  Ssnmpn70.dll     
   03-Dec-2004  17:49  2000.80.994.0    123,456  Stardds.dll      
   03-Dec-2004  17:49  2000.80.994.0    158,240  Svrnetcn.dll     
   03-Dec-2004  17:49  2000.80.994.0     76,416  Svrnetcn.exe     
   03-Dec-2004  17:49  2000.80.994.0     49,228  Ums.dll          
   03-Dec-2004  17:49  2000.80.994.0     74,304  Xplog70.dll      
   03-Dec-2004  17:49  2000.80.994.0     98,872  Xpweb70.dll

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

Back to the top

MORE INFORMATION

To enable this hotfix, trace flag 9062 must be activated. This is a dynamic trace flag that you can activate by using one of the following methods:

Back to the top

Activate flag 9062 by using SQL Query Analyzer

Open SQL Query Analyzer.
Connect to the related SQL Server-based computer.
Copy the following code to the Query window:
DBCC TRACEON(9062 , -1)
Click Execute.
Quit SQL Query Analyzer.

Back to the top

Add -T9062 to the SQL Server startup parameters

Open SQL Enterprise Manager.
Expand Microsoft SQL Servers, expand SQL Server Group, right-click the related SQL Server, and then click Properties.
On the General tab, click Startup Parameters.
In the Parameter box, type -T9062, and then click Add.
Click OK two times, and then quit SQL Enterprise Manager.
Restart the related SQL Server.

For additional information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499 (http://support.microsoft.com/kb/822499/ ) New naming schema for Microsoft SQL Server software update packages

For additional information about the terminology that Microsoft uses when it corrects software after it is released, click the following article number to view the article in the Microsoft Knowledge Base:

824684 (http://support.microsoft.com/kb/824684/ ) Description of the standard terminology that is used to describe Microsoft software updates

Back to the top