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