Configuring HP SIM for Optimal Performance in Large Environments
20
For
highest
performance,
you
can
use
a
caching
controller
for
Microsoft
SQL
Server
log
files
if
the
controller
guarantees
that
data
entrusted
to
it
is
written
to
disk
ev
entually,
even
if
the
power
fails.
Moving
tempdb
location
If
you
can
add
more
physical
drives
to
the
system,
HP
recommends
that
you
move
tempdb
locations to
different
physical
drives
to
avoid
read
or
write
delays
associated
with
operating
system
and
SQL Server
background
activity.
By
default,
tempdb
is
placed
in
the
same
physical
directory
as
the
SQL
Server
binaries.
To
move
tempdb
to
another
physical
location,
enter
the
following
commands
in
SQL
Server
Management Studio
or
an
SQL
Server
prompt
(note
that
<new
location>
represents
the
desired
location
of
tempdb
on
the
new
physical
drive:
use
master
go
Alter
database
tempdb
modify
file
(name
=
tempdev,
filename
=
‘<new
location>\tempdb.mdf’)
go
alter
database
tempdb
modify
file
(name
=
templog,
filename
=
‘<new
location>\templog.ldf’)
go
SQL
Server
minimum
and
maximum
memory
When
tuning
SQL
Server,
it
might
be
important
to
adjust
the
minimum
and
maximum
amount
of
memory
that
can
be
consumed.
If
Systems Insight Manager
and
SQL
Server
are
running
on
the
same
machine,
it becomes
even
more
critical
as
the
operating
system,
Systems Insight Manager,
SQL
Server,
and
other
applications compete
for
RAM.
It
might
be
difficult
to
know
how
much
memory
to
allocate
to
SQL
Server,
especially
as
system
configuration
becomes
more
complicated
as
noted
above.
On
a
server
with
4GB
of
RAM
that
is
also
running
Systems Insight Manager,
HP
recommends
that
50%
of
memory
be
allocated
to
SQL
Server
maximum
value.
As the
system
runs,
gather
performance
statistics
by
perfmon
over
an
extended
period
of
time.
These statistics
can
be
analyzed
to
determine
if
it
is
necessary
to
adjust
the
minimum
and
maximum
values further.
The
settings
can
be
configured
as
follows
(note
that
values
are
in
MB):
sp configure
'min
server
memory',
1024
RECONFIGURE GO
sp configure
'max
server
memory',
2048
RECONFIGURE
GO
PAE
and
AWE
Physical Address Extension (PAE) refers to the feature of the X86 processors that allows them to address
more than 4 GB of memory. Previously, the X86 32-bit processors were only able to address
2
(4 GB)
of memory, however the addition of more address lines increased that amount to
2
(64 GB). All
additional address space is still unusable without operating system support, and since the 32-bit virtual
addressing space is unchanged, it must use page tables to map the 4GB virtual space to the 64GB
physical space. Applications are still limited to the traditional 4GB virtual space as well.
The
method
for
monitoring
disk
queuing
is
different
for
Microsoft
SQL
Server
log
files
than
it
is
for
Microsoft
SQL
Server
database
files.
You
can
use
the
Performance
Monitor
counters
Microsoft
SQL
Server:
Databases
database
instance:
Log
Flush
Waits
Times
and
Microsoft
SQL
Server:
Databases
database
instance:
Log
Flush
Waits/sec
to
view
log
writer
requests
waiting
on
the
disk
subsystem
for
completion.