I have this query on my dev server:
select snStudyNumber as ProjectNumber 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 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) 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
This used to work ok on the dev server until yesterday. Now it's taking about 1 1/2 minutes to run. However, it works fine on the test server.
Here's the weird part. If i modified the query on dev to take out one of the left outer joins (either one), it would run fine (but the wrong dataset). If i added the join back it slowed down again.
Also, the data between the servers are within 90% similarity. Structure and amount wise.
I also tried rebooting the dev server, but that didn't fix the issue.
So, what could cause this discrepancy? Where should I be looking?
edit- ok, so i missed something in the execution plans. The test server (the one that works) is on the left, the dev server on the right.