9a439a49   
   XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Query Builder (querybuilder@gmail.com) writes:   
   > I have this unusual behavior in my SQL server 2008 Ent 64bit   
   > (Clustered) environment.   
   >   
   > I migrated my SQL Instance from a SQL 2000 Ent (32Bit) With SP4 to   
   > 2008 Ent SP1 (64BIT - Clustered) instance.   
   >   
   > I have this complex query which joins a bunch of tables and has a   
   > union. When I run the query outside the union (running the top and   
   > bottom of the query separately ) it returns the records in less than 1   
   > minute each. Each section of the union returns about 200 records or   
   > so.   
   >   
   > When I run the full query with the union the query is hung. There is   
   > no locking (by using with nolock) and checked for any missing indexes.   
   > The query simply runs for hours till I kill it.. I even tried UNION   
   > ALL and it keeps running..   
   >   
   > I can run the same query in the old SQL 2000 environment and it   
   > completes in a few mins. I even scripted all indexes and tried loading   
   > them on the 2008 instance but nothing is different other than the   
   > data. I have re-index the whole database. I have updated statistics..   
   > But the 2008 instance for some reason doesn't like UNION.   
   >   
   > When I insert the data individually and union the temp tables, its   
   > quiet fast. But I use a BO tool that lets user create these queries,   
   > and I have no way of doing this via temp tables.   
   >   
   > Has anyone seen this behavior? Can anyone suggest a way to see what   
   > the issue is?   
      
   A good start is to look at the query plans, at least the estimated   
   plan for the UNION query. Since the query never seem to complete,   
   looking at the actual plans may be more difficult.   
      
   You say that each section of the UNION returns 200 rows, but how big   
   are the underlying tables? That is, is there any potential for a   
   query plan from hell?   
      
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)