Blog - comments

Hi Stephane,

In fact at dbi services we have professional material ;-)

Samsung SSD 840 PRO Series

Gregory Steulet

I have a write throughput 10x lower than your test (is it the SSD?)

stephane (ebu)
You obtained :Running the test with following options: Number of threads: 64 Random number generator...
stephane (ebu)

I wrote a benchmark based on your own. I'm so far from your result (which is very good for a laptop)

stephane (ebu)
Hi Stephane,It depends, you can find the stats for MySQL 5.6 below:sysbench 0.5: multi-threaded sys...
Gregory Steulet
Blog David Barbarin SQL Server 2014: Deleting files of a hekaton table

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC 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 our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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:


CREATE TABLE [dbo].[StorageTestTable]
       [c1] [int] NOT NULL,
       [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL,
)WITH ( BUCKET_COUNT = 1048576)

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,
FROM sys.tables
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 !

FROM dbo.StorageTestTable




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



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! Smile

Rate this blog entry:

David Barbarin is Consultant at dbi services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies and associated topics such as installation, migration, security audits, troubleshooting of performance issues, or high availability architectures etc. Furthermore, he has many years of experience in .NET development, SSIS packages deployment, and database design in several sectors like retail, health sector, and other industries. David Barbarin is SQL Server MVP (since 2010), Microsoft Certified Master (MCM) for SQL Server, and Microsoft Certified Trainer (MCT). He holds an BTS in electronic from France and has a degree in computer sciences from CNAM in Lyon. His branch-related experience covers Public Sector, Financial Services / Banking, Automotive, Health Sector, IT, Watch Industry, etc.


  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 01 August 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter