Prev: Trying to migrate from SQL Server 2005 to Oracle 10g Express Edition
Next: Sync two tables
From: Query Builder on 23 Jun 2010 17:03
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.
From: Erland Sommarskog on 23 Jun 2010 18:16
Query Builder (querybuilder(a)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(a)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

From: bod on 23 Jun 2010 23:34
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:
> Query Builder (querybuilder(a)gmail.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?
>
>
>
>

From: Erland Sommarskog on 24 Jun 2010 02:38
bod (bod(a)newsguy.com) writes:
> 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.

That's correct, but "Query Builder" said that he tried UNION ALL and it
didn't help.


--
Erland Sommarskog, SQL Server MVP, esquel(a)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

 | 
Pages: 1
Prev: Trying to migrate from SQL Server 2005 to Oracle 10g Express Edition
Next: Sync two tables