An important new functionality of Microsoft SQL Server 2014 is the In-Memory OLTP engine, which enable you to load Tables and also Stored Procedures In-Memory for a very fast response time.
The goal is not to load all the database In-Memory but just Tables with critical performances and Stored Procedures with complex logical calculations.

To identify which Tables or Stored Procedures will give you the best performance gain after migration; Microsoft SQL Server 2014 has introduced a new tool: Analysis, Migrate and Report (AMR).

This tool will collect statistics about Tables and Stored Procedures in order to analyze the current workload. It will give you advice on the migration benefits of the different Tables or Stored Procedures. It will also give you an overview of the time/work needed to push Tables or Stored Procedures In-Memory.

In the following article I will show you how to setup and use this Tool.

Configuration of the Management Data Warehouse

The AMR Tool is built into SQL Server Management Studio.
It consists of:

  • Reports which come from a Management Data Warehouse and give recommendations about tables and Stored procedures which could be migrated to In-Memory OLTP
  • Memory Optimization Advisor which will help you during the migration process of a disk table to a Memory Optimized table
  • Native Compilation Advisor which will help you migrated a Stored Procedure to a Natively Compiled Stored Procedure

AMR Tool leverages the Management Data Warehouse and the Data Collector, with his new Transaction Performance Collection Sets, for gathering information about workloads.

AMR will analyze the collected data and provide recommendations via reports.
First, we have to configure the Management Data Warehouse.

To start the configuration, open Management Studio, go to Object Explorer, then Management folder, and right-click on Data Collection. Then select Tasks and click on Configure Management Data Warehouse as shown below:

AMR_picture1.png

On the Configure Management Data Warehouse Storage screen, enter the server name and the database name where you Management Data Warehouse will be host. AMR tool will collect, via its collection sets, data from three Dynamic Management Views every fifteen minutes and will save those data in the MDW database. Uploading data will have minimal performance impact.

If you already have a database, enter her name. If not, click the New button to create a new one.
On the Map logins and Users page, if needed, you can map a user to administer, read, or write the Data Management Warehouse database.
Verify the Management Data Warehouse configuration and proceed to the configuration.
When the configuration of the Management Data Warehouse has been successfully finalized, you should see the following screen:

 AMR_picture2.png

The Management Data Warehouse setup is finished.

Configuration of the Data collection

Take care, SQL Server agent has to be started on the instance that will collect the data.
To collect data, we will enable the new Transaction Performance Collection set which is composed of two new collection sets:

  • Stored Procedure Usage Analysis: used to capture statistics about Stored Procedures which could be migrate to Natively Compiled Stored Procedures
  • Table Usage Analysis: takes information about disk based tables for a future migration to Memory Optimized tables.

 In order to configure the Data Collection, go to Object Explorer, then Management folder, right-click on Data Collection, select Tasks, and click on Configure Data Collection, as shown below:

AMR_picture3.png

After having skipped the Welcome page, you have to select a server and a database name that will host the Management Data Warehouse.

Now, you need to select the data collector sets. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets. This will collect statistics for transaction performance issues.

If the Management Data Warehouse is located on a different SQL Server instance from the data collector and SQL Server agent is not running under a domain account which has dc_admin permissions on the remote instance you have to use a SQL Server Agent proxy.

AMR_picture4.png

After having performed the Data Collection configuration with success, you will have an enabled Data Collection which will collect information about all user databases.

Via SQL Server Agent Folder Jobs, you are now able to see new collection jobs used to collect data from your workloads with names like collection_set_N_collection and jobs used to populate our new Management Data Warehouse database with names like collection_set_N_upload.

It is also good to know that upload jobs will be run every thirty minutes for Stored Procedure Usage Analysis (job: collection_set_5_upload) and every fifteen minutes for Table Usage Analysis (job: collection_set_6_upload). So if you want to speed your upload, you can execute these jobs manually.
 

Reports

To access recommendations based on collected information about all user databases on the workload server, you have to right-click on your Management Data Warehouse database, select Reports, then Management Data Warehouse, and finally Transaction Performance Analysis.

In the Transaction Performance Analysis Overview report, you can choose among three reports, depending on what you want or need:

  • Usage analysis for tables
  • Contention analysis for tables
  • Usage analysis for Stored procedures

AMR_picture5.png

Usage analysis for tables

This report, based on table’s usage, shows you best candidate tables that could be pushed In-Memory.
On the left side of the report you have the possibility to select the database and the number of tables you would like to see.
The central part is a chart with two axis:

  • Horizontal axis represents significant to minimal works needed to migrate a table to In-Memory OLTP
  • Vertical axis represents increasing gains you will benefit after having moved the table to In-Memory OLTP

The best part of this graph is the top right corner, which shows tables that could be migrated easily In-Memory, but will give you the best performance gain.

AMR_picture6.png

When you click a table point on the graph, you will access a more detailed statistics report.

This report shows access characteristics (lookup statistics, range scan statistics, etc.) and also contention statistics (latches statistics, lock statistics, etc.) of the concerning table and for the monitoring time period of your instance’s workload with the Transaction Performance Collection Set.

AMR_picture7.png

Contention analysis for table

This report is based on table’s contention instead of usage. It shows you best candidate tables that could be migrated In-Memory.
As before, on the left side of the report you have the possibility to select the database and the number of tables you would like to see.
The central part is a chart with two axis:

  • Horizontal axis represents significant to minimal works needed to migrate a table to In-Memory OLTP
  • Vertical axis represents increasing gains you will benefit after having moved the table to In-Memory OLTP

The best part of this graph is the top right corner, showing tables that can be easily migrated In-Memory, but will give you best performance gain.

AMR_picture8.png

As for the usage analysis report, you can also click a table name on the graph to see the statistics details of the table.

Usage analysis for Stored Procedures

This report contains the top candidate stored procedures for an In-Memory OLTP migration with regards to their usage. This report is based on the Total CPU Time.

You also have the possibility to select the database and the number of stored procedure you would like to see.

AMR_picture9.png

If you want to see the usage statistics for a specific stored procedure, you can click on the blue bar. You will then have a more detailed report.

AMR_picture10.png

Now, you know which Tables and Stored Procedures will give you best performance gain after migration to In-Memory OLTP.
AMR provide us two Advisors which will help you to manage the transformation of your disk tables to Memory Optimized Tables as well as your Stored Procedures to Natively Compiled Stored Procedures. To know more about those advisors, please have a look to my blog.


Thumbnail [60x60]
by
Stéphane Savorgnano