hekaton.sql 1.17 KB
Newer Older
Dave Wentzel's avatar
Dave Wentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
--this is not yet integrated into PerformanceCollector 

--this ideally should be run in any database with hekaton enabled 


--overall memory usage by table
INSERT INTO PerformanceCollector.PerformanceCollector.xtp_table_memory_stats  
SELECT object_name(object_id) AS table_name, 
	memory_allocated_for_table_kb/1024 memory_allocated_for_table_mb,
	memory_used_by_table_kb/1024 memory_used_by_table_mb,
	memory_allocated_for_indexes_kb/1024 memory_allocated_for_indexes_mb,
	memory_used_by_indexes_kb/1024 memory_used_by_indexes_mb
FROM sys.dm_db_xtp_table_memory_stats

--total xtp memory
INSERT INTO PerformanceCollector.PerformanceCollector.xtpmem
SELECT type clerck_type
     , name
     , memory_node_id
     , pages_kb/1024 pages_mb 
FROM sys.dm_os_memory_clerks 
WHERE type LIKE '%xtp%'

--hash bucket metrics
INSERT INTO PerformanceCollector.PerformanceCollector.xtp_buckets
SELECT Object_name(his.object_id) AS table_name, 
	   si.NAME                    AS index_name,
	   his.empty_bucket_count,
	   his.total_bucket_count
FROM   sys.dm_db_xtp_hash_index_stats his 
INNER JOIN sys.indexes si 
ON his.object_id = si.object_id 
AND his.index_id = si.index_id