The SQL Server Working Set Message

  • [アーティクル]

Work continues around the working set issues. You can find the latest and greatest Information: https://support.microsoft.com/kb/918483

 ===========================================================
I spent several days last week investigating reported case trends and I will attempt to summarize the findings for you.

The following message was added to SQL Server 2005 SP2 to indicate that the working set (RAM resident portion of SQL Server) was paged out. This is a common indication of performance problems due to the paging.

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): 24484, committed (KB): 48036, memory utilization: 50%

There are several ways trimming can occur.

1. Signaled Trim – The low physical memory indicator event is set by the operating system (ref: QueryMemoryResourceNotification) when physical memory becomes low. The SQL Server Resource Monitor, Lazy Writer and other processing decommit portions of the BPool memory. This returns physical memory to the system and in turn lowers the Working Set of the SQL Server. When the event is signaled the sys.dm_os_ring_buffer, RING_BUFFER_RESOURCE_MONITOR records are logged. This is the normal working behavior of the SQL Server as it attempts to remain RAM resident. This activity does not trigger the error log message.

2. Self Trim – The operating system is handling a page fault for a newly allocated page and determines physical memory is low so the current working set should be trimmed (exchange a new page in RAM and move an older page to the page file). For example, if SQL Server is allocating a page for a stack, linked server, or other allocation and physical memory is in a low state the operating system may directly trim the working set of the SQL Server. On current operating system builds (Windows 2003 SP2, Windows XP SP2, …) the amount to trim can be ~1/4 the current working set size. On a 64GB system the SQL Server working set could page out 16GB in less than a second. Such a large trim does not occur on Windows Vista and we are looking at fixes for Windows 2003 and XP to avoid such dramatic trimming behavior.

3. Hard Trim – When the system gets into very low memory conditions a hard trim can occur. This activity can page out the entire working set for SQL Server and other processes on the box.

The signaled trim allows SQL Server to properly remove the oldest references from the BPool (LRU) and maintain optimal performance for the overall server load. The Self Trim and Hard Trim activities can trigger the SQL Server error log message indicating that a significant portion of the SQL Server memory has been placed in the page file and generally leads to performance problems.

There have been various blogs written about backing off the SQL Server max server memory setting or using locked pages. In testing I found that it was better to understand the root cause of the trimming issue than simply setting the options. For example, locked pages are only maintained for the BPool allocations. Thread stacks, linked server, XPROC and other non-BPool activity is still subject to trimming. A Hard Trim can page out stacks and other critical structures. The SQL Server working set trim will only be a few 100MB but all the critical performance structures have to be paged. At the same time the Hard Trim usually trims the working set of all other processes on the system so overall performance is affected.

Basic Troublshooting

I know of several bugs that can cause the behavior so here is the best set of troubleshooting steps at this time.

1. Make sure no other process are quickly eating up the memory. A key to this is watching the Memory:_Total Working Set Performance Counter. If you start an application that quickly uses memory all working sets can be trimmed quickly but the overall _Total remains steady. If the _Total drops significantly it is a good indication that it is not a single application quickly eating up memory but something like MiEmptyWorkingSet or MmAllocateContigiousMemory has taken place and the operating system has decided to engage in a hard trim. Our platforms support team can assist you in determining why the hard trim is being triggered.

2. Make sure the SQL Server process ID remains constant across the problem time. I found that if you stop the SQL Server service and restart it some of the SQL counters remain constant and can be misleading.

3. Make sure your system has the latest drivers. We have specifically encountered several drivers on X64 that trigger hard trim behavior.

4. Make sure the OS Terminal Service bug is not causing the problem. https://support.microsoft.com/default.aspx?scid=kb;EN-US;905865 When an admin TS’s into the server console, and later logs off, winlogon process might initiate trimming the processes running in Session 0. The post Windows 2003 Sp1 hot fix resolves this issue.

On the server you can match this to a winstations, SECURITY, event log entry for the System – Event ID 682 – Example of one shown below.

Session reconnected to winstation:

  User Name: USERNAME

  Domain: MYDOMAIN

  Logon ID: (0x0,0x12A8229)

  Session Name: RDP-Tcp#3

  Client Name: MYCLIENT

  Client Address: 615.513.165.351

