Home > In-MemoryOLTP > In-Memory OLTP –Part 1

In-Memory OLTP –Part 1

image

The Subject is introduced with SQL Server 2014,use of In-Memory can significantly improve the Database and Application performance.In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.

 

To use In-Memory OLTP, you define a heavily accessed table as memory optimized. Memory-optimized-tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables.

In this post we will see how to capture heavily accessed tables to be used as memory optimized using AMR Tool.

The AMR tool is a utility that reads captured workflow statistics to identify which tables and stored procedures would have the best performance gain if migrated to use In-Memory OLTP tables.  The AMR tool analyzes usage data that has been collected in the Management Data Warehouse.  Therefore before you use this tool you will need to set up the Management Data Warehouse and turn on the “Transaction Performance Collection Sets” of data collectors.

The transaction performance collector in SQL Server Management Studio helps you evaluate if In-Memory OLTP will improve your database application’s performance. The transaction performance analysis report also indicates how much work you must do to enable In-Memory OLTP in your application. After you identify a disk-based table to port to In-Memory OLTP, you can use the Memory Optimization Advisor, to help you migrate the table. Similarly, the Native Compilation Advisor will help you port a stored procedure to a natively compiled stored procedure.

 

  • Configure Management Data Warehouse.

  • Configure data collection.

  • Generate transaction performance analysis reports to identify performance-critical tables and stored procedures.

 

Configure Management Data Warehouse

  1. In Object Explorer, expand Management.

  2. Right click Data Collection and select Tasks and then Configure Management Data Warehouse. The Configure Management Data Warehouse Wizard begins.

  3. Click Next to select the database that will act as the Management Data Warehouse.

  4. Click New to create a new database to hold the profile data. After you finish creating the database, click Next in the wizard.

  5. The next step in the wizard lets you add users and logins. You may map logins to role memberships for the MDW instance. This is not required to collect data from the local instance. If you are not collecting data from the local instance, you can grant database role membership mdw_admin to the account that will run transactions that will be profiled. When done, click Next.

  6. Make sure that SQL Server Agent is running.

  7. On the next screen, click Finish to exit the wizard.

Navigate to Management—>Data Collection

image

 

By default Data Collector is set to disabled, Enable it.

 

image

image

Right Click Data Collector—> Tasks—> Configure Management Data Warehouse

image

image

image

image

image

 

Configure data collection

Data collection requires SQL Server Agent to be started. You only need to configure one data collector on a server.

A data collector can be configured on a SQL Server 2008 or later version of SQL Server.

To configure data collection to upload to a Management Data Warehouse database on the same instance,

  1. In Object Explorer, expand Management.

  2. Right click Data Collection, select Tasks, and then Configure Data Collection. The Configure Data Collection Wizard begins.

  3. Click Next to select the database that will collect the profile data.

  4. Select the current SQL Server instance and a Management Data Warehouse database on that instance.

  5. In the box labeled Select data collector sets you want to enable, select Transaction Performance Collection Sets. Click Next when done.

  6. Verify the selections. Click Back to modify the settings. Click Finish when done.

 

image

image

image

image

image

 

Generate Reports

You can generate transaction performance analysis reports by right clicking the database of the Management Data Warehouse and selecting Reports, then Management Data Warehouse, and then Transaction Performance Analysis Overview.

image

Categories: In-MemoryOLTP
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: