Some Complex Queries Run Slower After You Apply SQL Server 7.0 SP4 (821534)


The information in this article applies to:

  • Microsoft SQL Server 7.0
SQLBUG_70:200034

SYMPTOMS

After you apply Microsoft SQL Server 7.0 Service Pack 4 (SP4), some complex queries may run much slower if all the following conditions are true:
  • The query's FROM clause is made up of several SELECT statements.
  • The SELECT statements join several tables and views with FULL OUTER JOIN clauses, RIGHT OUTER JOIN clauses, and LEFT OUTER JOIN clauses
  • At least one of the views is made up of several SELECT statements that have subqueries that are concatenated through UNION clauses.
A query that runs in less than one second in SQL Server 7.0 SP3 may take more than 60 seconds to complete in SQL Server 7.0 SP4.

CAUSE

SQL Server 7.0 SP4 contains a change to the SQL Server query optimizer that improves the overall performance. However, this change may adversely affect some complex queries.

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 7.0 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this hotfix has the file attributes (or later) 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. 
   Date         Time   Version            Size    File name
   -----------------------------------------------------------------
   04-Oct-2002  14:59  2000.34.4.0        28,944  Dbmssocn.dll     
   06-Sep-2002  14:55  2000.33.6.0        53,520  Distrib.exe      
   06-Sep-2002  14:55  2000.33.6.0        98,576  Logread.exe      
   05-May-2003  09:34                     54,904  Opends60.dbg
   05-May-2003  09:34  2000.41.2.0       155,920  Opends60.dll     
   06-Sep-2002  14:56  2000.33.6.0       250,128  Rdistcom.dll     
   06-Sep-2002  14:55  2000.33.6.0        82,192  Replmerg.exe     
   06-Sep-2002  14:56  2000.33.6.0        78,096  Replres.dll      
   17-Sep-2002  13:52                      7,941  Securityhotfix.sql
   06-Sep-2002  14:56  2000.33.6.0       160,016  Snapshot.exe     
   29-May-2003  19:21                     59,214  Sp4_serv_uni.sql
   14-Jan-2003  16:33  2000.37.13.0      344,064  Sqlagent.exe     
   06-Sep-2002  14:55  2000.33.6.0        45,056  Sqlcmdss.dll     
   15-May-2003  15:18  2000.41.14.0    2,629,632  Sqldmo.dll       
   16-May-2003  04:29  2000.41.14.0       81,920  Sqlmap70.dll     
   30-Jun-2003  13:29  2000.42.30.0    5,062,928  Sqlservr.exe     
   04-Oct-2002  14:59  2000.34.4.0        45,328  Ssmsso70.dll     
   15-May-2003  15:18  2000.41.14.0       24,848  Ssnmpn70.dll     
   26-Sep-2002  11:30                     28,408  Ums.dbg
   26-Sep-2002  11:27  2000.33.25.0       57,616  Ums.dll          
   16-May-2003  04:31  2000.41.14.0      151,552  Xpweb70.dll
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

STATUS

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

MORE INFORMATION

To install and activate this hotfix, follow these steps:
  1. Obtain the hotfix that is described in the "Resolution" section.
  2. Apply the hotfix to the computer that is running SQL Server 7.0.
  3. Activate trace flag 9154 as a startup option or at query runtime by using the DBCC TRACEON command.
To activate trace flag 9154 as a startup option, follow these steps:
  1. In SQL Server Enterprise Manager, right-click the computer that is running SQL Server 7.0, and then click Properties.
  2. Click Startup Parameters.
  3. In the Parameter text box, type -T9154.

    Note Make sure that you use an uppercase T.
  4. Click Add, and then click OK.
  5. Restart the SQL Server 7.0 service.
To activate trace flag 9154 at query runtime, follow these steps:
  1. Start SQL Query Analyzer, and then connect to the computer that is running SQL Server 7.0.
  2. Run the following command: 
    DBCC TRACEON (9154, -1)
  3. Run the affected query.

    Note The trace flag remains active until the SQL Server service is restarted. You can also deactivate the trace flag by using the following command:
    DBCC TRACEOFF (9154, -1)
Trace flag 9154 disables the changes in SQL Server 7.0 SP4 that have a negative impact on some complex queries.

The following sample query is affected by this problem:
SELECT view_2.column2
FROM (SELECT *
      FROM   view_1
      WHERE  column1 = '80PROP' OR column1 IS NULL) b 

    FULL OUTER JOIN
     (SELECT *
      FROM   table_1
      WHERE  column1 = '80PROP') a 
    ON a.column1 = b.column1 AND a.column2 = b.column2

    RIGHT OUTER JOIN
      view_2 
    ON a.column2 = view_2.column2 OR b.column2 = view_2.column2 

    LEFT OUTER JOIN
      view_3 
    ON view_2.column2 = view_3.column2

WHERE  (view_2.IdSchema = 'GR1') 
   AND (view_2.IdLingua = '001') 
   AND (view_3.IdSegnalazioneRichiesta = 'C1' 
        OR  view_3.IdSegnalazioneRichiesta = 'V1' 
        OR  view_3.IdSegnalazioneRichiesta = '1') 
   AND (b.column1 = '80PROP' 
        OR b.column1 IS NULL) 
   OR (view_2.IdSchema = 'GR1') 
   AND (view_2.IdLingua = '001') 
   AND (b.column1 = '80PROP' 
        OR b.column1 IS NULL) 
   AND (a.ImportoRE + a.ImportoRN + a.ImportoNRE + a.ImportoNRN <> 0)

ORDER BY view_2.column2
GO
Modification Type:MinorLast Reviewed:10/25/2005
Keywords:kbHotfixServer kbQFE kbSQLServ700preSP5fix kbfix kbbug KB821534 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.