How to setup and use the AMR Tool in SQL Server 2014

The Analysis, Migrate and Report tool (AMR) is part of SQL Server Management Studio 2014 and it aims at helping you evaluate your workloads and how effective/time consuming would the transformation to in-memory be.

Under the covers the tool is nothing more than couple of reports in SSMS, integrated as part of Management Data Warehouse feature. So before you start using it, you must enable the MDW and start the collection jobs.

In SQL Server 2014 they are completely rewritten and now instead of having the well-known Server Activity, Disk Activity and Query Activity collections, we have Stored procedure analysis and Table usage analysis collectors:

AMR_01

Once this is implemented, the ARM reports are accessible through the MDW reports in SSMS:

AMR_02

Note: MDW is my Management Data Warehouse database.

Now let me tell you a bit more about the “tool” – there are three reports, that are useful and that aim helping you identify all tables and procedures you can plan transform in to in-memory OLTP.

The Transaction Performance Analysis Overview report:

AMR_1

This report is simply the starting point where you can start your exploring :)

 

The Stored Procedure analysis report:

AMR_2

This report shows you the most expensive stored procedures in terms of worker time and those are suggested to be the best candidates for migration to in-memory OLTP.

The Table Usage report:

AMR_3

This report is one of my favorite ones – it shows you graphically the best options for tables to transform to in-memory OLTP. On the left axis you have the “Gain” i.e. how much will you gain if you transform. On the bottom axis you have the work that needs to be done in order to transform. So – the best candidates for in-memory OLTP are the top-right ones!

The Table contention analysis report:

AMR_4

This one is basically the same as the previous one, but it shows you the tables that are mostly affected or part of contention. Again you have migration work and gain on the axis.

Well, that’s the AMR tool – nothing more than couple of reports and an activated MDW.

I am now really interested in the logic behind the reports and I’ll start investigating – especially how MS decided how much will it take to migrate a table/SP and what the gain would be. I suppose there are some magic numbers here as well :) But that is for another post ;-)

 

Leave a Reply

Post Navigation