April 25, 2006 at 2:07 pm
I know, I know, typically just don't do it. In fact, I never have. However, in this case I have a server that doesn't seem to use all the RAM it has available, but then drops things out of cache more quickly than I would like.
Here's what the server memory looks like:
RAM: 2,358,812 K
Swap: 1,936,704 K
____________________
4,295,516K
I am running SQL Standard so SQL can't use more than 2GB, or 2,097,152 K. Yet, this server has been running for 120 days and peak memory has never exceeded 1,787,520 K for the SqlServer.exe process. As I type this its sitting at ~1,726,620 K and "Available Physical Memory (K)" is ~360,000 K.
It seems that SQL could use all 360mb of that memory (it would be just below the 2GB limit) but its not. This is a dedicated SQL box so I expect the 2,358,812K-2,097,152K=261MB of remaining RAM should just about cover my system needs. Again, right now I have 360MB RAM doing nothing!
So, I am about to walk myself into a bunch of problems if I set the min amount and max amount to the 2 GB limit? Anyone out there done something similar?
April 25, 2006 at 3:03 pm
What you are seeing is normal. When you have 2GB allocated to SQL Server, it will allocate most of it for the buffer pool, but it will also reserve around 380MB (360 in your case) for other overhead purposes. To check this, you can monitor Target and Total memory. You should see that, in your case, the Target memory is also only ~1.7 GB.
April 26, 2006 at 5:08 pm
John is correct - your SQL Server is bahving normall in it's memory usage. I've noticed for 'standard edition' ~1.7 to 1.8 Gb used. If you go to 'enterprise edition' with the /3GB boot.ini switch you'll get ~2.7 to 2.8 GB used as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 27, 2006 at 8:22 am
Thanks for the replies. I have a couple of additional questions. My original concern came about when I compared the memory usage of the above SQL 2000 sever to a SQL 7.0 Standard server I also have in the environment. Some details...
SQL 7.0 Standard:
RAM: 3,833,372 K
sqlserver.exe process: MemUsage=1,918,640K; PeakUsage=2,016,468
SQLServer Memory:Target Server Memory=1,858,048 K
SQLServer Memory:Total Server Memory=1,858,048 K
While this server obviously has more RAM than the SQL 2000 Standard server I am asking about (first post), they both have more than 2GB (which each SQL install is limited too) and they're both dedicated boxes so I figured the sqlserver.exe memory potential should be about the same. The 7.0 box does not seem to be saving this 360-380MB of memory unless it is saving it above and beyond the 2GB limit. Which, if that is the case, why would the SQL 2000 box not be doing the same thing? I guess there is also the possibility that the buffer alone is limited to 2GB and the 360-380 CAN be above and beyond the 2GB limit, but because of other system/OS/admin related services taking up some memory on this box (2,358,812 K - (System Memory) - 360MB reserve) equals my current 1,720,000 K for sqlserver.exe. Does that sound right?
One other question, I have seen it written several times now that when the two counters I've listed above are the same value, memory pressure may exist. I assume this does not remain true when the values are dictated by a SQL memory limit, right?
April 27, 2006 at 8:43 am
Well, I would say that it still holds true. If your Total and Target memory are the same, SQL Server would benefit from additional memory. If you are not experiencing performance problems, you may be OK, but if this is a production system, you may consider upgrading to Enterprise Edition and adding the /3GB switch (and maybe more memory and AWE).
April 27, 2006 at 9:27 am
I'd hold off on the memory upgrade for a bit. Just because Total and Targte Memory are the same. I'd utilize the Windows Performance Monitor a bit and start checking CPU usage, Pagefile usage, context switches, Cache Hit Ratio, Buffer Hits/Misses etc before adding more RAM. Check out SQLServerPerformance.com for the Performance Monitor counters and explanations. Now to the difference between v7 and 2000 in memory, well, it's more acedemic than anything else at this point (for me at least).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply