Run sp_sysmon for in-memory databases to monitor their performance.
Cache Hits should always have a value of 100% for an in-memory database cache, and Cache Misses should always have a value of 0:
Cache: imdb per sec per xact count % of total ----------------------- ------------ ------------ ---------- ---------- Spinlock Contention n/a n/a n/a 50.9 % Utilization n/a n/a n/a 99.9 % Cache Searches Cache Hits 16954.4 5735.8 1152897 100.0 % Found in Wash 0.0 0.0 0 0.0 % Cache Misses 0.0 0.0 0 0.0 % ----------------------- ------------ ------------ ---------- ---------- Total Cache Searches 16954.4 5735.8 1152897
In-memory caches should not have a wash region, as reported below:
Buffer Wash Behavior Statistics Not Available - No Buffers Entered Wash Section Yet Cache Strategy Cached (LRU) Buffers 21225.3 7180.7 1443321 100.0 % Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
See Performance and Monitoring Series: Monitoring Adaptive Server with sp_sysmon.
The following sp_sysmon sections do not apply to in-memory storage caches because the entire database is bound to the cache and all database pages reside in the cache:
Utilization
Cache Hits
Found in Wash
Cache Misses
Large I/O Usage
Pool Turnover
These sections do not apply to in-memory databases because they do not perform buffer grabs:
LRU Buffer Grab
Grabbed Dirty
Total Cache Turnover
These sections do not apply to in-memory databases because they do not define a wash region. Disk reads and writes do not take place in a steady state:
Buffer Wash Behavior
Cache Strategy
These sections do not apply to in-memory databases because they do not use large pools:
Large I/Os Performed
Large I/Os Denied
Total Large I/O Requests
Large I/O Detail
