October 1, 2010 at 11:46 am
I am trying to find out how to control the amount of memory used by my SQL Server. I am using R2 Developer on a Windows 7 ultimate 64 bit with 4 gigs memory. When I run large stored procs the system will grab all the memory of the machine. When the proc ends the memory is still taken by SQL Server.
I have tried to restrict the memory under server properties but this does not seem to restrict the memory grab.
Thanks in advance for advice,
DataSort
October 1, 2010 at 11:55 am
what are you attempting to restrict the maximum memory to? SQL Server does not strictly abide to that maximum memory setting and can actually consume more than that; that being said are you sure it's actually sql server grabbing that much ram and not the OS?
Also, have to toss in a "you probably should run sql server on windows server 2003/2008, not win7" warning.
Take some perfmon statistics and look at taskmgr... figure out if its the os or sql server consuming the ram.
October 1, 2010 at 12:25 pm
SSC
Thanks for your reply. As for the box OS, it is a laptop so do not want to use Win Server. When i run the stored proc I can watch the memory from task manager baloon. When the proc finishes and I "restart" the SQL server then the memory usage drops by 3 gig. That is why I think it is SQL grabbing the memory
October 1, 2010 at 12:48 pm
please post your configuration settings "min server memory" and "max server memory".
On every 64-bit os, you should set the max server memory or else it may grab all ram.
(the same will happen on a 32-bit system having less then 1,7 gb ram or 3GB ram with /3GB enabled in boot.ini)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 1, 2010 at 1:29 pm
I have tried to limit the memory in the server properties under memory. this did not help
October 1, 2010 at 1:42 pm
DataSort (10/1/2010)
I have tried to limit the memory in the server properties under memory. this did not help
That is just for the buffer cache. There is additional overhead for running the actual sql processes, running SSMS, etc. You might have to cut it back even more.
October 2, 2010 at 5:00 pm
getoffmyfoot - can you explain/elaborate on your statement....
"SQL Server does not strictly abide to that maximum memory setting and can actually consume more than that"
Thanks!
October 3, 2010 at 1:19 am
DataSort (10/1/2010)
I have tried to limit the memory in the server properties under memory. this did not help
ALZDBA was asking what you set the properties to. Setting 'max server memory' is the primary way to limit SQL Server's memory usage - it sets the maximum size of the 'buffer pool' which is usually the largest user of memory. Run the following SQL statements and tell us what result you get:
SELECTvalue, value_in_use, description
FROMsys.configurations
WHEREname = 'max server memory (MB)';
GO
SELECTtotal_physical_memory_MB = OSM.total_physical_memory_kb / 1024,
available_physical_memory_MB = OSM.available_physical_memory_kb,
OSM.system_memory_state_desc
FROMsys.dm_os_sys_memory OSM;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply