I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on where to turn to next.
Here's what the plans look like, the top beign the dex execution (the one that works), and the bottom being the test execution (the slow one).
edit: here's what the sql looks like:
ALTER procedure [ardb].[NewProjects] -- Add the parameters for the stored procedure here as begin -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. set NOCOUNT on ; -- Insert statements for procedure here ;with b as ( select distinct coalesce(a.STUDY_NUMBER,b.STUDY_NUMBER, c.study_number) as StudyNumber from ( SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER FROM appdb.PROD.dbo.PA01201 AS PA01201_1 where PAPROJNUMBER>'0' and PAPROJNUMBER not like '%[a-z]%' ) a full outer join ( SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER FROM appdb.MO1.dbo.PA01201 AS PA01201_1 where PAPROJNUMBER>'0' and PAPROJNUMBER not like '%[a-z]%' ) b on a.STUDY_NUMBER=b.STUDY_NUMBER full outer join ( SELECT STUDY_NUMBER FROM appdb.ptwdb.dbo.tblARCompletedStudies where STUDY_NUMBER>'0' ) c on a.STUDY_NUMBER=c.study_number and b.STUDY_NUMBER=c.study_number ) select snStudyNumber as ProjectNumber, c.clName as CompanyName, q.quQuoteNumber as QuoteNumber, q.quQuoteID as QuoteID, q.quDateWon as DateWon, s.snPTWCompletionDate as PTWCompletionDate from CDB.cdb.Quotes q inner join CDB.cdb.LineItems l on q.PK_quID=l.FK_quID inner join CDB.cdb.StudyNumbers s on l.FK_snID=s.PK_snId inner join cdb.cdb.Clients c on q.FK_clID=c.PK_clID left outer join ardb.projects p on s.snStudyNumber=p.prProjectNumber left outer join b on s.snStudyNumber=b.studynumber where q.quDateWon>'' and s.snPTWCompletionDate >'' and p.PK_prID is null and l.liCreationDate>'12/31/06' and b.studynumber is null union all select CAST(ProjectNumber AS int) as ProjectNumber, null as CompanyName, null as QuoteNumber, null as QuoteID, null as DateWon, null as PTWCompletionDate from history.ProjectsToProcess a left outer join ardb.projects p on a.ProjectNumber=p.prProjectNumber where p.PK_prID is null order by ProjectNumber desc end
xml plans: here