PRF: Memory Management (Working Set Trimming)

Description: Working Set Trimming refers to a condition wherein all Working Sets are flushed to the disk. A Working Set is the amount of physical memory used by a process. Working Set Trimming is primarily seen on SQL Server systems when large amounts of physical memory are allocated to the SQL Server processes and a new request for resource allocations is made that exceeds the amount of free available resources. In order to satisfy the new allocation request the Working Set manager flushes all the Working Sets for all the processes to disk (not just the SQL ones). Only the bare minimum needed to run these processes remains allocated.

Scoping the Issue: Although we normally see Working Set Trimming Issues on SQL Servers (SQL Server 2000 and later), we have also seen the issue surface on other application servers that make use of large databases – for example, Microsoft Exchange 2007 servers and Microsoft Data Protection Manager (DPM) Servers. Let’s take a look at each of these scenarios in turn:

Microsoft SQL Servers: Based on the version of SQL Server in use, and the configuration, there are a combination of symptoms that you may experience.

SQL Server Standard Edition – From a systems perspective, you may notice sluggish performance on the server, both at the console and when accessing the server remotely. In addition, you may not be able to connect to the system via a Remote Desktop Connection. Over time the performance will improve as the running processes reacquire the resources that they were using previously. On systems running SQL Server 2005 SP2 or later, you may also notice a number of error messages that include this description: “A significant part of sql server process memory has been paged out” in the Application event log (Event ID 17890)

SQL Server Enterprise Edition –SQL Server Enterprise Edition includes additional functionality that allows you to lock the buffer pool in memory. This functionality is called “Lock Pages in Memory”. If you have this feature configured and your system encounters a condition that requires the Working Sets to be flushed, you may see a number of Event ID 333 in the System log. The description for this Event ID includes the text, “An I/O operation initiated by the Registry failed unrecoverably.” If you are running SQL Server 2005 SP2 or later and you have this feature configured, you will not see the error message referencing SQL Server Process Memory being paged. Again, as the running processes reacquire the resources that they were using previously, system performance will improve.

Microsoft Exchange Server 2007: In the event that Exchange 2007 servers experience a Working Set flush, common symptoms include the following:

Event ID 333

Slow response at the console or from a remote connection

Unable to connect via RDP

New mail connections to the server time out – existing connections continue to function normally

Remote Management is not possible – in essence new RPC connections are unavailable

Microsoft Data Protection Manager (DPM): DPM servers experience the same symptoms as described above for Microsoft SQL Servers. DPM servers will also experience failures when trying to take snapshots.

In each of the application server scenarios outlined above, the server will eventually recover as the running processes reacquires resources it was previously using. However, this may take anywhere from minutes to hours, depending on the amount of physical RAM and the processing taking place on the system.

Data Gathering: In all instances, collecting either MPS Reports with the General, Internet and Networking, Business Networks and Server Components diagnostics, or a Performance-oriented MSDT manifest must be done. Additional data required may include the following: