SQL Server 2014 CTP1 has a tool to help you decide which tables and stored procedures you can move to Hekaton or In-Memory OLTP. In this post I will give you a quick tour of this tool, the AMR (Analysis, Migration and Reporting). The AMR tool is integrated with the Data Collector and to enable it you have to enable the new Transaction Performance Collection Sets on the Configure Data Collection Wizard as shown next.
This will create two new collection sets, Stored Procedure Usage Analysis and Table Usage Analysis, in addition to the three system data collections sets previously available with the Data Collector.
Once you configure the Transaction Performance Collection Sets on the Data Collector you are ready to test the AMR tool. First, you need to create some database activity. In my case I am testing with the following stored procedures on a copy of AdventureWorks2012.
CREATE PROCEDURE test1 AS SELECT * FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE ProductID = 870 GO CREATE PROCEDURE test2 AS SELECT ProductID, SalesOrderID, COUNT(*) FROM Sales.SalesOrderDetail GROUP BY ProductID, SalesOrderID
Execute the procedures multiple times, for example, 20 or 30.
EXEC test1 GO EXEC test2 GO
After you create some database activity you may have to wait for the next Data Collector upload job to execute. For example, the Stored Procedure Usage Analysis upload job runs every 30 minutes and the Table Usage Analysis runs every 15 minutes. You could also run these jobs (collection_set_5_upload and collection_set_6_upload respectively) manually.
To access the AMR reports right-click your MDW database, select Reports, Management Data Warehouse and Transaction Performance Analysis Overview. This is the main AMR tool report.
Clicking on Usage Analysis on the Tables Analysis section will show the Recommended Tables Based on Usage report showing the top candidate tables for memory optimization based on the access patterns of the workload. The report graph shows the performance benefits on memory optimization along with the migration effort required to move tables to In-Memory OLTP based on how many unsupported features the table uses.
Selecting Tables Analysis, Contention Analysis on the main report will show the Recommended Tables Based on Contention report as shown next.
In both cases, the reports recommend to prioritize the tables in the top right corner of the graph. Clicking on a table name on any of previous reports will show the table performance statistics details, including both table usage and contention statistics. An example is shown next.
On the other hand, selecting on Stored Procedure Analysis, Usage Analysis on the main report will take us to the Recommended Stored Procedures Based on Usage report, showed next. This report will show the top stored procedures based on total worker time in milliseconds.
Similarly, clicking on a specific stored procedure will show the stored procedure execution statistics detail and the tables it references. An example for the test2 stored procedure is next.
You can also click on the listed tables to get the table’s performance statistics report shown previously.
In summary the AMR tool will be very helpful in providing recommendations as to which tables and stored procedures you might want to consider migrating into Hekaton. You could even upgrade your database to SQL Server 2014 and run this tool for analysis of your real performance data and iteratively move tables and stored procedures to In-Memory OLTP as required.
About the author
- More at
Does the System Data Collection Sets have to be enabled as well? Or is Transaction Performance Collection Sets sufficient? . Because although for Stored Procedures Based On Usage I get procedures for my database, I get No Data Available for Recommended Tables Based on Contention when I select my database, but there are results for the ReportServer$SQL2014?
Hi Mladen,
I just tested on another SQL Server 2014 CTP1 instance and it worked without the System Data Collection sets. Perhaps we just have to wait to see if this will be a supported configuration.
Ben
[…] A Tour of the Hekaton AMR Tool […]
Thanks for your reply . I made some progress, after using non-cached collection at least it does not say No Data Available but
In the Recommended Tables Based on Contention ( this time I used exactly your example with AdentureWorks2012 and EXEC test1 ) the tables now appear but when I click on Sales Order Details I get Error – A data source instance has not been supplied for the data source ‘Scan Details’ . Since this is the same CTP1 , same database, same procedure – this looks strange . Any idea?
[…] A Tour of the Hekaton AMR Tool […]
[…] A Tour of the Hekaton AMR Tool – Benjamin Nevarez (Blog|Twitter) shows us how to use the AMR Tool to identify good candidate objects for migration to HEKATON in SQL Server 2014. […]
[…] A Tour of the Hekaton AMR Tool – Benjamin Nevarez (Blog|Twitter) shows us how to use the AMR Tool to identify good candidate objects for migration to HEKATON in SQL Server 2014. […]
Can I analyze a remote instance in SQL 2008 R2?. I need information to decide the migration
Hi Roberto,
That is a great question: If the purpose of the AMR tool is to help determine if your application would benefit from Hekaton, it would make sense to run it against a previous version of SQL Server. But at least at the moment this tool is only available inside a SQL Server 2014 CTP1 installation and it does not provide the choice to install it on some other instance. I don’t know if this possibility would be available later.
Thanks,
Ben
Hi Roberto and Benjamin,
The AMR tool can profile a remote instance in SQL 2008 R2. In fact, it should support any version of SQL Server with Data Collection capabilities (that is, SQL 2008 and up). This was a conscious design decision made to give you the capabilities to monitor your existing installations.
To collect on a remote instance of any supported SQL Version, simply connect to that instance on the first screen of the wizard. You will then need to check the check box “Use a SQL Server Agent Proxy for Remote Uploads” at the bottom, and set up the necessary credentials and proxies you need to conduct this remote collection.
You can also connect to your instance and conduct local collection with the SQL 2014 CTP1 SSMS much like you do on a SQL 2014 CTP1 instance.
Note, however, that due to the limitations of the AMR Tool’s architecture, the MDW database to which the AMR data collectors are uploading their collected data must be on a SQL Server instance of the same version number or lower. That is, you cannot have your MDW reside on a SQL 2014 CTP1 instance and collect from a SQL 2008 R2 instance.
Thanks,
George Li
SQL Server
Great! Thanks for clarifying this George.
Regards,
Ben
[…] Nevarez has a nice tutorial on using the AMR Tool. Another good resource is the Hekaton whitepaper by Kalen Delaney. If you don’t already […]
I get the following error when I click on “Usage Analysis”…
A data source instance has not been supplied for the data source ‘ScanOverview’
Not sure how to correct that.
I am also facing the same error as above.
When I click on ‘Usage Analysis’ The error : A data source instance has not been supplied for the data source ‘ScanOverview’.
Any pointers to solve this issue ?
facing exactly same issue : A data source instance has not been supplied for the data source ‘ScanOverview’.
any pointer to solve this issue please?
Hi George Li
I select “Use a SQL Server Agent proxy for remote uploads” and I can connect to the remote instance, but I can’t select database in the remote 2008R2 instance. The database dropdown is empty.
If I select the local instance, the dropdown shows all the databases.
Any idea what’s wrong?
Thanks ahead,
Ronnie