Sorts Spills in tempdb due to incorrect behavour by Query optimizer... - by Abhay_78

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 515046 Comments
Status Resolved Workarounds
Type Bug Repros 0
Opened 11/27/2009 6:51:23 AM
Access Restriction Public


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 .

Please advice 
Sign in to post a comment.
Posted by Microsoft on 1/19/2010 at 5:50 PM
Thanks for reporting this issue to Microsoft.
I believe you are describing 2 different issues:
(1) Performance change with marginally different WHERE clause.
(2) Performance change from TOP 100 to TOP 101.
Unfortunately, we were not able to reproduce and determine root cause of issue (1) locally. On the other hand, we confirm issue (2) is a known behavior in SQL Server when dealing with "TOP N ORDER BY" syntax. If N <=100, SQL Server uses a routine optimized for low memory /low number of output rows. If N >100, it switches to TOP and SORT because a regular SORT is usually much faster for large number of rows. Unfortunately, it may consume large amount of memory if underlying table has many rows.
I hope this clears up part of your questions. Please let us know if you have any other questions.

Jay Choe
SQL Server Query Engine
Posted by Microsoft on 12/7/2009 at 10:55 AM

Thanks for your feedback. We'll look into this and see if we can improve the behavior in a future release. It doesn't appear to be a defect at first glance, but rather an design limitation.

Best regards,