Monitoring In-Memory OLTP Index Performance with sys.dm_db_xtp_index_stats

Summary: Audit Hekaton index efficiency using the sys.dm_db_xtp_index_stats DMV to track scans, retries, and row-level operations for In-Memory SQL Server tables.

Analyzing In-Memory OLTP Index Statistics

Understanding how your SQL Server indexes are performing is crucial for database optimization, especially when using In-Memory OLTP (Hekaton). Because memory-optimized tables utilize different data structures (like Hash and Range indexes), standard index DMVs often won't provide the full picture.

The following query leverages sys.dm_db_xtp_index_stats to provide deep insights into your in-memory index usage and health.

In-Memory Index Usage Query

-- Monitor In-Memory OLTP index activity
SELECT 
    OBJECT_NAME(i.[object_id]) AS [Object Name],
    i.index_id,
    i.[name] AS [Index Name],
    i.[type_desc] AS [Index Type],
    xis.scans_started,
    xis.scans_retries,
    xis.rows_touched,
    xis.rows_returned
FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON i.[object_id] = xis.[object_id]
    AND i.index_id = xis.index_id
ORDER BY [Object Name], i.index_id
OPTION (RECOMPILE);
        

Key Metrics Explained

• scans_retries: A high number here can indicate excessive transaction collisions or "interference" in the lock-free data structures, suggesting a need for better bucket counts in Hash indexes.
• rows_touched vs. rows_returned: If rows_touched is significantly higher than rows_returned, your index may be scanning through many irrelevant rows (common in poorly tuned Hash indexes with high collision rates).
• scans_started: Reflects the actual utilization frequency of the index by the query optimizer.

For further technical details, refer to the official Microsoft documentation for sys.dm_db_xtp_index_stats.

---

Related Performance Resources

• Retrieving SQL Server Memory Dumps
• Understanding Memory Clerk Usage
• Detecting Server Memory Pressure
• Analyzing Memory Grants Pending
• Resource Governor & Pool Configuration

Optimizing In-Memory performance? Ensure your Hash Index bucket counts are roughly 1x to 2x the number of unique values in your indexed column for maximum efficiency!