Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014


By:   |   Updated: 2016-03-24   |   Comments (1)   |   Related: More > In-Memory OLTP

Problem

I have heard of the AMR (Analysis, Migrate and Report) tool in SQL Server 2014. There is also a tip on Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables describing the use of this tool. I would like to know if there are any enhancements to this tool in SQL Server 2016.

Solution

From the previous tip on MSSQLTips.com - Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables, you would have seen that the process to set up the AMR tool is a bit lengthy in SQL Server 2014. In SQL Server 2014, there is a requirement to configure the Management Data Warehouse (MDW) and to set up data collection to perform collection of the data using dynamic management views (DMVs). However, with SQL Server 2016 there is no need for this anymore. It is as simple as just right clicking and running the required reports.

Data Collection Differences

SQL Server 2014

In SQL Server 2014, when you try to configure data collection after creating the Management Data Warehouse (MDW), you will see the option to enable "Transaction Performance Collection Sets". Refer to the screenshot below from SQL Server 2014.

SQL Server 2014 Configure Data Collection Wizard

SQL Server 2016

However, in SQL Server 2016, you will not see the option to enable "Transaction Performance Collection Sets". Refer to the screenshot below from SQL Server 2016.

SQL Server 2016 Configure Data Collection Wizard

Transaction Performance Analysis Report Differences

SQL Server 2016

In SQL Server 2016, the "Transaction Performance Analysis" report is integrated with SQL Server Management Studio (SSMS). You just need to right click on the database and go to Reports, Standard Reports and click on "Transaction Performance Analysis Overview". Refer to the screenshot below from SQL Server 2016.

Navigation to the Transaction Performance Analysis Overview in SQL Server 2016

SQL Server 2014

In SQL Server 2014, you have to first set up the AMR (Analysis, Migrate and Report) tool by configuring the Management Data Warehouse (MDW) and enable data collection as shown in Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables. Then if you right click on the database that has been setup you would see this report under Reports > Management Data Warehouse as shown below.

Navigation to the Transaction Performance Analysis Overview in SQL Server 2014

In SQL Server 2014, if you right click on a database not setup for the Management Data Warehouse and AMR you will not see the an option for Management Data Warehouse reports as shown below.

Reports for non Master Datawarehouse that is not configured for AMR

Report Differences

In order to generate a sample workload, first download and restore the AdventureWorks database as shown in this tip Install Your Own Copy of the SQL Server AdventureWorks2014 Database and then use the scripts provided in this article Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables. . Once the workload is generated, click on the option to view the "Transactional Performance Analysis" reports.

When you view the reports, there are just minor differences between SQL Server 2014 and SQL Server 2016.

SQL Server 2014

Transaction Performance Analysis Overview Report in SQL Server 2014

SQL Server 2016

Transaction Performance Analysis Overview Report in SQL Server 2016

For specific details, refer to the previous tip Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables.

There is not much different between the individual performance analysis reports for SQL Server 2014 and SQL Server 2016. The big difference is how the data is collected and how you get to the reports.

Details Report Differences

For the most part the data is similar between SQL Server 2014 and SQL Server 2016.

SQL Server 2014

On the left you will see a list of databases where data has been collected.  You will need to select a database to see that specific data for that database as shown below.

Transactional Performance Analysis with the Recommended Tables Based on Contention

SQL Server 2016

In SQL Server 2016, the reports are run per database, so the option to select a database as shown above is not there.

Next Steps
  • Try using the AMR (Analysis, Migrate and Report) tool on SQL Server 2014 and SQL Server 2016.
  • Compare the differences using this tool on both SQL Server 2014 and SQL Server 2016 and see which is easier from your perspective.


Last Updated: 2016-03-24


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, March 28, 2016 - 1:48:44 PM - Srinath Back To Top

Well explaind and excellent article,Moinu!!! Please keep up the good work!!!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools