May 15, 2009 at 9:31 am
We're running SQL Server 2005 Standard x64 on a dedicated Windows 2003 x64 box. It has quad 6-core cpus and 48gb of RAM.
We have Max Memory set to 44544 trying to leave enough memory for the OS, backup software, etc., but SQL keeps grabbing far more memory than that. It's been 10 days since our last reboot and we are down to 420mb free for the system (according to perfmon and our db monitoring tool). If the pattern holds true it will keep dropping until the OS finally pages SQL out to disk. Being Standard Edition the "lock pages" setting isn't an option for us.
I've searched this site, Microsoft's site, and googled the problem but can't really find an explanation. Please let me know what additional info you would need to see to help me with this and I'll get it on here.
May 15, 2009 at 12:35 pm
Amy Russell (5/15/2009)
We're running SQL Server 2005 Standard x64 on a dedicated Windows 2003 x64 box. It has quad 6-core cpus and 48gb of RAM.We have Max Memory set to 44544 trying to leave enough memory for the OS, backup software, etc., but SQL keeps grabbing far more memory than that. It's been 10 days since our last reboot and we are down to 420mb free for the system (according to perfmon and our db monitoring tool). If the pattern holds true it will keep dropping until the OS finally pages SQL out to disk. Being Standard Edition the "lock pages" setting isn't an option for us.
I've searched this site, Microsoft's site, and googled the problem but can't really find an explanation. Please let me know what additional info you would need to see to help me with this and I'll get it on here.
It is usually a mem-to-leave issue. You don't need to actually "reboot" the machine you can, *at a low activity time* change the max to a lower value.
Things to look for are : third-party products, CLR routines and extended procs if any, etc.
Do you have any of the above installed there ?
* Noel
May 18, 2009 at 7:49 am
Noel,
We aren't using any CLR routines or extended procs on that box. If anything it's under heavy load of ad hoc queries we are slowly getting cleaned up with stored procs. As for third party products on that box, the only ones are Litespeed (backup), Symantec AV, and Diskkeeper. Would any of those cause a problem?
Thanks!
May 18, 2009 at 9:58 am
you are not leaving enough memory for the o/s and other processes, esp with all those cores. I'd suggest you set max memory to no more than 40GB, ideally about 38GB. I have some posts on my blog about x64 config which might help. You might want to install process explorer to see what is using your memory. 420MB free isn't much, probably not enough by far - but it's still free memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2009 at 11:51 am
My bet is on "Litespeed (backup)"
BTW : make sure AV is not touching SQL Files, backups etc ...
How are you copying your backups out of the box ?
* Noel
May 18, 2009 at 3:02 pm
Backup Exec, missed that one.
May 18, 2009 at 3:25 pm
I'll go check out your blog this evening, Colin. We've been wondering if we didn't need to back off the max memory even more.
I copied process explorer over to my server and took a look. The sqlservr.exe process is holding all of the memory: private bytes= 48,183mb and working set= 46,823mb. The next largest chunk of memory is 292mb held by the SSIS process. For comparison, max memory= 44,544mb and the box only has 49,152mb total memory.
May 19, 2009 at 2:51 am
Max server memory only applies to the buffer pool and does not include significant other areas of SQL Server memory (multipage allocations, linked servers and thread stacks to name a few). Have a look at the following posts, which help you to calculate the max server memory in order to leave enough memory for other processes on the system.
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
Rob
May 19, 2009 at 7:47 am
Rob has the winning post - there are many things other than the buffer pool that eat memory.
I agree with Colin that 44GB is too much to give the buffer pool on a 48GB box, and that is with a server where NOTHING but the sql server engine is running.
Big question is are you experiencing significant performance problems? Are you seeing excessive paging?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 19, 2009 at 4:21 pm
We are definitely experiencing performance problems because the lack of memory for everything else on that box (backup, AV, etc.) causes the OS to page SQL Server out to disk and when that happens performance degrades for hours while SQL gets swapped back into memory. We are experiencing this about 3 times a week and always at an inopportune time.
I checked out the posts that Rob pointed me to and it looks like we need to bring our max memory setting down to 41gb, maybe 39gb. I just have to get a time for that change scheduled with the team.
In taking a closer look at everything running on that box, I noticed Diskkeeper wasn't using much memory when I was checking in but it had a peak private bytes value in taskman of almost 5gb. I'm going to go search this site but if anyone off the top of their head can point out some best practices regarding disk defrag on a dedicated SQL Server, I'm all ears.
May 19, 2009 at 4:44 pm
Amy Russell (5/19/2009)
We are definitely experiencing performance problems because the lack of memory for everything else on that box (backup, AV, etc.) causes the OS to page SQL Server out to disk and when that happens performance degrades for hours while SQL gets swapped back into memory. We are experiencing this about 3 times a week and always at an inopportune time.I checked out the posts that Rob pointed me to and it looks like we need to bring our max memory setting down to 41gb, maybe 39gb. I just have to get a time for that change scheduled with the team.
In taking a closer look at everything running on that box, I noticed Diskkeeper wasn't using much memory when I was checking in but it had a peak private bytes value in taskman of almost 5gb. I'm going to go search this site but if anyone off the top of their head can point out some best practices regarding disk defrag on a dedicated SQL Server, I'm all ears.
1) If you have lots of other stuff on the box even 39GB may not be low enough. But I would try there first.
2) Changing max memory is dynamic - no reboot required. Having said that, experience has shown that sql server REALLY doesn't like to give back it's memory. 🙂 But if you get paged out then it 'shouldn't' go back to above the current setting. I would change the value immediately if it were me.
3) Best Practice regarding disk defrag on sql server is to not have it run automatically with sql server running. I advise clients to do disk defrag during scheduled outages. I do have clients that run diskkeeper with sql server up - no one has complained of a corruption yet.
4) Another Best Practice is to disable all services that aren't required - and there are a bunch of them. Oh, and did you set your AV to exclude SQL Server file types such as ldf, mdf, ndf, maybe bak or whatever you use for backup files?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2012 at 5:09 am
Apologies for digging up and old thread 😀 but having the same issue with a Windows 2003 x64 SP2 and SQL 2005 Ent x64 SP4. My Prod server has 32GB RAM installed and SQL was hogging it all, only leaving a few hundred MB for the OS, so capped the SQL memory at 28672MB (28GB) and to be on the safe side rebooted the server as it had been up for over 200 days. Now checking Task Manager the sqlservr.exe is utilising 30,297,392 KB, I am surprised to see it that high when I capped the SQL memory at 28GB. I am running perf mon to see why it has ramped back up after the reboot, but any ideas? I do know that many of the indexes the the databases running on the SQL Instance need a rebuild but would index fragmentation cause this level of memory hogging?
Some interesting notes from a blog on here:
http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/[/url]
TIA
qh
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply