Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features. The following blog post shows how to monitor the table space usage.
;
WITH system_allocated_memory ( system_allocated_memory_in_mb )
AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2),
( SUM(TMS.memory_allocated_for_table_kb)
+ SUM(TMS.memory_allocated_for_indexes_kb) )
/ 1024.00)
FROM [sys].[dm_db_xtp_table_memory_stats] TMS
WHERE TMS.object_id <= 0
), 0.00)
),
table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb,
index_used_memory_in_mb, index_unused_memory_in_mb )
AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2),
( SUM(TMS.memory_used_by_table_kb)
/ 1024.00 ))
), 0.00) AS table_used_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
- SUM(TMS.memory_used_by_table_kb) )
/ 1024.00)
), 0.00) AS table_unused_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)
/ 1024.00 ))
), 0.00) AS index_used_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)
- SUM(TMS.memory_used_by_indexes_kb) )
/ 1024.00)
), 0.00) AS index_unused_memory_in_mb
FROM [sys].[dm_db_xtp_table_memory_stats] TMS
WHERE TMS.object_id > 0
)
SELECT s.system_allocated_memory_in_mb ,
t.table_used_memory_in_mb ,
t.table_unused_memory_in_mb ,
t.index_used_memory_in_mb ,
t.index_unused_memory_in_mb ,
ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),
'IsXTPSupported')
), 0) AS has_memory_optimized_filegroup
FROM system_allocated_memory s ,
table_index_memory t
SELECT t.object_id ,
t.name ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )
/ 1024.00)
), 0.00) AS table_used_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb
- TMS.memory_used_by_table_kb )
/ 1024.00)
), 0.00) AS table_unused_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )
/ 1024.00)
), 0.00) AS index_used_memory_in_mb ,
ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb
- TMS.memory_used_by_indexes_kb )
/ 1024.00)
), 0.00) AS index_unused_memory_in_mb
FROM sys.tables t
JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )
-- this DMV accounts for all memory used by the hek_2 engine
SELECT type ,
name ,
memory_node_id ,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%'
EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1
DECLARE @c BIT
EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT
SELECT @c AS 'collection status'
-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441
DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql += N'EXECUTE sp_recompile N'''
+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''
'
FROM sys.sql_modules sm
JOIN sys.objects o ON sm.object_id = o.object_id
WHERE uses_native_compilation = 1
EXECUTE sp_executesql @sql
-- Reset wait and latch statistics.
DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)
Msg 41317, Level 16, State 5, Line 6
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
Share this:
CloudIQ is a leading Cloud Consulting and Solutions firm that helps businesses solve today’s problems and plan the enterprise of tomorrow by integrating intelligent cloud solutions. We help you leverage the technologies that make your people more productive, your infrastructure more intelligent, and your business more profitable.
LATEST THINKING
INDIA
Chennai One IT SEZ,
Module No:5-C, Phase ll, 2nd Floor, North Block, Pallavaram-Thoraipakkam 200 ft road, Thoraipakkam, Chennai – 600097
© 2023 CloudIQ Technologies. All rights reserved.
Get in touch
Please contact us using the form below
USA
INDIA