Hi

It sounds crazy but it is very true. I have tried it in two separate environments i.e. another physical host using SQL Server 2012 and SQL Server 2008 and I get the same results. Below a certain MAX Memory threshold which varies from 4323 to 3856 the performance of the query increases exponentially from minutes (4 to 10 mins) to less than 4 secs. The query in question is below

SELECT t11.DocNum, convert(nvarchar, t11.DocDate,103), t11.Filler as 'Origin W/H',

t11.U_RecWshe as 'Final W/H', t12.ItemCode, t12.Dscription,

(SELECT t13.ItmsGrpNam FROM OITB t13 , OITM t14 WHERE t13.ItmsGrpCod=t14.ItmsGrpCod and t14.ItemCode=t12.ItemCode) as 'Item Group', t12.Quantity

FROM OWTR t11, WTR1 t12

WHERE t11.DocEntry=t12.DocEntry and t11.DocNum in

(SELECT t0.DocNum FROM OWTR t0 t0.U_TrnType=1 AND t0.DocDate<='2012/12/31' and t0.U_EType=0

AND CONVERT(NVARCHAR, t0.DocNum) NOT IN

(SELECT CONVERT(NVARCHAR,ISNULL(t11.U_TrnDocNo,'')) FROM OWTR t11))

Has anyone ever encountered this?

Basically when it takes 4 mins during that time the CPU usage shoots up to 100% for the entire time.

Regards

FK

P.S. I have attached actual query execution plans.