| |
 |
|
SQL Server Tips by Robin Schumacher
|
When to Consider More Memory
The most common set of configuration parameters that DBAs have had
to manage are those related to memory. However, starting with
version 7.0, Microsoft has provided auto-management for both the
total amount of server RAM given to SQL Server as well as the
allocation of the different memory regions such as the buffer and
plan cache.
The min server memory and max server memory parameters govern the
total gift of RAM provided SQL Server from the Windows machine.
These two parameters default in such a way as to allow SQL Server to
dynamically adjust the feed of memory to the database engine. In
normal circumstances, there is little need to disengage from SQL
Server autopilot and take manual control of memory allocations. It
is not uncommon for SQL Server to experience memory pressure and
require more memory than it is possible for it to obtain at that
time.
When deciding whether SQL Server could benefit from more physical
memory, the following checklist can be used:
-
Is the SQL Server total server memory
statistic at or near the target memory measure? If so, SQL
Server may need more RAM but be physically constrained from
allocating more.
-
Is the SQL Server page life expectancy
statistic less than 300? If TRUE, and large table scans do not
seem to be a problem, a shortage of RAM may be the cause.
-
Are the key memory ratios: buffer cache;
plan cache; and ad-hoc SQL below desired levels? While not the
best indicators of memory pressure, they can indicate a memory
constrained server.
-
Is the Windows performance counter of
Memory: Available Bytes consistently low or near zero?
-
Is the Windows performance counter of
Memory: Pages/sec consistently high, indicating paging and
swapping activity?
Another situation in which more memory may
be needed occurs when numerous query plans are involved in hash
joins or sort operations. These particular actions require more
memory resources than other query-based operations and may benefit
from increasing the min memory per query parameter. Increasing the
default may increase performance for these types of SQL requests,
but they will also increase overall memory consumption.
More memory may not be the silver bullet for an ailing SQL Server,
but it can work miracles in the right situation in which a server
has been accidently undersized for the workload.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
