Hi All,

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?

Any help will be much appreciated..

Regards,
Aravin Rajendra.

Query Builder (querybuilder (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Doesn't UNION imply a distinct over the entire combined result set?
(effectively SELECT DISTINCT * FROM (SELECT ... UNION ALL SELECT ...))
If you can construct the query such that each half of the union is
exclusive, then UNION ALL may yield better performance.

On 6/23/2010 7:16 PM, Erland Sommarskog wrote:

bod (bod (AT) newsguy (DOT) com) writes:
That's correct, but "Query Builder" said that he tried UNION ALL and it
didn't help.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx