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





Comments For This Article




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

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



download


Recommended Reading

Understanding SQL Server Memory-Optimized Tables Hash Indexes

Quick Guide to Installing SQL Server 2014

Determine BUCKET_COUNT for Hash Indexes for SQL Server Memory Optimized Tables

Getting started with SQL Server 2014 In-Memory OLTP

Compare SQL Server Performance of In-Memory vs Disk Tables





get free sql tips
agree to terms


Learn more about SQL Server tools