Database Reference
In-Depth Information
Memory and disk I/O are closely related. Even if you think you have a problem that is directly memory related,
you should also gather I/O metrics in order to understand how the system is behaving between the two resources.
I'll now walk you through these counters to get a better idea of possible uses.
Available Bytes
The Available Bytes counter represents free physical memory in the system. You can also look at Available Kbytes
and Available Mbytes for the same data but with less granularity. For good performance, this counter value should
not be too low. If SQL Server is configured for dynamic memory usage, then this value will be controlled by calls to a
Windows API that determines when and how much memory to release. Extended periods of time with this value very
low and SQL Server memory not changing indicates that the server is under severe memory stress.
Pages/Sec and Page Faults/Sec
To understand the importance of the Pages/sec and Page Faults/sec counters, you first need to learn about page faults.
A
page fault
occurs when a process requires code or data that is not in its
working set
(its space in physical memory).
It may lead to a soft page fault or a hard page fault. If the faulted page is found elsewhere in physical memory, then it
is called a
soft page fault. A hard page fault
occurs when a process requires code or data that is not in its working set or
elsewhere in physical memory and must be retrieved from disk.
The speed of a disk access is in the order of milliseconds for mechanical drives or as low as .1 milliseconds for a
solid-state drive (SSD), whereas a memory access is in the order of nanoseconds. This huge difference in the speed
between a disk access and a memory access makes the effect of hard page faults significant compared to that of soft
page faults.
The Pages/sec counter represents the number of pages read from or written to disk per second to resolve hard
page faults. The Page Faults/sec performance counter indicates the total page faults per second—soft page faults
plus hard page faults—handled by the system. These are primarily measures of load and are not direct indicators of
performance issues.
Hard page faults, indicated by Pages/sec, should not be consistently higher than normal. There are no
hard-and-fast numbers for what indicates a problem because these numbers will vary widely between systems
based on the amount and type of memory as well as the speed of disk access on the system.
If the Pages/sec counter is high, you can break it up into Pages Input/sec and Pages Output/sec.
•
Pages Input/sec
: An application will wait only on an input page, not on an output page.
•
Pages Output/sec
: Page output will stress the system, but an application usually does not see
this stress. Pages output are usually represented by the application's dirty pages that need to
be backed out to the disk. Pages Output/sec is an issue only when disk load become an issue.
Also, check Process:Page Faults/sec to find out which process is causing excessive paging in case of high
Pages/sec. The Process object is the system component that provides performance data for the processes running on
the system, which are individually represented by their corresponding instance name.
For example, the SQL Server process is represented by the sqlservr instance of the Process object. High numbers
for this counter usually do not mean much unless Pages/sec is high. Page Faults/sec can range all over the spectrum
with normal application behavior, with values from 0 to 1,000 per second being acceptable. This entire data set means
a baseline is essential to determine the expected normal behavior.
Paging File %Usage and Page File %Usage
All memory in the Windows system is not the physical memory of the physical machine. Windows will swap memory
that isn't immediately active in and out of the physical memory space to a paging file. These counters are used to
understand how often this is occurring on your system. As a general measure of system performance, these counters
