What do I monitor to prove the server needs more memory

  • 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.

  • 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).

  • But yes... 1GB of total server memory is really low.

  • 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.

  • 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.

  • Thanks. That worked. This server doesn't appear to be in that bad of shape.

  • 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

  • This is an older server with only 1 gig of memory. I am going to ask for another gig.

  • 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.

     

     

     

  • Hum, Interesting. The next time this process runs I will monitor this counter. Thanks !

  • 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 ->

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7C407047-3F1F-48B8-9E4C-DC32875E1961&displaylang=en

    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! **

  • 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

  • No I have not heard of that one. I will have a look at it. Thanks !

  • 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