Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Linux Administration for SQL Server DBA’s: Checking CPU Usage


By:   |   Last Updated: 2017-01-30   |   Comments (2)   |   Related Tips: More > SQL Server on Linux

Problem

With the 2016 release of SQL Server Microsoft has decided to expand SQL Server to run on Linux servers. Since most SQL Server DBA's are not used to working with Linux, there are common tasks like checking for free disk space or CPU consumption that may be difficult. In this tip I will make a short list of what we need to know about Linux administration to start using SQL Server on Linux.

Solution

Here we will cover some of the key things you shown know.

TOP command

The most basic console command to check CPU consumption is top. The output of this command consists of a header that shows general information and a grid with the information per process. Take a look at the next screen capture.

Screen capture of Linux top command.

For those of us used to Windows, it is important to understand what each line means, so I will go line by line describing its meaning.

Line 1

  • Current system time
  • Time since the system was powered on
  • Number of logged on users
  • Load Average measured at intervals of 5, 10 and 15 minutes

On Linux systems the Load Average measures the percentage queued tasks (either CPU or IO) over a specific timeframe. This value also depends on the number of CPU cores. For example, a system with one core with a load average of 1 has a full load, but if the system has two cores it has a half load. As a rule of thumb, if the load average is a number greater than the number of cores of your system it means that your system is under stress. Keep in mind that the load average could be a number far greater than the number of cores on your system due to waiting tasks.

Line 2

  • Number of total Tasks
  • Number of tasks actually running
  • Number of processes waiting for an event to continue execution
  • Number of stopped tasks
  • Number of processes in zombie state

Here we need to explain what a zombie process is. On a Linux system, when a child process finishes it keeps the process id on the processes table, so the parent process can read the child process exit status. That finished child process is called a zombie. After the parent process reads the child exit status the zombie is removed. The problem with zombies is that Linux has a limited number of process ids (which you can find by reading the file /proc/sys/kernel/pid_max) and in very rare circumstances this may lead to a lack of resources.

Line 3

This line shows information regarding CPU time usage.

  • Percentage CPU Time in User Mode
  • Percentage CPU Time in System Mode
  • Percentage CPU Time in Low Priority User Mode (Linux calls it Nice Priority)
  • Percentage CPU Idle Time
  • Percentage CPU Time Waiting for I/O
  • Percentage CPU Time Servicing Hardware Interrupts
  • Percentage CPU Time Servicing Software Interrupts
  • Percentage CPU Time Virtual CPU waits for a real CPU while the hypervisor is servicing another virtual processor (This is called Stolen Time)

Line 4

This line gives us information about our system’s physical memory.

  • Total Memory
  • Free Memory
  • Used Memory
  • Memory used by Buffer Cache

Line 5

This line shows information about the swap partitions, the equivalent to Windows pagefile.

  • Total Swap partitions space.
  • Free Swap partitions space.
  • Used Swap space.
  • Available Physical Memory.

Process Grid

The lower panel of the top command displays a grid with information regarding running processes.

Column

Description

PID

The Process Identifier

USER

User who owns the Process

PR

Process Priority. If you see RT on this column that means the process is running on Real Time.

NI

Nice Priority. If it is a negative value that means the process is running on high priority. A positive value means the process is running on low priority.

VIRT

Amount of Virtual Memory used by the process expressed in kilobytes.

RES

Amount of Physical Memory used by the process expressed in kilobytes.

SHR

Amount of Shared Memory expressed in kilobytes.

S

Process status
D = uninterruptible sleep
R = running
S = sleeping
T = stopped by job control signal
t = stopped by debugger during trace
Z = zombie

%CPU

The percentage usage of CPU by the given task. If you see a number greater than 100% don’t panic, that’s because top command is not running in thread mode.

%MEM

The percentage of Physical Memory used by the process.

TIME+

Amount of CPU Time used by the process.

COMMAND

The command who started the process.

HTOP as an alternative

There is an alternative to top which is a Linux monitoring tool called htop (http://hisham.hm/htop/). The advantage of this tool is that it uses color and shows information about CPU, Memory and Swap as graphic bars.

Screen capture of htop command output.

Usually this tool is not available on base Linux installations, so you will have to install it from your distribution repositories or downloading the packages from the htop website.

If you are using Ubuntu, the following command will install htop.

sudo apt-get  install htop
Next Steps


Last Updated: 2017-01-30


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 30, 2017 - 10:18:14 AM - sateesh Back To Top

 nice one,looking forward to see more tips on troubleshooting

 


Monday, January 30, 2017 - 9:39:04 AM - Brahma Reddy Back To Top

 Excellent!!!

 


Learn more about SQL Server tools