Database Reference
In-Depth Information
Lazy Writes/Sec
The Lazy writes/sec counter records the number of buffers written each second by the buffer manager's lazy write
process. This process is where the dirty, aged buffers are removed from the buffer by a system process that frees up the
memory for other uses. A dirty, aged buffer is one that has changes and needs to be written to the disk. Higher values
on this counter possibly indicate I/O issues or even memory problems. The Lazy writes/sec values should consistently
be less than 20 for the average system. However, with as with all other counters, you must compare your values to a
baseline measure.
Memory Grants Pending
The Memory Grants Pending counter represents the number of processes pending for a memory grant within SQL
Server memory. If this counter value is high, then SQL Server is short of buffer memory. Under normal conditions,
this counter value should consistently be 0 for most production servers.
Another way to retrieve this value, on the fly, is to run queries against the DMV sys.dm_ exec_query_memory_grants.
A
null
value in the column grant_time indicates that the process is still waiting for a memory grant. This is one
method you can use to troubleshoot query timeouts by identifying that a query (or queries) is waiting on memory in
order to execute.
Target Server Memory (KB) and Total Server Memory (KB)
Target Server Memory (KB) indicates the total amount of dynamic memory SQL Server is willing to consume. Total
Server Memory (KB) indicates the amount of memory currently assigned to SQL Server. The Total Server Memory
(KB) counter value can be very high if the system is dedicated to SQL Server. If Total Server Memory (KB) is much less
than Target Server Memory (KB), then either the SQL Server memory requirement is low, the max server memory
configuration parameter of SQL Server is set at too low a value, or the system is in warm-up phase
.
The
warm-up
phase
is the period after SQL Server is started when the database server is in the process of expanding its memory
allocation dynamically as more data sets are accessed, bringing more data pages into memory.
You can confirm a low memory requirement from SQL Server by the presence of a large number of free pages,
usually 5,000 or more. Also, you can directly check the status of memory by querying the DMO sys.dm_os_ring_buffers,
which returns information about memory allocation within SQL Server. I cover sys.dm_os_ring_buffers in more detail
in the following section.
Additional Memory Monitoring Tools
While you can get the basis for the behavior of memory within SQL Server from the Performance Monitor counters,
once you know that you need to spend time looking at your memory usage, you'll need to take advantage of other
tools and tool sets. The following are some of the commonly used reference points for identifying memory issues on a
SQL Server system. A few of these tools are only of use for in-memory OLTP management. Some of these tools, while
actively used by large numbers of the SQL Server community, are not documented within SQL Server Books Online.
This means they are absolutely subject to change or removal.
DBCC MEMORYSTATUS
This command goes into the SQL Server memory and reads out the current allocations. It's a moment-in-time
measurement, a snapshot. It gives you a set of measures of where memory is currently allocated. The results from
running the command come back as two basic result sets, as you can see in Figure
2-5
.
