SQL Server 2017 on Linux- Processes

So while finishing up my slides for the Microsoft Pass Linux Marathon webinar on Dec. 13th, Tim and I started to discuss the apples vs. oranges comparison of SQL Server on Linux process management vs. an Oracle instance.

We discussed the differences one might expect to see using different tools with the two database platforms and the first question surrounded information pmap would provide.  We commonly use pmap to map out processes, including child and sub-processes of a particular PID.   As my current images have multiple database platforms on each of them,  I quickly spun up a Docker image of SQL Server 2017 to ensure I had a clean image to work from.

First, what processes are running?

$ ps -ef
root         1     0  0 22:22 ?        00:00:00 /bin/sh -c /opt/mssql/bin/sqlservr
root         7     1  0 22:22 ?        00:00:00 /opt/mssql/bin/sqlservr
root         9     7  3 22:22 ?        00:00:24 /opt/mssql/bin/sqlservr
root       165     0  0 22:24 pts/0    00:00:00 bash
root       300   165  0 22:34 pts/0    00:00:00 ps -ef

Unlike Oracle, there aren’t separate background processes like the pmon, log writer, etc. that was present for us to identify, as you see in the following example:

$ ps -ef | grep orcl
delphix   2927     1  0 Nov22 ?        00:06:49 ora_vkrm_orcl
delphix   4542     1  0 18:48 ?        00:00:00 ora_w000_orcl
delphix   9331     1  0 Oct18 ?        00:04:24 ora_pmon_orcl
delphix   9333     1  0 Oct18 ?        00:05:21 ora_psp0_orcl
delphix   9335     1  0 Oct18 ?        05:45:03 ora_vktm_orcl
delphix   9339     1  0 Oct18 ?        00:01:01 ora_gen0_orcl
delphix   9341     1  0 Oct18 ?        00:01:44 ora_diag_orcl
delphix   9343     1  0 Oct18 ?        00:01:37 ora_dbrm_orcl
delphix   9345     1  0 Oct18 ?        00:16:50 ora_dia0_orcl
delphix   9347     1  0 Oct18 ?        00:00:57 ora_mman_orcl
delphix   9349     1  0 Oct18 ?        00:03:55 ora_dbw0_orcl
delphix   9351     1  0 Oct18 ?        00:18:27 ora_lgwr_orcl
delphix   9353     1  0 Oct18 ?        00:05:58 ora_ckpt_orcl
delphix   9355     1  0 Oct18 ?        00:01:36 ora_smon_orcl
delphix   9357     1  0 Oct18 ?        00:00:19 ora_reco_orcl
delphix   9359     1  0 Oct18 ?        00:05:22 ora_mmon_orcl
delphix   9361     1  0 Oct18 ?        00:12:19 ora_mmnl_orcl
delphix   9363     1  0 Oct18 ?        00:00:26 ora_d000_orcl
delphix   9365     1  0 Oct18 ?        00:00:24 ora_s000_orcl
delphix   9886     1  0 Oct18 ?        00:58:21 ora_arc0_orcl
delphix   9888     1  0 Oct18 ?        00:58:44 ora_arc1_orcl
delphix   9890     1  0 Oct18 ?        00:00:34 ora_arc2_orcl
delphix   9892     1  0 Oct18 ?        00:58:21 ora_arc3_orcl
delphix   9894     1  0 Oct18 ?        00:01:11 ora_ctwr_orcl
delphix   9896     1  0 Oct18 ?        00:00:26 ora_qmnc_orcl
delphix   9911     1  0 Oct18 ?        00:06:54 ora_cjq0_orcl
delphix  10082     1  0 Oct18 ?        00:00:27 ora_q000_orcl
delphix  10084     1  0 Oct18 ?        00:02:42 ora_q001_orcl
delphix  14181     1  0 Oct18 ?        00:01:17 ora_smco_orcl
delphix  30148     1  0 Nov22 ?        00:02:25 ora_m002_orcl
delphix  30151     1  0 Nov22 ?        00:02:27 ora_j000_orcl
delphix  30456     1  0 Nov22 ?        00:00:22 ora_j001_orcl
delphix  31062     1  0 Nov22 ?        00:02:25 ora_j002_orcl
delphix  31456     1  0 Nov22 ?        00:00:21 ora_j003_orcl

In our Oracle environment, we can see every background process, with it’s own pid and along with the process monitor, (pmon) db writer, (dbwr), log writer, (lgwr), we also have archiving, (arcx), job processing, (j00x) performance and other background processing.  I didn’t even grep for the Oracle executable, so you recognize how quickly we can see what is running.

In the SQL Server environment, we only have two processes- our parent process is PID 7 and the child is 9 for SQL Server and nothing to distinguish what they actually are doing.  If we decide to use the pmap utility to view what the parent and child process are doing, we see only sqlservr as the mapping information.

$ pmap -x 7

7:   /opt/mssql/bin/sqlservr
Address           Kbytes     RSS   Dirty Mode  Mapping
000000762cbcb000     888     724       0 r-x-- sqlservr
000000762cbcb000       0       0       0 r-x-- sqlservr
000000762cea8000      24      24      24 rw--- sqlservr
000000762cea8000       0       0       0 rw--- sqlservr
000000762ceae000     300      20      20 rw---   [ anon ]
000000762ceae000       0       0       0 rw---   [ anon ] 
0000400000000000       4       4       4 rw---   [ anon ]
...
ffffffffff600000       4       0       0 r-x--   [ anon ]
ffffffffff600000       0       0       0 r-x--   [ anon ]
---------------- ------- ------- ------- 
total kB           80212   13660    8896

The information mapping is filled with heap, (anon) info, along with libraries and bin executables.  Nothing out of the ordinary here, but nothing of detail, either.

