The SQL Server Working Set Message

re: The SQL Server Working Set Message

M a t t h e w — Sun, 03 Jun 2007 03:51:39 GMT

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.

re: The SQL Server Working Set Message

M a t t h e w — Mon, 18 Jun 2007 23:59:00 GMT

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!

Handling and troubleshooting memory issues in a SQL Server 2005 instance

SQL Server Performance and Tuning - (SSQA) — Wed, 20 Jun 2007 12:02:31 GMT

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

re: The SQL Server Working Set Message

M a t t h e w — Fri, 06 Jul 2007 21:57:43 GMT

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.

A significant part of sql server process memory has been paged out. This may result in performance degradation

Omar AL Zabir, Many Variety of Posts (MVP) — Wed, 19 Sep 2007 10:47:02 GMT

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

No progress on this issue for us

SaintNick — Wed, 26 Sep 2007 15:17:41 GMT

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

A significant part of sql server process memory has been paged out. This may result in performance degradation

Omar AL Zabir — Fri, 19 Oct 2007 16:33:55 GMT

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

re: The SQL Server Working Set Message

Louisducnguyen — Tue, 26 Feb 2008 18:51:55 GMT

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.

A significant part of sql server process memory has been paged out

SimonS Blog on SQL Server Stuff — Wed, 11 Mar 2009 01:07:16 GMT

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

[PL] Bug miesiąca – Lock pages in memory w Standard Edition

SQLGEEK — Thu, 12 Mar 2009 11:38:25 GMT

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

SQL Server reports working set trim warning message during early startup phase

CSS SQL Server Engineers — Tue, 12 May 2009 08:10:18 GMT

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

Are You Down With The BPA?

Strate SQL — Thu, 21 May 2009 21:04:33 GMT

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

Are You Down With The BPA?

Strate SQL — Thu, 21 May 2009 21:05:05 GMT

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

re: The SQL Server Working Set Message

Max Wei — Fri, 20 Nov 2009 14:29:50 GMT

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