SQL Server 2016: Transaction Performance Analysis Overview & In-Memory OLTP Migration Checklists

To be able to define which Tables or Stored Procedures will us the best performance gain after migration to In-Memory OLTP, SQL Server 2014 provided a tool called AMR (Analysis Migration and Reporting). This tool was really interesting but we should have, before to use it, to create a Management Data Warehouse and so on… (I wrote a blog here for this configuration and an article on SQL Magazine here)
With SQL Server 2016, actually in is CTP2.3 release, Microsoft introduces a new and easier mechanism for end-user and DBA.
For each database of our instance, we are able to generate a report directly by clicking Object Explorer, selecting Reports, then Standard Reports, and then Transaction Performance Analysis Overview:

We need of course to have a workload for our database or to have run a workload recently in order to have a meaningful report. This report will consist of two part: one for tables and a second one for Stored Procedure:

The report for table or Stored Procedure as the same look and field than the AMR ‘s one. For the table we don’t have any more two reports: Recommended Tables Based on usage and Recommended Tables Based on contention but just the first one:

The same applies if we click on the blue point below the table name. A second screen appears with scan and contention statistics for the selected table. Those statistics come from the DMV sys.dm_db_index_operational_stats. A third section gives the number of migration blockers, so the modification we will have to do on our table to be able to load it in-memory:

For Stored Procedure, the same screen exists and is named Recommended Stored Procedures Based on Usage:

Always the possibility to click on the blue point below the Stored Procedure. Details for the Stored Procedure appears with Execution Statistics coming from the DMV sys.dm_exec_procedure_stats and Tables References coming from the System View sys.sql_expression_dependencies:

Those both reports will be very helpful in order to determine which tables or Stored Procedure could be migrate to In-Memory OLTP.
After having selected which tables or Stored Procedure will be migrated, Memory Optimization and Native Compilation Advisors will help us to work on our tables or Stored Procedure code to eliminate features that are not supported with In-Memory OLTP.

A new possibility with SQL Server 2016 is to generate via those two Advisors an In-Memory OLTP Migration Checklist. This Checklist can be generated for a single disk-based table or an interpreted T-SQL Stored procedures or for multiple ones:

After a Welcome screen, we have to select:

• Where we want to save the checklist
• For which objects we want to generate the checklist, here for the table dbo.command_queue and the Stored Procedure dbo.usp_TMA_master

Click the Next button:

Once is done, a summary screen appears where we can review our selection and also generate the PowerShell scripts commands corresponding to our demand. This one looks like this for me:

$objectsList = "dbo.command_queue","dbo.usp_TMA_master"

for ($i = 0; $i -le $objectsList.count-1; $i++)

{

   $schema = $objectsList[$i].Split(".")[0]

   $object = $objectsList[$i].Split(".")[1]

   Save-SqlMigrationReport -Server 'VMDEMO\SQL2016' -Database 'GestionCom' -Schema $schema -Object $object -FolderPath 'C:\dbi services\InMemory OLTP'

}

Click the Finish Button:

The generation of the Migration Checklist for each selected objects begins and when it is finished the screen below appears.
Click the OK button.

Let’s have a look on the destination folder:

We have two new folder named Stored Procedures and Tables. On each of them, we will retrieve one HTML file per Table or Stored Procedure analyzed:

• for table named is  MigrationAdvisorChecklistReport_TableName.html
• for Stored Procedure named is NativeCompilationAdvisorReport_SPName.html

If we open our file for the table analyzed we see a list of unsupported features for Memory-optimized table and if our table is validated for the described feature:

In case of Failed validation status, a review of the table have to be done before the migration process to Memory-optimized table.
Same for Stored Procedure, where we see a list of T-SQL statements which are not supported for Natively Compiled Stored Procedure:

Those two features will help us to migrate our objects in memory more easily.
But will not avoid minimum work 😉