Forum Topic

Thanks for your participation and contribution.


Back to SQL Server

In-Memory OLTP

Post a reply
752 views
i90runner1
i90runner1

References :
https://sqlrelease.com/convert-existing-table-to-temporal-table. https://docs.microsoft.com/en-us/azure/azure-sql/database/temporal-tables-retention-policy
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/survey-of-initial-areas-in-in-memory-oltp?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitor-and-troubleshoot-memory-usage?view=sql-server-ver15.

-- memory consumers (database level)  
select  convert(char(10), object_name(object_id)) as Name,   
convert(char(10),memory_consumer_type_desc ) as memory_consumer_type_desc, object_id,index_id, allocated_bytes,  used_bytes   
from sys.dm_db_xtp_memory_consumers

-- Invokes the data flush task to move all committed rows from in-memory staging table to the disk-based history table.
-- sys.sp_xtp_flush_temporal_history @schema_name, @object_name

exec sys.sp_xtp_flush_temporal_history @schema_name='sch1', @object_name='Table1'

Last updated

i90runner1
i90runner1

Enable and Monitor in-Memory OLTP Procedure Statistics

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;
i90runner1
i90runner1

Reset in-memory OLTP procedure cache

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 MAXDOP configuration for Azure SQL Database

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8
i90runner1
i90runner1

Monitor statistics for chains and empty buckets.

[https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/hash-indexes-for-memory-optimized-tables?view=sql-server-ver15]

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  

  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];
i90runner1
i90runner1

PERFORMANCE MONITORING

Monitoring Microsoft Azure SQL Database and Azure SQL Managed Instance performance using dynamic management views

https://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs

Last updated

i90runner1

1-6 of 6

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.