Recently I needed a query to find all the in-memory tables in a database I was working on. This database has a mix of standard and in-memory tables.  So after poking around a few minutes I found this DMV: sys.dm_db_xtp_table_memory_stats

This provides information on the memory  usage by the in-memory tables in a database. After a little playing I tweaked the query like this:

select OBJECT_NAME(object_id) TableName, * from sys.dm_db_xtp_table_memory_stats where object_id > 0

Now I can get a quick list of my in-memory tables and the memory used as shown below. The database used is the model airplane database from Ami Levin and my new book on in-memory features coming this summer from Apress. You can also check out the new In-Memory Virtual User group we are running for SQL Pass.

TableName object_id memory_used_by_table_kb
PlaneTypes 798625888 1
AirplaneEntityCache 686625489 0
NonDurableTest 766625774 0
AirPlanes 430624577 2046
Latest posts by Ken Spencer (see all)