Memory Paged out every 30-40 mins with Memory Utilization around 50% (and 13GB available RAM)

  • This is where I could use some Advanced expertise

    Problem

    Every 30~40 minutes, the SQL server will generate an error (in the error log) for memory paged out

    It has happened for couple weeks, on this Production server

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

    Notice below at 12:34 it resets to 0%, and 12:54 1% again (that's only 20 minutes duration)

    and Memory Utilization % never goes beyond 51% ever.

    Plus, there is still 13GB+ available Physical Memory to use

    Notification_timeNotification_typeMemoryUtilization %TotalPhysicalMemory_MBAvailablePhysicalMemory_MB

    2008-11-25 12:54:40.363RESOURCE_MEM_STEADY32457013221

    2008-11-25 12:54:35.067RESOURCE_MEMPHYSICAL_LOW12457013191

    2008-11-25 12:42:53.553RESOURCE_MEM_STEADY482457012854

    2008-11-25 12:42:48.537RESOURCE_MEMPHYSICAL_LOW472457012868

    2008-11-25 12:41:47.977RESOURCE_MEM_STEADY462457012902

    2008-11-25 12:41:42.960RESOURCE_MEMPHYSICAL_LOW462457012926

    2008-11-25 12:40:42.397RESOURCE_MEM_STEADY412457013490

    2008-11-25 12:40:42.397RESOURCE_MEMPHYSICAL_HIGH412457013490

    2008-11-25 12:40:37.367RESOURCE_MEMPHYSICAL_LOW412457013638

    2008-11-25 12:39:36.803RESOURCE_MEM_STEADY342457014567

    2008-11-25 12:39:31.790RESOURCE_MEMPHYSICAL_LOW342457014624

    2008-11-25 12:38:31.227RESOURCE_MEM_STEADY322457014552

    2008-11-25 12:38:31.227RESOURCE_MEMPHYSICAL_HIGH322457014552

    2008-11-25 12:38:26.147RESOURCE_MEMPHYSICAL_LOW312457014502

    2008-11-25 12:37:25.587RESOURCE_MEM_STEADY282457014229

    2008-11-25 12:37:20.163RESOURCE_MEMPHYSICAL_LOW282457014146

    2008-11-25 12:36:19.603RESOURCE_MEM_STEADY252457013296

    2008-11-25 12:36:19.603RESOURCE_MEMPHYSICAL_HIGH252457013296

    2008-11-25 12:36:14.570RESOURCE_MEMPHYSICAL_LOW252457013234

    2008-11-25 12:35:14.010RESOURCE_MEM_STEADY152457013865

    2008-11-25 12:35:08.913RESOURCE_MEMPHYSICAL_LOW152457013822

    2008-11-25 12:34:08.353RESOURCE_MEM_STEADY122457013248

    2008-11-25 12:34:03.337RESOURCE_MEMPHYSICAL_LOW02457013218

    Error Logs

    25/11/2008 13:00:08 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 333 seconds. Working set (KB): 5653268, committed (KB): 20581208, memory utilization: 27%.

    25/11/2008 12:54:35 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 248476, committed (KB): 20588888, memory utilization: 1%.

    25/11/2008 12:39:31 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 6949908, committed (KB): 20382232, memory utilization: 34%.

    25/11/2008 12:34:03 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 98844, committed (KB): 20384152, memory utilization: 0%.

    25/11/2008 12:07:53 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 327 seconds. Working set (KB): 7795496, committed (KB): 20382232, memory utilization: 38%.

    Server

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02

    Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    Windows Server 2003 Enterprise x64 SP2

    Intel Xeon E5310 @ 1.60 GHz

    Processors 8

    24GB Physical RAM

    Memory 24571 (MB)

    Min memory 19456 (MB)

    Max memory 19456 (MB)

    Advanced settings is set to "Background Service"

    Total paging file size for all drives: 20480MB

    Disk drive spaces are plenty for DATA, LOG, TempDB drives

    Tried

    new Broadcome NIC drivers

    Google

    SSC

    From Microsoft - How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005

    SELECT

    CONVERT (varchar(30), GETDATE(), 121) as runtime,

    --DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks) - a.[Record Time]), GETDATE()) AS Notification_time,-- 2008

    DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - a.[Record Time]), GETDATE()) AS Notification_time, -- 2005

    a.* , sys.ms_ticks AS [Current Time]

    FROM

    (SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],

    x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],

    x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')/1024 AS [TotalPhysicalMemory_MB],

    x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024 AS [AvailablePhysicalMemory_MB],

    x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint')/1024 AS [TotalPageFile_MB],

    x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint')/1024 AS [AvailablePageFile_MB],

    x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint')/1024 AS [TotalVirtualAddressSpace_MB],

    x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint')/1024 AS [AvailableVirtualAddressSpace_MB],

    x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],

    x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint')/1024 AS [SQL_ReservedMemory_MB],

    x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint')/1024 AS [SQL_CommittedMemory_MB],

    x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],

    x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],

    x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators],

    x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]

    FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers

    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a

    CROSS JOIN sys.dm_os_sys_info sys

    ORDER BY 2 DESC-- a.[Record Time] ASC

    Please see the Query results in the Excel attachment

    Any help and insights are welcome!

    I can't believe it's happening on a powerful server

    I am reading the "Troubleshooting Performance Problems in SQL Server 2005" SQL Server Technical Article to find something... anything

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Check the Total Working Sets counter in perfmon. See if the total working set of all processes decreases or if it remains steady when this happens.

    What else is running on that server? You should not have min and max memory set the same. By doing that, SQL cannot release memory if the OS requests that memory be released. If that happens, the OS will have no choice but to page SQL's memory out to swap since the SQL engine is forbidden from releasing memory itself.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail

    I think Total Working Sets dropped when it happened (_Total and sqlservr)

    it was 7000000+, and now 1.3e+010 (sql: 1.2818e+010)

    peaks were 2.2102e+010 and 2.0922e+010 respectively

    I'll keep an eye on it and track it in SQLH2 to confirm it

    but what would each option mean? [DECREASE vs STEADY]?

    As for MIN/MAX memory, I wanted to change that in the first place

    but being Production server I am hesitant, although MIN shouldn't affect it except in this paged out situation, correct?

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • You can change min and max whilst your instance is online.

    You'll see its actual impact after issuing a system checkpoint.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Let's see how it goes with MIN 5000 and MAX 17000

    Initially it got even more frequent (with just MIN set). Then I decreased the MAX and it hasn't happened since for 10 mins

    However sqlservr.exe memory is increasing steadily from 5GB to 7.5GB now, with not much activity (sp_who2 'active' returns 1~3 RUNNABLE only)

    Process Explorer is showing ~10% CPU and 10GB Private Bytes for sqlservr.exe

    25/11/2008 15:59:59 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 4546508, committed (KB): 8973064, memory utilization: 50%.

    25/11/2008 15:54:31 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 5650908, committed (KB): 13963096, memory utilization: 40%.

    25/11/2008 15:53:25 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 8430000, committed (KB): 17292376, memory utilization: 48%.

    25/11/2008 15:50:03 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 8731856, committed (KB): 20386136, memory utilization: 42%.

    25/11/2008 15:44:35 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 231864, committed (KB): 20417944, memory utilization: 1%.

    25/11/2008 15:31:28 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 10109416, committed (KB): 20386200, memory utilization: 49%.

    25/11/2008 15:26:00 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 45444, committed (KB): 20408024, memory utilization: 0%.

    25/11/2008 15:01:00 A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 331 seconds. Working set (KB): 6790740, committed (KB): 20385880, memory utilization: 33%.

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Jerry Hung (11/25/2008)


    Thanks Gail

    I think Total Working Sets dropped when it happened (_Total and sqlservr)

    it was 7000000+, and now 1.3e+010 (sql: 1.2818e+010)

    That's not a decrease. 1.3e+010 is scientific notation for 13,000,000,000

    I'll keep an eye on it and track it in SQLH2 to confirm it

    but what would each option mean? [DECREASE vs STEADY]?

    Steady means some other process is using working set and resulted in memory pressure. A sharp decease is usually indicative of a driver problem.

    As for MIN/MAX memory, I wanted to change that in the first place

    but being Production server I am hesitant, although MIN shouldn't affect it except in this paged out situation, correct?

    Change it. It is not recommended that the min and the max be the same. By doing so you have effectively disabled some of SQL's memory management options.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/26/2008)


    Jerry Hung (11/25/2008)


    Thanks Gail

    I think Total Working Sets dropped when it happened (_Total and sqlservr)

    it was 7000000+, and now 1.3e+010 (sql: 1.2818e+010)

    That's not a decrease. 1.3e+010 is scientific notation for 13,000,000,000

    Sorry, I meant sqlservr working set DROPPED significantly when the paged out happens (no surprise), say to 7000000

    and then slowly climbing up (say to 1.3e+010), until the next problem

    Sigh, I thought this could work, then more bad news this morning

    Although it's happening "less" frequently

    So I changed the MIN/MAX yesterday 4 pm, MemoryUtilization went to 92% woo-hoo!

    Then didn't happen until 6:50 pm, and continues to happen until now

    Also saw a big Buffer Cache Hit Ratio drop around 9 am (dropped from 99.xx to 28.xx)

    Maybe we do need more memory?

    Notification_timeNotification_typeMemoryUtilization %AvailablePhysicalMemory_MB

    2008-11-26 09:10:56.713RESOURCE_MEMPHYSICAL_HIGH5217984

    2008-11-26 09:10:52.713RESOURCE_MEM_STEADY5217360

    2008-11-26 09:10:50.853RESOURCE_MEMPHYSICAL_LOW4416927

    2008-11-26 09:09:50.290RESOURCE_MEM_STEADY718433

    2008-11-26 09:09:45.230RESOURCE_MEMPHYSICAL_LOW216477

    2008-11-26 06:30:01.253RESOURCE_MEM_STEADY8915013

    2008-11-26 06:30:00.893RESOURCE_MEMPHYSICAL_LOW8915002

    2008-11-26 06:09:40.810RESOURCE_MEM_STEADY2421211

    2008-11-26 06:09:35.603RESOURCE_MEMPHYSICAL_LOW2421077

    2008-11-26 06:08:35.043RESOURCE_MEM_STEADY2021052

    2008-11-26 06:08:29.153RESOURCE_MEMPHYSICAL_LOW1719936

    2008-11-26 06:07:28.590RESOURCE_MEM_STEADY718878

    2008-11-26 06:07:23.593RESOURCE_MEMPHYSICAL_LOW016961

    2008-11-26 04:50:09.730RESOURCE_MEM_STEADY2217372

    2008-11-26 04:50:04.183RESOURCE_MEMPHYSICAL_LOW516132

    2008-11-26 04:00:01.300RESOURCE_MEM_STEADY9215069

    2008-11-26 04:00:01.300RESOURCE_MEMPHYSICAL_HIGH9215069

    2008-11-26 04:00:00.503RESOURCE_MEMPHYSICAL_LOW9215052

    2008-11-26 03:42:46.303RESOURCE_MEMPHYSICAL_HIGH5119690

    2008-11-26 03:42:45.313RESOURCE_MEM_STEADY5119696

    2008-11-26 03:42:40.430RESOURCE_MEMPHYSICAL_LOW4818966

    2008-11-26 03:41:39.867RESOURCE_MEM_STEADY4518002

    2008-11-26 03:41:34.537RESOURCE_MEMPHYSICAL_LOW3817432

    2008-11-26 03:40:33.990RESOURCE_MEM_STEADY2117389

    2008-11-26 03:40:28.900RESOURCE_MEMPHYSICAL_LOW416890

    2008-11-26 02:19:13.633RESOURCE_MEM_STEADY4120224

    2008-11-26 02:19:08.350RESOURCE_MEMPHYSICAL_LOW4519796

    2008-11-26 02:18:07.787RESOURCE_MEM_STEADY4020267

    2008-11-26 02:18:02.773RESOURCE_MEMPHYSICAL_LOW4619622

    2008-11-26 02:17:02.210RESOURCE_MEM_STEADY4020308

    2008-11-26 02:16:57.193RESOURCE_MEMPHYSICAL_LOW4419905

    2008-11-26 02:15:56.633RESOURCE_MEM_STEADY3920348

    2008-11-26 02:15:51.553RESOURCE_MEMPHYSICAL_LOW3719907

    2008-11-26 02:14:50.993RESOURCE_MEM_STEADY821223

    2008-11-26 02:14:45.977RESOURCE_MEMPHYSICAL_LOW1419435

    2008-11-26 02:13:45.413RESOURCE_MEM_STEADY618424

    2008-11-26 02:13:39.803RESOURCE_MEMPHYSICAL_LOW116281

    2008-11-25 22:00:03.523RESOURCE_MEMPHYSICAL_HIGH7017802

    2008-11-25 21:59:58.523RESOURCE_MEM_STEADY6218369

    2008-11-25 21:59:58.257RESOURCE_MEMPHYSICAL_LOW6218278

    2008-11-25 21:54:35.150RESOURCE_MEM_STEADY2920815

    2008-11-25 21:54:30.117RESOURCE_MEMPHYSICAL_LOW3220577

    2008-11-25 21:53:29.553RESOURCE_MEM_STEADY2820434

    2008-11-25 21:53:24.540RESOURCE_MEMPHYSICAL_LOW2519538

    2008-11-25 21:52:23.977RESOURCE_MEM_STEADY1818616

    2008-11-25 21:52:18.930RESOURCE_MEMPHYSICAL_LOW1717104

    2008-11-25 21:51:18.370RESOURCE_MEM_STEADY1016659

    2008-11-25 21:51:13.010RESOURCE_MEMPHYSICAL_LOW115178

    2008-11-25 19:22:50.347RESOURCE_MEM_STEADY4813499

    2008-11-25 19:22:45.333RESOURCE_MEMPHYSICAL_LOW4911664

    2008-11-25 19:21:44.770RESOURCE_MEM_STEADY710708

    2008-11-25 19:21:39.370RESOURCE_MEMPHYSICAL_LOW08528

    2008-11-25 19:16:55.880RESOURCE_MEM_STEADY4811729

    2008-11-25 19:16:50.863RESOURCE_MEMPHYSICAL_LOW449250

    2008-11-25 19:15:50.303RESOURCE_MEM_STEADY359860

    2008-11-25 19:15:45.177RESOURCE_MEMPHYSICAL_LOW308194

    2008-11-25 19:14:44.613RESOURCE_MEM_STEADY510870

    2008-11-25 19:14:39.430RESOURCE_MEMPHYSICAL_LOW18476

    2008-11-25 18:52:01.823RESOURCE_MEM_STEADY4918998

    2008-11-25 18:51:56.790RESOURCE_MEMPHYSICAL_LOW5018615

    2008-11-25 18:50:56.230RESOURCE_MEM_STEADY4319240

    2008-11-25 18:50:51.150RESOURCE_MEMPHYSICAL_LOW4418066

    2008-11-25 18:49:50.590RESOURCE_MEM_STEADY1319490

    2008-11-25 18:49:45.167RESOURCE_MEMPHYSICAL_LOW217717

    2008-11-25 18:25:01.397RESOURCE_MEMPHYSICAL_HIGH9212214

    2008-11-25 18:25:01.393RESOURCE_MEM_STEADY9212214

    2008-11-25 18:25:00.893RESOURCE_MEMPHYSICAL_LOW9212211

    2008-11-25 16:00:00.893RESOURCE_MEM_STEADY5117974

    2008-11-25 16:00:00.893RESOURCE_MEMPHYSICAL_HIGH5117974

    Working Set of sqlserv (_Total shows the same pattern)

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Hi,

    Are you saying that you have around 10 GB Available MB in the OS at the time when the SQL Server working set gets trimmed? That's very odd.

    Make sure you are not running into any of the known problems; Bob Dorr has written a blog post which dissects this error and also lists the fixes you want to make sure you have.

    http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx.

    Make sure you don't have the "Lock Pages in Memory" user right set for the SQL Server startup account.

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • Hi,

    Are you saying that you have around 10 GB Available MB in the OS at the time when the SQL Server working set gets trimmed? That's very odd.

    Make sure you are not running into any of the known problems; Bob Dorr has written a blog post which dissects this error and also lists the fixes you want to make sure you have.

    http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx.

    Make sure you don't have the "Lock Pages in Memory" user right set for the SQL Server startup account.

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • I'm not asking about SQL's working set. The message says that's getting reduced. I asked about the total working set of all processes on the server. If that's showing a steep drop, then you have a driver issue.

    There's a bug in windows where some driver memory requests can lead to a sharp trim in all process' working sets. I'll try and find a reference for you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Elisabeth Rédei (11/26/2008)


    Make sure you don't have the "Lock Pages in Memory" user right set for the SQL Server startup account.

    Standard edition ignores that setting.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I know about the Broadcom Gigabit NIC driver, and it's updated as well (I'll re-check it)

    We have

    2 Broadcom BCM5708C NetXtreme II GigE NIC

    2 Intel PRO/1000 PT Dual Port

    and this "Primary Team" NIC.... which I think it's teaming for 2 NIC's but I don't know if SQL is using that

    And being a Standard Edition, I don't think "Lock Pages In Memory" comes into effect here

    I'll read that PSS article....

    Pictures are worth thousand words

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Yeah, that looks like the 'falling off a cliff' behaviour that Bob Ward described. I'll hunt for an article and drop him a mail if I can't find it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Just heard back and that does look like a driver issue. The Broadcom is known to give issues, no ideas about the others. MS won't write articles about other people's drivers (for good reason). Maybe search around see if you can find any other people with the same issue with those drivers.

    Unfortunately this is not a SQL problem, it's a windows issue, and nothing can be done on the SQL side. I hear that this is fixed in Windows Server 2008, so if upgrading is an option, you may suggest that to the bosses.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the help Gail

    Yeah, "fortunately" we are moving away from this server (it's being migrated)

    Otherwise I don't know how easy it is to convince others to go on a "bug hunt" on a Production server

    2 Broadcom NICs are teamed together, with driver from Feb 2008 (new enough I'd think)

    Windows Update does want to install 39 High-priority updates..... wonder if these will magically fix the problem 😛

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

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

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