Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the development team.

SELECT 
        c.Start
        ,c.[End]
        ,c.Word
        ,doc.UniqueDocumentNumber
        ,doc.EID
        ,c.CUI
        ,c.Concept
        ,a.OID
        ,doc.DocumentTypeName
        ,doc.ActivityDtTm
        ,CAST(doc.DocumentTypeId AS INT) AS MedCode
        ,CASE WHEN c.[Count] = 0 THEN  CAST(0.00 AS REAL)
           ELSE CAST(LOG(c.TotalCount / c.[Count]) AS REAL) END AS 'idf'
        ,c.[Count]
        ,c.TotalCount
FROM ECHO..AEID201 a
INNER JOIN ALPHA..XADocuments doc (NOLOCK) ON a.EID = doc.EID
CROSS APPLY (SELECT t.start,t.[end],t.word,t.cui,t.eid,
   t.UniqueDocumentNumber,cu.[Count],cc.TotalCount,core.Concept 
FROM HOTEL.dbo.Htf_Index AS t
INNER JOIN HOTEL..Doc_CUI_Counter AS cu ON cu.CUI=t.CUI AND cu.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..Doc_Counter AS cc ON cc.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..CUI_CORE AS core ON core.CUI=cu.CUI
WHERE t.eid = doc.eid AND doc.UniqueDocumentNumber=t.UniqueDocumentNumber
) AS c
WHERE a.OID='00005159-9567-4187-a278-5b8e1e2ed20c' 
AND ISNUMERIC(doc.DocumentTypeId) = 1

On Server B, review of the execution plan indicates that the criteria for table [a] at bottom is part of an index seek operation at the start of the execution plan, which makes the subquery [c] resultset execute rapidly.

On Server A, review of the same execution plan indicates that subquery [c] is executing first with full index scans due to outer criteria not being applied.

Indexes utilized in both execution plans are identical. Table rows counts are slightly larger on Server A since the restore due to normal operations but index fragmentation is nearly identical. A participating index on table [t] in the subquery on Server B has double the number of pages as on Server A but identical row count. Statistics are updated nightly at the same time on both servers.

I've attempted index rebuilds on table [t] and manually updating statistics to attempt to get both execution plans to match. What other factors may be causing this change in the order of execution?

I suggested to the developer replacing the subquery with a UDF that takes the EID and UniqueDocumentNumber fields from [doc] as arguments. What other options can I explore with the developer to increase the probability of the execution plans on QA being utilized on Prod?

share|improve this question
add comment

1 Answer 1

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but...

How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?

Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics.

Table rows counts are slightly larger on Server A... Statistics are updated nightly at the same time on both servers.

Row counts are different and the statistics update was probably a default sample, rather than FULLSCAN. I've witnessed some comically unfortunate stats histograms as the result of sampling, each of which has been corrected by a FULLSCAN update.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.