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… 🙂
Pingback: Dew Drop - December 5, 2017 (#2617) - Morning Dew
Pingback: Process Mapping On Linux With SQL Server And Oracle – Curated SQL