Francis Kioko (10/24/2013)
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.
can you also provide result of set stattics io on and set statistics cpu on from both environments(dont forget to remove exec plan selection )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)