February 8, 2007 at 11:19 am
We have a SQL 2000 SP3 running on Win2000 SP4. I suspect that the server needs more memory but how do I prove it ? I am familiar with Profiler but I don't know how to tell if SQL Server is starved for memory and that adding memory will help. In looking at SQL statements they all appear to be running fine and are all using good indexing. This server runs SQL Server and another application and I think they are fighting for the 1 gig of memory on this server. Any help would be appreciated.
February 8, 2007 at 11:41 am
Use performance monitor and watch the Memory -> Pages/Sec counter. This should be right around 0 most of the time. Also SQLServer:Memory -> Buffer/Cache hit ratio is good too (this should be high, near 100).
February 8, 2007 at 11:41 am
But yes... 1GB of total server memory is really low.
February 8, 2007 at 11:50 am
OK, great I will fire it up. So, if we are paging as far as memory I should see the page count be much higher than zero I assume and the hit ratio will be lower than 100.
February 8, 2007 at 11:56 am
Exactly... When the buffer/cache hit ratio falls it means that the system was able to find the data it needed in the buffer and not have to go to disk. The buffer manager will load the data it needs into a buffer and hold it. If the system is starved for memory, that buffer gets cleared/overwritten very quickly and when the data is needed again, the system has to scan the disks for it and the ratio falls. It will never be 100 but it should be close.
February 8, 2007 at 1:06 pm
Thanks. That worked. This server doesn't appear to be in that bad of shape.
February 9, 2007 at 10:46 am
What is the total MEMORY on the Server & say if it is 4 GB then use switch /3GB /userva = 3000 in boot.ini file.
i hope this info may help
February 9, 2007 at 11:05 am
This is an older server with only 1 gig of memory. I am going to ask for another gig.
February 12, 2007 at 1:18 pm
take a look at the pagelifeexpectancy counter, it should not be less than 300 , it it is very low
you cache is clearning frequently because of less memory.
February 12, 2007 at 1:33 pm
Hum, Interesting. The next time this process runs I will monitor this counter. Thanks !
February 13, 2007 at 8:15 am
Just remember if you add more than 4 GB memory you need to get the patch as there is a bug in the SP4 which halves the available memory. - The patch details can be found here ->
If you are not using that much memory you can ignore it...
EDIT: this should be posted in the SQL2000 forums not the SQL2005 -
** What you see, Depends on what you Thought, Before, You looked! **
February 21, 2007 at 1:03 pm
Ever use the sp_monitor procedure?
It shows CPU, and IO bottlenecks. While not true memory, keepign a running log of these values would show if your issues are with CPU or IO.
Take a sample every minute and put to a small table.
Somthing different to look at.
EP
February 21, 2007 at 1:08 pm
No I have not heard of that one. I will have a look at it. Thanks !
April 14, 2007 at 8:28 pm
April 16, 2007 at 7:31 am
I use the Page Life Expectancy counter for this determination. 300 appears to be the accepted threshold for needing more RAM. Looking at the Cache hit ratio is sketchy, because all you get is a number that is ideally 100, but never is 100. So how close is close enough to 100%?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply