SQL Server 2014’s new In-Memory OLTP feature can potentially revolutionize your OLTP workloads by providing big performance increases with little or no changes to your servers or application code. Microsoft’s In-Memory OLTP delivers performance improvements of 2x to 30x depending on the application. As a brief reminder, SQL server 2014’s In-Memory OLTP works by memory-optimizing select tables and stored procedures. The In-memory OLTP engine uses a new optimistic multi-version concurrency control design that is lock and latch free. This eliminates wait states in the engine and allows transactions to be processes at in-memory speeds.
That all sounds great but how do you migrate your database over to take advantage of In-Memory OLTP? Well, you could do in manually using SQL Server Management Studio (SSMS) of T-SQL but for most organizations there’s a better way – the Analysis Migrate Report (AMR) Tool. AMR takes care of the heavy lifting involved that required to both analyze your database workloads and schema making recommendations about the tables and stored procedures that could be memory optimized. But beyond that it also has reports that point out any potential problems that you might encounter as well as tools that you can use to actually memory-optimize your traditional tables and stored procedure.
Read the rest of this article for more detailed information on using AMR to migrate SQL Server databases to in-memory OLTP and watch these two videos for a summary:
While it’s possible that you can take advantage of In-Memory OLTP with no application changes, it’s more likely that you might have to make some schema or applications changes in order to use it. Before jumping into any database conversion you should definitely look into the In-Memory OLTP requirements. Not all data types and database features are supported. There’s no support for xml, text, ntext, image data types or for database mirroring, TDE or snapshots. For more information on the data types and features that are supported by In-Memory OLTP you can refer to Supported Data Types and Supported SQL Server Features.
Getting started with AMR: capturing workloads
The first step to getting started with AMR is to capture a running SQL Server workload to a Master Data Warehouse (MDW). If you don’t already have one you can create an MDW with SSMS by opening Object Explorer then expanding the Management node. Next, right-click Data Collection and select Tasks, then choose Configure Management Data Warehouse. This will open the Configure Data Collection Wizard that you can see in Figure 1.
Figure 1 – Running data collection with AMR
Give your MDW a name. In this example I used AMR-MDW. Then finish the wizard to create the AMR-MDW database.
To start collecting data, use Object Explorer to expand the Management node. Next, right-click Data Collection, select Tasks, and choose Configure Data Collection. The wizard displays a dialog that lets you select the current SQL Server instance and the AMR-MDW database that was created earlier. After those selections are made, select the Transaction Performance Collection Sets check box. Click Next and Finish to start the data collection.
The AMR tool collects usage data every 15 minutes and Microsoft recommends that you run the AMR tool for at least one hour. That said, the longer you run the tool, the more representative your captured statistics will be of your actual production workload. It stores the data in two collections named Table Usage Analysis and Stored Procedure Analysis.
Analyzing your workload data
After collecting the SQL Server instance usage data you can run the Transaction Performance Analysis Overview report to get a recommendation of the tables and stored procedures to migrate. To run the Transaction Performance Analysis Overview, use Object Explorer to right-click the AMR-MDW database then select Reports, Management Data Warehouse, and Transaction Performance Analysis Overview. The Transaction Performance Analysis Overview provides information about all user databases on the server. You can drill into each database and display the different recommendations like you can see in Figure 2.
Figure 2 – The Transaction Performance Analysis Overview report
After running the reports and identifying the tables and stored procedures you want to memory-optimize, you can use the Memory Optimization Advisor to migrate the selected tables and stored procedures. To memory-optimize an object, open Object Explorer, navigate to the object you want to migrate then right-click the object and choose Memory Optimization Advisor. This will run the Table Memory Optimization Advisor that you can see in Figure 3.
Figure 3—Memory-optimizing a Table
The wizard analyzes the table and points out any issues that you may need to change or correct before migrating the table. If there are no issues you can just step through the wizard and the table will be memory-optimized when it’s finished. There’s another wizard for migrating stored procedures.
You can learn more about the SQL Server 2014 AMR tool at New AMR Tool: Simplifying the Migration to In-Memory OLTP.
Leave a Reply