March 9, 2014 at 7:41 pm
I have an instance of SQL Server 2008 R2 installed on a VM with 4 cpus and 16 GB of RAM. It currently uses 15.5GB of memory and I can't get it to use less. I'm having problems logging into the VM because there's not enough memory for remote desktop to function properly. I've tried setting the "Maximum server memory" setting and currently have it at 4096 but it seems to do nothing. I'm still maxing out the memory. Can anyone help?
March 9, 2014 at 11:32 pm
What process are you using to determine what amount of RAM the SQL Server instance is using?
The maximum server memory is only for what the buffer pool will allocate. At the other end the minimum setting is to specify when SQL Server has to release buffer pool memory it should not go below that value.
If you are using Task Manager to view the amount of RAM the "sqlservr.exe" process is using for your instance that is showing RAM for more than just the buffer pool.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 10, 2014 at 4:50 am
Yes, I'm using task manager. I'm not sure what the "buffer pool" is but what I do know is that if I do the following commands
NET STOP "SQL Server Agent (BRKSQL02)"
NET STOP "SQL Server (BRKSQL02)"
then task manager shows that the total Memory used goes from 15.6GB to 800MB and other programs (such as remote desktop) are then usable, that is for the next hour or so when SQL Server hogs it back up again. Is changing the amount of buffer pool the wrong way to limit SQL Server? Should I have something in the "Minimum server memory"? Currently it's a 0.
Thanks for your response!
March 10, 2014 at 5:12 am
Min server memory is fine at 0. What's the exact value for max server memory?
Run
exec sp_configure 'show advanced', 1
RECONFIGURE
exec sp_configure 'max server memory'
What's the exact output?
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
March 10, 2014 at 5:26 am
When I run that code I get:
name__________________minimum_____maximum____config_value____run_value
max server memory (MB)____16______2147483647______4096_________4096
March 10, 2014 at 5:30 am
Ok, that is weird.
Will have to hunt for a memory breakdown script to see what's happening.
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
March 10, 2014 at 9:45 am
DKY (3/9/2014)
I have an instance of SQL Server 2008 R2 installed on a VM with 4 cpus and 16 GB of RAM. It currently uses 15.5GB of memory and I can't get it to use less. I'm having problems logging into the VM because there's not enough memory for remote desktop to function properly. I've tried setting the "Maximum server memory" setting and currently have it at 4096 but it seems to do nothing. I'm still maxing out the memory. Can anyone help?
Is SQL Server 32 bit or 64 bit ? Did you have Lock Pages in Memory enabled ?
And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.
--
SQLServer
March 10, 2014 at 9:56 am
sqlbuddy123 (3/10/2014)
And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.
Max server memory does not require a restart. It is effective immediately.
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
March 10, 2014 at 10:32 am
I believe it's 64 bit (even though it seems to be installed in the 86 program files folder).
I followed this process for the lock pages in memory and there are no users or groups, it's blank.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
The server was restarted over the weekend and it's still having this issue.
March 10, 2014 at 10:46 am
GilaMonster (3/10/2014)
sqlbuddy123 (3/10/2014)
And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.Max server memory does not require a restart. It is effective immediately.
Yeah, I know. This is true when going from lower value to higher value and not necessarily Vice Versa. That's why I mentioned
This is to release excessive memory grabbed
--
SQLBuddy
March 10, 2014 at 10:48 am
March 10, 2014 at 11:00 am
Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?
March 10, 2014 at 11:17 am
What are your Memory Settings right now ? How much memory is SQL Server consuming in the Task Manager ?
What is the SQL Server Build number ?
Check the o/p of this query ..
select [Memory Used KB] = (pages_allocated_count * page_size_in_bytes)/1024 from sys.dm_os_memory_objects where type = 'MEMOBJ_RESOURCE'
Check the Values of these counters
Total Server Memory
Target Server Memory
Available Mbytes
Performance object: Process
Counter: Private Bytes
Instance: sqlservr
Performance object: Process
Counter: Working Set
Instance: sqlservr
--
SQLBuddy
March 10, 2014 at 11:19 am
DKY (3/10/2014)
Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?
Right. When did you stop the instance and when did you start getting this issue ?
--
SQLBuddy
March 10, 2014 at 11:20 am
sqlbuddy123 (3/10/2014)
GilaMonster (3/10/2014)
sqlbuddy123 (3/10/2014)
And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.Max server memory does not require a restart. It is effective immediately.
Yeah, I know. This is true when going from lower value to higher value and not necessarily Vice Versa. That's why I mentioned
This is to release excessive memory grabbed
It is true for both cases. When the Lazy Writer task runs it checks the value for Max and Min and adjusts the targets and the notifies the clerks to adjust their memory accordingly.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply