CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016in-memory-tables
Abdul Qayyum
Abdul Qayyum

Reputation: 51

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

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?

Upvotes: 4

Views: 3091

Answers (3)

Tarun
Tarun

Reputation: 59

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%' 

Upvotes: 1

Prashanth Jayaram
Prashanth Jayaram

Reputation: 11

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'

Upvotes: 1

Peter4499
Peter4499

Reputation: 685

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

Upvotes: 1

Related Questions