VARCHAR Size And Memory Grant Estimates

Arthur Daniels shows us a good reason for using better data sizes than just VARCHAR(MAX) everywhere:

That’s a lot of desired memory, 1,493,120 KB aka 1.4 GB, but there was only 
25 MB used in the sort. So why was SQL Server so far off the right estimate? It’s the data types we picked.

That’s a lot of memory for a fairly simple query returning 300,000 rows, each containing a string and an int.

Related Posts

Visualizing Hash Match Joins

Bert Wagner continues his series on visualizing physical join operators: Hash Match joins are the dependable workhorses of physical join operators.While Nested Loops joins will fail if the data is too large to fit into memory, and Merge Joins require that the input data are sorted, a Hash Match will join any two data inputs […]

Read More

Deep Dive On The Adaptive Join Operator

Hugo Kornelis covers the Adaptive Join operator: The Adaptive Join operator was added in SQL Server 2017 as an alternative to the other join operators: Nested Loops (ideal for joining a small data stream with a cheap input), Hash Match (most effective for joining large unsorted sets) and Merge Join (ideal for joining data streams that are sorted by the join […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

January 2019
MTWTFSS
« Dec  
 123456
78910111213
14151617181920
21222324252627
28293031