Holidays is over and let’s start working with a funny write-up. I was reading interesting stuff done by my colleagues Franck Pachot and Daniel Westermann about reducing the size of Oracle / PostgreSQL binaries at the minimum in the context of a Docker images. I was curious what can be done on SQL Server side on Linux environment but just keep in mind that some operations performed in this post are probably not supported in production.

Firstly, remember that SQL Server installation is package-oriented on Linux in a such way we may choose only installing packages that include the SQL Server engine as well as the basic client tools as SQLCMD. In other words, we just need to install the mssql-server-* (engine), mssql-tools-* (CLI tools) and their dependencies leading to the following result in my case (SQL Server 2017 RTM-CU9-GDR and CentOS 7):

$ du -sh /opt/mssql /u01/sqlserverdata/mssqlserver /var/opt/mssql/ /usr/share/doc/mssql-server/
891M    /opt/mssql
52M     /u01/sqlserverdata/mssqlserver
12M     /var/opt/mssql/
848K    /usr/share/doc/mssql-server/

 

A total size of 956MB.

In my context the following folders contain folders and files related to my SQL Server instance:

  • /opt/mssql hierarchy folder contains all the SQL Server engine binaries and libraries
  • /u01/sqlserverdata/mssqlserver contains all database system files (master, msdb, model, tempdb)
  • /var/opt/mssql contains SQL Server and SQL Agent log files, default extended event session files, default trace files and the machine.key
  • /usr/share/doc/mssql-server/ contains some license text files

 

Firstly, we can remove all files from /usr/share/doc/mssql-server/ with a disk space reduction of 848KB. One another easy way to continue reducing disk space consumed by SQL Server in /var/opt/mssql/log is to disable telemetry …

$ sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false

 

… extended events and the default trace as well …

DROP EVENT SESSION [system_health] ON SERVER 
GO
DROP EVENT SESSION [telemetry_xevents] ON SERVER 
GO
DROP EVENT SESSION [AlwaysOn_health] ON SERVER 
GO

EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE;
GO

 

… and drop their related files on disk then.

