FIX: You may notice a decrease in performance when you run a query that uses the UNION ALL operator in SQL Server 2000 Service Pack 4

• The issues that are fixed by the hotfix package
• The prerequisites for applying the hotfix package
• Whether you must restart the computer after you apply the hotfix package
• Whether the hotfix package is replaced by any other hotfix package
• Whether you must make any registry changes after you apply the hotfix package
• The files that are contained in the hotfix package

Back to the top

• You query data from joined tables.
• The joined tables contain columns that use constraints.
• The joined tables contain lots of records.

Back to the top

• The system uses the Advanced Micro Devices (AMD) AMD64 processor architecture or the Intel Extended Memory 64 Technology (EM64T) processor architecture.
  
  Note This issue does not occur on systems that use the Intel Itanium processor architecture.
• The system is running a 64-bit version of the Microsoft Windows Server operating system.
• The system is running a 32-bit version of SQL Server 2000.

Back to the top

Hotfix information

Prerequisites

• Microsoft SQL Server 2000 Service Pack 4 (SP4)
  
  For information about how to obtain SQL Server 2000 SP4, 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

Restart information

Registry information

Hotfix file information

SQL Server 2000, 32-bit x86 versions

SQL Server 2000, Itanium architecture version

Back to the top

Back to the top

Steps to reproduce the problem

1. In SQL Server 2000 SP4, run the following statements by using SQL Query Analyzer.

   use master
   go
   
   create database repro
   go
   
   use repro
   go
   
   create table t1 (i int constraint pkt1 primary key check ((i>11) and (i< 15)), j int )
   create table t2 (a int constraint pkt2 primary key check ((a>15) and (a< 20)), b int)
   create table t3 (c int constraint pkt3 primary key check ((c>7) and (c< 13)), d int)
   create table t4 (e int constraint pkt4 primary key check ((e>19) and (e< 24)), f int)
   create table s1 (i int constraint pkt5 primary key, j int)
   create table s2 (a int constraint pkt6 primary key, b int)
   create table s3 (c int constraint pkt7 primary key, d int)
   create table s4 (e int constraint pkt8 primary key, f int)
   go
   
   create view v1 as
   select t1.* from
   t1 inner join s1 on t1.i = s1.i
   union all
   select t2.* from
   t2 inner join s2 on t2.a = s2.a
   go
   
   create view v2 as
   select t3.* from
   t3 inner join s3 on t3.c = s3.c
   union all
   select t4.* from
   t4 inner join s4 on t4.e = s4.e
   go
   

2. To make SQL Server return execution information, run the following statements.

   set showplan_text on
   go

3. Run the following statements, and then examine the output.

   select * from v1 inner join v2 on i = c
   where i = 12 or i = 17
   go

   The following inefficient execution plan is displayed in the output.

   StmtText                                                                                                                
   ----------------------------------------------------------------------------------------------------------------------- 
     |--Concatenation
          |--Nested Loops(Inner Join, WHERE:([t3].[c]=[t1].[i]))
          |    |--Nested Loops(Inner Join)
          |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s1].[pkt5]), SEEK:([s1].[i]=12) ORDERED FORWARD)
          |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t1].[pkt1]), SEEK:([t1].[i]=12) ORDERED FORWARD)
          |    |--Nested Loops(Inner Join, OUTER REFERENCES:([t3].[c]))
          |         |--Clustered Index Scan(OBJECT:([Repro].[dbo].[t3].[pkt3]))
          |         |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s3].[pkt7]), SEEK:([s3].[c]=[t3].[c]) ORDERED FORWARD)
          |--Nested Loops(Inner Join, WHERE:([t4].[e]=[t2].[a]))
               |--Nested Loops(Inner Join)
               |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s2].[pkt6]), SEEK:([s2].[a]=17) ORDERED FORWARD)
               |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t2].[pkt2]), SEEK:([t2].[a]=17) ORDERED FORWARD)
               |--Nested Loops(Inner Join, OUTER REFERENCES:([t4].[e]))
                    |--Clustered Index Scan(OBJECT:([Repro].[dbo].[t4].[pkt4]))
                    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s4].[pkt8]), SEEK:([s4].[e]=[t4].[e]) ORDERED FORWARD)
   
   (15 row(s) affected)

StmtText                                                                                                                
----------------------------------------------------------------------------------------------------------------------- 
  |--Concatenation
       |--Nested Loops(Inner Join, OUTER REFERENCES:([s3].[c]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[i]))
       |    |    |--Nested Loops(Inner Join)
       |    |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s1].[pkt5]), SEEK:([s1].[i]=12) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t1].[pkt1]), SEEK:([t1].[i]=12) ORDERED FORWARD)
       |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s3].[pkt7]), SEEK:([s3].[c]=[t1].[i]) ORDERED FORWARD)
       |    |--Filter(WHERE:(STARTUP EXPR([s3].[c]<13 AND [s3].[c]>7)))
       |         |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t3].[pkt3]), SEEK:([t3].[c]=[s3].[c]) ORDERED FORWARD)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([s4].[e]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[a]))
            |    |--Nested Loops(Inner Join)
            |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s2].[pkt6]), SEEK:([s2].[a]=17) ORDERED FORWARD)
            |    |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t2].[pkt2]), SEEK:([t2].[a]=17) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([Repro].[dbo].[s4].[pkt8]), SEEK:([s4].[e]=[t2].[a]) ORDERED FORWARD)
            |--Filter(WHERE:(STARTUP EXPR([s4].[e]<24 AND [s4].[e]>19)))
                 |--Clustered Index Seek(OBJECT:([Repro].[dbo].[t4].[pkt4]), SEEK:([t4].[e]=[s4].[e]) ORDERED FORWARD)

(17 row(s) affected)

Back to the top