SQL SERVER – How to Identify InMemory Objects Can be Identified in SQL Server?


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.

<span>USE MASTER<br /> </span><span>GO<br /> </span><span>-- Let us create a small database for testing<br /> </span><span>CREATE DATABASE </span><span>IM_OLTP<br /> GO<br /> </span><span>-- Add the In-Memory Filegroup<br /> </span><span>ALTER DATABASE </span><span>IM_OLTP </span><span>ADD </span><span>FILEGROUP IM_OLTP_mod </span><span>CONTAINS </span><span>MEMORY_OPTIMIZED_DATA<br /> </span><span>ALTER DATABASE </span><span>IM_OLTP </span><span>ADD FILE </span><span>(</span><span>name</span><span>=</span><span>'IM_OLTP_mod'</span><span>, </span><span>filename</span><span>=</span><span>'c:/data/IM_OLTP_mod'</span><span>) </span><span>TO </span><span>FILEGROUP IM_OLTP_mod<br /> GO<br /> </span><span>USE </span><span>IM_OLTP<br /> GO<br /> </span><span>-- Create a InMemory table<br /> </span><span>CREATE TABLE </span><span>dbo.tbl_im_oltp </span><span>(<br /> </span><span>c1 </span><span>INT </span><span>NOT NULL,<br /> </span><span>c2 </span><span>CHAR</span><span>(</span><span>40</span><span>) NOT NULL,<br /> </span><span>c3 </span><span>CHAR</span><span>(</span><span>8000</span><span>) NOT NULL,<br /> </span><span>CONSTRAINT </span><span>[pk_tbl_im_oltp_c1] </span><span>PRIMARY KEY NONCLUSTERED </span><span>HASH </span><span>(</span><span>c1</span><span>) </span><span>WITH </span><span>(</span><span>BUCKET_COUNT </span><span>= </span><span>10000</span><span>)<br /> ) </span><span>WITH </span><span>(</span><span>MEMORY_OPTIMIZED </span><span>= ON</span><span>, </span><span>DURABILITY </span><span>= </span><span>SCHEMA_ONLY</span><span>)<br /> </span><span>GO</span>

Thenext step is to insert few data into the InMemory table.

<span>-- Insert 100 rows<br /> </span><span>SET NOCOUNT ON<br /> DECLARE </span><span>@i </span><span>INT = </span><span>0<br /> </span><span>WHILE </span><span>(</span><span>@i </span><span>&lt; </span><span>100</span><span>)<br /> </span><span>BEGIN<br /> INSERT </span><span>tbl_im_oltp </span><span>VALUES </span><span>(</span><span>@i</span><span>, </span><span>'a'</span><span>, </span><span>REPLICATE </span><span>(</span><span>'b'</span><span>, </span><span>8000</span><span>))<br /> </span><span>SET </span><span>@i </span><span>+</span><span>= </span><span>1</span><span>;<br /> </span><span>END<br /> </span><span>GO</span>

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.

<span>SELECT </span><span>CONVERT</span><span>(</span><span>CHAR</span><span>(</span><span>20</span><span>), </span><span>OBJECT_NAME</span><span>(</span><span>OBJECT_ID</span><span>)) </span><span>AS </span><span>Name</span><span>,*<br /> </span><span>FROM </span><span>sys.dm_db_xtp_table_memory_stats<br /> </span><span>WHERE </span><span>OBJECT_ID</span><span>&gt; </span><span>0<br /> </span><span>SELECT </span><span>CONVERT</span><span>(</span><span>CHAR</span><span>(</span><span>20</span><span>), </span><span>OBJECT_NAME</span><span>(</span><span>OBJECT_ID</span><span>)) </span><span>AS </span><span>Name</span><span>, *<br /> </span><span>FROM </span><span>sys.dm_db_xtp_memory_consumers<br /> </span>

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.

<span>-- Clean up task<br /> </span><span>USE MASTER<br /> </span><span>GO<br /> </span><span>DROP DATABASE </span><span>IM_OLTP</span><span>;</span>

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 ( http://blog.sqlauthority.com )