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… :)


Originally published at DBA Kevlar.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.