Investigating Resource Pool Memory Issues
The Resource Pools (RP) metrics are supported only beginning with SQL Server v2008.
The middle division of the Memory Summary dashboard provides metrics which may help you to prevent resource pool memory related errors. For example:
The first chart in the Resource Pool section displays the resource pools available for application and server usage and alerts memory utilization if occurs on any resource pool. The values are grayed out when the resource governor is disabled.
The list contains the following values:
Two resource pools "internal" and "default" are created when SQL Server is installed. Clicking on the resource pool displays how the usages changes over time.
Further drilling into the databases related to this resource pool is also available as of SQL Server v2014, for databases which have the In-Memory feature enabled. When using In-Memory databases, it is highly recommended to define a dedicated resource pool.
The lower part of the Resource Pool section provides metrics on up to eight resource pools over time. Click the Properties tab to view the definition parameters of the resource pools on the server
Investigating Cached Memory Issues
The Memory Plan metrics provide data on cache-related issues.
Plan cache is a component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer attempts to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan.
Low hit ratio may indicate a lack of memory resources, a new plan might also be compiled even if a similar plan exists in cache when:
Plan compilations are expensive though; if plans are flushed from cache only to be recompiled afterwards because of size constraints even more memory resources are consumed.
Selecting the Plan Cache hit ratio allows you to investigate changes over time. Further drilling down leads to the Plan Cache dashboard.
Buffer cache (Also called Buffer Pool) is the place in system memory that holds data and index pages read from disk. This has two purposes:
Buffer Cache size is determined among other things by server memory and the target server memory specified in the Max Server Memory parameter. When that threshold is reached and SQL Server needs to read more pages, previously cached pages are discarded.
A low hit ratio may indicate lack of memory. It also indicates that the Buffer Cache cannot improve performance by reducing IO operations. As such, the Buffer Cache size can correlate to improved performance during a heavy workload.
Improving Buffer Cache hit ration in SQL Server 2014 is also possible by setting a Buffer Pool Extension on an SSD drive.
The hit rate for this cache should normally be above 90%. Selecting the Buffer cache hit ratio bar allows you to investigate these metrics over time. Further drilling down leads to the Buffer Cache dashboard.
New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance.
The Memory Usage of In-Memory (XTP) indicates the amount of data used by memory optimized tables. A large percentage indicates a server that heavily uses In-Memory OLTP features. It is recommended that you dedicate a specific resource pool to databases containing In-Memory OLTP objects hence ensuring proper allocation of memory resources among applications on the SQL Server.
The Oldest XTP Transaction currently active on the server indicates issues regarding long running XTP transactions. Any value over 20 seconds is regarded a warning to an un-healthy behavior on the server. Long-running transactions should be avoided with memory-optimized tables. Such transactions increase the likelihood of conflicts and subsequent transaction terminations. A long-running transaction also defers garbage collection.
The longer a transaction runs, the longer In-Memory OLTP keeps recently deleted row versions, which can decrease lookup performance for new transactions.
Selecting the Oldest XTP transaction displays general information regarding the transaction and the session that had initiated it.
The Major Memory Areas chart displays the amount of memory allocated to the Buffer cache, Plan cache, free memory and XTP. Use this chart to ensure that:
The Minor Memory Areas chart displays the main areas SQL Server uses for carrying out database operations within memory. The display is broken down into each of the main cache areas:
Use this chart to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
Monitoring Buffer Cache-related Data
The Buffer Cache panel allows investigating the utilization of the instance’s buffer cache, by reviewing this cache’s top-consuming objects, the page allocation, and the buffer cache hit rate.
Reviewing the Top-consuming Objects of the Buffer Cache
The Buffer Cache table displays the objects that currently occupy the most space in the buffer cache (the Top N buffer cache objects).
To improve performance, Foglight for SQL Server limits the number of records that can be displayed in this table to 300 rows, but by default, a maximum of 20 records are displayed.
Because the process of determining the largest objects in the cache is highly CPU-intensive, this table can take some time to populate.