October 13 Microsoft SQL Relay Reading.

Interesting talks at Microsoft SQL Relay Reading, the first SQL Relay event this year.

The Introduction to OLTP In memory from Amanda Ford and another lady from Microsoft covered some items I had not seen before:

1. The in-memory oltp filegroup preallocates more pages that you make think when you first create the filegroup – this will increase the size of your backup. A database with just the in-memory filegroup with no tables will result in a 5GB backup!

2. The in-memory filegroup is made up of pairs of files called checkpoint pairs – a datafile and a delta file.
The datafile contains inserts and the delta file deleted rows.

3. You want to keep 30% of your buckets free.

4. In-memory OLTP uses Multi Version Concurrency Control (MVCC), rows have a start and end timestamp and the current version has an end timestamp of infinity. There are a maximum of 4096 checkpoint pairs hence maximum is 250GB for in-memory data including previous versions.

5. To list the current checkpoint files query sys.dm_db_xtp_checkpoint_files.

6. New data files are listed as “UNDER CONSTRUCTION” under a checkpoint occurs.

7. Trace flag 1851 disables the automerge functionality for in-memory oltp.

8. To manually merge checkpoint files use call sys.sp_xtp_merge_checkpoint_files, status goes to PENDING, checkpoint.

9. Checkouts occur anyway every 512MB of log.

10. Checkpoint files can have a status of “REQUIRED FOR BACKUP”

11. Merge requests can be see by quering sys.dm_db_xtp_merge_requests, once the merge is complete the request_state_desc changes to installed.

12. Garbage collection can be forced by calling sys.sp_xtp_checkpoint_force_garbage_collection similar to sp_filestream_force_garbage_collection.

13. In-memory OLTP limitations include no MERGE statements, no EXISTS query, no MARG (Multiple Active Results Sets), no nested queries, no parallel plans.

14. To determine tables which are suitable to move to In-memory OLTP use AMP (Access,Migrate,Repeat).
There is a new Data Collection Set for this, Transaction Performance collect set and AMR Reports.

Advertisement

---