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)
- Automating software development - October 7, 2014
- Uses for SQL Server 2014 - May 21, 2014
- Query to find in-memory tables in SQL Server 2014 database - April 29, 2014