The Memory drilldown shows details of memory usage for the server you are diagnosing.
To see the Memory drilldown
Click Monitor | Memory.
To investigate...
Look at the...
How SQL Server is using physical memory
Summary page
Shows the total amount of memory being used by SQL Server's caches over time.
Shows the total memory that SQL Server is using, scaled against the target amount of memory it could use.
Shows the Hit Rates for the main SQL Server caches.
A Hit Rate indicates the amount of work (I/O or compilations) that is being saved by caching information in memory.
Hit rates are shown for the following caches:
Buffer Cache - This is the most important cache, and should normally have a hit rate above 90%.
Procedure Cache - The hit rate for this cache will vary widely depending on how the application is written. This figure is averaged for all procedure cache object types except ad hoc SQL.
Spotlight calculates its hit rates using a differential sampling method. Unlike most SQL Server monitors, it is not reporting the average hit rate since SQL Server started.
Shows the amount of memory allocated to the Buffer Cache and Procedure Cache over time.
The buffer cache is normally the largest memory cache used by SQL Server.
Note: (SQL Server 2012 or above) Shows also the amount of memory allocated to Free Pages and Stolen Pages over time.
SQL Server's buffer cache
Buffer Cache page
Shows the top 20 objects that currently occupy the most space in the buffer cache.
To change the number of objects returned click Change Filter. The maximum number of rows that can be displayed in the Buffer Cache grid is 100.
Note: When connected to a SQL Server 2000 instance, the top 20 objects are always returned, regardless of the setting specified in the field above the grid.
Click Clear Cache to cause all in use (active) Buffer Cache pages to be immediately returned to the free list. This effectively removes all pages from the Buffer Cache, except those that have been modified and have not yet been written to disk (dirty), and those that are currently being referenced by active connections.
Clearing the Buffer Cache degrades performance until the most commonly used pages are read from disk again.
Note: Only members of the Spotlight Diagnostic Administrators group can use the Clear Buffer Cache option.
Shows the amount of memory allocated to Free Pages, Stolen Pages and Database Pages, over time.
Note: (SQL Server 2012 or above) This chart shows the amount of memory allocated to Database Pages over time. To investigate Free Pages and Stolen pages, see the Memory | Summary | Cache chart.
Shows the buffer cache Hit Rate over time. This is the ratio of logical reads to physical reads and indicates how much I/O is being saved by caching pages in the buffer cache. The buffer cache is the most important cache, and should normally have a hit rate above 90%.
Spotlight calculates its hit rates using a differential sampling method. Unlike most SQL Server monitors, it is not reporting the average hit rate since SQL Server started.
SQL Server's procedure cache
Procedure Cache page
Shows objects that are currently in the procedure cache.
The procedure cache contains executable plans for Transact-SQL, including objects such as Stored Procedures, Triggers, Views and Defaults, as well as ad hoc and Prepared SQL.
Click Change Filter to filter the results displayed in the grid or to change the number of rows returned.
To improve performance, Spotlight on SQL Server limits the number of records displayed in this grid to 1000. Spotlight retrieves the largest procedure cache entries first, so if any entries are not displayed, they will be the smallest ones.
Clicking Clear Cache causes all inactive Procedure Cache pages to be immediately returned to the Free List. This effectively removes all entries from the Procedure Cache, except those that are currently being used by active connections. Clearing the Procedure Cache degrades performance somewhat until the most commonly used plans are compiled again.
Note: Only members of the Spotlight Diagnostic Administrators group can use the Clear Procedure Cache option.
Shows the amount of memory allocated to the procedure cache over time and breaks it down by object type.
It shows how much memory was allocated to each type of procedure cache object.
Shows the following statistics for each type of procedure cache object:
Hit Rate: - Percentage of compilations that are being saved by objects already in the procedure cache. The hit rate for this cache will vary widely depending on how your applications are written.
Number of Objects: - Number of objects of each type that are currently in the procedure cache.
Use Count: - Number of cache objects in use.
Spotlight calculates its hit rates using a differential sampling method. Unlike most SQL Server monitors, it is NOT reporting the average hit rate since SQL Server started.
Tip: To switch between statistics, click the arrow next to the chart name.
Tip: When looking at charts, click an item in the legend to highlight its series (line) in the chart.
Memory Areas chart