1

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\Pages/sec
  • 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
  • System\Processes
  • 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

For Processor Queue Length:

Average = 0.5
Median = 0
Min = 0
Max = 89
Std Deviation = 3.07
Improve this question
4
  • The amount of memory paged out seems microscopic, less than 1GB on a machine with 32GB of RAM. That could be perfectly normal. I'd focus on the CPU usage unless/until you have better evidence that memory pressure is a problem.
    – David Schwartz
    Mar 27, 2015 at 21:21
  • Sorry, I should have mentioned that there were 12 of these same messages listed consecutively over the period of an hour in the Windows Event Log.
    – John Russell
    Mar 28, 2015 at 0:23
  • That really doesn't mean much. If there's no evidence of excessive I/O caused by memory pressure, I'd investigate the CPU usage.
    – David Schwartz
    Mar 29, 2015 at 0:49
  • Does the database contain CLR assemblies (and CLR procs/functions) which may consume lots of RAM? I usually limit the amount of RAM for SQL Server so that it never uses swap file. Look at SQL Server log, do you see messages like "unloading ... due to memory pressure". Custom CLR assemblies, memory pressure and bad SQL queries can all cause high CPU usage in different ways.
    – vaso
    May 23, 2015 at 0:16

1 Answer 1

Reset to default
1

Log into SQL Server Management Studio, Right-click your instance and go to Properties > Memory. By Default, SQL Server 2012 Standard sets your Maximum Server memory to something ridiculous like 2147483647 MB. I would set the Maximum server memory for SQL Server to be something like 8192 MB (or less than 32GB).

Instead of using PerfMon, perhaps you can use SQL Server Profiler under Tools in the SQL Server Management Studio. This will be resource heavy, but you can get a much better idea of whats happening in the application that may cause a spike in CPU or memory usage.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .