1

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get any help from here.

  1. Question: How much data is spilled into disk? 400 MB or 60 MB(7643KB*8)?

in the article he states:

And no matter how many times I update statistics, I’ll still get a ~400MB spill to disk.

I am kinda confused here(

enter image description here enter image description here

  1. Question: If everything is okay with estimates, stats are up to date, box has sufficient memory, and no queries were running at that time, then why does spill to disk happen?

look at the estimated number of rows versus the actual number of rows. They’re identical. The stats are fine.

I’m not using a small server, either: my virtual machine has 32GB RAM, and I’ve allocated 28GB of that to SQL Server. There are no other queries running at the same time – it’s just one lonely query, spilling to disk...

enter image description here

  • 1
    Can you show the actual execution plan? brentozar.com/pastetheplan – Danielle Paquette-Harvey Jun 30 at 19:42
  • What is the column getting sorted? What is the data type? The whole actual plan will definitely be more helpful than screenshots of select tool tips and properties. – Aaron Bertrand Jun 30 at 19:52
  • Okay. I will add them in a minute – RaufDBA Jun 30 at 20:00
8

As the tooltip shows, 7643 8KB pages are spilled = 59.71MB. The rest of the sort occurs within the allocated memory.

The sort spills for two reasons:

  1. The sort is parallel. The accurate memory estimate is split evenly among the sort threads, and cannot be dynamically reallocated between threads at runtime. If the rows are not distributed evenly, one or more sort threads may spill. This is somewhat more likely to happen at low DOP e.g. 4 rather than 8.

Scan & Sort Threads

  1. SQL Server does not account for spill management overhead when estimating the memory requirement. This is detailed in FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct.

On SQL Server 2019, the memory granted between executions may be automatically adjusted to avoid the spill via the row mode memory grant feedback mechanism.

| improve this answer | |
4

Question: How much data is spilled into disk? 400 MB or 60 MB(7643KB*8)?

60MB. You can verify this by dumping

select sum(num_of_bytes_written) / 1024 / 8 approx_pages_written
from sys.dm_io_virtual_file_stats(db_id('TempDb'),null)

before and after the query

If everything is okay with estimates, stats are up to date, box has sufficient memory, and no queries were running at that time, then why does spill to disk happen?

The row size is an estimate, and the memory needs to be divided among the parallel scanning threads, any of which may need to spill.

| improve this answer | |

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.