When SQL Server introduced InMemory OLTP functionality, it is a great addition to the database. I have seen people use this capability in limited capacity for specific use case scenarios. As a seasoned developer, it is difficult to know what are the consumers of memory. In a recent conversation, I remember someone asking me “Why is SQL Server using so much memory? I suspect since you introduced the InMemory OLTP, I think it is your new feature that is consuming all the memory in my servers. Is there any way to identify what are the InMemory objects currently in memory?”
In this blog, let me walk through a step by step approach to the figure. We will create a new database for testing.
USE MASTER
GO
-- Let us 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 a 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
The next step is to insert few 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
Now that we have created the table with data. Next is to find out what are the tables part of our InMemory OLTP. We have a number of DMVs available which 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
As you can see, we can see the objects available in memory at this point in time. Using DMVs for this requirement is an ideal case.
Next step is to clean-up our test cases.
-- Clean up task
USE MASTER
GO
DROP DATABASE IM_OLTP;
Do let me know if you found this script useful. Have you ever had this requirement in your environments? How do you troubleshoot such InMemory tables usage in your production servers?
Reference: Pinal Dave (https://blog.sqlauthority.com)
