{"id":5641,"date":"2015-09-28T09:48:17","date_gmt":"2015-09-28T07:48:17","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-transaction-performance-analysis-overview-in-memory-oltp-migration-checklists\/"},"modified":"2015-09-28T09:48:17","modified_gmt":"2015-09-28T07:48:17","slug":"sql-server-2016-transaction-performance-analysis-overview-in-memory-oltp-migration-checklists","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-transaction-performance-analysis-overview-in-memory-oltp-migration-checklists\/","title":{"rendered":"SQL Server 2016: Transaction Performance Analysis Overview & In-Memory OLTP Migration Checklists"},"content":{"rendered":"

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\u2026 (I wrote a blog here<\/a> for this configuration and an article on SQL Magazine here<\/a>)
\nWith SQL Server 2016, actually in is CTP2.3 release, Microsoft introduces a new and easier mechanism for end-user and DBA.
\nFor each database of our instance, we are able to generate a report directly by clicking Object Explorer, <\/strong>selecting\u00a0Reports<\/strong>, then\u00a0Standard Reports<\/strong>, and then\u00a0Transaction Performance Analysis Overview:<\/strong><\/p>\n

\"TransactionPerformanceAnalysisOverview1\"<\/a><\/p>\n

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:<\/p>\n

\"TransactionPerformanceAnalysisOverview2\"<\/a><\/p>\n

The report for table or Stored Procedure as the same look and field than the AMR \u2018s one. For the table we don\u2019t have any more two reports: Recommended Tables Based on usage and Recommended Tables Based on contention but just the first one:<\/p>\n

\"TransactionPerformanceAnalysisOverview3\"<\/a><\/p>\n

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:<\/p>\n

\"TransactionPerformanceAnalysisOverview4\"<\/a><\/p>\n

For Stored Procedure, the same screen exists and is named Recommended Stored Procedures Based on Usage:<\/p>\n

\"TransactionPerformanceAnalysisOverview5\"<\/a><\/p>\n

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:<\/p>\n

\"TransactionPerformanceAnalysisOverview6\"<\/a><\/p>\n

Those both reports will be very helpful in order to determine which tables or Stored Procedure could be migrate to In-Memory OLTP.
\nAfter 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.<\/p>\n

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:<\/p>\n

\"TransactionPerformanceAnalysisOverview10\"<\/a><\/p>\n

After a Welcome screen, we have to select:<\/p>\n