In A significant part of SQL Server process memory has been paged out we discussed about SQL Server working set trim warning, when they can occur and how to troubleshoot them.
I the same blog I mentioned SQL Server will log “A significant part of sql server process memory has been paged out” warning when working set reaches 50% or below of the overall committed memory by SQL Server memory manager. In this blog I will try to cover when this warning could be a false warning and how to identify it.
Let us recollect what is committed memory and different states of committed bytes in windows.
Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)
Committed working set: Committed memory which is currently in RAM
Committed Paged : Committed memory which is currently page file
Committed Mapped : Committed mapped to page file.
Committed untouched: committed memory which is never accessed (When a page is committed in windows it will never become working set unless accessed).
Let us understand what Committed untouched is. Download Memoryallocator exe from This link and Keep committing memory using the same exe.
You will notice that the committed memory of the Memoryallocator process increases, but the physical memory usage (RAM usage or Working set) (or) Page file usage will not increase at all. Only the committed memory of the process and committed memory of overall system increases.
Why?
When a page is committed in windows it will not become part of working set or page file unless it is accessed.
Similarly when SQL Server estimates the memory requirements of different clerks and allocates them during startup or on need. These allocated memory is part of committed memory but will not have a page in RAM or Page file unless accessed for the first time.
So during this condition SQL Server’s working set can go far below the committed bytes and once working set reaches 50% or below of overall committed bytes then ““A significant part of sql server process memory has been paged out” warning is logged in SQL Server errorlog.
How do you identify if this warnings are false warnings?
We can identify if these warnings are false using the SQL Server memory dump or using the Perfmon counters.
Let us stimulate a false warning situation using the below backup query and see how to identify if the warning is false.
Run the below query in your test system.
Note: If you do not get the warning message increase the buffer count in below query. If you get “There is insufficient system memory in resource pool” then reduce the buffer count.
WARNING: dumptrigger and below trace flag’s are undocumented and should be used only in test environments with caution (or) under Microsoft Support supervision. There is no guarantee that they will work in future versions of SQL Server.
DBCC TRACEON(8026,-1) --Trace flag –T8026 tells dump trigger to remove the trigger after the first dump has been triggered.
go
DBCC DUMPTRIGGER('SET',17890)
go
BACKUP DATABASE MSDB TO DISK = N'msdb.BAK' WITH NOFORMAT, INIT,NAME = N'msdb', SKIP, NOREWIND, NOUNLOAD, STATS = 1 ,BUFFERCOUNT = 10000,BLOCKSIZE = 65536 ,MAXTRANSFERSIZE=2097152
Once you run the above backup command you will see error: 17890 “A significant part of sql server process memory has been paged out” and a mini memory dump will be created in Errrorlog folder along with SQLDump00nn.txt
Using memory dump
Open the SQLDump00nn.txt and review the memory section in SQLDump00nn.txt. This will give you the system memory information when the error occurred.
Snippet from my SQLDump00nn.txt.
{
Memory
MemoryLoad = 26%
Total Physical = 131067 MB
Available Physical = 96691 MB
Total Page File = 393201 MB
Available Page File = 334217 MB
Total Virtual = 8388607 MB
Available Virtual = 8166328 MB
**Dump thread – spid = 0, EC = 0x00000020F19C2B90
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.RBS\MSSQL\LOG\SQLDump0048.txt
}
In the above output “Available Physical is 96,691 MB” which indicates there is no physical memory pressure when SQL Server raised 17890 warning so widows is not trimming the working set and obviously we can come to conclusion that this instance of warning is false warning.
Note: Above method may not work well in earlier versions of windows in which working set of all processes are hard trimmed when there memory pressure in the system.
Using perfmon counters
In below perfmon graph I have collected three counters
1. Process\SQLServr\Working set (highlighted Black)
2. Process\SQLServr\Private bytes (Committed memory. Green line)
3. Memory\AvailableMbytes (Red line)
I you review your SQL Server error log you would notice 17890 warning at the same time when Private bytes (Green line) spiked.
How to conclude that the warning is printed because of “untouched committed pages” by SQL Server.
In general when a page is committed and accessed it will be part of working set as long as there is enough available memory on the system. If you look at below graph you will notice that the private bytes (committed) is increasing but the working set is not increasing at same phase though there is adequate available memory in the system. This can happen only when the pages are committed and not accessed (If you look at below graph carefully committed memory increased and dropped with in 10 seconds so when you configure Perfmon choose sample rate every 1 second else perfmon might miss the data and you will find some thing like this happened).
Related posts
Troubleshooting steps for all SQL Server Memory errors
SQL Server lock pages in memory should I use it?
What is new in SQL Server 2012 Memory
How to set max server memory and min server memory
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights
MSSQLWIKI 