dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
SQL Server 2014: Deleting files of a hekaton table
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:
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:
The database id is 8 and the object id is 277576027. We can retrieve the object name by using T-SQL:
name AS table_name,
WHERE [object_id] = 277576027;
Now, let’s try to delete all the files by using the command line del:
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).
At this point, if we try to select data from the Hekaton table dbo.StorageTestTable, we notice it still works … that’s great !
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.
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 …
... 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
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:
The procmon tool traces give us the following result:
The createFile() method is called with the following desired access:
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:
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:
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)
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)
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!