I've been asked to troubleshoot a server hosting our application at a client's facility.
The client has complained to us on two separate occasions in the last week - week and a half. In one instance they claimed the server's memory was "maxed out," and in the other instance, they claimed the CPU was "maxed out". On both occasions our application became unusable and the client was forced to reboot the server.
The server specs are as follows:
System Manufacturer Dell Inc. System Model PowerEdge R320 System Type x64-based PC Processor Intel(R) Xeon(R) CPU E5-2430 0 @ 2.20GHz, 2200 Mhz, 6 Core(s), 12 Logical Processor(s) - Hyperthreading-enabled??? OS Name Microsoft Windows Server 2008 R2 Standard Version 6.1.7601 Service Pack 1 Build 7601 Installed Physical Memory (RAM) 32.0 GB Total Physical Memory 32.0 GB Available Physical Memory 26.1 GB Total Virtual Memory 41.7 GB Available Virtual Memory 34.9 GB Page File Space 9.77 GB Page File C:\pagefile.sys
Our application is powered by
Microsoft SQL Server 2012 (11.0.2100.60 (X64) - Standard Edition), IIS 7.5, a handful of proprietary Windows Services applications - they are all on the same server.
Recently, the application's performance has been more stable and acceptable. But I have a feeling it's only a matter of time before this server's health declines again.
I've been asked to investigate these prior occurrences. But I don't seem to have much to go on.
The Windows Event Viewer had some entries confirming their allegations of memory pressure. There were 12 of these entries consecutively listed in the event viewer of the course of an hour.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 221384, committed (KB): 434308, memory utilization: 50%.
In an effort to be proactive and prepare for another potential outage, I've enabled PerfMon logging to a csv file on the server which includes the following counters:
- Memory\Available MBytes
- Memory\% Committed Bytes In Use
- Paging File(\??\C:\pagefile.sys)\% Usage
- PhysicalDisk(*)\Avg. Disk sec/Read
- PhysicalDisk(*)\Avg. Disk sec/Write
- PhysicalDisk(*)\Disk Reads/sec
- PhysicalDisk(*)\Disk Writes/sec
- System\Processor Queue Length
- SQLServer:Buffer Manager\Page life expectancy
- SQLServer:General Statistics\User Connections
- SQLServer:Memory Manager\Memory Grants Pending
- SQLServer:SQL Statistics\Batch Requests/sec
- SQLServer:SQL Statistics\SQL Compilations/sec
- SQLServer:SQL Statistics\SQL Re-Compilations/sec
- Processor(_Total)\% Processor Time
- System\Context Switches/sec
- Process(x)\% Processor Time (where x is in (sqlservr, w3wp, etc))
I've also set up some basic email alerts in SQL Server agent for severity levels: 17-25 and for a couple of specific Error IDs.
(1) What else should I be tracking/monitoring/doing so that I am better prepared for this next crash? I would like sufficient data to retrace the events that led up to the crash.
(2) We know there was memory pressure based on the Windows Event Viewer entry, but after reviewing the PerfMon data I've collected so far, a couple of things stand out which may indicate CPU problems, but I'm not entirely sure. Do the following seem too high for comfort? The spikes of > 50 concern me although they are not for a pro-longed period
Processor Queue Length:
Average = 0.5 Median = 0 Min = 0 Max = 89 Std Deviation = 3.07