The Memory tab displays the dynamic memory currently in use by SQL Server as well as how that memory satisfies the requirements of SQL Server components. For example, you can see what percentage of the total allocated memory is currently used by the procedure cache. You can control the amount of memory SQL Server uses by either setting a fixed memory value or setting minimum and maximum memory usage values.
Static structures, like procedure headers, use the amount of memory derived from subtracting the sum of the graphed sections displayed on the Memory tab from the total allocated memory, also provided on the Memory tab.
Access the Memory view
To open the Memory view of the SQL Diagnostic Manager Resources tab, select the appropriate SQL Server instance, click Resources, and select Memory.
Memory tab charts
The following charts are available on the Memory tab:
SQL Server Memory Usage
Displays the SQL Memory Usage by what is used by SQL Server compared to what is allocated and the total used.
VM/Host Memory Usage
Displays the memory usage by your virtual machine and host as a percentage of total memory available.
Paging
Displays the memory pages per second.
SQL Server Memory Areas
Displays the memory usage of the Procedure Cache, Connections, Locks, Database, Optimizer, Sort, Hash, and Index.
VM Memory Areas
Displays the amount of memory allocated to and used by SQL Server to the total memory consumed on this virtual machine.
Host Memory Areas
Displays the amount of memory allocated to and used by SQL Server to the total memory consumed on this host server.
Cache Hit Ratios
Displays the hit ratios of the Buffer and Procedure cache.
Buffer Cache
Displays the buffer cache used by Free and Active pages.
Procedure Cache
Provides the amount of allocated memory used by dynamic and static stored procedures. Included in this value is the memory used by server side cursors and plans. From the total memory allocated to SQL Server, the pinned tables, open objects, connections, locks, and free buffer values are subtracted. The remainder is divided between procedure and data cache.
Page Life Expectancy (sec)
Provides the time in seconds that a page remains in the cache or buffer pool.
Understanding your chart data and refresh interval
SQL Diagnostic Manager charts provide data whether collected real-time or as part of a scheduled collection event. For additional information about how SQL Diagnostic Manager displays your chart data, see Charts.