SQL Server, Memory Manager Object
The Memory Manager object in Microsoft SQL Server provides counters to monitor overall server memory usage. Monitoring overall server memory usage to gauge user activity and resource usage can help you to identify performance bottlenecks. Monitoring the memory used by an instance of SQL Server can help determine:
If bottlenecks exist from inadequate physical memory for storing frequently accessed data in cache. If memory is inadequate, SQL Server must retrieve the data from disk.
If query performance can be improved by adding more memory or by making more memory available to the data cache or SQL Server internal structures.
This table describes the SQL Server Memory Manager counters.
SQL Server Memory Manager counters |
Description |
|---|---|
Connection Memory (KB) |
Total amount of dynamic memory the server is using for maintaining connections. |
Granted Workspace Memory (KB) |
Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations. |
Lock Blocks |
Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row. |
Lock Blocks Allocated |
Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases. |
Lock Memory (KB) |
Total amount of dynamic memory the server is using for locks. |
Lock Owner Blocks |
Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks. |
Lock Owner Blocks Allocated |
Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically. |
Maximum Workspace Memory (KB) |
Maximum amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations. |
Memory Grants Outstanding |
Total number of processes that have successfully acquired a workspace memory grant. |
Memory Grants Pending |
Total number of processes waiting for a workspace memory grant. |
Optimizer Memory (KB) |
Total amount of dynamic memory the server is using for query optimization. |
SQL Cache Memory (KB) |
Total amount of dynamic memory the server is using for the dynamic SQL cache. |
Target Server Memory (KB) |
Total amount of dynamic memory the server can consume. |
Total Server Memory (KB) |
The committed memory from the buffer pool (in kilobytes). .gif "Note") Note
This is not the total memory used by SQL Server.
|