 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Weekly maintenance plan hogging system resources
Hello: Our weekly maintenance plan takes up so much RAM on our Windows Server 2003 with service pack 2 (2 GB of RAM) that we have to reboot the server the next day in order to free up the RAM. Besides adding more RAM, is there anything else that can be done to eliminate this problem? Thanks! childofthe1980s > Hello: > [quoted text clipped - 3 lines] > next > day in order to free up the RAM. Why? RAM will be released whenever another application requires it. This behaviour is perfectly normal shouldn't be any reason for concern. 2GB is not very much for SQL Server in most circumstances so I would not recommend trying to restrict memory usage any further.  SignatureDavid Portas Thanks, David! childofthe1980s > > Hello: > > [quoted text clipped - 8 lines] > not very much for SQL Server in most circumstances so I would not recommend > trying to restrict memory usage any further. As David stated SQL Server will hold the memory it uses until the OS specifically asks for some of it back again and this is by design. But if you have any other apps running on that server you might want to set the MAX Memory of SQL Server so that you always leave x MB's for the other app and the OS. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hello: > [quoted text clipped - 10 lines] > > childofthe1980s Due to hardware limitations my SQL server 2005 SP2 runs as a VM. It sits on server 2003 and that VM only has 512 RAM dedicated to it. I tried 256 MB RAM but SSMS took FOREVER to launch and do anything within so I upped the VM to 512 MB RAM. Still, that VM is consistently at 90% memory utilization or more. I know I can constrain SQL server to use less memory, but how much less? Should I constrain to use no more than 256 MB? > As David stated SQL Server will hold the memory it uses until the OS > specifically asks for some of it back again and this is by design. But if [quoted text clipped - 16 lines] >> >> childofthe1980s I wouldn't even attempt to run the VM itself with only 512MB. SQL Server uses several hundred MB's of memory for what used to be called MemToLeave memory which does not show up as usage for SQL Server in task manager. The Max Memory setting is the memory for the buffer pool that does not count the MemToLeave area. Meaning that if you told SQL Server to use a Max of 256MB via the Max Memory setting it will in fact use around 500MB in total. So you would have to limit the Max Memory to far less than 256 to leave enough for the OS etc. Which makes it pretty much useless for any real processing. I don't understand the "due to hardware limitations" part. By adding VMware you automatically make any limitations of hardware much more limiting. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Due to hardware limitations my SQL server 2005 SP2 runs as a VM. It sits > on server 2003 and that VM only has 512 RAM dedicated to it. I tried 256 [quoted text clipped - 23 lines] >>> >>> childofthe1980s Given that I've already told SQL Server to use a Max of 256MB, how would I "limit the Max Memory to far less than 256 "? Andrew, this is on a laptop on which I have another VM running. I carry the laptop around with me, and that is what constrains me. >I wouldn't even attempt to run the VM itself with only 512MB. SQL Server >uses several hundred MB's of memory for what used to be called MemToLeave [quoted text clipped - 36 lines] >>>> >>>> childofthe1980s You would have to lower the setting of MAX Memory from 256 to ??. But going too low will limit what you can do with SQL Server without having severe performance problems. I am afraid you are pushing the limits of what can be done with the hardware you have and the way in which you choose to configure it. What I was really asking was why are you using a VM at all? Why don't you simply run SQL Server directly from the host OS? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Given that I've already told SQL Server to use a Max of 256MB, how would I > "limit the Max Memory to far less than 256 "? [quoted text clipped - 42 lines] >>>>> >>>>> childofthe1980s I am testing everything inside a confined "network" which means everything has to be installed on VMs. Your comments have given me a sanity check. I may up the VM to 768 MB RAM. Can you tell me how I "limit the Max Memory"? > You would have to lower the setting of MAX Memory from 256 to ??. But > going too low will limit what you can do with SQL Server without having [quoted text clipped - 49 lines] >>>>>> >>>>>> childofthe1980s You can do this via sp_configure or from the properties page of the server instance in SSMS. Have a look at MAX Memory in BooksOnLine for more details. I try not to have anything less than 1GB of memory for any virtual instance these days. There are just too many issues and performance problems that you have to deal with. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >I am testing everything inside a confined "network" which means everything >has to be installed on VMs. Your comments have given me a sanity check. I [quoted text clipped - 54 lines] >>>>>>> >>>>>>> childofthe1980s I suppose I could drop back down to SQL 2000 Server. It's faster (the GUI is much snappier), less resource-intensive, plus it puts that handy little green arrow icon in the system tray to give you a good feeling that the SQL server is running! > You can do this via sp_configure or from the properties page of the server > instance in SSMS. Have a look at MAX Memory in BooksOnLine for more [quoted text clipped - 61 lines] >>>>>>>> >>>>>>>> childofthe1980s >... plus it puts that handy little >green arrow icon in the system tray to give you a good feeling that the SQL >server is running! There is a free version of that tool which implements it for 2005, Google SQL Server 2005 Service Manager to find it. Roy Harvey Beacon Falls, CT Thanks Ron! I forgot to mention the other advantage of SQL 2000 - on top of being faster (snappier GUI), less resource-intensive, and the built-in little green arrow, it doesn't have that annoying dialog box you get every time you open SQL Mgmt Studio asking you which SQL instance you want to connect to! >>... plus it puts that handy little >>green arrow icon in the system tray to give you a good feeling that the [quoted text clipped - 6 lines] > Roy Harvey > Beacon Falls, CT |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.