• From what I can tell after a quick look, your issue is that with the lower memory setting SQL Server decides to do a hash match, whereas with more memory it correctly decides to do a nested loop. I suspect that your index is massively fragmented, so when it does the order for the nested loop it spills to disk which would consume a massive amount of CPU. I also dislike the amount of implicit conversions that are going on in your query, you're forcing the optimizer to do more work than is necessary.

    Really, you could do with Gail taking a look but with the amount of information you've added to this post, I think it's unlikely she'd stop by 😉

    A couple of bits of advice: -

    1. Have a read through this article (http://www.sqlservercentral.com/articles/SQLServerCentral/66909/) on how to post performance issues. It'll explain what information is required for people to help you.[/url]

    2. Formatting your code when posting it online is a godsend. What you've posted is a bit of a mess which makes it difficult to see what is going on. If we instead do it like this: -

    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

    WHERE 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)

    );

    It becomes much more obvious what is going on.

    After formatting it, I'd immediately stop doing the old style joins that you have going on in your query and start using the "new" (the old style for outer joins was deprecated in SQL Server 2005).

    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

    INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry

    WHERE t11.DocNum IN (SELECT t0.DocNum

    FROM OWTR t0

    WHERE 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)

    );

    The next thing I'd want to do is move the Item Group into an APPLY.

    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,

    oa.[Item Group],

    t12.Quantity

    FROM OWTR t11

    INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry

    OUTER APPLY (SELECT t13.ItmsGrpNam

    FROM OITB t13

    INNER JOIN OITM t14 ON t13.ItmsGrpCod = t14.ItmsGrpCod

    WHERE t14.ItemCode = t12.ItemCode) oa([Item Group])

    WHERE t11.DocNum IN (SELECT t0.DocNum

    FROM OWTR t0

    WHERE 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)

    );

    Next, I'd get rid of the implicit conversions.

    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,

    oa.[Item Group],

    t12.Quantity

    FROM OWTR t11

    INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry

    OUTER APPLY (SELECT t13.ItmsGrpNam

    FROM OITB t13

    INNER JOIN OITM t14 ON t13.ItmsGrpCod = t14.ItmsGrpCod

    WHERE t14.ItemCode = t12.ItemCode) oa([Item Group])

    WHERE t11.DocNum IN (SELECT CAST(t0.DocNum AS NVARCHAR(30))

    FROM OWTR t0

    WHERE 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)

    );

    I'd consider the following index: -

    /*

    The Query Processor estimates that implementing the following index could improve the query cost by 41.9829%.

    WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis

    of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its

    impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.

    */

    CREATE NONCLUSTERED INDEX [SQL_SERVER_SUGGESTED_INDEX] ON [dbo].[OWTR] ([DocDate]) INCLUDE ([DocNum],[U_TrnType],[U_EType]);


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/