A significant part of sql server process memory has been paged out. Every 3-5 min

  • Dear,

    We got the following error in every 3-5 mins or sometimes more often:

    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1842 seconds. Working set (KB): 27832, committed (KB): 296576, memory utilization: 9%.

    Five mins later

    Working set (KB): 21356, committed (KB): 304924, memory utilization: 7%%.

    So the sql server memory is reduced to 20Mb :F

    Configuration:

    Windows Server 2008 Enterprise Service Pack 2 (32bit)

    Intel Xeon X3330 @ 2.66Ghz - 8,00Gb RAM

    SQL Server 2008 Standard Edition

    Nothing special running on the server sqlserver uses 200-360Mb before paged out, there are plenty of free memory in the system so there would be no need to page at all. According to the resource monitor Used physical memory is 35-40%

    Do you have any idea what can cause this problem?

    Best regards,

    gimp

  • Do you have a limit on the SQL Server memory setting? I'd look there first.

    Second, what kind of queries are being run?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • There are no limit on sql server memory usage, it's running with default settings:

    Minimum was 0Mb increased it to 512Mb without any success

    Maximum is: 2147483647Mb

  • Are there other softwares or utilities running on the machine?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Have you looked ath this documentation from Microsoft? I'd start there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I did, but we are using 32bit OS not 64bit, I read almost everything I found regarding this issue, but nothing helped, there are some other applications but there are plenty of free memory on the system so there is no sense to page out...

  • Ah, so, there are other apps running on the machine? That's more than likely the issure, right there. SQL Server does not like to play well with others. In almost all instances you have to put it on a server by itself.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes, there are we can not afford to use a server dedicated for SQL only but as I mentioned there are more than 4G free memory and the sql server is using 150-200Mb which drops down to 20Mb! when memory paged... it's not realistic that because of an IIS and some smaller app which basically does nothing SQL would use 20MB...

  • But if some other app pages into memory, it's going to impact SQL Server, and hence the problem you're hitting. I'm not surprised. You're basically in a tough spot. You should put an upper limit on what SQL Server can allocate, and I'd make sure it's significantly below what the other apps are likely to use with some buffer for the OS itself. After that, you're really dependent on the OS.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • So there is no solution for this kind of problem, the system useage is minimal, page file is hardly used but the OS keep paging out memory... Aren't there any way to prevent sql memory to be paged, it could use 1-2G on it's will without any problem 😀

  • I'd say it's rather unlikely, but the 'other' app(s) may be experiencing a memory leak. If you use Task Manager and see how much memory it/they are using, that should help rule in or rule out a problem with a memory leak. It should certainly give you a clearer picture of where the memory is being allocated. Another test for memory leak is to reboot. If after the memory is quickly constrained again, its probably not a memory leak in an app.

    Also, look at where you swap/page file is placed. If you can afford to position it onto its own spindle, that can help tremendously in relieving memory constraints. Putting an old 40 or 80 gig drive in for swap/page activity can pay off big and such old drives are cheap and still available.

    /Scott

  • Try "Lock Pages in Memory" 🙂

    HTH

    RK

  • As far as I know that is not available in Standard edition

  • Not sure of SQL 2008 base version but for For SQL Server 2008 R2 Standard Edition to use locked pages in buffer pool, you need to enable Trace Flag 845 as a startup parameter for the SQL Server instance.

    http://msdn.microsoft.com/en-us/library/ms190730.aspx

    HTH

    RK

  • I dont think Lock pages in memory is the answer, especially because of this.

    http://msdn.microsoft.com/en-us/library/ms190730.aspx

    Lock pages in memory is available for standard edition as of SQL Server 2005 SP3 CU4 and SQL Server 2008 SP1 CU2.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply