Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sparxx
Starting Member
23 Posts |
 Posted - 2009-04-30 : 09:30:30
|
| Currently I am working on a workstation w/ SQL Server 2000 installed on it w/ sqlmanager running in the background.My current working environment requires me to run a generic database structure using the SQL Enterprise/Query Analyzer interface. This setup is to help test out issues our clients have with our software so we can diagnose their problems quickly.I have been dealing with an issue for awhile now with the process sqlservr.exe taking up valuable memory multiple MB's at a time. Normally upon bootup, my machine is set to start both sqlservr.exe and sqlmangr.exe so I will have a constant live test system ready to go. The sqlmangr will sit nicely around 5+ MB at all times while sqlservr will stay around 190+ MB. This is not so bad, as long as it stays at that level. However, on occasion, w/o even accessing the databases or Analyzer/Manager, it will start to RAPIDLY eat up memory until it reaches around 325MB running the background. This severly degrades my performance of my machine and have been stuck for 10 minutes of lag in some cases waiting for it to either drop, or the system balance itself out so I can work again.Currently, my workstation is running XP Pro, SP3/1.2 GB RAM/2.6Ghz P4. I have also increased my Virtual Memory size to max @ 4096MB in case I were to run out of physical memory. Am I missing a setting somewhere in the software that controls the dynamic usage of valuable resources? I don't want to have to keep rebooting each time this occurs and it is frustrating when in the middle of something I am forced to stop and wait for the PC to catch up....Any suggestions?TIA |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-30 : 10:31:04
|
| sql server will take any memmory that it can and need. it's designed that way. you can set its max memory though and it won't go over that.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-30 : 10:35:47
|
Well, SqlServer is designed to be run on a dedicated machine. The idea is to allocate a lot of memory to it and let it use all of it. It uses that memory to cache execution plans and data So the more memory it uses the better your SQL performance. If you need to run the server on your workstation then you'll probably need to decide how much memory you are willing to allocate to sql server and specify that in the server properties | memory tab. Just know that reducing it will impact your sql query performance and possibly skew your testing.EDIT: Be One with the OptimizerTG |
|
|
|
|
|
|
sqlservr.exe - eating valuable memory