Memory Hog

Question

Memory Hog

DataSort

Grasshopper

Points: 21
More actions
October 1, 2010 at 11:46 am

#224586

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 1

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.

DataSort Grasshopper Points: 21 More actions · Answer 2

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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

DataSort Grasshopper Points: 21 More actions · Answer 4

I have tried to limit the memory in the server properties under memory. this did not help

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

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.

mnDBA SSCrazy Points: 2032 More actions · Answer 6

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!

Paul White SSC Guru Points: 150467 More actions · Answer 7

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