Ten characters in storage do not guarantee a ten-character estimate during execution. Oversized varchar declarations can inflate the memory a sorting query requests.
Separate Storage Size From Execution Estimates
Variable-length columns do not reserve their declared maximum for every stored value. Their execution estimates are a different issue. A sort must request memory before it knows the complete actual workload.
Estimated row count and estimated row width both influence that request. A wide declaration can increase the assumed width even when existing values are short. The effect is especially visible when wide columns travel through memory-consuming operators.
I inspect row-width estimates alongside cardinality estimates during grant investigations. I also compare requested memory with actual maximum usage. Either dimension can explain a mismatch between the plan and the workload.
A common variable-length estimation heuristic uses roughly half the declared maximum width. Treat that as a useful explanation, not a universal formula. Operator behavior, expression types, plan shape, and engine version affect the final estimate.
For nvarchar, the declared length counts byte-pairs rather than ordinary single-byte characters. An nvarchar(4000) declaration therefore represents a much wider maximum than varchar(4000). Do not compare those declarations as if their byte limits were identical.
Build an Oversized varchar Table and a Narrow Twin
Use a new isolated test database for this example. Both tables receive the same chosen synthetic values and primary key layout. Only the declared length of Label differs.
The generated labels contain a fixed prefix and six digits. Their actual length remains within the narrower declaration. A four-digit cross join supplies ten thousand fixture rows without relying on catalog row counts.
CREATE TABLE dbo.WideLabel
(
RowId int NOT NULL PRIMARY KEY,
Label nvarchar(4000) NOT NULL
);
CREATE TABLE dbo.NarrowLabel
(
RowId int NOT NULL PRIMARY KEY,
Label nvarchar(40) NOT NULL
);
WITH D AS
(
SELECT N FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS V(N)
), Numbers AS
(
SELECT A.N * 1000 + B.N * 100 + C.N * 10 + D.N + 1 AS RowId
FROM D AS A CROSS JOIN D AS B CROSS JOIN D AS C CROSS JOIN D
)
INSERT dbo.WideLabel(RowId, Label)
SELECT RowId, N'Item' + RIGHT(N'000000' + CONVERT(nvarchar(10), RowId), 6)
FROM Numbers;
INSERT dbo.NarrowLabel(RowId, Label)
SELECT RowId, Label FROM dbo.WideLabel;
SELECT MAX(DATALENGTH(Label)) AS MaximumStoredBytes
FROM dbo.WideLabel;Compare Sort Plans for Oversized varchar Columns
Enable the actual execution plan in SSMS before running both queries. Inspect the Sort operator, estimated row size, and statement memory grant. Keep the selected columns and ordering identical between the two tables.
The queries use MAXDOP one to reduce one source of variation. RECOMPILE makes the comparison focus on fresh compilation. It also prevents the ordinary cached-plan feedback pattern from explaining a later change.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT RowId, Label
FROM dbo.WideLabel
ORDER BY Label DESC, RowId
OPTION (MAXDOP 1, RECOMPILE);
SELECT RowId, Label
FROM dbo.NarrowLabel
ORDER BY Label DESC, RowId
OPTION (MAXDOP 1, RECOMPILE);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;These statements return the complete fixture, so client rendering also contributes to elapsed time. Compare engine plan evidence rather than stopwatch impressions alone. On my test instance, the wide table’s sort requested far more memory than the narrow one, while both used the same small amount.
A tiny or differently optimized query can obscure the expected contrast. Confirm that both plans contain the intended memory-consuming operation. Do not infer a sort grant merely because ORDER BY appears in the query text.
Read the Memory Fields Together
MemoryGrantInfo in the plan distinguishes requested, granted, and maximum-used memory. Requested memory describes the query's reservation request. Granted memory records what it obtained, while maximum usage describes execution consumption.
A large gap between granted and maximum-used memory suggests overgranting for that execution. It does not prove declared width is the only cause. Check row counts and operator choices before selecting a fix.
For currently executing requests, inspect sys.dm_exec_query_memory_grants from another connection. Short queries can finish before a sample captures them. Use actual plans for completed demonstrations rather than assuming an empty DMV proves zero memory use.
SELECT session_id, request_id,
required_memory_kb, requested_memory_kb,
granted_memory_kb, used_memory_kb, max_used_memory_kb,
wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC, session_id;The DMV requires the applicable server visibility permission. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for this view. Avoid granting broad administration merely to collect this diagnostic.
Memory reservations also affect concurrency. Many overgranted sorts can leave other requests waiting for workspace memory. Review overlapping workload behavior rather than evaluating one fast query in isolation.
Right-Size Oversized varchar From a Business Contract
The largest currently stored value is only one input to a safe size decision. Future valid values, integrations, and documented business limits matter too. Narrowing solely to today's maximum can turn the next legitimate insert into a failure.
Measure DATALENGTH when reviewing bytes because LEN ignores trailing spaces. Use the appropriate interpretation for varchar, nvarchar, and the actual collation. Preserve Unicode support when the application requires international text.
The demonstration can safely narrow its wide column because the synthetic contract is known. The following guard checks its supplied values before the alteration. It is intended only for this isolated example table.
IF EXISTS
(
SELECT 1 FROM dbo.WideLabel
WHERE DATALENGTH(Label) > 80
)
THROW 50001, 'A label exceeds the proposed nvarchar(40) limit.', 1;
ALTER TABLE dbo.WideLabel ALTER COLUMN Label nvarchar(40) NOT NULL;A production alteration requires dependency and deployment review. Indexes, computed columns, parameters, interfaces, and concurrent writers can affect the change. An observed data maximum is not permission to alter an existing application contract.
Profile data while preserving a consistent validation boundary. A writer adding longer values after the check can invalidate the migration assumption. Use an approved deployment process that controls writes and verifies the resulting schema.
Consider Narrower Query Projections Too
Fixing oversized varchar declarations is not the only way to reduce grant pressure. Stop carrying unused wide columns through sorts and joins. A narrower projection can help without changing stored data definitions.
An index supplying the required ordering can also change the plan's memory needs. Evaluate its write and storage costs before creating it. An additional index should answer a real repeated workload, not just one demonstration query.
Casting a column in a query changes its expression width but can lose valid data. Never cast to a shorter length without a proven contract. Silent truncation is a poor trade for an attractive memory grant.
Memory grant feedback can adjust later executions on eligible versions and configurations. Row mode feedback arrived in SQL Server 2019 at compatibility level 150. Newer persistence features add further requirements and do not excuse inaccurate schema definitions.
Verify the Change Under Real Inputs
Does the proposed size still accept every legitimate future label? Have the owner answer that before approving a migration. Include longest valid values and trailing spaces in the test set.
Compare plans and grant usage before and after the approved change. Test representative parameter values and concurrent workload conditions. Also watch for spills, since excessive narrowing of other assumptions can create undergrants.
I treat oversized varchar as a schema and query-design question together. I keep byte measurements separate from optimizer estimates. Giving a ten-character label a conference hall does not improve its manners.
Record the chosen length and its business reason with the schema definition. Revisit that reason when new integrations arrive. A deliberate width remains maintainable long after the original memory investigation ends.
Related reading on this blog: MemoryGrantInfo Property Explanation and SQL SERVER 2022: Persistence and Percentile Memory Grant Feedback.
A shorter stored value is not a small execution estimate, it is data inside a declared type contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
