Distinct with left outer join slow in 2008

Dear All,

I'm facing the while running below query in 2008 SP2 server. I tried to backup and restore the same database in 2000 and restore on 2008. when i ran the same query on 2000 it fatch 3998 records in 5 min. but when i ran the same query on 2008 it dones't shown output even ran since 16-17 hours.


even i tried update stats index no used of execution plan.....

----------------------------------------------------

ALTER VIEW [dbo].[V_EAI_COMMON_INTERFACE]

AS
SELECT DISTINCT
rpt.APP_ID,
rpt.APP_CD,
rpt.APP_NM,
cast(rpt.APP_DSCR as varchar(8000)),
rpt.STTS_DSCR,
rpt.LOB_ID,
rpt.LOB_NM,
rpt.ADMN_LOB_ID,
rpt.ADMN_LOB_NM,
rpt.ADMN_LOB_NM,
rpt.AD_PM_NM,
rpt.AD_PM_CERT_NUM,
rpt.CRE_NM,
rpt.CRE_CERT_NUM,
rpt.AD_SCND_LVL_MGR_NM,
rpt.AD_SCND_LVL_MGR_CERT_NUM,
rpt.AD_THRD_LVL_MGR_NM,
rpt.CIO_NM,
rpt.CIO_CERT_NUM,
isnull(rpt.CRIT_IND,'N') AS CRIT_IND,
rpt.CMN_NM,
RTRIM(LTRIM((dbo.FN_GET_APPID_LST(rpt.CMN_NM)))) AS APPID_LST, -- List of applications with same common name.
RTRIM(LTRIM((dbo.FN_CONCAT_ROWS(rpt.APP_ID,10)))) AS ARM_NM_LST,
RTRIM(LTRIM((dbo.FN_CONCAT_ROWS(rpt.APP_ID,9)))) AS ARM_NUM_LST,
LEFT(prj.PROJ_CD,4),
rpt.MSTR_APP_NM,
rpt.DCOMM_DT,
rpt.CO_CD,
rpt.AD_PM_EMAIL_ADR,
rpt.MSTR_APP_ID,
rpt.MSTR_APP_CD,
rpt.MSTR_APP_DSCR,
rpt.CO_NM,
rpt.USR_CNT,
rpt.PROD_DT,
rpt.BUS_SPNSR_NM,
rpt.PLTFRM_NM,
rpt.PROD_DSCR,
rpt.TECH_NM,
rpt.CRT_DT,
rpt.ACT_SUNSET_DT,
rpt.SUNSET_RSN_TXT,
rpt.BXG_ID,
rpt.BXG_NM,
rpt.APP_IND,
rpt.AD_SPRT_IND,
rpt.AD_THRD_LVL_MGR_CERT_NUM,
rpt.BUS_SPNSR_CERT_NUM,
rpt.PLN_IMPL_DT,
rpt.APP_ORIG_CO_NM,
rpt.ORIG_APP_CD,
rpt.ORIG_APP_NM,
rpt.INT_DPOS_DSCR,
rpt.INT_HOST_APP_ID,
rpt.POC_TGT_CMPLT_DT,
rpt.CANC_DT,
rpt.CANC_RSN_TXT,
rpt.ALS_NM,
rpt.CERT_DT,
rpt.XPCT_SUNSET_DT,
rpt.CERT_IND,
rpt.RGLTRY_ATRB,
rpt.MSTR_APP_STTS_CD,
rpt.MSG_TYP_IND,
rpt.MSG_TYPS,
rpt.PORTFOL_MGR_LIST_TXT,
rpt.AD_SUPR_PM_LIST_TXT,
rpt.SLO_PCT,
rpt.MRCRY_ITG_PROJ_NUM,
rpt.SR_BUS_LEAD_CERT_NUM,
rpt.SR_BUS_LEAD_NM,
rpt.BUS_LEAD_CERT_NUM,
rpt.BUS_LEAD_NM,
rpt.BUS_WRK_CLNT_CERT_NUM,
rpt.BUS_WRK_CLNT_NM,
PROJALL.PROJ_CD,
PROJALL.PROJ_TYP_DSCR,
PROJALL.PROJ_STTS_DSCR,
TPM.PHYS_SRVR_NM,
TPM.LPAR_SRVR_NM,
TPM.DPAR_NM,
TPM.SRVR_TYP_NM,
LOC.LOC_NM,
TPM.ENVRN_NM,
TPM.STTS_DSCR,
TPM.OPER_SYS_NM,
TPM.SRL_NUM,
DPT.DEPT_NM,
CASE ISNULL(AVSCH.SCHD_DAY_DSCR,'')
WHEN '' THEN HRS.SCHD_DAY_DSCR
ELSE AVSCH.SCHD_DAY_DSCR
END AS [DAY],
HRS.STRT_TM AS [Hours of operation - Start Time],
HRS.END_TM AS [Hours of operation - End Time],
HRS.HR_CNT AS [Hours of operation - Num Of Hours],
AVSCH.STRT_TM AS [Scheduled Maintenance Window - Start Time],
AVSCH.END_TM AS [Scheduled Maintenance Window - End Time],
AVSCH.HR_CNT AS [Scheduled Maintenance Window - Num Of Hours],
PM.FRST_NM,
PM.LST_NM,
PM.WRK_TEL_NUM,
CIO.FRST_NM,
CIO.LST_NM,
CIO.WRK_TEL_NUM,
CIO.EMAIL_ADR_LN,
CRE.FRST_NM,
CRE.LST_NM,
CRE.WRK_TEL_NUM,
CRE.EMAIL_ADR_LN,
PM.DOC_ID,
BS.DOC_ID,
BXG.BXG_CD,
LOB.LOB_CD,
RPT.APP_CLASS_DSCR,
RPT.APP_CNTN_TYP_DSCR,
RPT.DATA_STOR_DSCR,
RPT.DATA_STOR_LOC_DSCR,
RPT.PRVLG_INFO_IND,
RPT.PROP_INFO_IND,
RPT.PDCSR_SYS_IND,
RPT.PDCSR_SYS_DSCR,
RPT.SRCH_ABL_IND,
RPT.DATA_SRCH_XPRT_IND,
RPT.AUTO_DEL_IND,
RPT.AUTO_DEL_DSCR,
RPT.DABL_AUTO_DEL_IND,
RPT.DABL_AUTO_DEL_DSCR,
RPT.CPTR_BACKUP_INFO_IND,
RPT.DR_PLN_IN_ARDB_IND,
RPT.DR_PLN_LOC_DSCR,
RPT.BCKUP_SCHD_DSCR,
RPT.BACKUP_TYP_DSCR,
RPT.BACKUP_MDM_DSCR,
RPT.BACKUP_STOR_LOC_DSCR,
RPT.BACKUP_INFO_RTR_DSCR,
RPT.BACKUP_RSPN_PARTY_DSCR,
RPT.BACKUP_SFTWR_DSCR,
RPT.DATA_RNG_STRT_DT,
RPT.DATA_RNG_END_DT,
RPT.RPO_TM,
RPT.RTO_TM,
TPM.TRU_ISLA_TYP_NM,
RPT.CNFR_INFO_TXT ,
RPT.TECH_CNTCT_NM,
RPT.CALC_IC_NUM,
RPT.CALC_TC_NUM,
RPT.ASSOC_TYP_CD,
TPM.ISLA_TYP_NM
FROM DBO.T_RPT RPT
LEFT OUTER JOIN T_PROJ prj
ON rpt.APP_ID = prj.APP_ID and PROJ_TYP_DSCR = 'Services' AND PROJ_STTS_DSCR = 'Active'
LEFT OUTER JOIN T_PROJ PROJALL ON(RPT.APP_ID = PROJALL.APP_ID AND PROJALL.PROJ_STTS_DSCR = 'Active')
LEFT OUTER JOIN T_TPM_SRVR TPM ON(TPM.APP_ID=RPT.APP_ID)
LEFT OUTER JOIN T_LOC LOC ON(LOC.LOC_CD=TPM.LOC_CD)
LEFT OUTER JOIN T_AVLB_SCHD AS SCHD ON(SCHD.APP_ID=RPT.APP_ID)
LEFT OUTER JOIN T_EMPE EMPE ON(EMPE.CERT_NUM= rpt.CRE_CERT_NUM)
LEFT OUTER JOIN T_DEPT DPT ON(EMPE.DEPT_CD=DPT.DEPT_CD)
LEFT OUTER JOIN dbo.T_AVLB_SCHD Hrs ON RPT.APP_ID = Hrs.APP_ID and Hrs.AVLB_PURP_CD = 'H'
LEFT OUTER JOIN dbo.T_AVLB_SCHD avsch ON RPT.APP_ID = avsch.APP_ID and avsch.AVLB_PURP_CD = 'M' AND avsch.SCHD_DAY_DSCR = Hrs.SCHD_DAY_DSCR
LEFT OUTER JOIN T_EMPE PM ON(RPT.AD_PM_CERT_NUM=PM.CERT_NUM)
LEFT OUTER JOIN T_EMPE CIO ON(RPT.CIO_CERT_NUM=CIO.CERT_NUM)
LEFT OUTER JOIN T_EMPE CRE ON(RPT.CRE_CERT_NUM=CRE.CERT_NUM)
LEFT OUTER JOIN T_EMPE BS ON(RPT.BUS_SPNSR_CERT_NUM=BS.CERT_NUM)
LEFT OUTER JOIN T_BXG AS BXG ON(RPT.BXG_ID=BXG.BXG_ID)
LEFT OUTER JOIN T_LOB AS LOB ON(RPT.LOB_ID=LOB.LOB_ID)

WHERE
NOT (RPT.MSTR_APP_NM LIKE '%DELETE%' OR RPT.MSTR_APP_NM LIKE '%CLOSED%')



GO

Assuming that both sql 2k & 2k8 are on the hw/load baseline (equiv hw, same load, et..).
My WAG that for this qry 'DISTINCT' in 2k uses hashtable while 2k8 plan uses groupby. Quite obvious that some of the LEFTJOINs are not 1-to-1 (partially cartesian), thus workingset became too large which thrash 2k8. Easy to test this theory:
SELECT count(*)
FROM dbo.t_rpt r
LEFT JOIN ...
WHERE ...
test0 - no left join
test1 - left join with t_proj only
testN - left join N entities
Once you identified the join that cause data explosion <= fix it. Aha, maybe it's not about 2k or 2k8 after all.
Best,