September 19, 2013 at 3:03 pm
One item you often see in "best practices" articles about SQL is the need to configure Maximum Memory at the server level. It is said that SQL will gradually consume more and more RAM until the server has to page memory, slowing everything to a crawl.
But in my experience this isn't necessary true. While I have had to lower the SQL memory on a few servers that have other processes running on them that require a significant chunk of RAM, I've also identified at least a dozen instances (a mix of SQL 2005 & 2008 R2) that have been running for a couple years or more with no performance issues and a healthy amount of RAM free. These servers generally have a default SQL instance only, and little or no vendor software or other processes running on them. It seems that SQL will not increase RAM indefinitely - it will use the amount needed to optimumly handle the workload it has and leave it at that. If the RAM doesn't exceed that need plus the need of the OS & other processes by a healthy margin, then Max Memory definitely needs to be set.
Do all of you always use the Max Memory setting? Or do you implement it only for large/high traffic DBs, multiple instances, when other processes compete for resources or paging occurs?
September 19, 2013 at 4:04 pm
dan-572483 (9/19/2013)
One items you often see in "best practices" articles about SQL is the need to configure Maximum Memory at the server level. It is said that SQL will gradually consume more and more RAM until the server has to page memory, slowing everything to a crawl.
That is of course wrong. SQL Server will grab as much memory it can, without causing damage. If someone else needs memory, it will yield memory.
That said, I'm still in favour of setting Max Server memory to make sure that the OS has some headroom. This is even more important if there are multiple applications on the server, including multipl SQL Server instances.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 19, 2013 at 4:14 pm
dan-572483 (9/19/2013)
It is said that SQL will gradually consume more and more RAM until the server has to page memory, slowing everything to a crawl.
No, it'll consume memory until Windows tells it to please reduce memory (unless you have the rare scenario where the entire working data size and plan cache size and other caches is less than physical memory). Then it will reduce memory until Windows tells it that there's memory available. Then it will consume memory until Windows tells it to reduce memory. Then....
You can see that by looking in the ring buffer or digging into memory allocations. It's a pointless dance.
Do all of you always use the Max Memory setting?
Yes.
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
September 30, 2013 at 9:39 am
I've been making it a practice to allocate 2gb of memory to the OS as a general rule. I have seen some random cases where SQL Server will overtake a machine causing it to hang. Ever since I've been throttling back memory to SQL Server I have never seen that problem.
A number of years ago I was monitoring a SQL Server box every 30 minutes. What I found was something rather interesting. My Boss and I realized there must have been some memory leak in some application where by memory was not being released by SQL Server. I did see it go up and down when when it released memory it was never back down to the initial level. After about 25-28 days that server would hang. With that same monitor process I would send out emails and txt messages to me when I got to about 3 days prior to a server hang. When I got the txt message and email I'd simply restart SQL Server and had no further issues. I did this for about 6-8 months until we moved the database server to a new platform. Post move I never saw that issue.
From Lessons Learned I make it a practice to tell SQL Server how much memory it can have.
All is happy...
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply