First published on MSDN on Jun 06, 2017
Hi All,
This message, which has error ID 17890 is a rather common troublemaker, as the number of web search hits against it will show.
Example :
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7215 seconds. Working set (KB): 7901284, committed (KB): 17040596, memory utilization: 46%.
Some previous blog
posts
have already given a good kit to understand and address the situation. This blog post's purpose is to give a clear explanation on the last value :
'memory utilization'
, which is often misunderstood : is it the total server memory payload ? Is it the percentage of RAM used by SQL Server ?
The answer is
this value is simply the ratio of the Working set vs the Committed Bytes
(ie. both values reported just before in the same error message).
So here : 7'901'284*100/17'040'596 = 46.37 ≈ 46%.
In other words, it represents how much of the process' committed virtual memory is actually in physical RAM for the
current
SQL Server instance, so this is fully specific to SQL Server process/instance, it doesn't directly reflect any system-wide information. To make it clear, lower percentages are obviously less desirable, and 100% would mean our process is fully in physical memory,
not
that we use 100% of system RAM (see the other two values for actual memory consumption information). T
he message 17890 is generated when the ratio is below 50%
.
Also, after the first occurence, the message appears at intervals if the condition is still in place, so repeated messages would likely indicate a persisting situation, not an 'oscillating' one. And there would therefore not be any correlated point-in-time event to be found at system level for later occurences (since they would only be driven by SQL Server's internal timer). Oh and the interval is not fixed, it will dynamically increase by steps from 5 minutes to a full day such as not to flood the errorlog if the situation remains the same for long, so keep that in mind when trying to establish if and when the situation was alleviated. I usually see the max interval being reached on low utilization instances like Windows Internal Databases (WID).
Finally, while this may appear like a clear-cut situation, the definition of Working Set can introduce some subtleties which in some rare cases could lead this message to appear outside actual memory stress conditions, so confirming if there is indeed a system-wide memory stress is the main priority when dealing with 17890. WID are again good candidates here, as they are likely to run on large hardware where SQL Server (WID) activity is very limited and not the main payload by far. But that is beyond the scope of this current post.
Hope this helps !
Guillaume Fourrat
Escalation Engineer
