When I look at a SQL Server, one of the first things I check is, “How much memory does this thing have relative to the amount of data we’re hosting on here?” I’ve long used some seat-of-the-pants numbers, but armed with data from SQL ConstantCare® users who opted into public data sharing, let’s do a little deeper analysis.
I took last Thursday’s data for about 1,400 servers, then excluded stacked instances (multiple instances installed on the same OS), Azure SQL DB, Managed Instances, and servers with less than 10GB of data.
How much memory do SQL Servers have?
The median SQL Server has 19% of the data size as RAM. Meaning, if it’s hosting 100GB of data, it has 19GB RAM in the server. (Max memory size and file sizes are discussions for another blog post.)
To take a few examples from that median:
- 84GB data hosted on a SQL Server with 16GB RAM
- 166GB data hosted with 32GB RAM
- 289GB data hosted with 55GB RAM
Let’s slice it a different way, though: let’s take the median data volume out of this sample, 219GB. (If I sort servers by how much data they host, the middle value is 219GB.) In servers in that range, a few include:
- 219GB data hosted on a SQL Server with 15GB RAM (OS has 7% of the data size)
- 222GB data hosted with 128GB RAM (58%)
- 222GB data hosted with 24GB RAM (11%)
These numbers are thought-provoking, but before you ask questions, think about this one: the data size vs hardware size doesn’t by itself mean that the users are happy. There are a lot of other factors at play, like query volume, tuning done, wait stats, etc.
A few examples from the extreme ends of memory-vs-data-size provisioning:
- 4.2TB of data hosted on 16GB RAM (HAHAHA)
- 20TB hosted on 64GB
- 10GB (not TB) hosted on 64GB RAM
- 61GB hosted on 256GB RAM
As data size grows, memory doesn’t.
I split the servers into quartiles based on the size of data they’re hosting:
- Servers hosting 10-59GB data: median RAM size is 74% of the data! (example: 27GB data, 20GB RAM)
- 60-224GB data: 23% RAM size (example: 210GB data, 48GB RAM)
- 225-600GB data: 13% RAM size (example: 488GB data, 64GB RAM)
- >600GB data: 6% RAM size (example: 2.1TB data, 128GB RAM)
The low end percentages are a little skewed since in the 10-59GB tier, the OS memory means a lot. In our SQL Server Setup Guide, we tell folks to leave at least 4GB to the OS, and I think most sysadmins would consider 2GB to be the bare minimum. But still, the dropping percentages as data grows – that’s pretty steep.
Do Enterprise Edition servers have more memory?
Overall, Enterprise Edition servers handle larger volumes of data, and they are configured with more memory to handle it:
- Standard Edition median data size is 168GB, median RAM size is 32GB
- Enterprise Edition: median data size is 358GB, median RAM size is 54GB
- Developer Edition: data 111GB, RAM 16GB (poor developers)
So in terms of pure server size, yes, Enterprise servers are larger, but as a percentage of data, something kinda interesting happens:
- Standard Edition median: RAM is 23% the size of the data
- Enterprise Edition: RAM is 17% of the size of the data
- Developer Edition: 11% (c’mon, man, let them get some memory!)
Do older SQL Servers have less memory?
Y’all are starving the dinosaurs:
- SQL Server 2008 and 2008R2 median RAM is 15% of the database size
- SQL Server 2012: 18%
- SQL Server 2014: 19%
- SQL Server 2016: 22%
- SQL Server 2017: 24%
Which might be due to a few factors, like not caring about the performance on older servers, or dealing with old servers built with much lower memory sizes.
Where to go from here
My next thoughts are:
- Which servers are more likely to experience PAGEIOLATCH waits?
- Which servers are more likely to experience RESOURCE_SEMAPHORE poison waits when they run out of query workspace memory?
- Can I build a sizing tool that predicts user happiness based on data size? (Meaning, if you put 3TB of data on a 64GB RAM VM, how bad will the wait stats be?)
- Then, can we give that same advice to customers? For example, showing them a chart of where they rank for data size vs memory vs happiness?
53 Comments. Leave new
I know you wouldn’t have this, but I’d really love to hear more about that “4.2TB of data hosted on 16GB RAM” system…
Are the users happy?
Is the OS happy?
How long does it take to boot up and shut down?
How *OLD* is the server (somehow, I’m picturing a 10 year old PowerEdge that hasn’t been replaced because “it ain’t broken”)
Yep, all great questions. I’ve seen one case in particular where folks restored a ~10TB data warehouse onto a 16GB RAM server just for periodic reporting & auditing purposes. The queries would take days to run, but they didn’t care because there wasn’t an urgency on those particular queries. The server was on fire, though, hahaha.
Are your numbers based on the allocated size of the databases, or the amount of space used in the databases? In my capacity management work I forecast based on the space used, figuring that unused space doesn’t contribute to buffer pool demand.
In general, it would be great to know more about these findings. I am in the process of making recommendations for how much data we can put on servers based on the amount of memory they have. It’s been pretty hard to get information on the responsiveness our systems are giving from a user point of view, so the big question you ask about user “happiness” often is unanswered.
Len – here, I’m using allocated size to keep things simple. But yep, absolutely, when I’m doing detailed space forecasting, I do the same thing.
We’ll definitely be sharing more about the findings with the clients & training students. My big plan for 2019 is to start feeding this data back to clients in a way that helps them make better decisions. There’s a huge value in having this kind of diagnostic data to improve their experience.
Interesting findings. I normally allocate 75% of the data storage. Is there such a thing as allocation of too much memory?
That’s a great question. The only reason I’d say yes is that if you give too much memory to one server that doesn’t need it – when you could give that memory to another server that does need it. (Or if you lose credibility with a client where someone else comes in and says, “No no, you’re wasting money here, there’s no need for that.”)
We’re right in there I spose. 4TB on 100GB. We actually have more memory, but the guys read something at some point that made them fearful of giving more RAM and getting worse performance (something about plan generation).
When I first started I was all in favour of giving it more RAM, but now it feels fine. Most of the data is older and we’re in need of an archiving strategy, or actually using our partitioning to offload older data to less performant disk.
The main issues we have is a few queries that hit a very active part of the database with poor design, and larger memory grants – apart from that things are pretty smooth, and memory is something I don’t worry on so much.
The perfect RAM:DB ratio is application dependant. For our purposes 1:10 usually proves adequate.
On one of our systems, we have 4TB db with 384GB RAM.
Really interesting thanks. I’m currently responsible for a server where the total db size is 4.2TB. The server has 256GB of RAM but is running SQL Server 2016 Standard Edition, therefore limited to 128GB. That equates to just 3% of the total db size in memory, roughly along the lines of Brent’s findings.
What’s interesting is if I dig into what’s eating up my buffer pool, I can see that the largest database (ReportServer – 2.6TB!) is only consuming 1.2GB of memory. So my db size vs memory stats may look bad, but the biggest databases aren’t those consuming memory. The highest consumer of the buffer pool is a OLTP database which is 500GB in size, and consuming 90GB of memory (just under 20%).
Conclusion? Increasing memory is unlikely to dramatically improve performance. I monitor Page Life Expectancy as my primary indicator of memory pressure, and currently it’s a fairly healthy 3.5 hours, although that does drop sharply during maintenance windows.
Interested to hear other people’s thoughts on this.
Query to return the buffer pool size per database in case it’s useful:
SELECT database_id AS DatabaseID,
DB_NAME(database_id) AS DatabaseName,
COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id),database_id
ORDER BY BufferSizeInMB DESC
I guess we’re really lucky. We’re running < 10GB of data with 80GB of RAM. 🙂
Of course this is a terminal server that's been press-ganged into having a SQL Server Std. instance on it for budget reasons but 800% of data size means our buffer page life expectancy is answered by "when was the last time the server was rebooted" and our buffer cache hit ratio rarely drops below 100%.
Hey, RAM is cheap, right? 🙂
Always good to see stats.
In our case we’re dealing with a 200GB+ legacy vendor database that tends to scan whole tables of data prior to presenting a dialog for users to search for what they need.
Historically, our performance point for memory has generally been closer to 70-80% of data. Lately we’ve been struggling with performance as we’ve migrated from physical to virtual and the infrastructure team don’t like the SQL Server memory hog, so they’ve halved the memory we used to have.
Cheers
Phil
Yikes, that’s a familiar horror story – “We don’t like how much memory this uses, so we’re going to feed it less. Why is the performance worse?”
If your tables are doing a lot of scans though, is it possible to add indexes? In the past I would have been reluctant to change anything from a vendor… but if performance is terrible, then… at the very least I’d be asking the vendor why that is and if they have a set of indexes they could apply.
It continually amazes me that people that don’t know are able to make those decisions, regardless of the professional support and guidence that is available nowdays.
We do have indexes where our main performance hits are. They only help marginally.
We’re talking tables with a couple million rows 150-200 columns that are either char or datetime. A majority of the most of the tables have compound primary keys across 5-6 char columns …
An to top it off, if we make structural changes outside their custom toolkit we have to be very carefule. They keep track of all the object_id’s in the database. If there is a difference, the application itself starts throwing errors, even if the part we changed was in a totally different area.
Cheers
Phil
I’ve always been able to go to the level where the IT folks and database folks have a common manager and show them real performance numbers. Database people are too fast to capitulate on these issues, then they have to deal with the fallout of bad performance, which is not something I’ve been willing to do in my career.
4TB total databases volume (only a small fraction is active in usage in terms of current processing and reporting), with 256GB RAM/16 cores. I know this was based on throwing hardware at a poorly tuned/optimized code base. Looking at going to AWS RDS, and based on the total amount of memory by database (vs free/empty space), it’s 104GB used with 34GB empty. The RDS instance RAM sizes are quite limited when looking at 8 and 16 cores (8 has 32 and 61GB; 16 has 64 and 122GB). I’m thinking an 8 core 61GB (based on what is actually used) instance is probably sufficient (to help keep costs down and also get multi-AZ). How do you know otherwise?
Sure, that’s exactly the kind of analysis we do in our consulting. I wish I could boil it down to a blog post comment, but….
Well, using a similar script to what Shaun posted, and trusting in first principles/concepts, I’m down to 61GB (used that target as it is an option in RDS) from about 230GB with no discernible degradation of anything. Took larger incremental drops in size in the beginning, one was too too much (from 180 to 140) and dumped everyone out. Had to increase for a bit, let things settle down, then started dropping 10GB at a time. Every once in a while PLE would nose dive, but come back up, and plan cache went from a few hundred MB to several GB in size, but as far as what Solarwinds shows on most panels, you can’t tell the difference in buffer cache size. In case this helps anyone.
https://ibb.co/VDPtBtt (image of memory usage over time, graph is from a Grafana SQL Server monitoring dashboard plugin, actually has some more useful things than what Solarwinds DPA shows)
SELECT
(CASE WHEN ([database_id] = 32767)
THEN N’Resource Database’
ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
order by 2 desc;
Psych barriers. Back in 2008, 16 gig was alot of memory. Now my PC has 16 and its easier to ask and easier to get 24 gig of server memory for 200 Gb PeopleSoft database.
Some storage admins think that SQL will use what ever memory is given it. That SQL would use 16Gb for an 8Gb database. I know this is not the case.
What is the real rule of thumb? Add memory till the performance curve flattens out ?
SQL Server will indeed use more memory than the data size – queries need workspace, you cache execution plans, etc.
SQL does need “room to think”. I guess it is my ignorance that I’ve seen SQL take and give memory back for small databases. ( Still waiting for Santa to drop off my server with 640GB of memory to test one and two hundred gigabyte databases with. )
Anyway, with the smaller database only needing part of the data in memory, SQL left free memory. Yes I need a picture “or it did not happen”
That when SQL started up it does not suck up all available memory. One can watch SQL grow as additional data pages are loaded. Yes additional queries will take up memory but data is loaded 64Kb at a time so it takes bigger chunks.
Does the 20% metric of memory for 100gig of data apply to a SQL Server 2014 Virtual machine in VMWare?
Thanks for any help.
Mark – yep, to SQL Server, they’re the same.
I’ll see your answer and raise you one:
Brent – so VM systems do not need a correction factor? My limited experience has shown network attached storage (NAS) and using the network interface card (NIC) for disk I/O and communication makes the database I/O bound. Mo disks – mo better. The disk queue may not get above 5, but disk busy time can reach 80% or more. [or this could be a local anomaly experienced by me and no one else]
So no additional CPU or memory needed for physical to VM transition as a performance correction factor ?
When you say “correction factor” – you’re overthinking it there. When you move from physical to virtual, you have all KINDS of changes: different CPU families, different memory speeds, different storage network, different storage, etc. You can’t fix all those with “correction factors.”
(hmmmm) I trust you.
Reformatting tempDB and data disks from 8k clusters to 64 K clusters yeilds predictable increase in performance. More than one storage admin used the 8k Microsoft recommendation.
Switch from HDD to SDD to NVMe also produced a predictable performance increase. (Both Azure and ORACLE offer NVMe SSD drives – too busy to check AWS)
Hence, when using VM add X amount to offset NAS and the additional overhead.
Nuff said. Too many factors for a correction factor.
Very Interesting. My question is: What happens when you have a 20TB data? We are working with 256GB RAM with reasonable performance. According to the rule, I should have between 1.5 and 4 TB of RAM!!! Any feedback will be appreciated
Hector – sure, if you need help with your system, shoot me an email and we can talk about what a consulting engagement would look like. (Obviously you wouldn’t wanna evaluate and architect a 20TB system in a blog comment.)
Just want to clarify, when you are looking at “data” size are you referring to total of all files for an instance or full server? Also “RAM” is it referring to max memory assigned or full OS?
I’d like to know if its actual data size or data file size. I got lots of free space in some dbs. Including log files too? I got some big uns.
Good news – the answers are in the post! Enjoy.
Just curious, did these percentages take into account the tempdb space as well?
No.
Brent, do you have any information about Minimum server memory ? I am very familiar and learned a lot from about MAX memory and other parameters lik MAX DOP, etc. but not about MIN memory parameter configuration.
Thank you
Luis – what makes you think you need to touch it?
The thing is that one of my clients is running a very “needy or hungry” for resources Sql-Server 2016. He is the developer and he likes to go behond his role and change parameters for his aplication. We changed other parameters los CTP, max memory, etc. In one of his clients othe DBA configure min memory + lock page in memory, so i agreed to make same change in the server given the fact that the other server runs a lot of process much faster than ours.
After changing both parameters process that took 3 hours went down to 30 minutos aprox.
I am not sure if the min memory made any diffrence. I almost sure that lock page in memory did make a diffrence.
Thank you
Dealing with a client-dedicated SQL Server 2016 Enterprise. 2.5 TB DB total, 1.7 TB data with 250 GB RAM dedicated to SQL. However, SQL will not use over 154 GB. While an identical server with the exact same DB, settings, software, AND hardware (both purchased at the same time) will run the same process and use all of the 250 GB of dedicated RAM.
All hardware tests out without any errors, including benchmark testing that will use 100% of RAM and all updates (including BIOS/firmware) are complete. As mentioned, run the exact same process on both servers and one will utilize all of the assigned RAM, while the other will top out at 154 GB. Any thoughts as to why the one will not utilize the RAM?
David – sure, that’s exactly the kind of problem I solve with my consulting services. Click Consulting at the top of the site to learn more.
Just a thought David – could this be either
– NUMA related? (https://blog.sqlauthority.com/2015/12/12/sql-server-discussion-on-understanding-numa/)
– Resource Governor? (https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15)
I wouldn’t present myself as an expert on either of these, but they are what spring to mind based on past-reading.
Good luck resolving your issue.
Hey Shaun – thanks for the reply, but sadly it is neither of those. NUMA is no longer “supported” and not setup on my registry on any of my servers including this one. Likewise I do not have the resource governor enabled on any server either. But I did take the time to verify prior to responding, just in case.
Thanks again and let me know if you or anyone else has any additional ideas.
Hello Brent,
I have database size around 90GB. And it consumes about 85GB of RAM. Initially when I restart system it takes about 10GB then when I hit query it goes up and up and never come back. I mean memory consumed by that query does not release after query execution finished.
Can you please help me with this.
Thank you
Breifly, SQL Server won’t release it’s memory until it has to. It is normal to see SQL Server consuming around 80% of server memory, even when under relatively low load. By holding data pages in memory, SQL Server avoids having to load those pages from disk when it requires them.
You can force SQL Server to clear out it’s memory buffers by running DBCC DROPCLEANBUFFERS, but I would not advise using this on a production system.
For future reference, I suggest posting requests for help such as this to dba.stackexchange.com or using the #sqlhelp handle on Twitter, where there is a large community that will be keen to help.
My Database is 1.6TB in size and was using all the RAM in my server 1.5TB. Just recently the server was updated and now SQL is not using more than 329GB? Has anyone experience the same issue?
Typically that’s because your SQL Server hasn’t needed the memory yet. Try running a CHECKDB, or investigate this issue: https://www.brentozar.com/archive/2018/06/cpu-cores-or-memory-offline-the-problem-with-standard-edition-vms/
Try monitoring the buffer pool size and you should see it increasing gradually. This query will give you the buffer pool usage per database:
SELECT
CASE WHEN database_id = 32767 THEN ‘ResourceDB’ ELSE DB_NAME(database_id) END AS DatabaseName,
COUNT(*) AS cached_pages,
(COUNT(*) * 8.0) / 1024 AS MBsInBufferPool
FROM
sys.dm_os_buffer_descriptors
GROUP BY
database_id
ORDER BY
MBsInBufferPool DESC
GO
You could also check the Max Server Memory setting and ensure it set to something sensible for your environment.
Also, you might find this interesting https://www.mssqltips.com/sqlservertip/6736/sql-server-lock-pages-in-memory-awe-api-windows-task-manager/
Let me throw my problem here as well, maybe ill get something that might help the situation im running into…
The db size is 560gb, by the above rule i should have 124gb of memory dedicated to sql. The thing is that the system was running well until one day that it didn’t, nothing changed concerning h/w or s/w aside… The DB server runs on hyper-v with 10 cores…TY.
Three words – Page life expectancy (PLE). Have you looked at PLE and new queries after checking stats, indexes and the like. More indexes more slowdown.
One user looking for “overview of the department broken down by cost centers v.s. departments v.s. grants / income can re-process the same data multiple ways.
One large set of updates can create page splits with lots of forward rows.
I need to figure out the SQL Server resource increase if adding 160 more users to an application. Right now we have 240 users, the DB is over 4TB, RAM = 640 GB, CPU = 32 virtual processors. How can I figure out how much more memory and CPU will I need
I wish it was as easy as a simple formula, but unfortunately it’s much more complex than that. To hire me to help, click Consulting at the top of the site.
Great article. If I have a 2TB SQL2019 db, and 1TB of that is a BLOB table can I assume I only need 22% RAM for the 1TB of data tables? 220GB of RAM for the server (plus overhead)? Excluding the blob table from the RAM calc all together?
For personalized advice, click Consulting at the top of the site.
Brent Ozar, you are better off running ads on your page than requesting users coming for help to ‘hire you’
You’d be surprised!