Now that we have explored the possibilities that memory-optimized tables offer, it would be fantastic to be able to somehow evaluate our existing databases. Ideally this evaluation would show how many tables or stored procedures could potentially be converted from traditional disk-based objects into ultra-fast memory-optimized objects.
Luckily for us, Microsoft has also provided us with some help here too. Inside SQL Server Management Studio are two interesting standard reports that help us to analyze our databases and see how we can benefit from the memory-optimized objects.
The first report is the Transaction Performance Analysis Overview. This report allows us to quickly identify possible candidates for a move from disk-based to memory-optimized. We can reach this report by navigating through SQL Server Management Studio, shown as follows:
Once we select the standard Transaction Performance Analysis Overview report, we are greeted with a start page asking whether we want to evaluate tables or stored procedures. Our example queries are focused on tables, so choosing Tables Analysis will give us a little sample data for illustration purposes:
Transaction Performance Analysis Overview Report
Clicking on Tables Analysis brings up a detailed report of all tables in the database that are eligible candidates for a migration to memory-optimized tables.
As the report states, eligible candidate tables are shown on this report. The higher up and the further to the right of the table, the higher the recommendation for a conversion to a memory-optimized table. Based on our sample queries, it should be no surprise that the table called DiskBasedTable is an ideal candidate for memory-optimized storage. The data types are perfect, the behavior of the data movement is ideal. There is pretty much no reason at this point to not migrate this table to the In-Memory OLTP engine:
In-Memory OLTP Table analysis report
A further click on the table name pulls up a report based on the table itself, providing details on lock and latch statistics for the table, along with recommendations on what indexes are currently available and what index these should be migrated into (either hash or non-clustered):
Table-level report for the Transaction Performance Analysis Overview