What does memory do?
In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along to eat up more of our RAM.
In general, not having enough of it means reading pages from disk all the time, but it can have RAMifications down the line (GET IT?!), like queries not having enough memory to compile or run, and your plan cache constantly being wiped out.
If you’re struggling with the limits of Standard Edition, older hardware, bad hardware choices, or budget issues, you may not be able to adequately throw hardware at the problem. So you’re left to have someone spend way more money on your time to try to mitigate issues. This of course means query and index tuning, perhaps Resource Governor if you’ve made some EXTRA BAD choices, and last but not least: cleaning up data types.
How can this help?
Leaving aside the chance to maybe make your storage admins happy, you can also cut down on large memory grants for some queries. Here’s a quick example.
We’ll create a simple table. In order to make Joe Celko happy, it has a PK/CX. We have an integer column that we’ll use to ORDER BY. The reason for this is that if you order by a column that doesn’t have a supporting index, SQL will need a memory grant. The VARCHAR columns are just to show you how memory grants increase to account for larger chunks of data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE TABLE dbo.MemoryGrants ( ID INT PRIMARY KEY CLUSTERED, Ordering INT, Crap1 VARCHAR(10), Crap2 VARCHAR(1000), Crap3 VARCHAR(8000) ); INSERT dbo.MemoryGrants WITH (TABLOCK) ( ID, Ordering, Crap1, Crap2, Crap3 ) SELECT c, c % 1000, REPLICATE('X', c * 10 % 10), REPLICATE('Y', c * 1000 % 1000), REPLICATE('Z', c * 8000 % 8000) FROM ( SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c FROM sys.messages AS m CROSS JOIN sys.messages AS m2 ) x SELECT mg.ID --Returning one column FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO SELECT mg.ID, mg.Ordering --Returning a couple INTs FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO SELECT mg.ID, mg.Ordering, mg.Crap1 --Returning a VARCHAR(10) FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO SELECT mg.ID, mg.Ordering, mg.Crap2 --Returning a VARCHAR(1000) FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO SELECT mg.ID, mg.Ordering, mg.Crap3 --Returning a VARCHAR(8000) FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO SELECT mg.ID, mg.Ordering, mg.Crap1, mg.Crap2, mg.Crap3 --Returning all columns FROM dbo.MemoryGrants AS mg ORDER BY mg.Ordering GO |
Some test queries
When we run the queries above, we can see in the query plans, and thanks to fairly recent updates (2014 SP2, 2016 SP1), a warning in actual plans about memory grant issues.
To make this a little easier to visualize, we’ll use an Extended Events session using a new event called query_memory_grant_usage. If you want to use this on one of your servers, you’ll want to change or get rid of the filter on session ID — 55 just happens to be the session ID I have.
|
1 2 3 4 5 6 |
CREATE EVENT SESSION [QueryMemoryGrantUsage] ON SERVER ADD EVENT sqlserver.query_memory_grant_usage( ACTION(sqlserver.sql_text) WHERE ([sqlserver].[session_id]=(55))) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO |
Here’s what we get from our XE session.
Our query memory grants range from around 8 MB to around 560 MB. This isn’t even ordering BY the larger columns, this is just doing the work to sort results by them. Even if you’re a smarty pants, and you don’t use unnecessary ORDER BY clauses in your queries, SQL may inject them into your query plans to support operations that require sorted data. Things like stream aggregates, merge joins, and occasionally key lookups may still be considered a ‘cheaper’ option by the optimizer, even with a sort in the plan.
Of course, in our query plans, we have warnings on the last two queries, which had to order the VARCHAR(8000) column.
Sort it out
You may legitimately need large N/VARCHAR columns for certain things, but we frequently see pretty big columns being used to hold things that will just never approach the column size. I’m not going to sit here and chastise you for choosing datetime over date or bigint over int or whatever. Those are trivial in comparison. But especially when troubleshooting memory grant issues (or performance issues in general), foolishly wide columns can sometimes be an easy tuning win.
Thanks for reading!
Brent says: whenever anybody asked me, “Why can’t I just use VARCHAR(1000) for all my string fields?” I didn’t really have a good answer. Now I do.
25 Comments. Leave new
I have this problem! An NVARCHAR(MAX) column that only contains 4 and 5 character strings (Thanks Entity Framework!), and it’s used frequently in JOINS and ORDER BY. Unfortunately, this has been in place for quite some time and the table has millions of rows, so I’m not sure if I’ll be able to modify that column, especially considering that Entity Framework connects to it all over the place.
That sounds like a good time.
Have you tested making the change in dev? I want to say in 2012+, certain changes like this aren’t too painful.
Am I brain dead? The 3 columns
REPLICATE(‘Y’, c * 10 % 10),
REPLICATE(‘Z’, c * 1000 % 1000),
REPLICATE(‘Z’, c * 8000 % 8000)
always return the empty string because the second parameter of Replicate always resolves to zero.
I must be missing something
You’re not, and yet look how they change the memory grants 🙂
That should be X Y Z though. Heh.
Cut him some slack it’s Friday LOL
This is so prevalent in most modern (post mainframe) application programs. Developers believe there is an infinite amount of server resources, I’ve observed it so many times that “face palm” doesn’t do it justice. This is why database architecture is so important and unfortunately over-looked by the vast majority of development teams. What I call “right sizing” the data architecture, if carried out properly, will often provide more performance benefit than than index tuning.
One major application database that I inherited had every text field in the application database set to NVARCHAR(255) or larger, even the phone number field. Memory pressure isn’t the only issue, try putting a non-clustered index over two NVARCHAR(255) fields for first name and last name… doesn’t work!
At the risk of being accused of pedantry; you would be able to create that index, you’d just get a warning that inserts/updates may fail. That is unless you already have data in the column of that length, which suggests you do need columns of that size (or you have some dodgy data).
I’ve supported an application where various clients kept requesting increases in to lengths of different columns on a regular basis. There was one particular column that started at varchar(80), then went to 100, 200, 600, 800 and on and on…. we eventually settled on using varchar(255) for “normal” text fields and varchar(8000) for what we expected to be “long” fields. Not the most efficient apparently, but we did avoid the exasperation of constantly tweaking the DB to accommodate longer lengths that we never foresaw being requested. It also avoided wasting dev team cycles debating whether a new column should be 40, 50, 60, etc. characters. Standard varchar lengths also made it easy to declare local variables in stored procedures that matched the column lengths and avoid accidental truncation issues.
PS. Love the posts, Eric, keep them coming!
Below some kind of memory grant problem walkaround. It introduces cost for loop join, but memory grant is just for sort on Id. Don’t You think the cases shown in the article seem to be SQL Server bug ?
SELECT kg.*
FROM dbo.MemoryGrants AS mg
inner join dbo.MemoryGrants AS kg on kg.id = mg.id
ORDER BY mg.Ordering
GO
> Why can’t I just use VARCHAR(1000) for all my string fields?
Also, if you use SSIS this is really bad for data flow performance. SSIS will think every column is 1000 bytes wide (doesn’t matter what size the data actually has) and the data flow buffer won’t be large (less rows in the buffer because the estimated row size is too big). In other words: SLOW.
Good stuff, thanks. There’s also an amazing deep dive session “Query Tuning Mastery – Zen and the Art of Workspace Memory” by Adam Machanic, highly recommended for anyone who wants to learn more about memory grants.
[…] https://www.brentozar.com/archive/2017/02/memory-grants-data-size/ […]
Do you know if the parameter size influence memory in the same way like the column size?
It is recommended to specify a fixed size for varchar parameters in order to avoid numerous execution plans of the same query cached for parameter values of different length. Unfortunately, it’s not always possible to set the appropriate size especially if the query is generated dynamically.
Is it safe to set the parameter length to the max value 4000?
Alexandra – that’s a great question! Using the code in this post, you can go ahead and run experiments to see the answer for yourself. Build a stored procedure that hits the table in this post, and experiment with different data types and sizes to see if they affect the memory grant.
have you found the answer to your question? I’m seeking out before experimenting.
Have anyone use that info to reduce the capacity of text field and seen an increase in performance?
If so, was it worth it and would you recommend investing the time to do that on a large database (1-3 TB)
Yep, I’ve done that myself at a couple of clients. We discuss how to measure and prioritize it in the Mastering Server Tuning module on RESOURCE_SEMAPHORE waits.
Working on the idea that half the column length is used to help figure out a memory grant, my friend was wondering if he should size his columns (in a DWH) based on the average source column length * 2? Or should they just be as small as possible?
In terms of codes / pre-defined descriptions they are very consistent, he will just need to be careful of slightly larger strings, and of course check for any outliers that would truncate.
Currently working through the fundamental classes, learning a lot even after doing this for years, so thank you for that!
Just as small as possible.
If the actual average data length is known, would the optimal field size be whichever is greater of the following options?
2 * actual average data length (providing accurate memory grants)
maximum expected/permitted data length (ensuring all valid data can be accepted)
Consider a CustomerId field of 7 to 8 characters.
Varchar(8) would cause consistently low memory grants, so use Varchar(16) to give consistently accurate memory grants
Consider a FirstName field (average 6 characters, maximum 20)
Use Varchar(20) to accommodate the maximum permitted length
Is this correct, or are there other things I should consider?
I don’t think I understand the question, and you may need to rephrase it as concisely as practical. The data type length should be whatever your max data size is for that column, period. That’s not a database question – it’s a question for your data modeler.
I’m sorry if I wasn’t clear – please let me try to clarify.
If I recall correctly, you said in Mastering Server Tuning that for memory grants, SQL Server assumes that each field will be half full.
Does this mean SQL Server will under allocate memory when fields are more than half full?
e.g. For a field that is always populated with either 7 or 8 characters, most people would use a varchar(8). Will SQL Server under allocate memory by assuming the average length is 4 characters (half full) when the average length is actually 7.5 characters?
It can, but just for that one column. When you get to the point that you’re micromanaging memory grants at the column level, though, you’re missing the bigger picture. We discuss it in more details in my Mastering Query Tuning and Mastering Server Tuning classes.
(I just wanna make sure we’re not trying to recreate those classes here in the comments – just only so far I can go in the comments here. If you have a question on one of the training modules, though, by all means, comment there.