September 27, 2011 at 6:48 am
Hi all,
sometimes I found queries in the profiler with sort warnings like this example:
exec sp_executesql N'SELECT TOP 25 TAB1.COL1, TAB1.COL2, ...FROM TAB1 WHERE TAB1.TENANTID LIKE @param0 AND TAB1.ID IN ( SELECT ID FROM TAB2 WHERE NAME= @param1 AND VALUE LIKE @param2 ) AND ORDERHEAD.TENANTID= @param3 ORDER BY TAB1.COL1 DESC, TAB1.COL2 ASC ',N'@param0 nvarchar(7),@param1 nvarchar(9),@param2 nvarchar(16),@param3 nvarchar(7)',@param0=N'DEFAULT',@param1=N'TYPE',@param2=N'Export',@param3=N'DEFAULT'
The memory grant in the execution plan is 1616 kb. Dependend on the @param2 sometimes the needed memory for this query is more than 1616 kb and I get the sort warning. How can I increase the needed memory allocation? In my first tests I removed the 3rd parameter and the profiler didn't show any sort warnings. After changing the TOP to 500 I've got the same problem (the value for the TOP clause is changeable by the user). How can I avoid the sort warning? An index on the sorted columns? Or anything else?
Regards
Alex
September 27, 2011 at 7:12 am
This was removed by the editor as SPAM
September 27, 2011 at 7:33 am
Thanks,
I changed the query to an inner join and for the TOP 25 everything works fine. But with TOP 500 I've got the same problem.
With select * from sys.dm_exec_query_memory_grants I found granted_memory_kb: 4864 and max_used_memory_kb: 4864. The sort warning already exists. Any other ideas?
Regards
Alex
September 27, 2011 at 8:13 am
This was removed by the editor as SPAM
September 27, 2011 at 8:34 am
Sort warnings (and hash warnings) are typically due to row estimation errors. You may have parameter sniffing, you may have stale stats. Check for both.
btw, IN and INNER JOIN can't just be swapped one for the other, they can easily return different results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 5:43 am
Thanks for the answers,
I think we must rewrite some statements to avoid the sort warnings. But one last question:
If I use the the hint OPTION (MERGE UNION) in our statements (with TOP, Union ALL ORDER BY)
I never found sort warnings in the profiler, the row estamation seems okay with this option.
I have no experience with hint options. Can I use the hint option or would you say keep your hands off?
Regards
Alex
September 30, 2011 at 7:26 am
Hands off unless you are 120% certain that you know better than the optimiser for all possible parameter values and all possible data changes and you know exactly what the hint is doing and why it's necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply