checkpoint process and flushing pages

I have a server that has just been upgraded from 1Gb RAM to 4Gb. Previously,
the cache was only around 800Mb and I could see that alot of data was being
flushed from the cache in order to bring in new data.
Now it has 4Gb but is only using around 1.8Gb (total/target pages).
My question is, during a checkpoint when dirty pages are written to disk,
does it also flush these pages from the cache? I would expect that the
server will use the full 4Gb as best it can and only flush data once the
cache is full but this doesn't seem to be whats happening.
Best regards
MarkAdd /3GB in the boot.ini file in the OS root folder, if you have memory
pressure, you should see that counter value (total/target) move up to 2.x GB
range.
Linchi
"Mark Baldwin" wrote:

> I have a server that has just been upgraded from 1Gb RAM to 4Gb. Previousl
y,
> the cache was only around 800Mb and I could see that alot of data was bein
g
> flushed from the cache in order to bring in new data.
> Now it has 4Gb but is only using around 1.8Gb (total/target pages).
> My question is, during a checkpoint when dirty pages are written to disk,
> does it also flush these pages from the cache? I would expect that the
> server will use the full 4Gb as best it can and only flush data once the
> cache is full but this doesn't seem to be whats happening.
> --
> Best regards
> Mark
>
>|||Hello Mark,
I understand that after you upgraded ram from 1GB to 4GB, you still only
see SQL uses 1.8GB. If I'm off-base, please let's know.
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
Also, you could enable /3GB switch as Linchi mentioned to let SQL to use
3GB virtual memory for user mode space. Please see the following article
for details:
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/defaul...kb;EN-US;274750
Per your question, during a checkpoint when dirty pages are written to
disk, the pages might still be needed and may not flush from the pool. The
checkpoint process goes through the buffer pool, scanning the pages in
buffer number order, and when it finds a dirty page, it looks to see
whether any physically contiguous (on the disk) pages are also dirty so
that it can do a large block write. But this means that it might, for
example, write pages 14, 200, 260, and 1000 at the time that it sees page
14 is dirty. (Those pages might have contiguous physical locations even
though they're far apart in the buffer pool. In this case, the
noncontiguous pages in the buffer pool can be written as a single operation
called a gather-write. I'll define gather-writes in more detail later in
this chapter.) As the process continues to scan the buffer pool, it then
gets to page 1000. Potentially, this page could be dirty again, and it
might be written out a second time. The larger the buffer pool, the greater
the chance that a buffer that's already been written will get dirty again
before the checkpoint is done. To avoid this, each buffer has an associated
bit called a generation number. At the beginning of a checkpoint, all the
bits are toggled to the same value, either all 0's or all 1's. As a
checkpoint checks a page, it toggles the generation bit to the opposite
value. When the checkpoint comes across a page whose bit has already been
toggled, it doesn't write that page. Also, any new pages brought into cache
during the checkpoint get the new generation number, so they won't be
written during that checkpoint cycle. Any pages already written because
they're in proximity to other pages (and are written together in a gather
write) aren't written a second time.
All databases, except for tempdb are checkpointed. Tempdb does not require
recovery (it is recreated every time SQL Server starts) so flushing data
pages to disk is not optimal for tempdb and SQL Server avoids doing so.
Therefore, dirty page may not get to 0 even if checkpoint is run.
You could use DBCC CHECKMEMORYSTATUS to check your SQL Server 2000 memory
status. A dirty page cannot be removed from the SQL Server buffer pool
until the associated log records have been written and the page itself
written to stable media. You can refer to this article:
SQL Server 2000 I/O Basics
http://www.microsoft.com/technet/pr...n/sqlIObasics.m
spx
Here is an extraction:
Dirty Page Latency - A page is considered dirty when data modifications
have taken place. A dirty page cannot be removed from the SQL Server buffer
pool until the associated log records have been written and the page itself
written to stable media. Increasing the checkpoint interval (by increasing
the recovery interval) on a busy system moves the pressure of handling
dirty pages to the lazy writer code line. This can result in overall
performance degradation because the lazy writer is not designed to perform
checkpoint-like activities.
The lazy writer does perform proper activity on the dirty pages to ensure
data integrity and free list maintenance but, unlike the checkpoint
process, it is not designed to remove the dirty page I/O latency.
Checkpoints allow dirty pages to be written more aggressively. Leaving the
checkpointing actions to the lazy writer introduces latency because the
lazy writer is forced to perform I/O to age a buffer instead of simple,
in-memory operations to maintain the free list(s). If you have adjusted the
recovery interval, you should watch the lazy writer performance counter(s)
activity closely.
Hope this is helpful. Please feel free to let's know if you have any
questions or comments. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.