Query runs on Dev, but not on Test | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query runs on Dev, but not on Test

The Issue: (If interested and you have access to msdn managed groups, more info is available athttp://tinyurl.com/yj6dst)
I’ve got an issue where a join takes about 3 minutes to run on a development server (2 X 1.4 GHz/ 2GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) standard) and does not finish (even after 10 Hours!) on a monster (8 X ? GHz/ 12GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) clustered Enterprise) data center test machine. This is a join between a table with 360K rows (ODS_Global_Stage.dbo.FPVShipments) and another with 5.6 Mil rows (ODS_Global.dbo.shipments). The query also involves Two small lookup tables in a third (configuration) db. This involves identical data, schema, indexes, auto-stats, etc. between the two boxes. I see no CXPackets, so the parallel processing issue doesn’t seem to apply. DBCC showcontig reveals nearly identical page use/frag. Any ideas what to look for?
GO Run this on both servers and compare the resulting execution plans