Sql-server – What would cause the speed difference between servers

sql serversql-server-2008t-sql

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.
Difference 1
Difference 2

Best Answer

Sounds like your statistics are out of date. Run UPDATE STATISTICS on the tables on the server running slow and see if that fixes the problem.

Related Question