Databases Reference
In-Depth Information
Identifying memory pressure can be further categorized as identifying either internal or external mem-
ory pressure. If SQL Server is unable to acquire sufficient memory, it is external memory pressure, or if
there's contention within memory allocated to SQL Server, it is internal memory pressure. Internal Mem-
ory pressure can occur when the SQL Server Buffer Pool (the memory location that holds data pages)
is reduced in response to other processes (linked server, extended stored procedures, COM
and so on)
consuming memory in the same memory address space as SQL Server. System Monitor can help in iden-
tifying and eliminating external memory pressure, and with some of the SQL Server-specific counters
(such as Buffer Manager) you're able to get good visibility of internal memory pressure too. There are
also some great Dynamic Management Views (DMVs) and Database Consistency Checks (DBCCs) that
you can use to investigate memory pressure.
+
An initial high-level indicator of the status of user mode memory availability is the Memory counter
Available Bytes (also available as Available KBytes and Available MBytes). On most healthy servers you
should expect to see at least some available memory at all times. Typically you should be concerned if
available memory drops below around 4MB.
Windows creates a Virtual Address Space (VAS) for every application. The VAS may be made up from
physical memory and memory provided in the page file. The page file will reside on the system drive
(unless it's been moved elsewhere), and accessing data from the page file is considerably slower than
accessing pages that are held in memory. Notably, in the case of SQL Server you can sometimes see
SQL making great efforts to pre-empt user requests and load data pages from disk into memory, only to
see the operating system serve this requirement from the page file. Obviously this is counter-intuitive,
increases disk I/O, and defeats the purpose of SQL Server reading the data pages ahead. Unfortunately,
SQL Server doesn't know whether Windows will offer memory from physical RAM or the page file,
as it's only aware of the VAS. There is a way to request the operating system only serve requests from
SQL Server from RAM, not from the page file. This is controlled by the Lock Pages in Memory privilege,
which must be granted to the SQL Server service account, which is covered in more detail in Chapter 6.
Monitoring page file activity will also give you some idea of whether you're experiencing a memory
shortage. There's a counter within System Monitor that you can use for this very purpose. It's called
memory pages/second
. This will tell you the total number of data pages that have to be read into memory
from the page file or written to the page file, per second. If a system is running low on memory, you
should expect to see a ramp-up in memory pages/second as the operating system is forced to use the
paging file for temporary data storage. The reason the memory pages/second counter increases is that as
a system runs low on memory, Windows becomes more reliant on the page file to service user requests.
Hence more data pages are read and written. You should expect this counter to sit at or around zero. If
it's any different (especially on a SQL Server), you should continue investigating memory pressure.
If you suspect a system is experiencing poor performance because memory pressure is causing increased
page file activity, you can confirm this. Take a look at the Paging File counters. Here you'll see two objects:
% Usage and % Usage Peak. If you're not familiar with the server or haven't checked already, it might
now be worth checking how much physical memory the system has and the size and location of the Page
File. Once you've added % Usage and % Usage Peak, you should look for some correlation between an
increase in memory pages/second and an increase in % Usage of the paging file. If you see a relationship
between high memory pages/second and increased paging file % Usage, you can be confident that you're
seeing external memory pressure.
It's worth mentioning at this point that an increase in memory pages/second alone isn't necessarily
indicative of a low memory condition. This counter also includes a count of sequential reads for memory
mapped files. So you'll need to qualify memory pressure by considering available memory and page file
activity too.
Search WWH ::
Custom Search