SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

How Hekaton will impact the RTO of your database

(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



Leave a comment on the original post [www.sqlpassion.at, opens in a new window]

Loading comments...