how much memory that we need for SQL

Question

how much memory that we need for SQL

WhiteLotus

SSChampion

Points: 12120
More actions
September 29, 2021 at 3:47 am

#3934398

Hi All,
Our server has a Datawarehouse database is like 600 GB ( 500 GB for data file only ) and memory is ONLY 8 GB ( 5 GB for SQL ) . There are only 2 Databases and the other database is very small (58 MB)
SQL edition is SQL 2014
I feel The memory is too low.
But how much memory that i should recommend ?
Your feedback is appreciated
September 29, 2021 at 5:31 am

This was removed by the editor as SPAM
December 10, 2021 at 9:49 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

5GB is less than the VM running on my laptop has for managing SQL Server.

You have to think about how SQL Server works. Let's just talk about reads for a moment. A read requires access to a page on the disk. Even a one row read is going to go and get an 8k page. That 8k page has to, must, absolutely has to, go into memory before it can be sent to your results on your query. Let's imagine two people running an aggregate against your 500gb data warehouse. Each one is accessing, let's be nice and say, 3gb worth of pages to get the aggregation across a bunch of data. That's 6gb. Even if all 5gb was available for these two queries, and it isn't, you have a whole bunch of other memory caches & requirements that must be met, you're still 1gb shy of being able to move that data through memory for the query. So, it has to page all that out to disk. Yes, it writes out to disk when it doesn't have enough room in memory to gather up a result set. This causes slow downs, additional disk requirements, all sorts of crazy stuff. And we're only talking about two benign queries. What about lots more queries? What about bad queries that are scanning huge amounts of data? What about the need for memory for hash match joins or hash match aggregates? What about the plan cache? I can keep going.

You don't have enough memory on the server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

WhiteLotus wrote:

Hi All,
Our server has a Datawarehouse database is like 600 GB ( 500 GB for data file only ) and memory is ONLY 8 GB ( 5 GB for SQL ) . There are only 2 Databases and the other database is very small (58 MB)
SQL edition is SQL 2014
I feel The memory is too low.
But how much memory that i should recommend ?
Your feedback is appreciated

Soooooo... let's ask the most important question of all. Are you having performance issues with the workload you have against it? And, how many CPUs (virtual or otherwise) does the database have access to?

Considering that the database is more than half a Terabyte and not knowing any of the above, I'd recommend at least 64GB but I also don't recommend you go cheap. I suspect you only have the Standard Edition (you really need to get better at providing enough facts in your questions) so go for 128GB. Increasing memory does NOT increase licensing cost, either!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

4Manley wrote:

Thanks for sharing, I am very impressed with your post.

Smell that? {sniff}... smells like a prelude to SPAM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Jeff Moden wrote:

4Manley wrote:
Thanks for sharing, I am very impressed with your post.
Smell that? {sniff}... smells like a prelude to SPAM.

'

Yeah. Fixed it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning