Update statistics, top 100 percent and Sort warnings - by Sankar Reddy

Status : 


Sign in
to vote
ID 457024 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 5/23/2009 9:12:40 PM
Access Restriction Public


Sort Warnings are generated when explicit sorting of queries is spilled into tempdb when there are less memory buffers left. Index maintenace scripts do NOT generate these and the expectation is, these shouldn't happen for Update Statistics also. Here is a code snippet generated from Update Statistics.

SELECT StatMan([SC0], [SB0000]) FROM 
(SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM 
 (SELECT [cEndDateTime] AS [SC0] FROM [dbo].[Tablename] 

In the above query, note the Order by is used inside a sub query with top 100 percent. Queries like these generated from Update statistics are leading to hundreds of sort warnings on my server which could have been avoided.  Can you provide some documentation on why you need to use TOP 100 percent with order by inside a sub query for Update Statistics or better yet do NOT fire these warnings for Update Statistics.
Sign in to post a comment.
Posted by Eric [MSFT] on 2/1/2012 at 4:41 PM
We're resolving this as a duplicate of another item.

Eric Hanson
Program Manager
SQL Server Query Processing
Posted by thulasi muneiah on 2/23/2011 at 10:42 PM
Dear Yavor,

Can you suggest the alternative for the above query in SQL 2005.
Posted by Sankar Reddy on 6/3/2009 at 4:17 PM

Thanks for the update and look forward for the change in the later this year/ next year.
Posted by Sankar Reddy on 6/3/2009 at 4:16 PM

Thanks for the update and look forward for the change in the layer this year/ next year.
Posted by Microsoft on 6/3/2009 at 4:09 PM
Dear Customer,

Thanks for reporting this issue to us. We have reproduced it, but can't make a fix available for SQL Server 2005 (due to scope of the change involved). We will fix this in the next major release of SQL Server (after SQL Server 2008 R2).

Senior Program Manager
SQL Engine