Azure SQL in-Memory OLTP

It's time to get outdoors and Explore

Azure SQL in-Memory OLTP High Concurrency

We have been working in-memory OLTP Technologies to achieve high concurreny. Our platform needs to support about 500 K Concurrent users doing small ( less than 64 KB) reads/writes at high frequency.

We have noticed LATCH, LOCK and WRITE Log waits using disk based tables and we are able to avoid those issues using in-memory OLTP in Azure SQL and we are able to achieve the high concurrency.

Queries used

Reset Procedure Stats


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;

-- 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)


Enable and Monitor in-Memory OLTP Procedure Statistics

By default, procedure statistics are turned off for in-memory OLTP procedures.

Monitor Memory Optimized 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
   )
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 )


Configure MAXDOP to preserve Worker Thread exhaustion

Reset MAXDOP configuration for Azure SQL Database


ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8

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];

#### Errors

-- Msg 41359, Level 16, State 0, Procedure xxxx.xxxxx, Line 288 [Batch Start Line 1]
-- A query that accesses memory optimized tables using the READ COMMITTED isolation level, cannot access disk based tables when the database option READ_COMMITTED_SNAPSHOT is set to ON.
--  Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

### Fix

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

XTP Memory Usage



--- Is in-memory-oltp supported
SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');

-- in-memory-oltp tables
SELECT * FROM sys.tables WHERE is_memory_optimized=1
SELECT * FROM sys.table_types WHERE is_memory_optimized=1
SELECT * FROM sys.sql_modules WHERE uses_native_compilation=1

SELECT object_name(object_id) AS Name  
     , *  
   FROM sys.dm_db_xtp_table_memory_stats

   SELECT memory_consumer_desc  
     , allocated_bytes/1024 AS allocated_bytes_kb  
     , used_bytes/1024 AS used_bytes_kb  
     , allocation_count  
   FROM sys.dm_xtp_system_memory_consumers

 -- Memory consumption at run-time when accessing memory-optimized tables

   SELECT memory_object_address  
     , pages_in_bytes  
     , bytes_used  
     , type  
   FROM sys.dm_os_memory_objects WHERE type LIKE '%xtp%'

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

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

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

-- 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='testmgmt', @object_name='RawResponse'

exec sys.sp_xtp_flush_temporal_history @schema_name='testmgmt', @object_name='Response' 

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

Hash Index Guidelines
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#hash_index
Diagnose and resolve spinlock contention on SQL Server
https://docs.microsoft.com/en-us/sql/relational-databases/diagnose-resolve-spinlock-contention?view=azuresqldb-current
Transaction Locking and Row Versioning Guide
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=azuresqldb-current
In-Memory OLTP Benchmarking
https://azure.microsoft.com/en-us/blog/in-memory-oltp-in-azure-sql-database/
Bob Ward In-Memory OLTP YouTube
https://www.youtube.com/watch?v=P9DnjQqE0Gc

Posted in Azure, SQL Server on Dec 14, 2020.


Comments

Please sign in to comment!