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.