Foglight for SQL Server (Cartridge) 5.9.4.20

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:

•

There are changes in the schema.

•

A query is running parallel that may have run serially before.

•

Parameters have changed.

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:

•

To modify pages according to INSERT and UPDATE statements. Those pages are marked as "dirty" and are flushed to disk when a checkpoint is performed.

•

To increase response time when retrieving the same data.

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.

Investigating In-Memory OLTP (XTP) Performance

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.

Investigating Major and Minor Memory Areas

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 buffer cache has appropriate size of memory, which is sufficient to hold the most frequently used data pages.

•

The free memory value is not consistently close to zero, which may indicate use of inefficient queries or shortage of SQL Server cache memory.

•

The plan cache has appropriate size of memory, which is sufficient to hold the most frequently used execution plans.

•

The XTP memory has no significant peaks that might mean there are memory utilization issues during the day.

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:

•

Lock Area — memory allocated to keeping track of locks.

•

Optimizer Code — a work area for the SQL Server optimizer. For details, see glossary definition of Optimizer.

•

Sort, Hash, Index — memory used for each of these operations.

•

User Connections — memory allocated to keeping track of each connection's attributes.

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.


	NOTE: To define global settings about data retrieval to the Buffer Cache table panel, use the Buffer Cache view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings. For details, see Setting Options for Displaying Data in the Buffer Cache .