Why does TempDB spill happen even though statistics are correct?

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

Why do tempdb spills still occur even with good row and data size estimates (better than actuals)?

We’re seeing tempdb DB spills for some hashing operations. If the estimates are indeed good as shown what would be the next thing(s) to look for? Looking for a generic answer without having to resort to the specifc query.

This is part of an SP. Just switched to 2019 version to see if it would auto adjust but still getting spills so far.

Microsoft SQL Server 2019 (RTM) – 15.0.2000.5 (X64)

Hash Match Warnings

High write latency on tempdb datafiles only

I’m experiencing large write latency on our tempdb database, but on no other database. (from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms.

For comparison: Write latency on all databases except tempdb are below 5ms, tempdb was about 500ms when using 8 datafiles. I removed 4 datafiles 2 days ago just to check if something changes and it looks like the latency doubled after doing this.

A few notes on our environment:

  • SQL Server 2008 R2 SP3 VM running on Hyper-V
  • one large RAID10 array attached to our Hyper-V hosts via fibre channel where everything is stored

I have no explanation for this and I don’t know where to look for answers. I could of course assume that doubling the data files will half the WriteLatency but I don’t know if this is the way to go.

Any ideas on where to start?

¿Qué significa que tempdb no se modifique?

Lo primero considerad que tengo pocos conocimientos en sql server.

Tengo unos jobs que se ejecutan diariamente en sql server. Hemos ido controlando el tamaño de la base de datos ya que ha ido creciendo diariamente. Esto es normal ya que se van generando datos diariamente hasta que lleguen al tiempo límite que hemos establecido, tiene que llegar un momento en el que se estabilice.

Mi pregunta viene porque los procesos almacenados se están ejecutando correctamente todos los días y no dan error pero el tamaño de la base de datos ha dejado de crecer desde hace unos días cuando aún debería seguir creciendo.

He visto que los archivos tempdb no se han modificado desde el día 30/09 que es la misma fecha en la que dejó de crecer la base de datos. ¿Esto por qué puede ser? ¿Qué significa que los archivos tempdb no se modifiquen? ¿Qué es lo que está fallando y no soy capaz de ver?

Muchas gracias de antemano y lo siento si me he extendido en exceso, he intentado ser lo más claro posible.