April 21, 2011 at 5:41 am
I am puzzled why I am getting frequent sort warnings on my SQL Server. I have SQL server configured with 180GB RAM on the server with min server memory 80GB max server memory 160GB. My database size is currently 50GB. I am getting frequent sort warnings (single pass only) from a few selected queries. With such a small DB (compared to RAM size) I am supprised to be getting any sort warnings at all. Looking at the queries they are using distinct and from sys.dm_exec_query_memory_grants I can see that these sort are using approx 30MB each. Has anyone any ideas as to why I should be getting these sort warnings? Is there any other memory configuration I may have overlooked?
SQL Server 2008R2 x64 on Win2k8R2 x64
Thanks in advance.
May 2, 2011 at 11:57 pm
DISTINCT performs a implicit SORT. You can check the query plan.
When SQL Server finds that there is not enough memory to perform SORT it spills it to tempdb.
Whenever data is spilled to tempdb during SORT, Sort warnings occur.
As per Microsoft single pass Sort warning can be ignored
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply