SQL Server Memory Bottleneck

SQL Server Memory Bottleneck

Post by Greg J » Mon, 01 Dec 1997 04:00:00



This is a multi-part message in MIME format.

------=_NextPart_000_000B_01BCFDE4.5BDE5700
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

In the SQL Server Resource Kit, there is a note in the Performance =
Tuning section under Memory Configuration that states:
Symptom: If the SQLServer: I/O - Lazy Writes/sec counter indicates a =
great deal of activity over a long interval of time, then a memory =
bottleneck may be indicated. Typically, this counter should be zero =
until the LRUthreshold (default 3 percent) is reached with respect to =
free buffer pages. However, frequent activity may indicate not enough =
memory is available for data-page caching.
   =20
     Action: Compare the SQLServer: Cache - Number of Free Buffers value =
against the LRUthreshold value. This value is derived by obtaining the =
total number of buffers allocated by the DBCC MEMUSAGE statement and =
multiplying this number by the LRUthreshold percentage. If the number of =
free buffers is close to the derived value then either allocate more =
memory to SQL Server or increase the amount of system memory.
   =20
What is the LRUthreshold? How can you find it? Or set it?

------=_NextPart_000_000B_01BCFDE4.5BDE5700
Content-Type: text/html;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.71.1712.3"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#000000 face=3DArial size=3D2>In the SQL Server =
Resource Kit, there=20
is a note in the Performance Tuning section under Memory Configuration =
that=20
states:</FONT></DIV>
<BLOCKQUOTE>
    <DIV><FONT color=3D#000000 face=3DArial><FONT size=3D1>Symptom: If =
the SQLServer:=20
    I/O - Lazy Writes/sec counter indicates a great deal of activity =
over a long=20
    interval of time, then a memory bottleneck may be indicated. =
Typically, this=20
    counter should be zero until the LRUthreshold (default 3 percent) is =
reached=20
    with respect to free buffer pages. However, frequent activity may =
indicate=20
    not enough memory is available for data-page =
caching.</FONT></FONT><FONT=20
    size=3D1></FONT>&nbsp;</DIV>
    <DIV><FONT color=3D#000000 face=3DArial><FONT size=3D1><BR> Action: =
Compare the=20
    SQLServer: Cache - Number of Free Buffers value against the =
LRUthreshold=20
    value. This value is derived by obtaining the total number of =
buffers=20
    allocated by the DBCC MEMUSAGE statement and multiplying this number =
by the=20
    LRUthreshold percentage. If the number of free buffers is close to =
the=20
    derived value then either allocate more memory to SQL Server or =
increase the=20
    amount of system memory.</FONT></FONT><FONT=20
size=3D1></FONT>&nbsp;</DIV></BLOCKQUOTE>
<DIV><FONT color=3D#000000 face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#000000 face=3DArial size=3D2>What is the =
LRUthreshold? How can you=20
find it? Or set it?<BR></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_000B_01BCFDE4.5BDE5700--