When SQL Server introduced InMemory OLTP functionality, it was a significant addition to the database. However, many developers find it challenging to understand the memory consumption of InMemory objects and troubleshoot any related issues.
In a recent conversation, someone asked me, “Why is SQL Server using so much memory? I suspect that the InMemory OLTP feature is consuming all the memory on my servers. Is there any way to identify the InMemory objects currently in memory?” In this blog post, I will walk you through a step-by-step approach to figure out the InMemory objects in memory.
Let’s start by creating a new database for testing:
USE MASTER
GO
-- Create a small database for testing
CREATE DATABASE IM_OLTP
GO
-- Add the In-Memory Filegroup
ALTER DATABASE IM_OLTP ADD FILEGROUP IM_OLTP_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE IM_OLTP ADD FILE (name = 'IM_OLTP_mod', filename = 'c:\data\IM_OLTP_mod') TO FILEGROUP IM_OLTP_mod
GO
USE IM_OLTP
GO
-- Create an InMemory table
CREATE TABLE dbo.tbl_im_oltp (
c1 INT NOT NULL,
c2 CHAR(40) NOT NULL,
c3 CHAR(8000) NOT NULL,
CONSTRAINT [pk_tbl_im_oltp_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 10000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO
Now, let’s insert some data into the InMemory table:
-- Insert 100 rows
SET NOCOUNT ON
DECLARE @i INT = 0
WHILE (@i < 100)
BEGIN
INSERT tbl_im_oltp VALUES (@i, 'a', REPLICATE('b', 8000))
SET @i += 1;
END
GO
With the table and data in place, we can now find out which tables are part of our InMemory OLTP. SQL Server provides several Dynamic Management Views (DMVs) that can be used to view this information:
SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name, *
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID > 0
SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name, *
FROM sys.dm_db_xtp_memory_consumers
By executing these queries, we can see the objects currently in memory. Utilizing DMVs for this requirement is an ideal approach.
Finally, let’s clean up our test cases:
USE MASTER
GO
DROP DATABASE IM_OLTP;
I hope you found this script useful. Have you ever encountered a similar requirement in your environments? How do you troubleshoot the usage of InMemory tables in your production servers? Share your experiences in the comments below!