Stack Dumping and performance degradation of sql server process

Question

I would like to say that today i got first time SQL Server log message in My SQL Server 2012 Database.

***Stack Dump being sent to D:\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0012.txt

* *******************************************************************************

* BEGIN STACK DUMP:

* *******************************************************************************

* -------------------------------------------------------------------------------

* Short Stack Dump

Stack Signature for the dump is 0x000000000000011A

External dump process return code 0x20000001.

External dump process returned no errors.

There were 3 prominent errors

Resource Monitor (0xda8) Worker 0x000000017F050160 appears to be non-yielding on Node 0. Memory freed: 3784 KB. Last wait: MISCELLANEOUS. Last clerk: type CACHESTORE_OBJCP, name Object Plans. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 61143.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 402 seconds. Working set (KB): 281064, committed (KB): 1658940, memory utilization: 16%.

Resource Monitor (0xda8) Worker 0x000000017F050160 appears to be non-yielding on Node 0. Memory freed: 3784 KB. Last wait: MISCELLANEOUS. Last clerk: type CACHESTORE_OBJCP, name Object Plans. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 61143.

And the D:\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0012.txt message is like that

Current time is 02:56:35 12/22/15.                                                                               
=====================================================================                                            
       BugCheck Dump                                                                                             
=====================================================================                                            

This file is generated by Microsoft SQL Server                                                                   
version 11.0.3128.0                                                                                              
upon detection of fatal unexpected error. Please return this file,                                               
the query or program that produced the bugcheck, the database and                                                
the error log, and any other pertinent information with a Service Request.                                       


Computer type is Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz.                                                       
Bios Version is INTEL  - 6040000                                                                                 
PhoenixBIOS 4.0 Release 6.0                                                                                      
1 X64 level 8664, 14 Mhz processor (s).                                                                          
Windows NT 6.2 Build 9200 CSD .                                                                                  

Memory                               
MemoryLoad = 87%                     
Total Physical = 4095 MB             
Available Physical = 526 MB          
Total Page File = 7144 MB            
Available Page File = 1254 MB        
Total Virtual = 134217727 MB         
Available Virtual = 134210033 MB     
**Dump thread - spid = 0, EC = 0x0000000000000000                                                                
***Stack Dump being sent to D:\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0012.txt                                     
* *******************************************************************************                                
*                                                                                                                
* BEGIN STACK DUMP:                                                                                              
*   12/22/15 02:56:36 spid 1472                                                                                  
*                                                                                                                
* Non-yielding Resource Monitor                                                                                  
*                                                                                                                
* *******************************************************************************                                
* -------------------------------------------------------------------------------                                
* Short Stack Dump 

Any suggestion or help would be appreciated.

Answer 1

This is not a complete answer it is a workaround as others have suggested in comment you need to raise case with Microsoft to get information about what exactly might be causing this issue.

NOTE: Not every stack dump is because of bug in SQL Server. But I strongly suggest you to apply SQL Server 2012 SP3. AFAIK there is no such bug where you get paging information and then Stack dump is produced in SQL Server 2012.

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 402 seconds. Working set (KB): 281064, committed (KB): 1658940, memory utilization: 16%.

This is something which you should be worried about this MIGHT be what is causing the issue. There is program or process running which is taking away memory from SQL Server and as a result SQL Server is paging the process to disk and thus logging this message. You need to find out what it is. I suggest you read This Article about how to move when you get such message.

Workaround: Make sure SQL Server service account has Locked pages in memory privilege LPIM. This will not allow the other process to trim SQL Server memory and force it to page the process. As I said this is workaround you need to find out what is taking away from SQL Server the memory consumed. Before you give LPIM make sure you set appropriate value or max server memory and leave enough for OS to function correctly.

Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

Since you have HyperV you must also look from OS side whether any process running which which requires excessive memory.