How to Find Whether SQL Server 2016 Db is set to be Memory Optimized or not?

Question

I want to get information through SQL query for any database in SQL Server 2016 whether it is already set to be memory optimized or not. I know following query

SELECT DatabasePropertyEx('DATABASENAME', 'IsXTPSupported');

tells you that whether you can set (by setting the Memory Optimized Data file Group) the database to have in memory tables or not. But I want to know that whether db is already set to contain in memory table or not i.e. its Memory Optimized Data File Group have been already set or not?

How I can achieve this through SQL Query for any given database?

Answer 1

Googling the title of your question I found this: https://msdn.microsoft.com/en-CA/library/dn232522.aspx

SELECT 
    quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) AS [table],
    s.name AS [statistics object],
    1-s.no_recompute AS [auto-update enabled]
FROM sys.stats s JOIN sys.tables o ON s.object_id=o.object_id
WHERE o.is_memory_optimized=1

Answer 2

Try this:

SELECT distinct DB_NAME()
FROM sys.master_files sdf
INNER JOIN
sys.filegroups fg
ON sdf.data_space_id=fg.data_space_id and fg.type='FX'

Answer 3

Many ways... One more alternative than the above list:

SELECT type, name, memory_node_id, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'