$ sudo rm -f /var/opt/mssql/log/*.trc
$ sudo rm -f /var/opt/mssql/log/*.xel

 

~ another 4 MB saved … not a really a good job, isn’t it? 🙂

$ du -sh /opt/mssql /u01/sqlserverdata/mssqlserver /var/opt/mssql/
891M    /opt/mssql
52M     /u01/sqlserverdata/mssqlserver
8.8M    /var/opt/mssql/
0       /usr/share/doc/mssql-server/

 

By taking a look at the other folders in the /var/opt/mssql hierarchy seems to confirm we can’t do more. Let’s try to cleanup other MSSQL related folders.

We may try now to reduce the size of the system databases but these files will probably grow over the time and moving them on a persistent volume storage with containers is an easy task in a such way we may exclude them from the equation.

$ sudo du -sh /u01/sqlserverdata/mssqlserver/*
4.0M    /u01/sqlserverdata/mssqlserver/master.mdf
8.0M    /u01/sqlserverdata/mssqlserver/modellog.ldf
8.0M    /u01/sqlserverdata/mssqlserver/model.mdf
15M     /u01/sqlserverdata/mssqlserver/msdbdata.mdf
768K    /u01/sqlserverdata/mssqlserver/msdblog.ldf
8.0M    /u01/sqlserverdata/mssqlserver/tempdb.mdf
8.0M    /u01/sqlserverdata/mssqlserver/templog.ldf

 

Another 52MB. Most of the work could be done in the /opt/mssql hierarchy folder with both SQL Server binaries and libraries.

$ sudo du -sh /opt/mssql/* | sort -r
862M    /opt/mssql/lib
29M     /opt/mssql/bin

 

The above output indicates that the SQL Server binaries size is rather small compared to the related libraries. As Franck and Daniel, I attempted to execute a strip command against SQL Server binaries and libraries but /! keep in mind this operation is probably not supported by Microsoft. /!

$ sudo strip /opt/mssql/bin/*
strip:/opt/mssql/bin/compress-dump.sh: File format not recognized
strip:/opt/mssql/bin/crash-support-functions.sh: File format not recognized
strip:/opt/mssql/bin/generate-sql-dump.sh: File format not recognized
strip:/opt/mssql/bin/handle-crash.sh: File format not recognized
strip:/opt/mssql/bin/mssql-conf: File format not recognized
$ sudo strip /opt/mssql/lib/*
strip: Warning: '/opt/mssql/lib/loc' is not an ordinary file
strip: Warning: '/opt/mssql/lib/mssql-conf' is not an ordinary file
strip:/opt/mssql/lib/secforwarderxplat.sfp: File format not recognized
strip:/opt/mssql/lib/sqlagent.sfp: File format not recognized
strip:/opt/mssql/lib/sqlservr.sfp: File format not recognized
strip:/opt/mssql/lib/system.certificates.sfp: File format not recognized
strip:/opt/mssql/lib/system.common.sfp: File format not recognized
strip:/opt/mssql/lib/system.netfx.sfp: File format not recognized
strip:/opt/mssql/lib/system.sfp: File format not recognized

 

The below output was beyond my expectation but looking further, the size reduction concerns only paldumper in /opt/mssql/bin/ (27MB to 1.6MB). Other executables remain unchanged. Some other Linux dynamic shared libraries as libc++abi.so.1 (from 2.5MB to 384KB), libunwind.so.8 (from 368KB to 48KB) or libsqlvdi.so (from 348KB to 48KB).

$ sudo du -sh /opt/mssql/* | sort -r
857M    /opt/mssql/lib
2.9M    /opt/mssql/bin

 

Another 31MB. That’s said, restarting my SQL Server instance remained successful and I was able to connect to it. I think it could be safe to test the generation of a dump but for now let’s consider that it does not interfere with the correct execution of the SQL Server engine…

$ sudo systemctl restart mssql-server

$ sqlcmd -S localhost -U sa -P XXXXX -Q"SELECT @@VERSION"
                                                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64)
        Jul  6 2018 18:24:36
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

 

However, the biggest remaining part is about SQL Server files with sfp extension as shown below.

$ sudo du -sh /opt/mssql/lib/* | sort -hr
370M    /opt/mssql/lib/sqlservr.sfp
304M    /opt/mssql/lib/system.netfx.sfp
158M    /opt/mssql/lib/system.common.sfp
15M     /opt/mssql/lib/sqlagent.sfp
8.3M    /opt/mssql/lib/system.sfp
1.6M    /opt/mssql/lib/system.certificates.sfp
652K    /opt/mssql/lib/libc++.so.1
384K    /opt/mssql/lib/mssql-conf
304K    /opt/mssql/lib/libc++abi.so.1
48K     /opt/mssql/lib/secforwarderxplat.sfp
48K     /opt/mssql/lib/libunwind.so.8
48K     /opt/mssql/lib/libsqlvdi.so
44K     /opt/mssql/lib/loc

 

Files with sfp extension are not supported by the strip command (see above) and it turns out these files are packed archive and are part of the “SQL Platform Abstraction Layer” (or SQLPAL) that hosts the operating system in a library (aka Drawbridge Library OS). These files are used to load SQL Server and its dependent programs and DLLs to run under SQLPAL.

We may dig into these packed archives by using the sfpack tool (available on GitHub) that include a lot of what we may recognize as windows files. Here some of them:

  • .exe files
$ egrep *.exe sqlservr.sfp.txt | head -n 10
/opt/mssql/lib/sqlservr/Content/sqlservr.exe.lnx.hiv
/opt/mssql/lib/sqlservr/Content/sqlservr.exe.win.hiv
/opt/mssql/lib/sqlservr/Content/binn/sqlcmd.exe
/opt/mssql/lib/sqlservr/Content/binn/useraccounttool.exe
/opt/mssql/lib/sqlservr/Content/binn/sqlceip.exe.config
/opt/mssql/lib/sqlservr/Content/binn/SqlDumper.exe
/opt/mssql/lib/sqlservr/Content/binn/reg.exe
/opt/mssql/lib/sqlservr/Content/binn/rep.exe
/opt/mssql/lib/sqlservr/Content/binn/OSQL.exe
/opt/mssql/lib/sqlservr/Content/binn/sqlservr.exe.config

 

  • .dll library files
$ egrep *.dll sqlservr.sfp.txt | head -n 10
/opt/mssql/lib/sqlservr/Content/binn/sqlsatellite.dll
/opt/mssql/lib/sqlservr/Content/binn/SQLOS.dll
/opt/mssql/lib/sqlservr/Content/binn/pidgenx.dll
/opt/mssql/lib/sqlservr/Content/binn/cpprest120_2_8.dll
/opt/mssql/lib/sqlservr/Content/binn/hadrres.dll
/opt/mssql/lib/sqlservr/Content/binn/msvcr100.dll
/opt/mssql/lib/sqlservr/Content/binn/autoadmin.dll
/opt/mssql/lib/sqlservr/Content/binn/sqlboot.dll
/opt/mssql/lib/sqlservr/Content/binn/sqlaccess.dll
/opt/mssql/lib/sqlservr/Content/binn/qds.dll

 

According to the GitHub documentation dbpatch files are binary patch files to their respective siblings with the same name without any syscalls instructions from binaries like ntdll.dll, win32k.sys etc …

$ grep dbpatch   system.sfp.txt
/opt/mssql/lib/system/Content/Windows/System32/DkDll_2.dll.dbpatch
/opt/mssql/lib/system/Content/Windows/System32/DkDll.dll.dbpatch
/opt/mssql/lib/system/Content/Windows/System32/sqlpal.dll.dbpatch
/opt/mssql/lib/system/Content/Windows/System32/DkDll_4.dll.dbpatch
/opt/mssql/lib/system/Content/Windows/System32/DkDll_3.dll.dbpatch
/opt/mssql/lib/system/Content/Windows/System32/DkDll_1.dll.dbpatch

$ grep dbpatch system.common.sfp.txt
/opt/mssql/lib/system.common/Content/Windows/System32/win32kfull.sys.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/ntdll.dll.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/win32u.dll.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/win32kbase.sys.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/Drivers/msrpc.sys.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/Drivers/ksecdd.sys.dbpatch
/opt/mssql/lib/system.common/Content/Windows/System32/Drivers/cng.sys.dbpatch

 

Well that would be definitely lead to another interesting write-up but for now I believe this funny work seems to be over because reducing the size of these packed archives is out of our control (at least in a supported way as far as I know). So, the minimum final size I may get would be 869.7MB. I didn’t manage to significantly reduce the size by myself but my feeling is there is some room and optimizations that can be done in the SQLPAL layer by Microsoft. Let’s see in the future!

$ sudo du -sh /opt/mssql /u01/sqlserverdata/mssqlserver /var/opt/mssql/
860M    /opt/mssql
9.7M    /var/opt/mssql/

 

See you!

 

 

 

 

Post Views: 277
Thumbnail [60x60]
by
Oracle Team