Several interfaces that allow you to display metadata are enhanced in the new version of the Database Engine to provide information concerning memory-optimized tables. The following subsections describe different possibilities to edit metadata of memory-optimized objects.
1. Property Functions
The OBJECTPROPERTY function is extended with the TablelsMemoryOptimized property. Example 21.8 shows the use of this property.
Example 21.8
SELECT OBJECTPROPERTY(OBJECT_ID(‘sample2.dbo.employee_mem’),
‘TableIsMemoryOptimized’);
Example 21.8 uses the OBJECT_ID function to get the ID of the employee_mem table, because the first parameter of the OBJECTPROPERTY function must be the ID of the table. The result of Example 21.8 is 1, meaning that the TablelsMemoryOptimized property is true for the employee_mem table.
2. Catalog Views and System Stored Procedures
Several catalog views allow you to access information related to memory-optimized tables and their indices. I will discuss three of them: sys.tables, sys.hash_indexes, and sys.sql_modules.
The sys.tables view has three additional columns in relation to memory-optimized tables: durability, durability_desc, and is_memory_optimized. The durability column can contain either of two values, 1 or 0, depending on whether or not the DURABILITY clause is specified in the CREATE (ALTER) TABLE statement. The durability_desc column can contain either of two values, SCHEMA_AND_DATA or SCHEMA_ONLY, and these values correspond to the values of the DURABILITY clause in the CREATE (ALTER) TABLE statement. The is_memory_optimized column also has either of two values: 1 or 0. The value 1 is displayed if a table is memory-optimized. Example 21.9 shows the use of the sys.tables view.
Example 21.9
SELECT object_id ,is_memory_optimized, durability, durability_desc
FROM sys.tables WHERE name = ’employee_mem’;
The result is
The sys.hash_indexes view shows the current hash indices and the hash index properties. This view contains the same columns as the sys.indexes view and an additional column named bucket_count. For more information concerning the sys.indexes view, see Chapter 10.
The most important catalog view in relation to compiled stored procedures is sys.sql_ modules. This view is extended with the uses_native_compilation column, which has the BIT data type. The value true (1) tells you that the module is a compiled stored procedure.
Since SQL Server 2017, the sp_spaceused system stored procedure has been extended to display the disk space reserved and used for databases with at least one MEMORY_ OPTIMIZED filegroup. Example 21.10 shows sp_spaceused in action.
Example 21.10
–Displaying space usage for a DB with MEMORY_OPTIMIZED filegroup
sp_spaceused
@updateusage = ‘FALSE’,
@mode = ‘ALL’,
@oneresultset = ‘1’,
@include_total_xtp_storage = ‘1’
The result is
The two last parameters—@oneresultset and @include_total_xtp_storage—are relevant for the databases with memory-optimized filegroups and memory-optimized tables. The former indicates whether to return a single result set or not. When @oneresultset = 1, the parameter @include_total_xtp_storage determines whether the single result set includes columns for MEMORY_OPTIMIZED_DATA storage. These columns are xtp_precreated and xtp_pending_trunc (see the result of Example 21.10).
3. Dynamic Management Views
Generally, dynamic management views allow you to access metadata information that changes dynamically. While the content of memory changes steadily, DMVs are the right tool to get the necessary information about the storage of memory-optimized tables and compiled stored procedures.
This section describes several DMVs introduced by Microsoft to display information about memory-optimized database objects:
- dm_db_xtp_table_memory_stats
- dm_db_xtp_object_stats
- dm_db_xtp_hash_index_stats
The sys.dm_db_xtp_table_memory_stats view returns memory usage statistics for each memory-optimized table (user and system) in the current database. The system tables have negative object IDs and are used to store run-time information for In-Memory OLTP. Unlike user objects, system tables are internal and only exist in memory; therefore, they are not visible through catalog views. System tables are used to store information such as metadata for all data files and delta files in storage, merge requests, dropped tables, and relevant information for recovery and backups. The In-Memory OLTP component can have up to 8192 data and delta file pairs; for large in-memory databases, the memory taken by system tables can be a few megabytes. Example 21.11 shows the use of this view.
Example 21.11
SELECT OBJECT_NAME(object_id) table_name,
memory_allocated_for_table_kb, memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats;
The result is
The sys.dm_db_xtp_object_stats view reports the number of rows affected by operations on each of the In-Memory OLTP objects since the last database restart. Statistics are updated when the operation executes. This view can help you to identify which memory-optimized tables are changing the most. You may decide to remove unused or rarely used indices on the table, as each index affects performance. If there are hash indices, you should periodically reevaluate the bucket count. You can do so with the sys.dm_db_xtp_hash_index_stats view.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.