(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)

Over the weekend I have encountered a very interesting side effect of Hekaton in SQL Server 2014, which unfortunately impacts your RTO (Recovery Time Objective) of your database in a negative way. As you know, Hekaton creates for every native compiled Table and Stored Procedure a DLL, which contains the native implementation in C code. These DLL files are loaded into the process space of sqlservr.exe. You can see all the current loaded Hekaton tables from the DMV sys.dm_os_loaded_modules with the following query:

SELECT * FROM sys.dm_os_loaded_modules
WHERE description LIKE 'XTP%'

The side effect that I have currently encountered in CTP2 is the fact that the loaded DLL files of Hekaton ARE NOT unloaded when you drop the corresponding Table or Stored Procedure. Imagine you are creating a Native Compiled Stored Procedure, and at some point in time you want to drop that Stored Procedure to recreate it afterwards to get a better Native Compiled Execution Plan (Recompilations during the runtime are currently not supported in Hekaton). In that case the old implementation of your Stored Procedure is still present in the process space of sqlservr.exe, and consumes additional memory. The same happens when you drop a Table, the DLL itself is still present in memory!

The only way to get rid of these additional unnecessary DLL files is to take your WHOLE database offline, and online again. When you afterwards query the DMV sys.dm_os_loaded_modules you will see that only the current implementation of your Native Compiled Table and Stored Procedure is loaded into sqlservr.exe. When you have no dedicated maintenance window available, this is the first big bummer that you encounter with Hekaton.

And now things are really getting nasty: when you are restarting SQL Server, SQL Server compiles and links EVERY DLL that you have generated in the past. Compiling and linking every Hekaton DLL takes some CPU time, and during that phase your database is in the RECOVERY phase, which means it is unaccessible from a users perspective! Even when you try to access a traditional disk based table, you are getting the following error message during that phase:

Msg 922, Level 14, State 1, Line 1
Database ‘HashCollisions’ is being recovered. Waiting until recovery is finished.

You can also see in the folder C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp that every old .c, .obj, and .dll is still present, because they are needed for the next startup of SQL Server.

Imagine you are creating and dropping immediately 50 Native Compiled Tables and Stored Procedures. In that case you are ending up with 100 DLL files, which are compiled and linked during the startup phase of SQL Server. In a test case that I was running against CTP2 of SQL Server 2014, my SQL Server database was online again after 69 seconds! See the following screenshot from the SQL Server Error Log:

So please be aware of these side-effects, because they are impacting your RTO (Recovery Time Objective) in a huge way! Just think you are performing a Cluster failover. In that case EVERY DLL must be compiled and linked on your other Cluster node until your database becomes ONLINE for your end users. As I have said earlier, this is the behavior that I have encountered on CTP2 of SQL Server 2014, so I’m hoping for some improvements in this area until the RTM version is released.

Thanks for reading

-Klaus