SQL Server Performance

Memory utilization???

Discussion in 'Performance Tuning for DBAs' started by crichardson, Dec 18, 2002.

  1. crichardson New Member

    I am doing some analysis on a Windows 2000/SQL Server 2000 server. The box has 4Gb RAM. Via Perfmon, I was surprised to see quite a lot of a Paging going on at the same time as it is showing me that there is about 1Gb of available memory. I would have thought with the amount of available RAM, there would not have been much page faulting?

    Clive
  2. Chappy New Member

    It could be that SQL server does not have full access to the 4GB RAM. This would depend on a few factors, such as edition of SQL Server, and version of Windows 2000 server. Also, check you or someone else hasnt restricted amount of memory sql is allowed to reserve for itself (server properties).

    Check your buffer cache hit ratio perf counter. If this is running high, then it tends to suggest sql is not suffering too much from the finite amount of memory it has.

    Task Manager may be useful in discovering which process is generating the majority of page faults. Bear in mind that Windows itself requires memory, and as such will generate paging.
    It would all depend on what you define as 'quite a lot'as to whether there is a problem.
  3. satya Moderator

    And also how about min and max memory settings on server?

    Satya SKJ
  4. crichardson New Member

    SQL Server is limited to 2Gb but that's ok since other apps have to live there too. It turns out to be other apps that cause the majority of the page faults. Windows 2000 Advanced Server should see all 4Gb I think. So, although this is getting away from SQL Server a little, I'm still puzzled why there should be a consistant 1Gb of memory available on that server given the page faulting that's going on.

    Also, in Task Manager, I spotted that the 'VM Size' for SQL Server is almost exactly the same as 'Mem usage' - approaching 2Gb. I don't see an unreasonable amount of page faulting for SQL Server but there is some. Is this an indicator that this instance of SQL Server could do with seeing more RAM than its current 2Gb limit?

    Clive
  5. satya Moderator

    As complied, pagefile setting should set to 3 times of RAM and in your case you need to calculate the same.

    I suggest to leave SQL server memory settings to dynamic.

    Satya SKJ
  6. crichardson New Member

    Satya,

    Perhaps I didn't make myself clear... The 'VM Size' as reported in Task Manager is the amount of virtual memory used *by each process*. I wasn't talking about the size of the page file - which happens to be 6Gb or 1.5 times the physical RAM. However, keeping this on track, the question related to the fact that SQL Server is apparently using nearly 2Gb of RAM and the same amount of VM. Is this a sign that SQL Server needs more physical memory?

    I checked again and, in fact, SQL Server is set to configure memory dynamically. This is even more puzzling because, if it is set to use memory dynamically, why doens't it grab more of that free 1Gb instead of using VM?

    Clive
  7. satya Moderator

    Clive, we had seen similar issues with our production system earlier and this was during peak timings then after we added one more GB to the memory and the problem resolved. I had gone thru this article in this websitehttp://www.sql-server-performance.com/awe_memory.asp.

    HTH

    Satya SKJ
  8. Chappy New Member

    I believe that if you have enabled AWE memory on Windows 2000, the operating system reserves a gig of physical memory for its own uses.<br /><br />What edition of sql server are you using, And have you specifically configured Windows to use AWE memory addressing? Perhaps the free gig could be not consumed by sql server because it is physically beyond its range of addressable memory (just a possible theory, not gospel <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />, its difficult to diagnose without being able to see what windows is reporting.<br /><br />What Id concentrate more on is the buffer cache hit ratio. If this is close to 100% then sql server it would not benefit that much from more physical memory. Rememeber, you will never eliminate paging altogether, it is part and parcel of a multitasking os. Im sure this page holds the answer to the unclaimed gig.<br /><br />I would recommend you read<a target="_blank" href=http://www.sql-server-performance.com/awe_memory.asp>http://www.sql-server-performance.com/awe_memory.asp</a> and check that your systems memory addressing is configured as you think it is.
  9. crichardson New Member

    No AWE enabled.

    Thanks for giving me the leads - I checked out the version of SQL Server and, to my surprise, it is 2000 Standard edition. I then checked the implications of this and there is a 2Gb limited on Standard (as per that link you quoted -http://www.sql-server-performance.com/awe_memory.asp). So, if my deduction is correct, SQL Server is utilizing quite a bit of VM despite the fact that there is a free Gig of RAM because Standard edition won't let it take any more than 2Gb.

    I checked out a few other SQL Server systems and noted that the available memory can go right down to almost nothing if SQL Server is able to take it.

    Clive
  10. bradmcgehee New Member

    One other comment, is that SQL Server 2000 will not use much paging. Yes, you will see some, but very little. If you ever see SQL Serve 2000 responsible for excessive paging, then memory settings are incorrect, as already has been discussed. What is your buffer cache it ratio? It is is above 99%, then memory will not be a problem for the current load you are experiencing.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  11. crichardson New Member

    I can't get the SQL Counters included in perfmon - due to a conflict with BMC. We have some patches for BMC and hope to get this fixed soon. I believe there is a way to access the Buffer Cache Hit Ratio via a stored procedure but I can't quite find that info yet. Until then, I can't say what this metric is but my bet is that it will be low. The point is that if Task Manager is showing a 50/50 split between 'mem usage' and 'VM size' for the SQL Server process, isn't this telling us that, at some stage, about 50% of SQL Server's working set has been swapped out to disk? Is my logic correct?

    In this example, Paging is noticeable but not excessive for SQL Server - I suspect a good job is being done of keeping the less accessed parts of the system in Virtual Memory. As I get a consistent figure of 1Gb 'Available Memory' in Perfmon, isn't this telling us that SQL Server isn't getting access to that available memory despite the fact that it's paged 50% of itself to virtual memory?

    Clive
  12. sqljunkie New Member

    I also look at the Page Life Expectancy and Target Server Memory Perfmon counters when determining if SQL needs more memory.
    Did you set Min and Max Server Memory in the cases you see paging?
  13. bradmcgehee New Member

    To the best of my knowledge, your logic for virtual memory is correct. One command you can use to see memory usage is: DBCC MEMORYSTATUS. It won't provide you the ratio, but it will provide some useful information. I don't know if there is any other way, other than the Performance Monitor, to find the ratio. Does anyone know of any special tricks to find the buffer hit cache ratio without using Performance Monitor?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page