Antares686 - Monday, July 24, 2017 1:24 PM

I have multiple SQL Instances Our Developer and Integration Testing run on a virtualized host environment. I have User Acceptance Testing environment that runs on an Active/Passive cluster hosted on a physical server. I have odd behavior for this query (has been altered to not reveal names of objects)

This works fine in DEV/SIT bit not in UAT

SELECT

B.ACCT_ID

,C.[ACCT]

,C.DISP_ID

,'MAT'

,C.FSCD

,'MATCH'

,GETDATE()

,C.LAST_NAME

,C.FIRST_NME

,C.ADDR

,GETDATE()

FROM

(

SELECT

DISTINCT

A.ACCT AS ACCT_NUM,

RCVD_DTE,

COUNT(B.[ACCT]) DispCount

FROM

[dbo].TABLEA A

LEFT JOIN

TABLEB B

ON

A.ACCT = B.ACCT

WHERE

B.[FSCD] = 'BIR' AND

B.[DISP_STTS_CDE] = 'P' AND

B.[ORG_CDE] = 'LTR' AND

A.[ACCT] NOT IN (SELECT [ACCT] FROM [dbo].[TABLEB] WHERE [FSCD] IN ('BIQ','BCU'))

GROUP BY

A.ACCT,

RCVD_DTE

HAVING

(COUNT(B.[ACCT]) = 1)

) A

INNER JOIN

[dbo].TABLEA B

ON

A.ACCT_NUM = B.[ACCT] AND

A.[RCVD_DTE] = B.ReceivedDate

INNER JOIN

TABLEB C

ON

A.ACCT_NUM = C.ACCT AND

A.RCVD_DTE = C.[RCVD_DTE]

WHERE

C.[FSCD] = 'BIR' AND

C.[DISP_STTS_CDE] = 'P' AND

C.[ORG_CDE] = 'LTR' AND

B.[DNA_CODE] = 'DNA' AND

B.[ReceivedDate] >= @DateChecker

I did try the NOLOCK hint as well as TRANSACTION ISOLATION LEVEL to no avail. We had to do this for UAT to work for us

DECLARE @nouse TABLE ([ACCT] varchar(100) PRIMARY KEY)

INSERT INTO @nouse SELECT [ACCT] FROM [dbo].[TABLEB] WHERE [FSCD] IN ('BIQ','BCU') GROUP BY [ACCT]

SELECT

B.ACCT_ID

,C.[ACCT]

,C.DISP_ID

,'MAT'

,C.FSCD

,'MATCH'

,GETDATE()

,C.LAST_NAME

,C.FIRST_NME

,C.ADDR

,GETDATE()

FROM

(

SELECT

DISTINCT

A.ACCT AS ACCT_NUM,

RCVD_DTE,

COUNT(B.[ACCT]) DispCount

FROM

[dbo].TABLEA A

LEFT JOIN

TABLEB B

ON

A.ACCT = B.ACCT

WHERE

B.[FSCD] = 'BIR' AND

B.[DISP_STTS_CDE] = 'P' AND

B.[ORG_CDE] = 'LTR' AND

A.[ACCT] NOT IN (SELECT [ACCT] FROM @nouse)

GROUP BY

A.ACCT,

RCVD_DTE

HAVING

(COUNT(B.[ACCT]) = 1)

) A

INNER JOIN

[dbo].TABLEA B

ON

A.ACCT_NUM = B.[ACCT] AND

A.[RCVD_DTE] = B.ReceivedDate

INNER JOIN

TABLEB C

ON

A.ACCT_NUM = C.ACCT AND

A.RCVD_DTE = C.[RCVD_DTE]

WHERE

C.[FSCD] = 'BIR' AND

C.[DISP_STTS_CDE] = 'P' AND

C.[ORG_CDE] = 'LTR' AND

B.[DNA_CODE] = 'DNA' AND

B.[ReceivedDate] >= @DateChecker

Anyone have any thoughts on why this only happens in UAT?

Also a few other things of note.

1) I thought maybe due to UAT being larger but here it was smaller (DEV 12 GB, UAT 3 GB).
2) We noticed this with another database the same issue but this except in that case DEV was 500 GB and UAT was 2 TB and it seems to always be sub query type queries such as a sub query or even use of WITH statements. In each case we found a temporary table variable did solve the issue but no real reason determined it seemed to be this way. And since we were in for performance on 2 TB we didn't think much more about this behavior.