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 |
