神刀安全网

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


By:Mohammed Moinudheen |  |   Related Tips: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.

Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 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.

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

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.

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

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.

Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 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.

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

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

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

SQL Server 2016

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

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.

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

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 Update:

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

About the author

Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014 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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