SQL Server: Memory Manager

3 4

This object provides information about SQL Server memory other than the buffer cache. This object's counters are as follows:

• Connection Memory (KB) Amount of memory, in kilobytes, that is used for maintaining connections.
• Granted Workspace Memory (KB) Total amount of memory, in kilobytes, that has been granted to processes for sort and index creation operations.
• Lock Blocks Number of lock blocks in use on the server.
• Lock Blocks Allocated Total number of allocated lock blocks.
• Lock Memory (KB) Total amount of memory, in kilobytes, that is allocated to locks.
• Lock Owner Blocks Number of lock owner blocks that are currently in use on the server.
• Lock Owner Blocks Allocated Number of lock owner blocks that have been allocated on the server.
• Maximum Workspace Memory (KB) Total amount of memory that has been allocated to executing processes. This memory can be used for hash, sort, and index creation operations.
• Memory Grants Outstanding Number of current processes that have acquired a workspace memory grant.
• Memory Grants Pending Number of processes waiting for a workspace memory grant.
• Optimizer Memory (KB) Amount of memory, in kilobytes, that the server is using for query optimization.
• SQL Cache Memory (KB) Total amount of memory, in kilobytes, that the server is using for the dynamic SQL cache.
• Target Server Memory (KB) Total amount of dynamic memory, in kilobytes, that the server can consume.
• Total Server Memory (KB) Total amount of dynamic memory, in kilobytes, that the server is currently consuming. SQL Server dynamically allocates and deallocates memory based on how much memory is available in the system. This counter offers you a view of the memory that is currently being used. If large swings in memory usage occur over the course of a day, or when special events occur, you might want to fix the amount of memory that SQL Server is using by setting the min server memory and max server memory parameters. For example, if every day another application runs that consumes large amounts of memory, such as OLAP services, you may want to reserve memory, rather than making SQL Server go through the resource-intensive task of releasing memory.