The memory performance view allows the breakdown of memory (RAM) related events, multiple instances of the same machine events, processes affecting the memory and more.
Use this view to monitor and identify performance issues that are directly related to memory-intensive systems.
Memory – Part 1
| Legend | |
| 1 | Allocated memory usage of the instance. This will show the latest instance memory allocation from the machine total. |
| 2 | Used memory/RAM for the entire server. This will show the latest collected value. |
| 3 | Enhanced Chart of all instances on the selected instance’s machine over time with alert values for the selected time range. |
| 4 | Enhanced Chart of the server used memory/RAM allocation for the selected time range. |
| 5 | The OS top processes AVG of used RAM (working set). AVG is calculated for the selected time range. The list is ordered by the most significant consumer to the least. |
| 6 | Sorted list of databases that consume the most amount of buffer cache. This list is manufactured from the queries themselves, not from the distribution of the buffer cache. In addition, this is a ratio–based list, meaning if you sum all database values, you will reach 100%. |
Memory – Part 2
| Legend | |
| 1 | Wait control sorted by wait events that are associated with memory consumption:
CMEMTHREAD – Caused when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object. RESOURCE_SEMAPHORE – Caused when a query memory request cannot be granted immediately due to other concurrent queries. Long waits and wait times may indicate an excessive number of concurrent queries or excessive memory request amounts. RESOURCE_SEMAPHORE_MUTEX – Caused when a query waits for its request for a thread reservation to be fulfilled. The wait also occurs when synchronizing query compile and memory grant requests. RESOURCE_SEMAPHORE_QUERY_COMPILE – Caused when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans. RESOURCE_SEMAPHORE_SMALL_QUERY – Caused when a memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries. SOS_RESERVEDMEMBLOCKLIST – Caused when internal synchronization in the SQL Server memory manager occurs. |
| 2 | PLE Graph over a selected time range.
Page life expectancy (PLE) -Indicates the number of seconds a page will stay in the buffer pool without references. The recommended value of the PLE counter is (updated: minimum of) 300 seconds. I have seen this value on busy systems to be as low as 45 seconds and on an unused system, as high as 1250 seconds. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simpler words, if your page stays longer in the buffer pool (area of the memory cache), your PLE is higher, leading to higher performance as every time a request arrives, there is a chance it may find its data in the cache itself instead of going to the hard drive to read the data. |
| 3 | Queries control – sorted by logical reads in descending order. |