Databases Reference
In-Depth Information
significant drops in page life expectancy, this would be a cause for concern. If you're able to
correlate any drops in performance with perceived application performance issues (assum-
ing the poor performance isn't completely continuous), this could be very valuable in under-
standing the performance issue.
❑
SQLServer:Buffer Manager — Buffer cache hit ratio:
The buffer cache hit ratio reports the num-
ber of pages requested by a query that were found in the SQL Server buffer pool (in
physical memory). If data pages are not found in the buffer, SQL Server must read them into the
buffer from disk. This is usually a slow process because of disk latency and seek times. Even on a
responsive enterprise SAN, the time to read a page from disk compared with time to read a page
from memory is many multiples greater.
❑
The size of the buffer pool is determined by the
sp_configure
min and max server memory
options. It's worth noting that just because you've configured these options, that doesn't mean
that SQL Server has been able to reserve this memory. This is especially true on a shared server
where other applications may reserve that memory faster than SQL Server and the operating
system is unable to release the memory addresses to SQL Server.
Best Practice
You should plan for a buffer pool of sufficient size to achieve at least a 98 percent buffer
cache hit ratio. While monitoring, if you observe a buffer cache hit ratio of less than
98 percent, it's likely that the server doesn't have sufficient free memory.
SQLServer:Buffer Manager — Stolen pages:
Stolen pages are those pages in memory which are
stolen by another process. Servers which are experiencing memory pressure will typically show
high quantities of stolen pages relative to the total target pages. More detailed information on
memory pressure can be obtained with the
DBCC MEMORYSTATUS
command from a
query window.
❑
SQLServer:Memory Manager —Memory Grants Pending:
Memory grants pending is
effectively a queue of processes awaiting a memory grant. In general, if you have any processes
queuing waiting for memory, you should expect degraded performance. The ideal situation for a
healthy server is no outstanding memory grants.
❑
SQLServer:Buffer Manager — Checkpoint pages/sec:
The SQL Server checkpoint operation
requires all dirty pages to be written to disk. The checkpoint process is expensive in terms of
disk input/output (I/O). When a server is running low on memory the checkpoint process will
occur more frequently than usual as SQL Server attempts to create space in the buffer pool. If you
observer sustained high checkpoint pages/second compared with normal rates for your server,
it's a good indication of a low memory condition.
❑
❑
SQLServer:Buffer Manager — Lazy writes/sec:
This counter records the number of times per
second that SQL Server relocates dirty pages from the buffer pool (in memory) to disk. Again,
disk I/O is expensive and you should attempt to provide SQL Server with enough space for the
buffer pool that lazy writes are as close to zero as possible. If you're seeing lazy writes of 20 per
second or more, then you can be sure the buffer pool isn't big enough.
Configuration-BasedMemory Bottlenecks
There are two groups of memory problems that you should be familiar with; these are internal
memory pressure and external memory pressure. External memory pressure arises when SQL Server
Search WWH ::
Custom Search