SQLServer – Listing Memory Optimized Tables


Having started to play around quite a bit with memory optimized tables, I sometimes get the feel that I need to free them from by memory.  As their name suggests, once in memory always in memory.   And, so the one way to reduce memory foot-print is to purge them by deleting their contents. I suppose that I probably can offline their container filegroup as well, but let us stay within our earlier scenario of identifying them.


Before purging them let us go identify them: Here is a sample script that binds sys.objects, sys.partitions , sys.allocation_units, and sys.filegroups.


	  schema_name(tblObject.schema_id) as schemaName
	, tblObject.name
	, tblFileGroup.name as [fileGroupName]
	, tblFileGroup.type as [fileGroupType]
	, tblFileGroup.type_desc as [fileGroupTypeDesc]
	, sum(tblPartition.rows) as rows

from   sys.objects tblObject

	inner join sys.partitions tblPartition

	   on tblObject.object_id = tblPartition.object_id

	inner join sys.allocation_units tblAllocationUnit

	   on tblPartition.hobt_id = 

	inner join sys.filegroups tblFileGroup

	   on tblAllocationUnit.data_space_id = 

where tblObject.type = 'U'

and tblFileGroup.type = 'FX'

group by
	, tblObject.name
	, tblFileGroup.name
	, tblFileGroup.type
	, tblFileGroup.type_desc

order by
	, tblObject.name



Our basis for this query is that memory optimized table have to reside in their own filegroup known as  MEMORY_OPTIMIZED_DATA_FILEGROUP.


Please keep in mind that you can not truncate them, you have to issue a delete statement. No worries it is fast enough.

Though not obvious from our screenshot, the number of records in each Memory Optimized’s object partition is always 0.

I am sure there is  a clearer pathway towards identifying them, getting stats on memusage and row count, but as I am old school I will go the hard & blind way pending someone else’s blog post.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s