I apologize to all you folks who attended my Hekaton precon at the SQLPASS Summit last month, who have been eagerly awaiting my answers to some of the unanswered questions. I had got most of the answers sorted out, but I was waiting for one last reply, and put the whole list on the back burner. I just realized today it was now December, and I still hadn’t published this.
So here it is, six questions that I didn’t completely answer during my talk, although for some of them I did make a guess.
1. Do SCHEMA_ONLY tables affect the upper limit of memory available for memory-optimized table?
Remember, there actually is no hard upper limit for the amount of memory you can use for memory-optimized tables. The recommended maximum of 256 GB has to do with the number of CFPs (Checkpoint File Pairs) that can be supported. So in that sense you might be able to have more data in SCHEMA_ONLY tables because you don’t have to worry about the size of the CPFs, as the data in those tables is never written to the files. But you still need to be aware of the overall system memory limit and make sure your machine has enough memory for all the memory_optimized tables, both SCHEMA_AND_DATA and SCHEMA_ONLY) as well as memory for the buffer pool for your disk-based tables. Plus memory for other SQL Server and system needs.
If you have bound a database to a resource pool with a fixed upper limit of memory, all your tables have fit within that memory limit.
2. Is Point-in-time RESTORE possible for databases that have performed transactions on memory-optimized tables? (i.e. can we do a RESTORE LOG WITH STOPAT command?)
Simple answer here: YES
3. Can a database with memory-optimized tables be restored or attached onto a SQL Server 2014 instance using STANDARD Edition?
No. Both restore and attach will require that all filegroups are available, including the filegroups for your memory-optimized tables. And once you have those special filegroups, the database cannot be restored or attached on STANDARD Edition and you get an error. So you really don’t even have to have any memory-optimized tables. As soon as you create a memory-optimized filegroup, the database will no longer be accepted.
I wish I could tell you what the exact error would be, but I don’t have a SQL Server 2014 STANDARD Edition to test on. I have frequently requested that Microsoft provide a feature that allows us to ‘switch off’ Enterprise features if we are using EVALUATION or DEVELOPER Edition, so we can actually test using a base other than ENTERPRISE. But so far, Microsoft has not complied. There are two Connect items that seem to address this this need. The first one at https://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode is marked “Closed as WON’T FIX’ but the other is still active at https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version, so you might want to go give it a vote.
4. Can views on memory-optimized tables be accessed from within a natively compiled procedure? (I think the question was sort of hinting at a workaround where you could create a view on a disk-based table and access that through the native procedure.)
The answer is NO. You can create a view on a memory-optimized table, but you cannot access it from a native procedure. No views at all can be referenced in a native procedure.
5. With REPEATABLE READ Isolation, with which SQL Server needs to guarantee READ STABILITY, what happens if a column that wasn’t read is changed? (i.e. your query read col1 and col2 from Table1, what happens if another query changes col3?)
It’s the row that is important. If Tx1 reads col1 and col2 from Table1 in REPEATABLE READ and doesn’t commit, then Tx2 updates col3 in Table1, when Tx1 commits it will then fail due to REPEATABLE READ violation.
6. Can transactions on memory-optimized tables run in RCSI (READ COMMITTED SNAPSHOT Isolation)?
RCSI is just a variant of READ COMMITTED Isolation. Operations on memory-optimized tables have to run in something higher than READ COMMITTED, so RCSI is moot.
I hope this is useful!