Worldwide Locations
Many experts agree that memory is the most important resource with regard to SQL Server performance. There are hundreds of performance counters, and therefore, it can be complex to figure out just what to monitor. Here are some suggestions in understanding whether memory pressure might be the performance problem with your SQL Server. You can use Performance Monitor to collect this data. You can also obtain the SQL Server specific counters using the Dynamic Management View (DMV) that is built into SQL Server. The DMV is an excellent and lightweight option for performance monitoring. You can use this syntax. Select * from sys.dm_os_performance_counters
| Object | Counter | Description | Desired value |
|---|---|---|---|
| SQL Server: Buffer Manager | Lazy Writes/sec | Monitors the number of times per second that the Lazy Writer process moves dirty pages from the buffer to disk. A lower number is better than a higher number. Zero is ideal. If this number is greater than 20, more memory is needed. | 20 |
| SQL Server: Buffer Manager | Page Life Expectancy | This is the average number of seconds SQL Server expects a data page to remain in cache. On an OLTP system, this should be at least 300 (or 5 minutes). If this is less than 300, it could indicate poor index design or a shortage of memory. | >300 |
| SQL Server: Buffer Manager | Page Reads / sec | Number of physical database page reads that are issued per second. In a normal OLTP system, workloads can support 80-90 per second. If the value is higher than 90, it could indicate poor index design or a shortage of memory. | 90 |
| SQL Server: Buffer Manager | Page Writes/sec | Number of database pages physically written to disk per second. In a normal OLTP system, workloads can support 80-90 per second. If the values are higher than this, check “lazy writes / sec” and “checkpoint” counters. If all three values are high, this could indicate insufficient memory. | 90 |
| SQL Server: Buffer Manager | Checkpoint Pages / sec | This counter monitors the number of dirty pages per second, which are flushed to disk when SQL Server invokes the checkpoint process. High values for this counter may indicate insufficient memory or that the recovery interval (set using sp_configure) is too high. | Defined in Description |
| SQL Server: Buffer Manager | Free List Stalls / sec | This monitors the number of requests per second where data requests stall because there are no buffers available. Any value greater than two indicates that SQL Server needs more memory. | 2 |
| SQL Server: Memory Manager | Memory Grants Pending | This is the number of processes per second that are waiting for a workspace memory grant. Any value higher than zero indicates a lack of memory. | 0 on average |
| SQL Server: Memory Manager | Target Server Memory(KB) | This is the maximum physical memory SQL Server can consume on the box. | This should be close to the physical size of memory. |
| SQL Server: Memory Manager | Total Server Memory (KB) | This is the amount of physical memory that is currently assigned to SQL Server. | This should be close to the Target Server Memory (KB). |
| Process | Private Bytes | This is the size, in bytes, of memory that this process has allocated that cannot be shared with any other process. | Defined in description |
