I am running SQL Server 2000 on a desktop PC. Just recently upgraded my PC to 2gig of ram from 1 gig, in part, to try and fix the problem below. Didn't work. Have SP3 installed but not SP4 at this point.
When I open up query analyzer and edit some code, regardless of whether I actually execute the code, SQL server eventually sucks up available and cached memory to the point that my system comes to an effective halt --takes forever to do anything either in SQL server or other applications. In the task manager PF Usage climbs to just over the 2gig memory limit.
I conceptually understand the dynamic memory operation of SQL server ... but why is it sucking up most available memory when nothing is executing
Is there a way I can release/clear the memory Ideally, code that coould be run under a stored procedure would best meet my needs. Right now, I am "fixing" by shutting down and then restarting SQL server.
Thanks,
SQL Server 2000 -- Taking over my memory
Moh_Sheybani
I understand. After you set me on the right track in your earlier reply I was able to access the appropriate help screens and figured out not to reset the working set size. I will play with the max server memory to see what works best.
Thanks again.
DueyDooDah
You can limit the memory that SQL Server will grab for use with data cache by setting the 'Max Server Memory' server option in EM or Query Analyzer. If you open a query session and execute the following for example, you'll limit the buffer pool for SQL Server to 750 MB:
exec sp_configure 'Max Server Memory', 750
go
reconfigure
go
To take effect, you'll have to recycle your server. As for why the server is grabbing all the available memory on the machine, that seems odd for the system you described above. Couple of things I'd be curious: 1) what are your current min/max server memory settings 2) do you have the 'set working set size' option set to 1, i.e. turned on 3) do you have AWE enabled which would be odd on the system you described.
You can run the following to get the answers to the above questions:
sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'min server memory'
exec sp_configure 'max server memory'
exec sp_configure 'awe enabled'
exec sp_configure 'set working set size'
go
iamhtran
From http://p2p.wrox.com/topic.asp TOPIC_ID=147:
If you don't set a ceiling on memory usage SQL Server will use as much memory as is available. When it has aquired memory it will not release it, unless you drop the ceiling. This is by design and is not a problem. Just set a ceiling that leaves enough space for whatever else is running on the machine.
To set a ceiling, open Enterprise Manager, right click the server, then Properties, Memory tab. You can adjust the minimum and maximum memory used by SQL Server on this tab.
SQL Server generally runs best with the 'Dynamically configure SQL Server memory' option selected. You should use the Fixed memory option only under special circumstances (like you know what you are doing!) as you can starve other server processes if you are not careful.
Alex Leung
No, DON'T set the 'set working set size' to 1, that will in effect tell SQL Server to take the memory configuration you have and reserve it at startup, and not release it until it's shutdown...that's an advanced option, and shouldn't be used unless recommended in reality, and I wouldn't recommend it here for sure
...
Set your 'max server memory' to a value like 750MB, or 1024MB, or whatever you think is appropriate, which will tell SQL Server to take no more than that for the buffer pool. Bear in mind SQL Server will use additional memory outside the buffer pool, but that is definately the largest factor on mem usage in SQL.
HTH
Burlow
Thanks to both of you for the help.
the advanced option was set to 1.
The min server memory was min=0, max a little over 2.1 gig, config_value 0, run_value 0
for the rest in the same order
the max server memory -- 4, 2.1 gig, 0,0
awe enabled--0,1,0,0
working set size -- 0,1,0,0
I inferred from your response, I should set the working set size to 1 instead of 0. Correct
I will play around with various ceiling sized to see what the impact is.