Issue
On one of SQL 2016 server HIGH CPU usage alert was received. It was constantly above 95% and SQL server was highest CPU consumer. As checked with sp_who2, Resource Monitor command running under system SPID was biggest CPU consumer and its CPU usage was not coming down.
Observations
Below were observations during investigation
- Highest CPU was being consumed by background process against SPID 10, running command “Resource Monitor”.
- There were very few user process with little CPU usage.
- SQL server minimum and maximum server memory was configured to 16 GB.
- Process was experiencing wait type “PREEMPTIVE_XE_CALLBACKEXECUTE“
- sqlservr.exe process’s committed memory was constantly 16 GB
- Server was having 32 GB RAM and server was also being used for Sentry application.
- Virtual memory was not configured on the server.
- Memory usage was also very high ~97-99% constantly.
Analysis
- Resource monitor task periodically listen for memory events classified as low, high, or steady. The monitor notifies event subscribers when these events occur. When server runs low on virtual memory, resource monitor has to do extra work and cause CPU overhead. This was fixed in SQL 2008 under fix http://support.microsoft.com/kb/968722/ . Although we were experiencing this issue on SQL 2016.
- “PREEMPTIVE_XE_CALLBACKEXECUTE” wait type occurs when a thread is switching to preemptive mode before sending a message to the Extended Events engine through a callback mechanism. SQL server works on non-preemptive scheduling i.e. cooperative scheduling and switching a process to preemptive scheduling means that SQL server waiting for a OS thread outside SQL server to complete. (Ref: https://www.sqlskills.com/help/waits/preemptive_xe_callbackexecute/ )
- SQL server minimum server memory and maximum server memory was same which is not a ideal scenario until there is good amount of memory left for OS. SQL server does not release memory to OS voluntarily once it reaches minimum server memory. This meant in our case SQL server will keep on consuming 16 GB memory even if there is no load on the SQL server.
Conclusion
As server’s memory usage was constantly very high and resource monitor was waiting on a OS task to complete with no virtual memory configured on the server, issue was suspected to be with low memory available for OS processes.
After confirmation from client, we changed the minimum server memory to 4 GB from 16 GB which reduced the CPU usage instantly and memory usage also came down to 95% on the server.
Other solutions for this issue could be configuration of good amount of virtual memory, limit memory usage by other application processes.
Hope this article helps you in troubleshooting similar issues!
Happy learning!!