In What to Monitor on SQL Server (Part 1), we reviewed two of the four main categories of performance metrics to monitor in order to gauge SQL Server efficacy, namely Disk Activity and Processor Utilization. Today's blog will cover Memory and Server operations.
Memory
Memory Utilization monitoring attempts to determine the amount of memory used by the database server while processing a request. You should monitor your instance of SQL Server periodically to confirm that memory usage is within typical ranges.
By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache), depending on the physical memory load that the operating system reports. As long as sufficient memory (between 4 MB and 10 MB) is available to prevent paging, the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and obtain several megabytes of memory each second. This allows for SQL Server to quickly adjust to memory allocation changes.
SQL Server works with objects and counters, with each object comprising one or more counters. For example, the Buffer Manager object provides counters to monitor how SQL Server uses memory to store data pages, and buffer pool.
To monitor for a low-memory condition, use the following counters:
Available MBs: indicates how much memory is available for new processes. If available memory is constantly low and server load cannot be reduced, it’s necessary to add more RAM.
Pages/sec: this counter indicates how many times the virtual memory is getting accessed. A rule of thumb says that it should be lower than 20. Higher numbers might mean excessive paging. Using Memory: Page Faults/sec can further indicate whether SQL Server or some other process is causing it.
You can also establish upper and lower limits for how much memory is used by the SQL Server database engine with the min server memory and max server memory configuration options.
Other Server-related Metrics
Although Disk Activity, Processor Utilization, and Memory are the most important areas to monitor, there are a few other general server metrics worth checking.
Access Methods – Full scans/sec: higher numbers (more than 1 or 2) may mean you are not using indexes and resorting to table scans instead.
Buffer Manager – Buffer Cache hit ratio: This is the percentage of requests serviced by data cache. When cache is properly used, this should be over 90%. The counter can be improved by adding more RAM.
Memory Manager – Target Server Memory (KB): indicates how much memory SQL Server “wants”. If this is the same as the Memory Manager — Total Server Memory (KB) counter (see below), then you know SQL Server has all the memory it needs.
Memory Manager — Total Server Memory (KB): much memory SQL Server is actually using. If smaller than the Memory Manager — Target Server Memory (KB), then SQL Server could benefit from more memory.
Locks – Average Wait Time: This counter shows the average time needed to acquire a lock. This value needs to be as low as possible. If unusually high, you may need to look for blocking processes. You may also need to examine your users’ SQL statements, as well as check for any other I/O bottlenecks.
Although these metrics are some of the most useful, SQL Server offers a number of other metrics that may also come in handy. We'll examine these in a future blog.
