CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

The SQL Server Working Set Message

The SQL Server Working Set Message

Rate This
31 May 2007 12:15 PM
  • Comments 18

Work continues around the working set issues.  You can find the latest and greatest Information:  http://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.  http://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. http://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. http://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

, , ,
Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
Comments
  • 2 Jun 2007 8:51 PM

    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.

  • 18 Jun 2007 4:59 PM

    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!

  • SQL Server Performance and Tuning - (SSQA)
    20 Jun 2007 5:02 AM

    We had a major issue on one of the SQL Server instance with the memory and getting following error message

  • 6 Jul 2007 2:57 PM

    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.

  • Omar AL Zabir, Many Variety of Posts (MVP)
    19 Sep 2007 3:47 AM

    If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently

  • 26 Sep 2007 8:17 AM

    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 utilization: 7%.

    2007-09-26 02:14:25.79 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): 2090396, committed (KB): 11823600, memory utilization: 17%.

    2007-09-26 02:19:53.70 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): 2348868, committed (KB): 11823600, memory utilization: 19%.

    2007-09-26 02:49:24.37 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): 882912, committed (KB): 11824536, memory utilization: 7%.

    2007-09-26 03:49:31.33 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): 774544, committed (KB): 11824536, memory utilization: 6%.

    2007-09-26 05:49:46.99 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14429 seconds. Working set (KB): 1189096, committed (KB): 11827104, memory utilization: 10%.

    2007-09-26 09:49:18.56 spid1s      A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 28800 seconds. Working set (KB): 1769260, committed (KB): 11828504, memory utilization: 14%.

  • Omar AL Zabir
    19 Oct 2007 9:33 AM

    If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently

  • 26 Feb 2008 10:51 AM

    Inspecting our servers, some of them have "system properties\advanced\performance\advanced\memory 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.

  • SimonS Blog on SQL Server Stuff
    10 Mar 2009 6:07 PM

    Do you have the following occuring on your SQL Server system. If so it is likely you are running SQL

  • SQLGEEK
    12 Mar 2009 4:38 AM

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

  • CSS SQL Server Engineers
    12 May 2009 1:10 AM

    In the previous posts we have discussed the working set trim message “ A significant part of sql server

  • Strate SQL
    21 May 2009 2:04 PM

    Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working with.

  • Strate SQL
    21 May 2009 2:05 PM

    Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working

  • Max Wei
    20 Nov 2009 6:29 AM

    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

  • Don
    28 Jan 2010 9:28 AM

    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.

Page 1 of 2 (18 items) 12
Locations of visitors to this page
Recent Posts