RESOLVED:

This is how I resolved it...

No new indexes - no change in anything.

I copied over only the tables involved in the query

to production database - renamed it and ran the...

DBREINDEX was also run on the table (as DBREINDEX updates statistics and DEFRAG doesnt).

MAXDOP doesnt work...

LIST OF INDEXES ON BOTH DATABASES FOR THE BIGGEST TABLE - INVOICE_FACT:

1. Clustered - day_sqn, sales_org_sqn

2. sales_org_sqn

3. inv_nbr, doc_typ_sqn, inv_lin_nbr

4. ord_sqn

5. sold_to_cus_sqn

6. pro_sqn

7. sal_typ_sqn

8. sal_typ

9. trans_typ_sqn

I checked the...

Joe

Since I am testing on production server - I am very cautious to make sure there are no processes running... I run this query only when there are no processes,...

No patches applied... SELECT COUNT(*) also runs for ever and takes 100% CPU...

This is query works with a BETWEEN clause instead of IN clause on the problem database/server. The other database/server...

This is the query...

select                                                               a14.cbl_sqn  cbl_sqn,

                                                                         a17.CG_des  CG_des,

                                                                         a13.pro_typ_sqn  pro_typ_sqn,

                                                                         0  XKYCGT,

                                                                         sum(a11.inv_qty)  WJXBFS1

into #ZZT090005WKPO000

from                                                                  dbo.invoice_FACT          a11

                                                                         join  dbo.day   a12

Couldnt afford being a 24/7 system...

I have asked my network admin folks and SAN support team - they say everything is fine. There was no other activity on that system other than these backups. And...

CHKDSK and DBCC CHECKDB didnt return any errors

I did check the event logs. Nothing suspicious