Max Memory

Question

Max Memory

Admingod

SSCertifiable

Points: 5997
More actions
June 7, 2017 at 10:22 am

#326413

On sql server 2008 server I can see the Page life expectancy is lower than 300 secs during business hours. However, the server has 34 GB memory and SQL is assigned to 22GB. Was thinking to give more memory to sql with max memory as 30GB. Does that help memory bottleneck on the server. Is any other metrics i need to look before i consider doing this change. Please advise?
Thanks

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

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

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

Tune your queries, that's often a far better return than adding memory.

And with 34GB memory, I would probably not go above 28GB on max server memory, and that's only if it's a dedicated database server.

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

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

PLE all by itself as a measure doesn't tell much. What kind of wait statistics are you experiencing? How fast are the queries? Are you experiencing blocking? I'd be much more concerned about all this rather than PLE.

"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

Admingod SSCertifiable Points: 5997 More actions · Answer 3

GilaMonster - Wednesday, June 7, 2017 10:41 AM
Tune your queries, that's often a far better return than adding memory.
And with 34GB memory, I would probably not go above 28GB on max server memory, and that's only if it's a dedicated database server.

Thanks!
I will give a try. It's a dedicated database server.

Admingod SSCertifiable Points: 5997 More actions · Answer 4

Grant Fritchey - Wednesday, June 7, 2017 10:51 AM
PLE all by itself as a measure doesn't tell much. What kind of wait statistics are you experiencing? How fast are the queries? Are you experiencing blocking? I'd be much more concerned about all this rather than PLE.

I measured for over a past month and it doesn't seems to be new. The PLE is low for last few months. I am not seeing any blocking. Does giving more memory to sql you think will improve query performance little bit? Seems like a good idea?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Admingod - Thursday, June 8, 2017 10:08 AM
Grant Fritchey - Wednesday, June 7, 2017 10:51 AM
PLE all by itself as a measure doesn't tell much. What kind of wait statistics are you experiencing? How fast are the queries? Are you experiencing blocking? I'd be much more concerned about all this rather than PLE.
I measured for over a past month and it doesn't seems to be new. The PLE is low for last few months. I am not seeing any blocking. Does giving more memory to sql you think will improve query performance little bit? Seems like a good idea?

More memory is almost always a good idea, however, it won't necessarily fix any performance problems. As I said, low PLE, especially just low PLE all by itself, doesn't mean very much. You'd be much better served looking to the wait statistics and the general query performance as I said.

"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