A recurrent question I have often heard about Hekaton objects is the following: Is an accidental deletion of the compilation files of a hekaton table on the file system irreversible and could this compromise the execution of SQL Server?

To check the SQL Server behaviour in such situation, we can perform the following test with an in-memory optimized table:

CREATE TABLE [dbo].[StorageTestTable]
(
       [c1] [int] NOT NULL,
       [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL,
 
CONSTRAINT [index91113] PRIMARY KEY NONCLUSTERED HASH
(
       [c1]
)WITH ( BUCKET_COUNT = 1048576)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
 

During the creation of the hekaton table, several files are also created on the file system. We can easily identify that the files belong to the hekaton table by using the naming convention:

xtp_[database_id]_[object_id]

billet_3_procmon_create_files_with_compil_5

The database id is 8 and the object id is 277576027. We can retrieve the object name by using T-SQL:

SELECT
    name AS table_name,
    type_desc,
is_memory_optimized,
    durability_desc
FROM sys.tables
WHERE [object_id] = 277576027;
GO

 billet_3_select_object_name

Now, let’s try to delete all the files by using the command line del:

billet_3_cmd_delete_file

We can see that all files except the dll file have been deleted. If we take a look at this file by using the process explorer tool from sysinternals to see if the dll is handled by a specific application, we notice that SQL Server is handling the dll file (as expected).

billet_3_procexplorer

At this point, if we try to select data from the Hekaton table dbo.StorageTestTable, we notice it still works … that’s great !

 
SELECT *
FROM dbo.StorageTestTable

billet_3_select_data_hekaton_table

Let’s try something else and delete the dll file after shutting downing the SQL Server instance.

SHUTDOWN WITH NOWAIT;

This time, there is no “access denied” error message.

billet_3_delete_dll_file

Now it’s time to restart the SQL Server instance and then take a look at the folder. What a surprise! All files have been recreated …

billet_3_procmon_create_files_with_compil_5

… and if we try to select data from the corresponding hekaton table a second time, we can see it still works, but how? In fact, SQL Server only needs the dll file associated with the hekaton table to use it. Each time the SQL Server instance or a database is restarted, the hekaton table dll is recompiled and injected to the SQL Server process. In order to do this, SQL Server uses both a C compiler and a linker located to the path MSSQL12.MSSQLBinnXtpVCBin. On my lab machine, it i:

C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnXtpVCbin

billet_3_cl_link_exe

As a reminder, creating a dll in C is a multistage process divided into two steps: compilation and linking. We can check how SQL Server compiles and generates the different files of the hekaton table during the start / restart phase by using the procmon tool from sysinternals. We just need to filter the process name for sqlservr.exe, cl.exe link.exe.

First when a Hekaton database is shutting down the hekaton tables, files are deleted as we can see on the following picture:

billet_3_procmon_delete_files

The procmon tool traces give us the following result:

billet_3_procmon_close_files

The createFile() method is called with the following desired access:

billet_3_procmon_create_file_delete_flag

The delete flag tells us that the file will immediately be deleted after all of its handles get closed (issued later by each CloseFile() function) . This means that all the hekaton table compilation files are opened and closed with this specific flag and will be deleted during the database shutdown.

Next, during the restart of the SQL Server instance, a procmon captures give us an interesting picture. For convenience, I’ve broken down the result on three pictures with explanations:

billet_3_procmon_create_files_with_compil

billet_3_procmon_create_files_with_compil_1

billet_3_procmon_create_files_with_compil_2

SQL Server recreates the folder hierarchy and the files associated with the hekaton table (disposition = Create) but that’s not all. Later in the procmon trace, we can see that the C compiler is called by the sqlserver.exe process as showed in the below picture:

billet_3_procmon_create_files_with_compil_3

In my test screeshot above the process ID (PID) 1144 is my SQL Server instance. Finally, we later see that the compiler calls the linker. The process id (PID) 1560 is the C compiler (cl.exe)

billet_3_procmon_create_files_with_compil_4

Once my SQL Server instance is up and running all the files are finally recreated. In short, we showed an existing process about C compilation. My purpose is not to give a C compilation course but just enough for you to get to know how a dll is compiled. Here is a very simplified process schema for the hekaton dll compilation:

Source code file (.c) –> compiler (cl.exe) –> object file (.obj) –> linker (link.exe) –>

dynamic library (.dll)

billet_3_procmon_create_files_with_compil_5

How about other files? The pdb file is created by the linker and is related to the target executable or the DLL. This file contains the complete debug information and among them the symbols that we can use with windbg (one of my favorite tool to try to understand how SQL Server works). Then, the file with the extension .out is a verbose file that contains log messages for troubleshooting and finally the xml file contains MAT representation (Mixed Abstract Tree) transformed by SQL Server into PIT (Pure Imperative Tree) representation in order to transform SQL Like- data types into C-like data types. We retrieve the transformation in the C file.

What can we conclude after seeing the SQL Server process using only the dll file during its execution? I would say it’s good news because an accidental deletion of these files will not compromise the good execution of SQL Server and hekaton objects. However, as usual, let’s keep the good habits alive and don’t touch them! It’s still bad! 🙂

By David Barbarin