SQL Server Performance

SQL Server 2005 Memory problem

Discussion in 'SQL Server 2005 General DBA Questions' started by entela, Mar 27, 2007.

  1. entela New Member


    I got a problem with the page file usage from SQL Server is getting bigger and bigger.

    We're using Intel(R) Xeon(TM) CPU 3.4GHz and 8GB of RAM, Windows 2003 64bit and SQL Server 2005 64 bit.

    We are running this server since january 2007 and suddenly we saw the page file usage was getting bigger, the SQL Server stop running and the datbase was down.

    We found in the internet some additional configurations like:

    1. Adding additional physical memory/tuning statements. The symptoms of this error message occurs when on 64-bit standard edition the buffer pool of SQL server grows to be larger than physical memory. The server has 8GB, we cannot extend the memory any more.

    2. Set Max Server Memory. Setting max server memory to a value less than physical memory prevents the buffer pool from growing beyond the amount of physical memory and therefore prevents this error from occurring. We generally recommend leaving some physical memory for the OS since the OS handles all of SQL Server's IO requests, etc.
    We put the Max Server Memory = 6144 MB

    3. Upgrade to Enterprise Edition of SQL Server 2005 and set "Lock Pages in memory Option." Enterprise Edition of SQL Server can take advantage of the system policy "lock pages in memory" which also alleviates this error. Unfortunately, SQL Server 2005 standard edition is not coded to take advantage of this setting and is a limitation of the software. This is why making this change on your system has had no effect since the server is running SQL Server 2005 standard edition.

    I'm not sure if this is the right think to do, because after doing all this the page file usage is still 6.68 GB.

    Before doing these configurations I was using dbcc freesystemcache('All') every 5 min and the max page file uasge was 4 GB.
    Should I change back the configurations I've done?

    Can anybody please help me with that.

    Thanks a lot

  2. bradmcgehee New Member

    Can you please be a little more specific on what you mean by page file usage? How are you measuring it? What was it before the problem started, and what is it now after the problem? What other things have changed in the environment (number of connections, number of transactions, etc.)?

    Brad M. McGehee, SQL Server MVP
  3. thomas New Member

    Have you set a Min Server Memory? It is my recommendation that you do so if you have not already. I have had issues with SQL Server 2005 64-bit not taking as much RAM as it should.
  4. entela New Member


    Thanks for the e-mail.

    I saw the page file usage on task manager at performance tab. Before the error occured this parameter was less than 3.5 GB, sometimes went down and sometime up but the limits were 2.88 min and 3.5 max.
    Last friday for a moment the SQL Server went down and the error was
    "SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required."
    at that time we saw the PF was getting bigger for few minutes.

    I have set the minimum 2048MB.

    Thanks a lot
  5. MohammedU New Member

  6. entela New Member


    Actually I have enabled the Locked pages in memory, giving this right to the administrator (Should it be any specific user account) but after this configuration the PF Usage is getting bigger, around 6.8 right now.
    Before enabling the Locked Pages in Memory, when I was using DBCC FreeSystemCache the PF Usage was at max 3.5.

    I don;t know what is wrong?

    Thanks a lot
  7. MohammedU New Member

    What was the Total server memory and Target server memory counter values at that time?

    If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).

    Troubleshooting Performance Problems in SQL Server 2005

  8. entela New Member


    I didn't mention the counter at that time, because the SQL Server went down, and I run to change the node, The system should be running 24/7.

    The server have 8GB RAM,
    MIN Server Memory = 2048MB
    MAX Server Memory = 6144MB

    pagefile.sys at that time was around 4GB and actually is around 2GB.

    The error I got is this one:

    Application popup: Windows - Virtual Memory Minimum Too Low : Your system is low on virtual memory. Windows is increasing the size of your virtual memory paging file. During this process, memory requests for some applications may be denied. For more information, see Help.

    What do you recommend in this situation?

  9. MohammedU New Member

    Is your server is dedicated sql server ? or is there any application also running?

    Increase PF file size, I don't think it is the right size based on the memory you have.

    Run perfmon(sysmon) to capture memory usage and pf usage etc...

  10. entela New Member


    Yes there is an application running.

    I saw the performance and I got the following:
    Average 0.00min 0.00max 0.00

    Processor Time
    Average 15.551min 7.03max 33.89

    Avg.Disk Queue Length
    Average 1.38min 0.023max 66.245

  11. joechang New Member

    something is not right
    there is no paging file info on the Task Manager Performance tab

    there is commit charge, which includes both physical and page usage
    there is Kernel Paged
  12. joechang New Member

    never mind, the graphs say PS usage & history
    but is actually committed bytes usage, not page file

    note that performance shows no pages/sec

    run the VAS script in my other post in the DBA performance section

    anyways, the symptoms shown here point to VAS problems, not physical memory

  13. entela New Member


    Can you please send me again the VAS Scripts?

    Thanks a lot
  14. MohammedU New Member

  15. entela New Member


    I run the VAS Scripts and I got these values:

    available memory in all free regions
    3890148 KB

    get size of largest availble region
    31168 KB

    What do you think about those?

    Thanks a lot

  16. entela New Member


    We still are in trouble, the PF Usage is big 6.75 GB. this continues to remain the same for more than 4 days. the phisical memory is 8GB.

    Do you think somethink is going wrong?

  17. MohammedU New Member

    can you check the memory usage by the sql...
    Check through perfmon TOTAL SERVER MEMORY and TARGET SERVER MEMROY...

  18. ghemant Moderator

  19. satya Moderator

    PF usage is "page file" usage, which is a variable amount of hard drive space that is dedicated to your total system memory. When your free RAM is low, the computer will start using this space to store RAM instructions. Because hard drive access is much slower than that of RAM, this can reduce performance - but reduces the risk of your computer locking up due to low amounts of free RAM. The page file is also referred to as 'virtual memory' or the 'swap file'.

    The size of your page file can be changed by going to Start - Control Panel - System - Advanced tab - clicking 'Settings' under the 'Performance' section - Advanced tab - and clicking 'Change' under the 'Virtual Memory' section.

    However, because the page file is significantly slower than your RAM, it is best to keep it close to the start of your drive and make it a set size, not variable. Generally, a system total of 2.5GB should be good for recent applications. For example, if you have 1GB of physical RAM, setting your page file to 1024MB will give a total of 2GBs of available memory space.

    Increasing the size of the page file is not an alternative to adding more RAM.

    quote:Originally posted by entela


    We still are in trouble, the PF Usage is big 6.75 GB. this continues to remain the same for more than 4 days. the phisical memory is 8GB.

    Do you think somethink is going wrong?


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page