OS : Windows XP SP2 , Windows Server 2003 , windows server 2008
SQL Build : 9.00.4035 ENT EVAL , 10.00.1600 ENT EVAL
RAM : 2GB
When we run an orderby select query for x number of rows in where clause it runs very fast but when we increase just 1 row the query runs 15 to 20 times slower .
This is because of Single-Pass sorting in tempdb by the query that is running slow .
The Execution plans of both the queries are identical .
But if I reduce my max server memory to 100-110 MB the bad query runs perfectly fine .