Memory Utilization Advise

Question

Memory Utilization Advise

chewychewy

Hall of Fame

Points: 3126
More actions
June 11, 2012 at 4:02 am

#392478

Hi Guys,
I do understand that SQL Server's caching behaviour is the reason for the substantial memory use. This behavior is normal and is an intended behavior of the SQL Server.
Just need some advise here from experts out there.
1. With setting max server memory limit: Let's say we have 8GB Physical Ram and SQL Server Memory is limit at 4GB. With this limit in placed, we will have a nice monthly memory utilization chart, probably about 70% monthly. However the memory utilization chart doesn't mean anything since the memory is capped. Kindly advise how do we know if the memory (4gb) allocated to SQL Server is really sufficient and any indicators that will tell us that it's time to allocate more memory to SQL Server.
2. Without setting max server memory limit: Let's say we have 8GB Physical Ram and the memory utilization is 95%. How do we know when the memory is really insufficient so that we can plan to beef up the Memory?
I'm asking this as basically in alot of environment, the management use memory utilization as the sign to deem whether a server is healthy or not. However for SQL Server, we can't really use the memory utilization as a gauge anymore. As such, I wondering if there is any indicators that we can use that can justify that the memory is healthy.
thanks

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

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

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

http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx

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

chewychewy Hall of Fame Points: 3126 More actions · Answer 2

Hi Gila,

thanks.

According to the url, we can monitor 3 perfmon counters to see if our memory utilization is healthy:

•SQL Server:Buffer Manager\Page Life Expectancy

•SQL Server:Buffer Manager\Page reads/sec

•Physical Disk\Disk Reads/sec

Based on ur expertise, what is a good value range for the above counters?

thanks

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

You could start by what Jonathan says in his blog post, the paragraph right below where he lists the counters.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

"good enough" values for pretty much ANY counter are VERY dependent on your system and what type of performance you need and are actually getting. Assuming your server can hold more memory, adding RAM is usually WAY CHEAPER and WAY MORE EFFECTIVE at making things go faster in SQL Server, because stuff that is already in the larger RAM-based buffer pool doesn't have to be retrieved from the 3-4 orders-of-magnitude-slower disk system.

Having said that, virtually EVERY client I have EVER had has not had sufficient IO response time and/or throughput for what their server really needed to make things as fast as they needed/wanted.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service