Here are the results

When the memory is reduced to below the threshold and the query executes in less than 2 seconds

SQL Server parse and compile time:

CPU time = 327 ms, elapsed time = 328 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(45 row(s) affected)

Table 'OWTR'. Scan count 48, logical reads 54504, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 83, logical reads 298, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'WTR1'. Scan count 38, logical reads 187, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OITB'. Scan count 0, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OITM'. Scan count 0, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 594 ms, elapsed time = 415 ms.

When i increase the max memory and the query runs for more than 4 mins these are the results

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(45 row(s) affected)

Table 'OWTR'. Scan count 47, logical reads 233120944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'WTR1'. Scan count 38, logical reads 187, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 84, logical reads 300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OITB'. Scan count 0, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OITM'. Scan count 0, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1138776 ms, elapsed time = 292035 ms.

The difference with in both results is that when the first query was run the max memory setting was set at 3856 and it took a couple of seconds to execute.

When i increased the memory to 3857 the exact same query took almost 5 mins to execute.

Regards

FK