S‌QL Server using 97% of RAM.

Question

S‌QL Server using 97% of RAM.

coolchaitu

SSChampion

Points: 10334
More actions
November 13, 2017 at 9:51 am

#329515

Good Morning Experts,
SQL Server is using 97% of RAM. Could you please advise what needs to be done.

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

(1) Make sure you set max server memory such that there is sufficient memory left for the operating system and any other applications or SQL Server components on the server
Then...
(2) Be happy that SQL Server is running as it should.

John

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 2

By design, SQL Server will take every bit of memory that you offer it. It's supposed to. It will grow into the memory allocation limit that you set and then it will sit there, all the time. It's one reason why monitoring percent of memory used is kind of a waste of time. As for as what to set it to, here's some very good guidance from a source I trust.

"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

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 3

You can also look at Target Server Memory in Performance monitor to get an idea of what SQL Server thinks it needs.

coolchaitu SSChampion Points: 10334 More actions · Answer 4

Steve Jones - SSC Editor - Monday, November 13, 2017 11:56 AM
You can also look at Target Server Memory in Performance monitor to get an idea of what SQL Server thinks it needs.

SQL Server memory usage reached to 99%. Please let me know what should i do. Shall I decrease the max server memory value?

vimazi SSC Rookie Points: 25 More actions · Answer 5

Hi coolchaitu,

You should limit the memory SQL Server uses. In the SQL Server Management Studio, after you connect to the db server:
1. Right click on the name and choose Properties
2. In the Server Properties window, choose Memory
3. In this window you have Server memory options. Edit the Maximum server memory(in MB) to the value you think it would be appropriate in your case (i've set it to max 80% of the total RAM available, but you could adapt).
You have to restart the server for the setting to become effective.

Let me know if it helps.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 6

vimazi - Tuesday, November 14, 2017 4:03 AM
Hi coolchaitu,
You should limit the memory SQL Server uses. In the SQL Server Management Studio, after you connect to the db server:
1. Right click on the name and choose Properties
2. In the Server Properties window, choose Memory
3. In this window you have Server memory options. Edit the Maximum server memory(in MB) to the value you think it would be appropriate in your case (i've set it to max 80% of the total RAM available, but you could adapt).
You have to restart the server for the setting to become effective.
Let me know if it helps.

Just to point out you don't have to re-start for the changes to take effect.

Thanks

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

Also to point out and speaking from a bit of experience where I made a Phat Phinger mistake... BE VERY VERY CAREFUL WHEN MAKING THE SETTING CHANGE!!!! If you screw up and tell the server to only use, say, 100MB max memory by mistake, you may not be able to fix it and because you won't be able to start SQL Server to fix it.

--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: 398880 More actions · Answer 8

Jeff Moden - Tuesday, November 14, 2017 8:56 AM
Also to point out and speaking from a bit of experience where I made a Phat Phinger mistake... BE VERY VERY CAREFUL WHEN MAKING THE SETTING CHANGE!!!! If you screw up and tell the server to only use, say, 100MB max memory by mistake, you may not be able to fix it and because you won't be able to start SQL Server to fix it.

yeah, I did this too. There is a way to force the memory setting on startup. It's a traceflag somewhere. If you do make this happen (as I did), you can get out of it... after a lot of panicked searching on the web.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Grant Fritchey - Tuesday, November 14, 2017 1:51 PM
Jeff Moden - Tuesday, November 14, 2017 8:56 AM
Also to point out and speaking from a bit of experience where I made a Phat Phinger mistake... BE VERY VERY CAREFUL WHEN MAKING THE SETTING CHANGE!!!! If you screw up and tell the server to only use, say, 100MB max memory by mistake, you may not be able to fix it and because you won't be able to start SQL Server to fix it.
yeah, I did this too. There is a way to force the memory setting on startup. It's a traceflag somewhere. If you do make this happen (as I did), you can get out of it... after a lot of panicked searching on the web.

-f startup switch, to start in minimal configuration

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass