August 25, 2011 at 9:31 am
We have a newer SQL Server 2008 R2 machine running on Server 2008 x64, and we noticed that the sql service was eating up all the RAM on the box. I then applied a maximum server memory of 45056, and the service has since surpassed this number and is now consuming over 47 GB out of the 48 GB on the box. Any ideas as to why it would ignore the specified number?
Thanks!
August 25, 2011 at 9:37 am
Did you restart the sql server after you change the max memory setting?
August 25, 2011 at 9:38 am
The SQL Service was restarted, but I don't believe the server has been restarted.
August 25, 2011 at 9:52 am
You do not have to restart the box. restart sql server service should make it works. Could you post me the stat ? By the way, I think you gave too much memory to SQL Server service. Do you know SQL Agent, SSIS, SSRS, ( If they are on the box) using separate memory than the SQL Server Service.
August 25, 2011 at 9:52 am
Doesn't matter, max server memory takes effect without any restart necessary.
SQL is not ignoring the limit. The max server memory sets the size of the buffer pool (data cache, plan cache and a bunch of others). SQL also takes memory outside of the buffer pool (back on 32-bit that was called the MemToLeave)
That's for backup buffers, CLR memory, linked server drivers, extended stored procs, thread stacks and a few other things. It's not usually more than a few hundred MB but, especially if lots of CLR is used, it can become noticeably large.
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
August 25, 2011 at 10:10 am
Regarding BlueTiger's comments:
SQL Agent and SSIS are both on the box, no for SSRS.
SQL Agent has it's own proces which isn't consuming too much memory. As for SSIS...is that combined with the sqlservr.exe process or does it have an independent process? If it's combined, is it subject to the max memory usage value?
How much memory should be allocated to SQL Server? (This box just runs SQL Server)
Regarding GilaMonster's comments:
Is there way to see how much memory the buffer pool is using versus the other memory that SQL is using? Also, CLR isn't enabled so I'm pretty sure that's not it.
August 25, 2011 at 10:49 am
😀
CLR not enabled != no CLR in use.
Some of the internal stuff uses CLR without needing CLR to be explicitly enabled.
General rule of thumb here. if SQL is using too much memory, drop the max server memory setting. It's the only setting you have in 64-bit SQL.
SSIS is a completely separate process. It's DTEXEC.exe or something like that.
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 11, 2013 at 4:09 am
I am facing the same issue. Would enabling LPIM(Lock pages in memory) Help My cause.
I have a VMware server with 4 GB ram and i have limited to 2040 MB.:-)
but we are getting Physical memory issues.
September 11, 2013 at 4:12 am
Zalim Murga (9/11/2013)
I am facing the same issue. Would enabling LPIM(Lock pages in memory) Help My cause.I have a VMware server with 4 GB ram and i have limited to 2040 MB.:-)
but we are getting Physical memory issues.
Is SQL the only application on the server?
September 11, 2013 at 4:15 am
Yes its a dedicated SQL server for a small application.
Following is the memory Clerk output. I see VM_reserve dis more that my Max_mem
typesingle_pages_kbmulti_pages_kbvirtual_memory_reserved_kbvirtual_memory_committed_kbawe_allocated_kb
MEMORYCLERK_SQLBUFFERPOOL0400421478425208320
OBJECTSTORE_LOCK_MANAGER219200819281920
MEMORYCLERK_XE_BUFFER00422442240
MEMORYCLERK_SQLSTORENG24324016224022400
MEMORYCLERK_SQLUTILITIES8801201200
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply