-- Klassische Tabellenvariable (nutzt TempDB) 
DECLARE @TempTable TABLE (Id INT, Name NVARCHAR(100)); 
 
-- Speicheroptimierte Tabellenvariable (keine TempDB-Nutzung) 
DECLARE @MemOptTable TABLE ( 
Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000), 
Name NVARCHAR(100) NOT NULL 
) WITH (MEMORY_OPTIMIZED = ON); 
 
-- Einfügen und Abfragen wie gewohnt 
INSERT INTO @MemOptTable VALUES (1, N'Demo'); 
SELECT * FROM @MemOptTable;

-- RESOURCE_SEMAPHORE Wartezeiten anzeigen 
SELECT  
wait_type, 
waiting_tasks_count, 
wait_time_ms, 
max_wait_time_ms, 
signal_wait_time_ms 
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'RESOURCE_SEMAPHORE'; 
 
-- Aktuelle Speicherzuteilungen für Abfragen 
SELECT  
session_id, 
requested_memory_kb, 
granted_memory_kb, 
required_memory_kb, 
used_memory_kb 
FROM sys.dm_exec_query_memory_grants 
WHERE grant_time IS NULL; -- Wartende Abfragen

-- Dauerhafte speicheroptimierte Tabelle (nur Schema, keine Datenpersistenz) 
CREATE TABLE dbo.SharedWorkMemory ( 
WorkId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), 
SessionId INT NOT NULL, 
Payload VARBINARY(8000), 
INDEX ix_Session NONCLUSTERED (SessionId) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
 
-- Session A schreibt Daten 
INSERT INTO dbo.SharedWorkMemory (WorkId, SessionId, Payload) 
VALUES (1, @@SPID, 0x1234); 
 
-- Session B liest (geeignete Synchronisation erforderlich!) 
SELECT * FROM dbo.SharedWorkMemory WHERE SessionId = 42;

-- Aktuelle Speicherkonfiguration anzeigen 
SELECT name, value_in_use  
FROM sys.configurations  
WHERE name IN ('max server memory (MB)', 'min server memory (MB)'); 
 
-- Erhöhen (Beispiel: 24 GB) 
EXEC sp_configure 'max server memory', 24576; 
RECONFIGURE;

-- Abfragen mit hohem Speicherbedarf finden 
SELECT TOP 10 
q.text, 
mg.requested_memory_kb, 
mg.granted_memory_kb, 
mg.query_cost 
FROM sys.dm_exec_query_memory_grants mg 
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) q 
ORDER BY mg.requested_memory_kb DESC;

-- MAXDOP global setzen 
EXEC sp_configure 'max degree of parallelism', 4; 
RECONFIGURE; 
 
-- Oder für eine bestimmte Abfrage 
SELECT ... OPTION (MAXDOP 1);

-- TempDB-Seitenallokationen pro Session (vorher/nachher Vergleich) 
SELECT  
session_id, 
SUM(internal_objects_alloc_page_count) AS AllocPages, 
SUM(user_objects_alloc_page_count) AS UserAllocPages 
FROM sys.dm_db_task_space_usage 
GROUP BY session_id 
ORDER BY AllocPages DESC; 
 
-- Speicher, der von speicheroptimierten Tabellen belegt wird 
SELECT  
OBJECT_NAME(object_id) AS TableName, 
SUM(memory_allocated_for_table_kb) / 1024 AS AllocatedMB 
FROM sys.dm_db_xtp_table_memory_stats 
GROUP BY object_id 
ORDER BY AllocatedMB DESC; 
 
-- Aktuelle RESOURCE_SEMAPHORE-Wartenden 
SELECT  
session_id, 
requested_memory_kb, 
granted_memory_kb, 
required_memory_kb 
FROM sys.dm_exec_query_memory_grants 
WHERE grant_time IS NULL;