Hi,

 We are migrating frm 2000 to 2005 and experiencing some performance issues in 2005.

 Example Query:

Following is the execution plan in 2000 and 2005:

2005
select
subcategory_id,subsubcategory,cat_code_3
from #tmpsubsubcategory t
where str(subcategory_id) + cat_code_3 not in
       (select str(subcategory_id) + cat_code3 from stage.stg_sub_subcategory)

  |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Expr1008]))
       |--Nested Loops(Left Anti Semi Join)
       |    |--Nested Loops(Left Anti Semi Join, WHERE:([Expr1008] IS NULL))
       |    |    |--Compute Scalar(DEFINE:([Expr1008]=str(CONVERT_IMPLICIT(float(53),[tempdb].[dbo].[#tmpsubsubcategory].[subcategory_id] as [t].[subcategory_id],0))+[tempdb].[dbo].[#tmpsubsubcategory].[cat_code_3] as [t].[cat_code_3]))
       |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tmpsubsubcategory] AS [t]))
       |    |    |--Top(TOP EXPRESSION:((1)))
       |    |         |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]))
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]), WHERE:((str(CONVERT_IMPLICIT(float(53),[CostcoCanada].[stage].[stg_sub_subcategory].[subcategory_id],0))+[CostcoCanada].[stage].[stg_sub_subcategory].[cat_code3]) IS NULL))
       |--Top(TOP EXPRESSION:((1)))
            |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]), WHERE:([Expr1008]=(str(CONVERT_IMPLICIT(float(53),[CostcoCanada].[stage].[stg_sub_subcategory].[subcategory_id],0))+[CostcoCanada].[stage].[stg_sub_subcategory].[cat_code3])))

2000
  select  
subcategory_id,subsubcategory,cat_code_3 
from #tmpsubsubcategory t 
where str(subcategory_id) + cat_code_3 not in        
(select str(subcategory_id) + cat_code3 from stg_sub_subcategory)

  |--Hash Match(Right Anti Semi Join, HASH:([Expr1004])=([Expr1005]), RESIDUAL:([Expr1005]=[Expr1004]))
       |--Compute Scalar(DEFINE:([Expr1004]=str(Convert([stg_sub_subcategory].[subcategory_id]), NULL, NULL)+[stg_sub_subcategory].[cat_code3]))
       |    |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]))
       |--Compute Scalar(DEFINE:([Expr1005]=str(Convert([t].[subcategory_id]), NULL, NULL)+[t].[cat_code_3]))
            |--Nested Loops(Left Anti Semi Join)
                 |--Nested Loops(Left Anti Semi Join, WHERE:(str(Convert([t].[subcategory_id]), NULL, NULL)+[t].[cat_code_3]=NULL))
                 |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tmpsubsubcategory__________________________________________________________________________________________________00000000EDA1] AS [t]))
                 |    |--Row Count Spool
                 |         |--Top(1)
                 |              |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]))
                 |--Row Count Spool
                      |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]), WHERE:(str(Convert([stg_sub_subcategory].[subcategory_id]), NULL, NULL)+[stg_sub_subcategory].[cat_code3]=NULL))

Here Nested loop is used in 2005 and Hash join is used by default in 2000. Is there any way the database engine can be tuned so that 2005 use Hash Join by default?

Regards

Hari

Discussion continues here: http://sql-server-performance.com/Community/forums/t/28587.aspx