[Translation] SQL Server Working Set message

Q: I found that messages that point to the Working Set (the memory reserved by SQL Server) are paged out:
The memory of important SQL server processes has been paged. This may cause performance degradation. Duration: 0 seconds. Working Set (KB): 2484, submitted (KB): 48036, memory usage rate: 50%.
What causes this problem?

A:
You can adjust the working sets in the following ways:
1. Signal Adjustment
Low physical memory event reminder events are set by the operating system (refer to: QueryMemoryResourceNotification

2. Self-Tuning
The operating system encounters a page error when applying for a new page and determines that the physical memory is insufficient. For example, If SQL Server is a stack that connects to the Server or other application pages and finds that the memory is insufficient, the working set of SQL Server is directly adjusted.

3. Hard Adjustment
When the system memory is seriously insufficient, it will be hard adjusted. This action directly paging all the SQL Server worker sets and other processes.

Besides signal adjustment, both self-tuning and hard-tuning write error logs, resulting in performance problems.

Basic troubleshooting steps:
1. Check that no other processes have completed the memory quickly. You can check the performance counter of the memory: _ Total working set. If you enable a memory-consuming application, the workset is quickly adjusted, but _ Total remains unchanged. If _ Total drops rapidly, it means that the MiEmptyWorkingSet or MmAllocateContigiousMemory occurs instead of consuming the memory of an application. Then the operating system decides to make a hard adjustment.

2. Check whether the SQL Server process ID exists during the time when the problem occurs. If the SQL Server service has been restarted, the counter may produce misleading data.

3. confirm that your system driver is up to date. The driver may also cause adjustments.

4. confirm that the bug in the operating system terminal service is not the cause of this problem: http://support.microsoft.com/default.aspx? Scid = kb; EN-US; 905865

5. Capture the complete performance counters and SQL Server's sys. dm_ OS _ring_buffers.

6. Apply OS Patch: http://support.microsoft.com/default.aspx? Scid = kb; EN-US; 920739

7. Apply OS Patch: http://support.microsoft.com/default.aspx? Scid = kb; EN-US; 931308

8. Apply SQL Server 2005 SP3.