Technical: Microsoft – 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.
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.