How Hekaton affects the target recovery time (RTO) of your database

This weekend I found an interesting side effect of Hekaton in SQL Server 2014, unfortunately it negatively affects your database's target recovery time (Recovery-Objective,rto). As you know, for each local compiled table and stored procedure, Hekaton creates a DLL, which is implemented in C language code. These DLL files are loaded into the sqlservr.exe execution space. You can use the following query to view the currently loaded Hekaton table through the DMV sys.dm_os_loaded_modules :

1 SELECT *  from Sys.dm_os_loaded_modules 2 WHERE  like ' xtp% '

In CTP2, the side effect I encountered was: when you delete the corresponding table after the stored procedure, the loaded Hekaton DLL file is not unloaded. Suppose you create a locally compiled stored procedure, and after a certain amount of time you want to delete that stored procedure to rebuild, in order to get a better local compilation execution plan (the current execution time in Hekaton does not support recompilation). In that case, the old implementation of your stored procedure is still in sqlservr.exe 's execution space, consuming extra memory. The same happens when you delete a table, and the DLL itself is in memory!

The only way to get rid of these extra unwanted DLL files is to take your entire database offline and back online. Then when you query the DMV sys.dm_os_loaded_modules , you will see that only the currently implemented local compilation tables and stored procedures are loaded into the sqlservr.exe. This is the first bad problem you hekaton when you don't have a specific maintenance interface available.

But things get even worse: When you restart SQL Server,sql server compiles and links each DLL that was originally built. Compiling and linking each Hekaton DLL consumes some CPU time, during which your database is processing the recovery phase, and from the user's point of view, it means that your database is inaccessible! Even when you try to access a table based on a traditional hard disk, during this time, you get an error message similar to the following:

MSG 922, Level A, State 1, line 1
Database ' Hashcollisions ' is being recovered. Waiting until recovery is finished.

You can also Files\Microsoft SQL Server\mssql12 in C:\Program. The original old. C,.obj and. dll are still in the MSSQLSERVER\MSSQL\DATA\XTP directory, because they are required for the next SQL Server startup.

Suppose you create 50 locally compiled tables and stored procedures and delete them immediately. In that case you end up with 100 DLL files that are compiled and connected during SQL Server startup. I tested SQL Server 2014 CTP2 on a virtual machine, and my database came online in 69 seconds!

So keep an eye out for these side effects as they can significantly reduce your target recovery time (RTO)! Imagine that you are in a cluster failover, in which case each DLL must be compiled and linked at your additional cluster point, and for the end user, your database will be online . As I said, this is a problem I encountered in SQL Sever CTP2 because I hope that Microsoft will improve on this when the RTM version is released!

Thanks for your attention!

How Hekaton affects the target recovery time (RTO) of your database