As with many of the features in SQL Server, In-Memory OLTP also has some useful tools to assist in working with the technology.
The main desire for many developers is to take their existing database solutions and to convert them to memory-optimized solutions (ideally automatically). Microsoft has provided a few options for investigating a database and delivering feedback on a database design with suggestions of memory-optimized solutions where possible.
The simplest of these tools is the In-Memory OLTP Migration Checklist. This is a wizard provided inside SQL Server Management Studio, which performs a variety of checks against an entire database and provides a set of checklists and reports that inform the user exactly what objects inside a database are candidates for conversion into memory-optimized objects. An overview of the work required is also provided (for example, supported and unsupported data types). The Checklist Wizard can be found by right-clicking a database and choosing Tasks and then Generate In-Memory OLTP Migration Checklists.
The following screenshot illustrates the checklist:
This will then launch the wizard for the chosen database, which guides us through the process of evaluating a database for compatibility with the In-Memory OLTP engine. After choosing the objects to be processed by the wizard, and where to store the resulting report, the wizard can be started.
The wizard then runs through all the chosen objects in the database. Depending on the number of objects, this can take some time, as shown in the following screenshot:
Upon completion, the wizard can be closed and the reports found in the location specified before running the analysis. The resulting report files are HTML files, and there will be one per database object that has been analyzed that shows what changes, if any, are required to make the object compatible with the In-Memory OLTP engine. As we can see in the following screenshot, the report files generated by the wizard provide an overview of what properties of an object are or are not supported by the In-Memory OLTP engine:
Where appropriate, a link is supplied that loads a Books Online support page describing the issue found and possible solutions.
The next tool to assist in evaluating a database for possible object-migration is a standard report delivered with SSMS, the Transaction Performance Analysis Overview. This report does not require the pre-configuration of a database as long as the database being hosted is on a SQL Server 2016 instance or later. The report collects execution statistics for queries in the database that has been chosen and shows which tables or stored procedures are possible candidates for migration. The report will only have meaningful data in it after the database has been in use for a few hours, or even days:
The report loads inside SSMS and offers the choice of table or stored procedure analysis. Both options deliver a similar report, displaying the objects that have been recently accessed. These objects are plotted on a graph showing the potential performance impact of a migration to the In-Memory OLTP engine versus the estimated work required to make the change. The best candidates are objects plotted towards the top-right of the graph, indicating a high impact and minimal migration work.
These two analysis options allow us to get an idea of how much impact and how much work would be involved in migrating to the In-Memory OLTP engine. The results should be considered as a basic indication only and offer by no means a guarantee of accuracy:
