Memory grants sized by declared width

In one line: “make it varchar(4000), storage is the same anyway”. For storage, that’s true. For memory it isn’t: the optimizer sizes a sort’s or hash’s memory grant from the declared width, assuming every value is half full.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/memory-grant-declared-width on GitHub.

What you’ll see

Queries waiting on RESOURCE_SEMAPHORE, queued for memory.
Huge grants that are almost entirely unused. In the demo, sorting 500,000 seventeen-character names:

declared as	granted	used
`varchar(50)`	68 MB	24 MB
`varchar(4000)`	1,456 MB	24 MB
`nvarchar(4000)` / `varchar(max)`	1,472 MB (hit the per-query cap)	32–45 MB

Why it happens

The optimizer can’t know how long the values in a variable-length column really are, so it assumes half the declared length. A varchar(4000) value is costed at about 2,000 bytes whatever it holds, nvarchar doubles that, and varchar(max) is costed at about 4,000.

The query itself runs fine. The damage is to everyone else: granted memory is reserved for the whole execution, used or not. Two or three of these at once and other queries queue behind reservations that are 98% empty.

What to do

Declare columns as wide as the data, not as wide as you can imagine.
Where you can’t change the column, CAST it to an honest width before the sort: ORDER BY CAST(Name AS varchar(50)) brought the grant back to 68 MB. It’s only safe if no value is ever longer.
Memory grant feedback (2019+, compatibility level 150) cut the grant to 36 MB from the second execution. On SQL Server 2022 with Query Store on, the learned grant even survives a recompile. The first execution after a compile still pays unless that feedback was persisted.

What doesn’t help

“Storage is the same.” It’s the memory grant that differs.
Adding memory. The per-query cap is 25% of query memory by default, so a bigger server grants bigger over-grants.

Check your server

-- Grants right now: how much each query was given, how much it's using, and who is waiting.
SELECT mg.session_id,
       mg.requested_memory_kb / 1024 AS requested_mb,
       mg.granted_memory_kb   / 1024 AS granted_mb,
       mg.used_memory_kb      / 1024 AS used_mb,
       mg.wait_time_ms,
       Waiting = CASE WHEN mg.grant_time IS NULL THEN 1 ELSE 0 END,
       st.text
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
ORDER BY mg.requested_memory_kb DESC;

See it happen

The memory-grant-declared-width demo sorts the same names stored under four declarations. It measures the grant each is given, the cap, memory grant feedback and its persistence, and the CAST fix.

Measured on SQL Server 2022 CU25 (16.0.4255.1), compatibility level 160, about 9.6 GB of memory. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.