Potential data latency with AlwaysOn Availability Groups and Memory Optimized Tables

Today I was testing a scenario with a readable secondary in an Availability Group (AG). The database in the AG contained a memory-optimized table and I was testing read-only query support on the secondary.

The mystery

While changes for on-disk tables are replicated near-instantaneously (of course in my test setup there was no other overhead) and were almost immediately visible on the secondary replica, I was surprised to see that it was not the case with the memory-optimized table.

For example, I would insert a row into the table at the primary replica. That row would not appear in the readable secondary, not even after waiting a few seconds. Note that there was no other workload related to memory optimized tables at the time.

Interestingly, if I would insert a new row in the table on the primary, it would cause the previously inserted row to show up at the secondary! Smile I was quite puzzled to see this behavior. On a hunch, I issued a manual CHECKPOINT on the primary. This ‘flushed’ the queue and I was able to see both rows on the secondary.

Truth revealed!

Since this was a bit strange, I dug around a bit and found a gem in the documentation which explains this behavior. There is something called a ‘safe timestamp’ which is used on the readable secondary to only return rows older than a ‘safe point’ which is normally updated by the garbage collection thread on the primary.

Normally on an active system, this safe timestamp would be periodically changed and periodically sent across to the secondary on a regular basis. But if there is not much activity on memory optimized tables in the database (like in my case) then there is an inordinate delay in sending this safe timestamp across.

Conclusion

If such data latency for in-memory tables is not tolerable on the readable secondary, the product team recommends the usage of ‘dummy’ transactions to propagate this safe timestamp more periodically:

Changes made by transactions on the primary replica since the last safe-timestamp update are not visible on the secondary replica till the next transmission and update of the safe-timestamp. If transactional activity on the primary replica stops before the internal threshold for safe-timestamp update is crossed, the changes made since the last update to safe-timestamp will not be visible on the secondary replica. To alleviate this issue, you may need to run a few DML transactions on a dummy durable memory-optimized table on the primary replica. Alternatively, though not recommended, you can force shipping of safe-timestamp by running a manual checkpoint.

Be aware of this behavior if you plan to use readable secondary and have some phases where there is minimal activity in the system but you still need the changes to in-memory tables to show up on the secondary as soon as possible.

Advertisements

Error message “Invalid column name ‘uses_native_compilation’” when scripting objects in SQL 2014 CTP1

Due to the In-Memory OLTP enhancements in SQL 2014, there is metadata support added in the form of a new column: uses_native_compilation in the sys.all_sql_objects catalog view. When you use SMO to retrieve a database object (typically when you script it using the SSMS Generate Scripts wizard, for example,) it retrieves properties from such catalog views to populate the corresponding SMO properties such as IsSystemObject and IsNativelyCompiled.

Unfortunately the code in the CTP1 (after all, it is non-production ready status for a good reason Smile) has an issue because it does not consider that IsNativelyCompiled property is absent from the catalog views for older releases of SQL Server. You may see this today as an error when scripting objects from Management Studio 2014, or if you directly use SMO to script objects as well. Note that this issue is only when connected to SQL 2012 or SQL 2008; scripting objects for SQL 2014 instances will work just fine.

FYI the relevant portion of the call stack might look like this:

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. 
System.Data.SqlClient.SqlException: Invalid column name ‘uses_native_compilation’.

… at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveSprocDependencies(List`1 schemaboundList)

So if you are getting this issue, please vote on the Connect Bug I filed for this issue, so that the concerned team in the product group can prioritize this and fix it. Here is the link again in plain text format:

https://connect.microsoft.com/SQLServer/feedback/details/792637/system-data-sqlclient-sqlexception-invalid-column-name-uses-native-compilation-when-scripting-older

SQL 2014 In-Memory OLTP ‘Hekaton’: training videos and white papers

Personally, the feature I am most excited about in SQL Server 2014 is the In-Memory OLTP (codenamed ‘Hekaton’) feature. Since SQL Server CTP1 was released, a lot of my interactions with colleagues and customers have revolved around this new feature and the one question everyone has is, how do I get started? Well, here’s a readiness roadmap I have put together to help you!

Videos

Sunil Agarwal, Principal Program Manager from the SQL Server product team, has been one of the key people steering the work towards its eventual public unveiling in CTP1. I’ve been fortunate enough to interact with Sunil on a few occasions and I marvel at his passion and energy. He recently presented three sessions at TechEd 2013 North America, which are now available online, complete with slides and video!

A related set of deep sessions were delivered by Jos de Bruijn (also a key member of the Hekaton PM team) at TechEd Europe 2013. Jos is a PhD and his technical depth and passion to share information is clearly evident – take a look for yourself!

 

In-Memory OLTP Code Samples

Here are some useful samples from MSDN.

 

General Reading

 

Research Papers

All the inside scoops from the team which made it happen!

If you find more resources which you would like to share, please post a comment or contact me through this blog to let me know, I will gladly link them up in this roadmap!

Error 41342 when creating In-Memory OLTP (‘Hekaton’) filegroup

A Twitter conversation unearthed a specific requirement of using in-memory OLTP features, namely the processor instruction set requirements. The user was trying to create a ‘Hekaton’ database using the sample script from here, and was getting error message 41342:

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors.

This error is documented at our (brand new) SQL 2014 Books Online page. According to the page, memory-optimized tables require a processor model that supports atomic compare-and-exchange operations on 128-bit values. In the Intel x86-64 instruction set, such 128-bit atomic operations are accomplished by the CMPXCHG16B (assembly language in case you were wondering Smile) instruction.

With this background, and knowing that the user was running VirtualBox, I thought this is a problem with the CPU emulation code in VirtualBox. Personally, I am fortunate to be running Windows 8 Hyper-V so I would never see this issue!

For VirtualBox, it turns out that we need to adjust the virtualization software settings as per this thread. More information is also available at this link. From my understanding the VirtualBox setting is only effective if the physical hardware allows it as well. In other cases, if you get this error on physical hardware, check if it is a processor which supports the CMPXCHG16B instruction (older AMD processors might not support it, for example.)

Hope this helps!