Blog - comments

Can someone please forward me all the netbackup commands with detail information I want CLI.

Shekhar D

thanks like it

internet shop
Thanks,I have installed AV server successfully but agent deployment failed on windows2008 OS. After ...
Silvere
Hi Eyal, thanks for your comment. I'm not sure to understand what you mean by predicate here but let...
Hi Greg, thanks for your comment. No, it doesn't matter because dbcc checkdb will issue an internal ...
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,
 
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! Smile

Rate this blog entry:
2

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.

Comments

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

Leave your comment

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

Contact

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