Configuring HP SIM for Optimal Performance in Large Environments
18
principles
in
mind
when
you
are
tuning
Microsoft
SQL
Server:
Plan
for
disk
input/output
subsystem
performance.
The
placement
of
the
paging
file,
transaction
logs,
and
system
databases
can
have
a
big
impact
on
system
performance
and
recoverability.
Place
log
files
and
database
files
on
different
physical drives.
Microsoft
SQL
Server
performance
can
be
enhanced
by
locating
log
files
and
database
files on
separate
physical
drives. The
physical
disk
subsystem
must
provide
a
database
server
with
sufficient
input/output
processing
power
for
the
database
server
to
run
without
disk
queuing,
which
indicates
poor
performance.
RAM
is
a
limited
resource
An
integral
feature
of
the
database
server
environment
is
the
management
of
RAM
buffer
cache.
Access
to
data
in
RAM
cache
is
much
faster
than
access
to
the
same
information
from
disk,
but
RAM
is
a
limited
resource.
If
database
input/output
can
be
reduced
to
the
minimum
required
set
of
data
and
index
pages,
these
pages
stay
in
RAM
longer.
Too
much
unnecessary
data
and
index
information
flowing
into
buffer
cache
quickly
pushes
out
valuable
pages.
The
focus
of
performance
tuning
is
to
reduce
input/output
so
that
buffer
cache
is
best
utilized.
Memory
is
a
cheap
resource
always
in
demand.
No
amount
of
tuning
can
compensate
for
a
lack
of
RAM.
Let
Microsoft
SQL
Server
do
most
of
the
tuning.
Microsoft
SQL
Server
has
been
enhanced
to
create
an
auto-configuring
and
self-tuning
database
server.
Take
advantage
of
the
auto-tuning
settings
available.
These
settings
help
Microsoft
SQL Server
run
at
peak
performance
even
as
user
load
and
queries
change
over
time.
Use
the
Create
Index
Wizard
feature
to
create
indexes
for
queries
you
add.
A
key
factor
in
maintaining
minimum
input/output
for
all
database
queries
is
to
ensure
that
good
indexes
are
created
and
maintained.
This
wizard
guides
you
through
a
series
of
steps
to
create
indexes,
prompting
you
for
input
along
the
way.
Take
advantage
of
Microsoft
SQL
Server
Profiler
and
Index
Tuning
Wizard.
You
can
use
Microsoft
SQL
Server
Profiler
to
monitor
and
log
a
Microsoft
SQL
Server
workload,
which
can
then
be
submitted
to
the
Index
Tuning
Wizard
to
tune
indexes
for
better
performance.
Regular
use
of
Microsoft
SQL
Server
Profiler
and
the
Index
Tuning
Wizard
helps
you
optimize
the
indexes,
allowing
Microsoft
SQL
Server
to
perform
well
with
changing
query
workloads.
Monitor
the
management
server
with
Windows
Performance
Monitor.
Regularly monitoring the management server, especially its disk utilization, reveals important clues for
troubleshooting performance problems. Use Microsoft SQL Performance Monitor before you have a
problem to establish a performance baseline for your management server. Microsoft SQL Server
provides counters that the Microsoft Performance Monitor can track. Counters that can help you
monitor disk activity include PhysicalDisk (Percentage Disk Time), PhysicalDisk (Average Disk
Queue Length), Microsoft SQL Server (Buffer Manager Page Reads/second), and Microsoft SQL
Server (Buffer Manager Page Writes per second). These disk input/output counters are available in
the Windows Server 2008 performance monitor tool.
Optimizing Microsoft SQL Server performance with Microsoft SQL
Server components
Worker threads
Microsoft
SQL
Server
maintains
a
pool
of
Microsoft
Windows
operating
system
threads
to
service
batches
of
Microsoft
SQL
Server
commands
submitted
to
the
database
server.
The
total
of
these threads
(called
worker
threads)
available
to
service
all
incoming
command
batches
is
dictated
by
the setting
for
the
sp configure
option
max
worker
threads.
If
the
number
of
connections
actively submitting
batches
is
greater
than
the
number
specified
for
maximum
worker
threads,
the
worker threads
are
shared
among
connections
actively
submitting
batches.
The
default
setting
(255)
works well
for
many
installations.