5. Capture a full set of performance counters as well as the sys.dm_os_ring_buffers from SQL Server. For example when an application on the computer starts using memory quickly the ring buffer entries clearly show the low physical memory indicator. When a self or hard trim occurs the low physical memory indicator is not set or only set for a short period and when compared to the performance counters the behavior pattern is clarified. The self and hard trim can require Microsoft Platforms support to help determine root cause.

6. Apply the OS fix. https://support.microsoft.com/default.aspx?scid=kb;EN-US;920739 The dirty page threshold is set at half the amount of physical memory. This has caused severe problems on systems with large amount of memory, when applications that use Buffered I/O, leading to trimming, and large amount of data being flushed to disk when the threshold value is hit. The registry key must be set as recommended by the article along with the hot fix.

7. Apply the OS fix. https://support.microsoft.com/default.aspx?scid=kb;EN-US;931308 You may experience increased paging to the hard disk when you run a program on a Windows Server 2003-based computer

8. Apply SQL Server 2005 SP2. SP2 has a few changes allowing it to react to the ‘Low Physical memory’ faster as well as increased sys.dm_os_ring_buffer information.

Bob Dorr – SQL Server Senior Escalation Engineer

Comments

  • Anonymous
    June 02, 2007
    Very helpful analysis and troubleshooting, thank you, sir. We are currently experiencing what I believe is a hard trim issue on our production database server (matching symptoms of your troubleshooting point #1 and experiencing application db timeouts as a result). We've got a case open with SQL Server support, but finding the cause of the hard trims is proving to be a bit difficult. I'll post here as things progress.  [RDORR] Glad to hear is was helpful.   We have been working very closely with the operating system CPR and Dev team so things should get better for you.

  • Anonymous
    June 18, 2007
    We applied the hotfix in KB #931308 and restarted the server; aside from an interesting reduction in SQL Server's working set size, we see no changes (e.g. still seeing hard trims). The cause of the hard trims may be more of an elusive beast than we thought. From MS Support: "as to what algorithm Mm (Memory Manager) follows before trimming the Memory, I have no clues on that and there is no way to tweak that even if we knew, so pretty much that portion is something which we don’t have any control over." So, we are going to attempt to reduce the "maximum server memory" setting of SQL Server, in hopes that maybe SQL Server's dynamic memory  mgmt isn't playing nicely with the OS. Fingers crossed!

  • Anonymous
    June 19, 2007
    We had a major issue on one of the SQL Server instance with the memory and getting following error message

  • Anonymous
    July 06, 2007
    Well, after changing the "maximum server memory" from the default setting and leaving about 2 gig for OS etc, the hard trims have stopped occurring! So the fix appears to have been a simple config change. I hope this helps other who might be experiencing similar issues.

  • Anonymous
    September 18, 2007
    If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently

  • Anonymous
    September 26, 2007
    We've been dealing with this exact same problem for months now. We have a support case open with Microsoft, but everything we've tried has lead to no improvement whatsoever. Every time something did look as though it had helped: after the reboot it would be fine for a few days, but then it would start happening again. The frustrating thing is we have another identical(!) server which has never ever had the issue. In the sql server log I can definitely see intriguing patterns, but I can't understand them. The most evident pattern is in the Duration field of the log entry: this is an excerpt of a few days: 2007-09-23 00:12:37.47 spid1s      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): 383800, committed (KB): 11801760, memory utilization: 3%. 2007-09-23 00:18:09.18 spid1s      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): 2651432, committed (KB): 11801760, memory utilization: 22%. 2007-09-23 00:23:37.09 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 659 seconds. Working set (KB): 1403452, committed (KB): 11801760, memory utilization: 11%. 2007-09-23 00:27:59.09 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 921 seconds. Working set (KB): 2243196, committed (KB): 11801760, memory utilization: 19%. 2007-09-23 00:33:28.61 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1251 seconds. Working set (KB): 1989840, committed (KB): 11801760, memory utilization: 16%. 2007-09-23 00:37:50.93 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1513 seconds. Working set (KB): 3573024, committed (KB): 11801760, memory utilization: 30%. 2007-09-23 00:43:18.83 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1841 seconds. Working set (KB): 5002408, committed (KB): 11801640, memory utilization: 42%. 2007-09-23 00:50:24.71 spid1s      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): 61060, committed (KB): 11806568, memory utilization: 0%. 2007-09-23 00:55:52.64 spid1s      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): 3041964, committed (KB): 11801640, memory utilization: 25%. 2007-09-23 01:01:20.57 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 1319536, committed (KB): 11804840, memory utilization: 11%. 2007-09-23 01:05:42.90 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 1527424, committed (KB): 11804968, memory utilization: 12%. 2007-09-23 01:11:10.83 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1246 seconds. Working set (KB): 2394260, committed (KB): 11804848, memory utilization: 20%. 2007-09-23 01:15:33.16 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1508 seconds. Working set (KB): 1438816, committed (KB): 11804848, memory utilization: 12%. 2007-09-23 01:21:01.09 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1836 seconds. Working set (KB): 1953928, committed (KB): 11804848, memory utilization: 16%. 2007-09-23 01:50:31.87 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3607 seconds. Working set (KB): 4497384, committed (KB): 11801776, memory utilization: 38%. 2007-09-23 02:50:38.82 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7213 seconds. Working set (KB): 2121912, committed (KB): 11802288, memory utilization: 17%. 2007-09-23 04:50:55.44 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14430 seconds. Working set (KB): 845800, committed (KB): 11800624, memory utilization: 7%. 2007-09-23 08:50:29.00 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 28803 seconds. Working set (KB): 2151876, committed (KB): 11804888, memory utilization: 18%. 2007-09-23 16:51:24.58 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 57659 seconds. Working set (KB): 2861464, committed (KB): 11804888, memory utilization: 24%. 2007-09-23 18:44:30.17 spid1s      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): 23772, committed (KB): 11805216, memory utilization: 0%. 2007-09-23 18:49:58.08 spid1s      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): 1874064, committed (KB): 11805280, memory utilization: 15%. 2007-09-23 18:55:26.00 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 2500840, committed (KB): 11805344, memory utilization: 21%. 2007-09-23 18:59:48.33 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 2828416, committed (KB): 11805344, memory utilization: 23%. 2007-09-23 19:05:16.23 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1246 seconds. Working set (KB): 3701268, committed (KB): 11805592, memory utilization: 31%. 2007-09-23 19:09:38.54 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1508 seconds. Working set (KB): 1377936, committed (KB): 11805592, memory utilization: 11%. 2007-09-23 19:15:06.47 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1836 seconds. Working set (KB): 2118884, committed (KB): 11805472, memory utilization: 17%. 2007-09-23 19:44:37.16 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3606 seconds. Working set (KB): 3115628, committed (KB): 11805600, memory utilization: 26%. 2007-09-23 20:44:49.95 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7219 seconds. Working set (KB): 859280, committed (KB): 11806360, memory utilization: 7%. 2007-09-23 22:45:03.81 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14433 seconds. Working set (KB): 1673520, committed (KB): 11806424, memory utilization: 14%. 2007-09-24 00:51:26.30 spid1s      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): 225768, committed (KB): 11812120, memory utilization: 1%. 2007-09-24 00:56:54.21 spid1s      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): 3119180, committed (KB): 11806496, memory utilization: 26%. 2007-09-24 01:02:22.12 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 1573036, committed (KB): 11809696, memory utilization: 13%. 2007-09-24 01:06:44.46 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 2740832, committed (KB): 11809696, memory utilization: 23%. 2007-09-24 01:12:12.43 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1246 seconds. Working set (KB): 1614560, committed (KB): 11809456, memory utilization: 13%. 2007-09-24 01:16:34.75 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1508 seconds. Working set (KB): 2009292, committed (KB): 11809456, memory utilization: 17%. 2007-09-24 01:22:02.66 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1836 seconds. Working set (KB): 2493024, committed (KB): 11809456, memory utilization: 21%. 2007-09-24 01:51:33.55 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3607 seconds. Working set (KB): 4742328, committed (KB): 11809712, memory utilization: 40%. 2007-09-24 02:51:40.72 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7214 seconds. Working set (KB): 922168, committed (KB): 11806768, memory utilization: 7%. 2007-09-24 04:51:54.16 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14428 seconds. Working set (KB): 1038548, committed (KB): 11806064, memory utilization: 8%. 2007-09-24 08:51:30.10 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 28803 seconds. Working set (KB): 1764916, committed (KB): 11812656, memory utilization: 14%. 2007-09-24 16:51:33.44 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 57606 seconds. Working set (KB): 1738296, committed (KB): 11814320, memory utilization: 14%. 2007-09-24 18:37:50.39 spid1s      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): 88856, committed (KB): 11815008, memory utilization: 0%. 2007-09-24 18:43:18.38 spid1s      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): 1575168, committed (KB): 11815008, memory utilization: 13%. 2007-09-24 18:48:46.31 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 2162688, committed (KB): 11815008, memory utilization: 18%. 2007-09-24 18:53:08.66 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 2822472, committed (KB): 11815008, memory utilization: 23%. 2007-09-24 18:58:40.54 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1250 seconds. Working set (KB): 1395628, committed (KB): 11815072, memory utilization: 11%. 2007-09-24 19:03:02.89 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1512 seconds. Working set (KB): 1275492, committed (KB): 11815256, memory utilization: 10%. 2007-09-24 19:08:31.32 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1840 seconds. Working set (KB): 1282192, committed (KB): 11815256, memory utilization: 10%. 2007-09-24 19:38:05.65 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3615 seconds. Working set (KB): 2362296, committed (KB): 11815328, memory utilization: 19%. 2007-09-24 20:38:13.00 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7222 seconds. Working set (KB): 1108300, committed (KB): 11816096, memory utilization: 9%. 2007-09-24 22:38:36.63 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14446 seconds. Working set (KB): 1303440, committed (KB): 11816408, memory utilization: 11%. 2007-09-25 00:48:56.94 spid1s      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): 280488, committed (KB): 11824024, memory utilization: 2%. 2007-09-25 00:54:24.84 spid1s      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): 3024340, committed (KB): 11816856, memory utilization: 25%. 2007-09-25 00:59:52.78 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 1371272, committed (KB): 11816792, memory utilization: 11%. 2007-09-25 01:04:17.63 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 920 seconds. Working set (KB): 1883076, committed (KB): 11819416, memory utilization: 15%. 2007-09-25 01:09:45.54 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1248 seconds. Working set (KB): 2768128, committed (KB): 11815920, memory utilization: 23%. 2007-09-25 01:14:07.89 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1510 seconds. Working set (KB): 3098484, committed (KB): 11818992, memory utilization: 26%. 2007-09-25 01:19:35.82 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1838 seconds. Working set (KB): 3467688, committed (KB): 11818992, memory utilization: 29%. 2007-09-25 01:49:06.62 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3609 seconds. Working set (KB): 1569788, committed (KB): 11819248, memory utilization: 13%. 2007-09-25 02:49:13.59 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7216 seconds. Working set (KB): 1026696, committed (KB): 11816560, memory utilization: 8%. 2007-09-25 04:49:27.58 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14430 seconds. Working set (KB): 883140, committed (KB): 11816112, memory utilization: 7%. 2007-09-25 08:49:11.24 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 28814 seconds. Working set (KB): 2200932, committed (KB): 11821416, memory utilization: 18%. 2007-09-25 16:49:20.97 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 57623 seconds. Working set (KB): 2266832, committed (KB): 11821936, memory utilization: 19%. 2007-09-25 18:33:46.73 spid1s      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): 115004, committed (KB): 11822752, memory utilization: 0%. 2007-09-25 18:39:14.69 spid1s      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): 1678212, committed (KB): 11822368, memory utilization: 14%. 2007-09-25 18:44:42.61 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 2326272, committed (KB): 11822368, memory utilization: 19%. 2007-09-25 18:49:04.92 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 1042948, committed (KB): 11822304, memory utilization: 8%. 2007-09-25 18:54:36.82 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1250 seconds. Working set (KB): 669164, committed (KB): 11822304, memory utilization: 5%. 2007-09-25 18:58:59.15 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1512 seconds. Working set (KB): 672332, committed (KB): 11822304, memory utilization: 5%. 2007-09-25 19:04:34.64 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1847 seconds. Working set (KB): 1961080, committed (KB): 11822424, memory utilization: 16%. 2007-09-25 19:34:05.68 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3618 seconds. Working set (KB): 1811564, committed (KB): 11822432, memory utilization: 15%. 2007-09-25 20:34:20.75 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7233 seconds. Working set (KB): 2379140, committed (KB): 11823072, memory utilization: 20%. 2007-09-25 22:34:47.29 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14460 seconds. Working set (KB): 586956, committed (KB): 11823320, memory utilization: 4%. 2007-09-26 01:49:17.29 spid1s      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): 26792, committed (KB): 11831984, memory utilization: 0%. 2007-09-26 01:54:45.25 spid1s      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): 3004440, committed (KB): 11824048, memory utilization: 25%. 2007-09-26 02:00:13.21 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 655 seconds. Working set (KB): 3205048, committed (KB): 11826736, memory utilization: 27%. 2007-09-26 02:04:35.56 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 918 seconds. Working set (KB): 3487836, committed (KB): 11824560, memory utilization: 29%. 2007-09-26 02:10:03.47 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1246 seconds. Working set (KB): 842648, committed (KB): 11827568, memory utilizat

  • Anonymous
    October 19, 2007
    If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently

  • Anonymous
    February 26, 2008
    Inspecting our servers, some of them have "system propertiesadvancedperformanceadvancedmemory usage adjust for best performance" set to Programs while others have System Cache.  Shouldn't SQL server set this switch to Programs?  Does Programs correspond to LargeSystemCache =0? (http://technet2.microsoft.com/windowsserver/en/library/efa621bd-a031-4461-9e72-59197a7507b61033.mspx?mfr=true).  Please correct me if I'm wrong.  Thanks.

    [RDORR] SQL Server won't change these settings.  It is up to the computer admin to setup up the environment in this way.

  • Anonymous
    March 10, 2009
    Do you have the following occuring on your SQL Server system. If so it is likely you are running SQL

  • Anonymous
    March 11, 2009
    Jeśli zdarzyło Ci się kiedykolwiek, że w logach SQL Servera (najpewniej wersja 2005 lub 2008 Standard

  • Anonymous
    May 11, 2009
    In the previous posts we have discussed the working set trim message “ A significant part of sql server

  • Anonymous
    May 21, 2009
    Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working with.

  • Anonymous
    May 21, 2009
    Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working

  • Anonymous
    November 19, 2009
    This following article solved our problem. It is about the system file cache size, which is by default UNLIMITED and eating all the memories and pushes out SQL processes. When you set the size to certain level, SQL processes will be staying in memory and not being pushed out. This is the link: http://weblogs.asp.net/omarzabir/archive/2007/10/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx

  • Anonymous
    January 28, 2010
    Hi again, Bob...it's been a while...just hit this bug at a client, sql process dumped about 16 of memory all at once...not sure what caused it...I just installed Best Practice Analyzer and it identified the problem...said to disable TCP chimney...I'm at 9.00.4053...what's the fix...this is a weird one... Thanks.

  • Anonymous
    March 31, 2010
    We have seen this problem also and were able to resolve it after several weeks of digging for a solution. The issue particularly effects SQL Server 2005 x64 running on Windows Server 2003 x64 where you are using Broadcom Network adapters. The issue is to do with "TCP Chimney" which needs to be disabled. It is this feature when enabled that causes the SQL pageouts. Also, if you can, do an update to latest BIOS/firmware/drivers on your system. Running the SQL Server Best Practices Analyser on your system should show this as a recommendation if your system is experiencing the problem. Hope it helps, it worked for us.

  • Anonymous
    May 04, 2010
    Had this issue.   Resolved by setting Max Server Memory to a little under 20 GB (we have 24 GB on the server).  Prior to this, the max server memory was not set, hence it gobbled up everything available.  Thanks for the advice!

  • Anonymous
    June 08, 2010
    Just had this error message on a SQL 2005 SP2 server x86, running on Windows 2003. Fortunately, just the one instance of the message, and no ill-effects that we can see, but it's something we'll be keeping an eye on.

  • Anonymous
    September 15, 2016
    We are noticing this issue on SQL Server 2014 / Window Server 2012 R2 Standard.