If I were to do the same for Oracle-  let’s say the pmon process, you’d see more information, including what the process is doing:

$ pmap -x 9331

9331:   ora_pmon_orcl
Address           Kbytes     RSS   Dirty Mode   Mapping
0000000000400000  189264   12340       0 r-x--  oracle
000000000bed4000    2020     256      36 rw---  oracle
000000000c0cd000     348      60      60 rw---    [ anon ]
000000000c6d5000     584      84      84 rw---    [ anon ]
0000000060000000   12288     356     356 rw-s-    [ shmid=0x50001 ]
0000000060c00000  364544    2824    2824 rw-s-    [ shmid=0x58002 ]
0000000077000000    2048       4       4 rw-s-    [ shmid=0x60003 ]
...

We would see every library being called, along with the memory usage, etc.  The child process of 9 showed substantially more memory usage, but we’re left with little to go on for what each process is responsible for in the database engine:

$ pmap -x 9 

9:   /opt/mssql/bin/sqlservr
Address           Kbytes     RSS   Dirty Mode  Mapping
000000006a400000       4       4       4 r---- system.sfp
000000006a400000       0       0       0 r---- system.sfp
000000006a401000    1308    1304     164 r-x-- system.sfp
...
ffffffffff600000       4       0       0 r-x--   [ anon ]
ffffffffff600000       0       0       0 r-x--   [ anon ]
---------------- ------- ------- ------- 
total kB         3064272  604820  587480

Digging further into this process and using TOP, we can filter for just the child PID 9 and see the memory usage, but still nothing that tells us what 9 is responsible for.

$ top -H -p 9
top - 22:50:39 up 34 min,  0 users,  load average: 0.00, 0.00, 0.00
Threads: 154 total,   0 running, 154 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.1 us,  0.1 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  4042116 total,  1722984 free,   678736 used,  1640396 buff/cache
KiB Swap:  1048572 total,  1048572 free,        0 used.  2936276 avail Mem 
  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND 
26 root      20   0 3017376 591652  15800 S  1.7 14.6   0:27.98 sqlservr                     
22 root      20   0 3017376 591652  15800 S  0.3 14.6   0:01.41 sqlservr                    
14 root      20   0 3017376 591652  15800 S  0.3 14.6   0:00.32 sqlservr                  
9 root       20   0 3017376 591652  15800 S  0.0 14.6   0:00.00 sqlservr                     
10 root      20   0 3017376 591652  15800 S  0.0 14.6   0:00.04 sqlservr                   
11 root      20   0 3017376 591652  15800 S  0.0 14.6   0:00.00 sqlservr                  
12 root      20   0 3017376 591652  15800 S  0.0 14.6   0:00.04 sqlservr                     
13 root      20   0 3017376 591652  15800 S  0.0 14.6   0:00.43 sqlservr      
...

We can verify that there are a number of background processes, but like we discussed in the beginning, there isn’t any type of assignment letting us know what they are each responsible for.

Using the PS utility doesn’t provide us much more:

ps -T -p 9

  PID  SPID TTY          TIME CMD
    9     9 ?        00:00:00 sqlservr
    9     1 ?        00:00:00 sqlservr
    9     2 ?        00:00:00 sqlservr
...
    9    12 ?        00:00:00 sqlservr
...
    9    60 ?        00:00:00 sqlservr
    9    61 ?        00:00:00 sqlservr

It does inform us that there are 56 subprocesses connected to child PID 9 and now I’ve decided there’s only one way to get the details behind what is what.  It’s time to log into the database and look inside:

Connect via SQLCMD:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P ‘<password>’

Then queried the information I needed, (going old school) from sysprocesses, as the DMVs weren’t as efficient:

1> select spid, program_name, cmd from MASTER..sysprocesses;
2> go

spid   program_name                         cmd    
------ -----------------------------------------------------
 1                                          XTP_CKPT_AGENT 
 2                                          LOG WRITER      
 3                                          LAZY WRITER     
 4                                          RECOVERY WRITER 
 5                                          SIGNAL HANDLER 
 6                                          LOCK MONITOR    
 7                                          XIO_RETRY_WORKER
 8                                          XIO_LEASE_RENEWA
 9                                          BRKR TASK       
 10                                         TASK MANAGER    
 11                                         TASK MANAGER    
 12                                         XE TIMER        
 13                                         RESOURCE MONITOR
 14                                         XE DISPATCHER   
 15                                         TASK MANAGER    
 16                                         TASK MANAGER    
 17                                         TRACE QUEUE TASK
 18                                         SYSTEM_HEALTH_MO
 19                                         RECEIVE         
 20                                         TASK MANAGER    
 21                                         CHECKPOINT      
 22                                         TASK MANAGER    
 23                                         HADR_AR_MGR_NOTI
 24                                         BRKR EVENT HNDLR
 25                                         BRKR TASK       
 26                                         BRKR TASK       
 27                                         BRKR TASK      
 28                                         TASK MANAGER    
 29                                         TASK MANAGER    
 30                                         TASK MANAGER    
...  
 51      SQLCMD                             SELECT          
 52                                         TASK MANAGER      
 53      SQLServerCEIP                      AWAITING COMMAND
 54                                         TASK MANAGER    
 ...    
 60                                         TASK MANAGER    
 61                                         TASK MANAGER    
(61 rows affected)

If you were curious what I queried looking at the DMVs, here’s the high level versions:

select thread_address, os_thread_id from sys.dm_os_threads;

select session_id, host_process_id, endpoint_id, program_name, client_interface_name, is_user_process, quoted_identifier, database_id, authenticating_database_id from sys.dm_exec_sessions;

Well, no matter what, that answers some of my questions, but there’s still a lot more to learn… 🙂