Benjamin Nevarez Rotating Header Image

A Tour of the Hekaton AMR Tool

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.

clip_image002

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.

clip_image004

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.

clip_image006

Selecting Tables Analysis, Contention Analysis on the main report will show the Recommended Tables Based on Contention report as shown next.

clip_image008

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.

clip_image010

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.

clip_image012

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.

clip_image014

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

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin’s blog is at http://www.benjaminnevarez.com, can be reached by e-mail at admin at benjaminnevarez dot com, on twitter at @BenjaminNevarez and on .

15 Comments

  1. Mladen Andrijasevic says:

    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?

  2. 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

  3. Mladen Andrijasevic says:

    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?

  4. […] 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. […]

  5. […] 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. […]

  6. Roberto says:

    Can I analyze a remote instance in SQL 2008 R2?. I need information to decide the migration

  7. 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

  8. George Li [MSFT] says:

    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

  9. Great! Thanks for clarifying this George.

    Regards,

    Ben

  10. […] 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 […]

  11. Craig says:

    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.

  12. Priyanka K says:

    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 ?

  13. jeetendra says:

    facing exactly same issue : A data source instance has not been supplied for the data source ‘ScanOverview’.

    any pointer to solve this issue please?

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>