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

Testing Inline Scalar UDF Performance

Erik Darling whips up a performance test covering scalar UDF changes in SQL Server 2019: This is a slightly different take on yesterday’s post, which is also a common problem I see in queries today. Someone wrote a function to figure out if a user is trusted, or has the right permissions, and sticks it […]

Read More

Making Non-SARGable Queries SARGable with an Index

Denis Gobo violates Betteridge’s Law of Headlines: This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?) He said his query had a WHERE clause that looked like […]

Read More

Categories

January 2019
MTWTFSS
« Dec Feb »
 123456
78910111213
14151617181920
21222324252627
28293031