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
