Counters Needs to check for Memory Performance Issues in SQL Server
What are the counters that needs to put on high focus when working on Memory issues with SQL Server:
Performance Object Name: Buffer Manager
| Counter Name | Counter Description |
|---|
| Buffer cache hits ratio | Percentage of pages that were found in the buffer pool without having to incur a read from disk. |
| Checkpoint pages/sec | Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed. |
| Database pages | Number of pages in the buffer pool with database content. |
| Free list Stalls/sec | Number of requests that had to wait for a free page. |
| Free Pages | Total number of pages on all free lists. |
| Lazy Write/sec | Number of buffers written by buffer manager's lazy writer. |
| Page life expectancy | Number of seconds a page will stay in the buffer pool without references. |
| Page lookups/sec | Number of requests to find a page in the buffer pool. |
| Page reads/sec | Number of physical database page reads issued. |
| Page writes/sec | Number of physical database page writes issued. |
| Read aheadpages/sec | Number of pages read in anticipation of use. |
| Reserved Pages | Number of buffer pool pages reserved |
| Stolen Pages | Number of pages used for miscellaneous server purposes (including procedure cache). |
| Target Pages | Ideal number of pages in the buffer pool. |
| Total Pages | Number of pages in the buffer pool (includes database, free, and stolen). |
Performance Object Name: Buffer Partition
| Counter Name | Counter Description |
|---|
| Free list empty/sec | Number of times a free page was requested and none were available. |
| Free list requests/sec | Number of times a free page was requested. |
| Free pages | Number of pages on partition free list. |
Performance Object Name: Memory Manager
| Counter Name | Counter Description |
|---|
| Connection Memory (KB) | Total amount of dynamic memory the server is using for maintaining connections |
| Granted Workspace Memory (KB) | Total amount of memory granted to executing processes. This memory is used for hash, sort and create index operations. |
| Memory Grants Outstanding | Current number of processes that have successfully acquired a workspace memory grant |
| Memory Grants Pending | Current 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 is willing to consume |
| Total server memory (KB) | Total amount of dynamic memory the server is currently consuming |
Performance Object Name: Transactions
| Counter Name | Counter Description |
|---|
| Transactions | The total number of active transactions. |
