Configuring HP SIM for Optimal Performance in Large Environments
Worker
threads
write
out
most
of
the
dirty
8-KB
pages
from
the
Microsoft
SQL
Server
buffer
cache.
Input/output
operations
are
scheduled
by
worker
threads
asynchronously
for
maximum
performance.
Lazy
Writer
Microsoft
SQL
Server
Lazy
Writer
helps
produce
free
buffers,
which
are
8-KB
data
cache
pages
that
contain
no
data.
As
Lazy
Writer
flushes
each
8-KB
cache
buffer
out
to
disk,
it
initializes
the
cache page
identity
so
that
other
data
can
be
written
into
the
free
buffer.
Lazy
Writer
produces
free
buffers during
periods
of
low
disk
input/output,
so
that
disk
input/output
resources
are
readily
available
for use
and
so
that
there
is
a
minimal
impact
on
other
Microsoft
SQL
Server
operations.
Microsoft
SQL
Server
automatically
configures
and
manages
the
level
of
free
buffers.
Monitor
the
Microsoft
SQL
Server:
Buffer
Manager,
Free
Buffers
object
to
ensure
that
the
free
buffer
level
remains
steady.
Lazy
Writer
maintains
the
level
of
free
buffers
to
keep
up
with
the
user
demand
for
free buffers.
The
Microsoft
SQL
Server:
Buffer
Manager,
Free
Buffers
object
should
not
drop
to
zero
as
this indicates
that
there
were
times
the
user
load
demanded
a
higher
level
of
free
buffers
than
the Microsoft
SQL
Server
Lazy
Writer
provided.
If
the
Lazy
Writer
cannot
keep
the
free
buffer
steady,
or
at
least
above
zero,
it
might
mean
the
disk
subsystem
cannot
provide
Lazy
Writer
with
the
disk
input/output
performance
that
it
needs
to maintain
the
free
buffer
level.
You
can
compare
drops
in
free
buffer
level
to
any
disk
queuing
to confirm
that
there
is
a
disk
subsystem
problem.
One
solution
to
the
disk
queuing
problem
is
to
add
more
physical
disk
drives
to
the
database
server
disk
subsystem
to
provide
more
disk
input/output
processing
power.
The
Microsoft
SQL
Server:
Buffer
Manager,
Lazy
Writes/sec
object
indicates
the
number
of
8-KB
pages
written
to
disk
by
Lazy
Writer.
Monitor
the
current
level
of
disk
queuing
in
Performance
Monitor
by
looking
at
the
counters
for (logical
or
physical)
Disk:
Average
Disk
Queue
or
Current
Disk
Queue.
The
disk
queue
needs
to
be
at a
level
less
than
2
for
each
physical
drive
associated
with
any
Microsoft
SQL
Server
activity.
For database
servers
that
employ
hardware
RAID
controllers
and
disk
arrays,
divide
the
number
reported by
disk
counters
(logical
or
physical)
by
the
number
of
actual
hard
disk
drives
associated
with
that logical
drive
letter
or
physical
hard
disk
drive
number
reported
by
the
Disk
Administrator
program. Microsoft
Windows
and
Microsoft
SQL
Server
are
unaware
of
the
actual
number
of
physical
hard disk
drives
attached
to
a
RAID
controller.
You
should
know
the
number
of
drives
associated
with
RAID array
controller
to
interpret
the
Performance
Monitor
reports
on
disk
queue
numbers.
Monitor
disk
queuing
on
hard
disk
drives
associated
with
Microsoft
SQL
Server
data
files
to
see
if
Microsoft
SQL
Server
is
sending
down
more
disk
input/output
requests
than
the
disks
can
handle.
If this
is
the
case,
then
more
disk
input/output
capacity
must
be
added
to
the
disk
subsystem
to
handle the
load.
Log
manager
Like
other
major
Relational
Database
Management
System
(RDBMS)
products,
Microsoft
SQL
Server
ensures
that
all
write
activity
(inserts,
updates,
and
deletes)
performed
on
the
database
is
not
lost
if
something
interrupts
the
Microsoft
SQL
Server
online
status
(power
failure,
disk
drive
failure,
fire
in
the
data
center,
and
so
on).
The
Microsoft
SQL
Server
logging
process
helps
guarantee
recoverability.
Before
any
implicit
(single
Transact-SQL
query)
or
explicit
(transaction
that
issues
Begin
Transaction,
Commit,
or
Rollback
statements)
transactions
can
be
completed,
the
Microsoft
SQL
Server
log manager
must
receive
a
signal
from
the
disk
subsystem
telling
it
that
all
associated
data
changes
have been
w
ritten
successfully
to
the
associated
log
file.
This
rule
guarantees
the
transaction
log
can
be read
and
reapplied
in
Microsoft
SQL
Server
when
the
management
server
is
restarted
after
an
abrupt shut
down
during
which
the
transactions
written
into
the
data
cache
are
not
yet
flushed
to
the
data files.
Flushing
data
buffers
are
Checkpoint
or
Lazy
Writer
responsibility.
Reading
the
transaction
log and
applying
the
transactions
to
Microsoft
SQL
Server
after
management
server
stoppage
is
referred to
as
recovery.
Disks
containing
Microsoft
SQL
Server
log
files
must
have
sufficient
disk
input/output
handling capacity
for
the
anticipated
transaction
load
because
Microsoft
SQL
Server
must
wait
for
the
disk subsystem
to
complete
input/output
to
Microsoft
SQL
Server
log
files
as
each
transaction
is
completed.