There are two groups of tools that you can use in relation to In-Memory OLTP:
- Memory management tools
- Migration tools
After introducing memory management for In-Memory OLTP, we will discuss both groups of tools.
1. Memory Management for In-Memory OLTP: Overview
When running In-Memory OLTP, the Database Engine must be configured with sufficient memory for memory-optimized tables. Insufficient memory will cause transactions to fail at run time during any operations that require additional memory. This can happen when you execute any DML statement. The In-Memory OLTP memory manager is fully integrated with the Database Engine memory manager and can react to memory pressure by cleaning up old row versions.
A rule of thumb to predict the amount of memory you’ll need for your memory-optimized tables is to have twice the amount of memory that your data will take up. Also, the total memory requirement depends on your workload; if there are a lot of data modifications due to OLTP operations, you’ll need more memory for the row versions. If you’re using mostly queries (i.e., read operations), less memory might be required.
For planning space requirements for indices, the calculation for the hash is easy. Each bucket requires 8 bytes, so you can just compute the number of buckets times 8 bytes. The calculation of the size of your memory-optimized nonclustered indices is difficult, because it depends on the size of the index key as well as the number of tables’ rows.
2. Memory Management Tools
The following tools can be used to manage memory of memory-optimized objects:
- SQL Server Management Studio
- Resource Governor
2.1. Memory Usage Report Using SQL Server Management Studio
SQL Server Management Studio can be used to get a report of the current memory used by memory-optimized tables and their indices. To create such a report, right-click the name of the database containing memory-optimized tables and select Reports | Standard Reports | Memory Usage by Memory Optimized Objects. Management Studio displays a report similar to the one shown in Figure 21-1.
The report in Figure 21-1 shows the space used by the table rows and the indices, as well as the amount of space used by the system. Allocation of memory for hash and nonclustered indices is significantly different: hash indices will have memory allocated for the declared number of buckets as soon as they’re created, and the corresponding report will show memory usage for those indices before any rows are inserted. For nonclustered indices, memory will not be allocated until rows are added, and the memory requirement will depend on the size of the index keys and the number of rows.
2.2. Managing Memory with Resource Governor
As you already know from Chapter 20, Resource Governor is a tool that allows database administrators to specify resource limits for different workloads. Therefore, you can use this tool to assign memory to a resource pool to which a database with memory-optimized tables is bound. After that, all memory-optimized tables in that database can maximally use that amount of memory. In other words, if the storage of all memory-optimized tables needs more memory than is available, some of them will be stored on the disk. The upper limit, which can be assigned to the pool, is 80 percent of the whole memory.
The whole process can be done in two steps, which are given in Examples 21.12 and 21.13.
Example 21.12
CREATE RESOURCE POOL Pool_mem WITH (MAX_MEMORY_PERCENT=50);
ALTER RESOURCE GOVERNOR RECONFIGURE;
Example 21.12 creates a memory pool for the sample2 database using the MAX_ MEMORY_PERCENT clause. This specifies the percentage of the memory that may be allocated to memory-optimized tables in a database associated with the pool.
The second step, shown in Example 21.13, is to bind the databases that you want to manage to the respective pool. You can do this with the stored procedure sp_xtp_bind_db_resource_ pool. Note that one pool may contain many databases, but a database is only associated with one pool at any point in time.
Example 21.13
EXEC sp_xtp_bind_db_resource_pool ‘sample2’, ‘Pool_mem’;
3. Migration Tools for In-Memory OLTP
As you already know, the goal of In-Memory OLTP is to load in memory the tables and stored procedures that are crucial for performance. The Database Engine provides three tools that you can use to identify traditional tables and stored procedures that will provide the best performance gain after migration to In-Memory OLTP:
- Transaction Performance Analysis report
- Memory Optimization Advisor
- Native Compilation Advisor
The Transaction Performance Analysis report collects data concerning tables and stored procedures and analyzes their workloads. Reports based on this information give you recommendations for the best migration candidates among them.
After you identify a disk-based table to port to In-Memory OLTP, you can use the Memory Optimization Advisor to help you to migrate the table. In other words, the Advisor guides you through the process of migration.
The task of the Native Compilation Advisor is to help you to port a stored procedure to the corresponding natively compiled procedure.
NOTE This section discusses only the first component, Transaction Performance Analysis reports. After that, the use of the Memory Optimization Advisor and Native Compilation Advisor is straightforward.
Transaction Performance Analysis reports are installed as a part of SQL Server Management Studio. To generate such reports, right-click the database and select Reports | Standard Reports | Transaction Performance Analysis Overview. Choose Tables Analysis. (The database needs to have an active workload, or a recent run of a workload, in order to generate a meaningful analysis report.)
Figure 21-2 shows the Recommended Tables Based on Usage report concerning the Person .Person table of the AdventureWorks database. (I executed several Transact-SQL statements to create an active workload for this table.) This report tells you generally which tables are the best candidates for migration to In-Memory OLTP based on their usage. On the left side, you can select how many tables you would like to choose from the given database. The chart shows the selected number of tables.
The horizontal axis shows the amount of work needed to transform a table to a memory-optimized one. The vertical axis shows the gains that can be achieved through the transformation. As you can see from Figure 21-3, migration of the Person.Person table gives very good performance gain, but requires significant work for its migration. Therefore, the best candidates for migration are those tables that appear in the top-right corner of the chart.
You can access a detailed report for a table by clicking its name in the chart. The report provides the overall access statistics for the table as well as contention statistics. (The contention statistics display information concerning locks and latches.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.