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.
select 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 = tblAllocationUnit.container_id inner join sys.filegroups tblFileGroup on tblAllocationUnit.data_space_id = tblFileGroup.data_space_id where tblObject.type = 'U' and tblFileGroup.type = 'FX' group by schema_name(tblObject.schema_id) , tblObject.name , tblFileGroup.name , tblFileGroup.type , tblFileGroup.type_desc order by schema_name(tblObject.schema_id) , 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.