1

I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator. What is the reason for this warning in Sort operator?

The Query Plan URL: https://www.brentozar.com/pastetheplan/?id=HJULL9H4N

I provided the screen shot of Execution Plan. enter image description here

The Warning: enter image description here

  • Maybe this helps: brentozar.com/blitzcache/query-plan-warnings What tooltip says when you click over the sort operator? – McNets Feb 4 at 8:03
  • Cardinality estimation for the second partecipant in your last join was wrong, server underestimated it, as the result merge join was chosen where hash is more appropriate. Underestimation caused memory grant to be lower and your Sort was spilled into tempdb – sepupic Feb 4 at 8:06
  • To fix the issue you can save the join result into a temporary table before joining it to dbo.Student. This will help SQL Server to have the exact estimation of the second partecipant of your last join – sepupic Feb 4 at 8:08
  • @sepupic, maybe there is not enough memory to be granted instead of the wrong estimation – Denis Rubashkin Feb 4 at 8:13
  • @Denis Rubashkin When you see the actual number of rows in red be sure it differs from estimated number of rows – sepupic Feb 4 at 8:15
1

What is the reason for this warning in Sort operator?

You can see the warning description in properties of the element. I'm sure you will see information like below:

Operator used tempdb to spill data during execution.....

It means that memory which was granted for the query turned out to be less than it needed. Two possible reasons for that:

  1. Wrong cardinality estimation so the query asked for the less memory grant than it really needed
  2. Lack of memory so the query asked enough memory but got less

Detailed information about memory grant can be found in the properties window of SELECToperation from your query plan.

0

I am not writing a complete new answer. Most of it is covered between the comments and answer by Denis.

Two possible reason mentioned. Wrong cardinality estimation and Lack of memory. I will explain how you can rule out Lack of Memory.

MaxQueryMemory under MemoryGrantInfo (once you right click the leftmost select node): Maximum amount of memory available for individual query grant in KB, if the query requires memory to run.

enter image description here

So if the MaxQueryMemory is larger than requested memory it is a good indication that sort warning is NOT due lack memory, it is rather due bad cardinality estimation.

A worth reading about Understanding SQL server memory grant

Ref:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.