3GB RAM vs. 4GB RAM with SQLServer 2000 Standard
-
Everyone agrees that SQL Server Standard Edition will use up to 2GB of RAM. What constitutes "use" seems to be the source of some disagreements. I have seen suggestions that the 2GB limit only applies to the Buffer Pool and that the BPool can itself consume up to 2GB of RAM. Others have suggested that the MemToLeave (MTL) is subtracted from the 2GB so the most available to SQL Server would be 1.7 GB.
I have a server that had 2 GB of RAM (Windows Server 2003, SQL Server 2000 Standard). According to the SQL Memeory Manager and the Task Manager the SQL Server process caps out at about 1.7 GB. I *really* need a short-term gain in memory to increase my Cache Hit Ratio - so I had another 1 GB of RAM added to the server. Now there's tons of available memory but SQL Server will not stay above 1.7 GB. It will spike up to 1.8 or so and then start releasing memory again. I've tried fixed memeory of 2048, and dynamic from 1842 to 2048 and have not seen a difference.
Now the question (hopefully someone else had done this): If I add another 1 GB of RAM (total of 4GB) and enable the /3GB switch can I *then* get SQL Server Standard Edition to use the full 2GB?
Frustrated by Design: Gary -
Gary
If you use the /3GB switch then you will, in theory, be able to use 3GB of RAM. I'm sure I don't need to remind you to reboot after you've made the change. Experiment with having memory dynamically allocated and fixed at (or up to) 3072MB. Do you have any other applications running on the server? They could be grabbing memory that SQL Server would otherwise be using.
John
-
I've left the amount of RAM in the server at 3 GB but added the /3GB switch to the boot.ini file. Re-started the server and the sqlserver process started taking a smidge (1%) more memory. It looks like SQL Server is capping at 2GB *including* all processes. Either that or there is not any other information that SQL Server feels in important enough to load into memory. My conclusions:
1> Adding 3GB of RAM to a dedicated SQL Server Standard box helps anything else that would be running on that server. (before the upgrade I had less than 100MB of available RAM, now I'm at 900MB).
2> The Buffer Pool is capped below 2 GB of RAM. The 2 GB RAM limit of SQL Server 2000 Standard Edition counts more than just the buffer pool in its cap.
3> The /3GB switch will help and is valid on servers with only 3 GB of RAM. You don't have to wait until you're at 4 GB to get applications to load higher in memory.
Thanks for your time John,
Gary
-
Thanks for sharing the results of your experiments - it's nice to see feedback appear on the forums. Hopefully when someone else has the same question they'll at least be able to find a ready answer from someone who's "been there, done that"
-
32bit sql server std will only use 2gb ram no matter what. 900mb of free ram shows that you have 900mb of wasted memory - I fail to understand why people consider large amounts of "free" memory a good thing. If you ran a restaurant would you always leave 10 covers unused just to be sure?
Certainly you want to have adequate memory, remember that many "SQL Server" components actually use out of process memory so this can impact performance. A read of Ken Henderson's guru's guide to architecture would prove useful.
With STD edition you will not improve your cache hit ratio this way - try tuning the queries/plans that use most cache memory, make sure you have maximum re-use and parameterisation - illiminate table scans and alrge data laods in and out of data cache.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
With 4 GB or RAM 2 G goes to the Kernal and 2 G goes to User processes. So Sqlserver (a user process) shares 2G with all other running processes, including Anti-virus, Backup agents, etc.
With the 3GB switch set the user processes share 3 GB.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply