Thoughts from the EPS Windows Server Performance Team
Here's an issue we've been seeing more and more of on the Performance team lately concerning SQL 2005 and Memory Management. The issue gets reported as: "Windows Memory Management is trimming the SQL Working Set." When you review the event logs, you'll see lots of Event ID 17890 messages: A significant part of SQL server process memory has been paged out. This may result in a performance degradation. This message itself was introduced in SQL 2005 SP2 - basically what is happening is that the working set for SQL is getting paged out by Windows. If you do a KB Search, you'll find KB Article 918483.
First - let's define what exactly "Working Set" is. The working set of a program is a collection of those pages in its virtual address space that have been recently referenced. This includes both shared and private data. The shared data includes pages that contain all instructions an application executes, including those in its own DLL's and the system DLL's. As the working set size increases, memory demand increases. A process has an associated minimum working set size and maximum working set size. Each time a process is created, it reserves the minimum working set size for the process. The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.
If you search around for guidance for SQL Memory Tuning, you'll no doubt find several articles that provide specific guidelines about how much memory to allocate to SQL. In Slava Oks's Weblog post on the topic of SQL 2005 Memory Pressure, he provides some guidelines around how much RAM to leave for the OS based on the amount of RAM.
The problem with solid numbers and rules of thumb is that they are subject to change based on the server load. Every environment is different and has its quirks - so you have to start with a baseline and then tune accordingly. SQL Server max server memory is ONLY the buffer pool and doesn’t include a few other significant sqlservr.exe memory allocations such as the Multipage Allocations (MPA), Thread Stacks (1.17GB), Linked Servers, to name a few. So when you look at a Perfmon for the server in question, you will see the SQL Server Private Bytes counter will be greater than the max server memory configuration.
Let's look at a problem scenario based on the guidelines in Slava's blog:
Now let's try to generate a baseline with some other factors taken into consideration:
So, getting back to Working Set trimming. We know that SQL has a large specified amount of memory reserved, but what happens when another process asks for memory? If there is not enough memory available, the OS memory manager will still attempt to accommodate the new allocation request. This is accomplished by trimming the working set (reducing in-RAM memory usage by paging out) of other processes, including SQLSERVR.EXE. If you are trying to find out what's causing the trimming, you'll at least need Performance Monitor data with Process performance objects. There's one other thing to point out here. If available server memory drops below 100MB, then the Memory Manager will trim the Working Set of all processes.
Finally, there is one other factor to consider: according to KB Article 905865 if you're running Windows Server 2003, there is an issue (fixed in Service Pack 2) regarding Working Set trimming that takes place whenever you log on via RDP (even with the /console switch). If you log on at the physical console, this trimming does not occur. The problem occurs on W2K3SP1 or W2K3 servers with the patch from KB834293 applied.
Until next time ...
- CC Hameed
PingBack from http://www.ditii.com/blog/2007/06/01/windows-memory-management-and-sql-working-set/
WIth the recent application of Service pack 2 for SQL Server we tend to see more messages on the log
Regarding KB905865, won't you agree that trimming working sets behind our backs (rather than adjusting them smoothly) is a misfeature, that should have been gone when memory sizes increased beyond 256 MB. When an entry-level system has at least 512 MB, it just doesn't make sense. I get mack to my session in FUS-enabled XP, and have to wait, painfully watchilg while all my processes are being paged back in.
There is an almost constant stream of posts on forums asking about configuration of SQL Server 2005 memory,
Have you seen the above message within the SQL Server error logs? If not then no need to worry and make
Your above baseline calculation helped me solve a customer issue on which he even had switched hardware vendor
Another gr8 post !!
Couldn’t find a better place that explain EVENT ID 17890...
Just one thing, elaborating the "BASELINE" and "FACTORS" could facilitate deeper understanding.
I really liked the way you explained Working set and gave abstract Idea how to calculate memory requirement for SQL server