SQL Server On Linux – Configuration – Processors

Background

There are a few ways to throttle SQL Server Performance.

We can cap the number of memory available.

We can also cap the number of processors that it can use.

 

Configuration – Processor

SQL Server Management Studio

Outline

If we are to limit CPU availability to a lone processor :-

  1. Launch SSMS
  2. Connect to the SQL Server Instance
  3. Right click on the SQL Server Instance
  4. Choose Properties from the drop-down menu
  5. Access the Processors tab
    • Enable processors
      • Set Processor Affinity to CPU0

Images

processors.ssms.linux.processor.01.20181129.1008pm

Validate

Linux

OS

ps
Command

ps -eo pid,ni,pri,psr,pcpu,pmem,stat,wchan:40,user,comm,stime,time,sz | egrep "sql|PID"

Output

 

 

linux.ps.20181129.0757pm

Explanation
  1. For our SQL Server Engine Process :-
    • Our PSR is at 1

 

/proc/[pid]/stat
Syntax

cat /proc/[PID]/stat | cut -d ' ' -f39

Command

cat /proc/24333/stat | cut -d ' ' -f39

Output

proc.stat.20181129.1014pm.PNG

Explanation
  1. For our SQL Server Engine Process :-
    • Our PSR is at 1

 

taskset
Syntax

taskset -c -p [PID]

Command

taskset -c -p 24333

Output

taskset.20181129.1021pm

Explanation
  1. For our SQL Server Engine Processor :-
    • Our current affinity list is set to 0-3
      • All processes
        • This does not seem right

 

Referenced Work

  1. Stack Exchange
    • Unix & Linux
      • Determining the particular processor on which a process is running
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s