By: Mohammed Moinudheen | Comments (1) | Related: > 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 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.
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.
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.
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.
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
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.
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.
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
