As a SQL Server DBA, one of the common questions we often encounter is why SQL Server is using up all the memory and not releasing it even when idle. With the release of SQL Server 2014, this question became even more complicated due to the introduction of In-Memory OLTP, a completely new concept that significantly increases our dependency on memory.

In reality, not much changes with the introduction of In-Memory OLTP, but we now have memory-optimized objects such as tables and stored procedures that reside completely in memory, improving performance. As a DBA, it can be overwhelming to keep up with all the innovations and new features introduced in each version of SQL Server.

In this blog post, we will focus on a report added to SQL Server Management Studio (SSMS) that provides a high-level view of the In-Memory OLTP feature. This report is only available from SQL Server 2014 onwards, as it was introduced in that version. Earlier versions of SSMS will not show the report in the list.

To demonstrate the report, let’s create a new database called MemoryOptimizedDB without any special file group. We can use the following query to identify whether a database has a memory-optimized file group:

SELECT TOP (1) 1
FROM sys.filegroups FG
WHERE FG.[type] = 'FX'

If we try to launch the report on a database that does not have a memory-optimized file group defined, we will see a message indicating that the report is empty.

To add a file group, we can use the following command:

USE [master]
GO
ALTER DATABASE [MemoryOptimizedDB] ADD FILEGROUP [IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA
GO

After adding the file group, the report will still be empty because we have not defined any memory-optimized tables in the database. The total allocated size will be shown as 0 MB.

Now, let’s add a folder location to the file group and create a few in-memory tables. We will use the nomenclature of “IMO” to denote “InMemory Optimized” objects.

USE [master]
GO
ALTER DATABASE [MemoryOptimizedDB] ADD FILE (NAME = N'MemoryOptimizedDB_IMO', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\MemoryOptimizedDB_IMO') TO FILEGROUP [IMO_FG]
GO

Next, we can create a table using the following script:

USE MemoryOptimizedDB
GO

-- Drop the table if it already exists
IF OBJECT_ID('dbo.SQLAuthority', 'U') IS NOT NULL
    DROP TABLE dbo.SQLAuthority
GO

CREATE TABLE dbo.SQLAuthority (
    ID INT IDENTITY NOT NULL,
    Name CHAR(500) COLLATE Latin1_General_100_BIN2 NOT NULL DEFAULT 'Pinal',
    CONSTRAINT PK_SQLAuthority_ID PRIMARY KEY NONCLUSTERED (ID),
    INDEX hash_index_sample_memoryoptimizedtable_c2 HASH (Name) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

After executing the script, both the table and index will be created. If we run the report again, we will see that the table memory is zero, but the index is using memory. This is because the hash index needs memory to manage the buckets created. Even if the table is empty, the index will consume memory.

To populate the table with 10000 rows, we can use the following script:

INSERT INTO SQLAuthority VALUES (DEFAULT)
GO 10000

The report will now display three sections:

1. Total memory consumed by In-Memory objects
2. Pie chart showing memory distribution based on the type of consumer (table, index, and system)
3. Details of memory usage by each table

All the information in the report is taken from a single DMV (Dynamic Management View) called sys.dm_db_xtp_table_memory_stats. This DMV contains memory usage statistics for both user and system In-Memory tables.

By querying the DMV, we can easily identify which In-Memory object is consuming a lot of memory, which can be used as a pointer for designing a solution.

In future blog posts, we will delve into the internals of how In-Memory indexes and tables work. Stay tuned!

To learn more about In-Memory OLTP, you can visit Balmukund’s Blog and read the In-Memory OLTP Series.