SQL Server Hardware Optimization

An important concern in optimizing the
hardware platform is hardware components that restrict performance, known as
bottlenecks. Quite often, the problem isn’t correcting performance bottlenecks
as much as it is identifying them in the first place.

Start with obtaining a performance
baseline. You monitor the server over time so that you can determine Server
average performance, identify peak usage, determine the time required for
backup and restore activities, and so on. This gives you a baseline from which
you can judge the server against to determine if you have a performance
bottleneck.

Hardware bottlenecks

Most hardware bottlenecks can be attributed
to disk throughput, memory usage, processor usage, or network bandwidth. Usually,
the best tools you can use to identify hardware bottlenecks are System Monitor
and counter logs. You can also get useful information from the Windows Event
Viewer logs.

One problem can often mask another. You
might suspect hard disk performance as the cause of degraded query performance.
However, when you check the hard disk performance, you find the hard disk
performing well but under a less-than-expected load. It could be that the
problem actually lies with the network and that the bottleneck is in getting
the data to and from the hard disk.

You can sometimes relieve bottlenecks by
rescheduling resource-intensive SQL Server activities so that there’s less of a
load on the server.

SQL Server bottlenecks

Some performance problems might appear to
be hardware bottlenecks initially, but actually have a different cause. SQL
Server bottlenecks often look like hardware problems until you collect
performance data. Common SQL Server bottlenecks include Locking conflicts, Resource
contention, Excessive user connections and activity, and tempdb activity.

The SQL Server Activity Monitor and SQL
Server Profiler, along with Windows System Monitor or Performance Monitor, are
the primary tools you use to monitor SQL Server performance.

One area you should check is the
performance of queries. If you have queries that use excessive server
resources, overall performance suffers. SQL Profiler and the Database Engine
Tuning Advisor can both help you gather information about queries.

Monitoring processor use

High processor utilization can be an
indication that:

  1. You
    need a processor upgrade (faster, better processor).
  2. You
    need an additional processor.
  3. Processor
    affinity isn’t configured correctly.
  4. You
    have a poorly designed application.

By monitoring processor activity over time,
you can determine if you have a processor bottleneck and often identify the
activities placing an excessive load on the processor. Counters that you should
monitor include those in the following table.

Object

Counter

Description

Processor

%
Processor Time

Value should be consistently less than
90%. A consistent value of 90% or more indicates a processor bottleneck.
Monitor this for each processor in a multiprocessor system and for total
processor time (all processors).

Processor

%
Privileged Time

Amount of time that’s spent processing
operating system commands. This value should be relatively low in comparison
to the % Processor Time. High values here and in Physical Disk counters can
indicate that you need a faster disk subsystem.

Processor

%
User Time

Time spent on user processes
(applications), which includes SQL Server. If this value is relatively low in
comparison to the % Processor Time, the problem could be a server process.
Determine the process that’s loading the processor.

System

%
Total Processor Time

Average processor time for all processors
in a multiprocessor system. There’s typically no reason to monitor this in a
single processor system.

System

Context
Switches/sec

The rate at which processors are switched
between threads. A value of 8,000 with a % Processor Time of 90 or more
indicates that you should enable SQL Server fiber mode (Windows NT fibers)
scheduling.

System

Processor
Queue Length

The number of threads waiting for
processor time. A consistent value of 2 or more can indicate a processor
bottleneck. This value is system-wide, not per processor.

If you observe a significant difference in
the “%Processor Time” values for the processors in a multiple processor system,
it could indicate that SQL Server isn’t configured to use all available
processors. If SQL Server is the only server application running on the
computer, then all processors should be available to SQL Server. You can
configure processor affinity through the Server Properties on the Processors
page.

In most cases, a processor bottleneck
indicates you need to either upgrade to a faster processor or to multiple
processors. In some cases, you can reduce the processor load by fine-tuning
your application.

Monitoring disk use

SQL Server is a disk-intensive application.
Because of this, the disk subsystem is often a bottleneck to system
performance. Disk subsystem components include the hard disk drives and
controllers as well as CD-ROM drives and tape drives if used. Typically, the
counters you need to monitor include those described in the following table.

Object

Counter

Description

PhysicalDisk

% Disk Time

Should be consistently less than 90%. In
RAID subsystems, the value can range higher than 100%, and you need to rely
primarily on disk queue values.

PhysicalDisk

Avg. Disk Queue  Length

Should be less than or equal to two times
the  number of spindles.

PhysicalDisk

Current Disk Queue  Length

Number of disk requests waiting at any
one time. Helps determine which activities are generating the most disk
activity.

Memory

Page Faults/sec

Determine whether the disk activity is
being generated by SQL Server or because of excessive paging.

SQLServer: Buffer Manager

Page Reads/sec

Determine the amount of disk activity
being generated by SQL Server.

You determine the percentage of the
activity being generated by SQL Server by comparing SQL Server read/write activity
with disk read/write activity. If the hard disk is configured with a single
partition, use physical disk read/sec and write/sec counters. If the hard disk
is configured as multiple partitions, you should use the logical disk read/sec
and write/sec counters for those logical drives employed by SQL Server. Disk
bottlenecks often require either installation of faster disk subsystems or
multiple hard disks and disk controllers to allow split reads and writes.

Monitoring memory use

There are two general categories of memory
counters that you should monitor:

  1. Potential
    low-memory conditions in system memory.
  2. SQL
    Server memory usage so you can determine whether or not SQL Server is causing
    the problem.

The following table describes counters that
monitor memory use.

Object

Counter

Description

Memory

Available
Bytes

Amount of memory available for processes
and should be consistently higher than 5,000 KB.

Memory

Pages/sec

Should never climb consistently above 0.

Process

Page
Faults/sec

A high value indicates excessive paging,
which can result in both memory and the hard disk becoming performance
bottlenecks. You must determine if the problem is being caused by SQL Server
or by another process running on the system.

Process

Working
Set

Monitor this value for each instance of
SQL Server running on the system to determine the amount of system memory
being used by SQL Server. This value should be greater than 5,000 KB at all
times.

SQLServer:
Buffer Manager

Buffer
Cache Hit Ratio

For most applications, this value should
be 90 or higher, indicating that most data requirements are being serviced
from the cache. A lower value can indicate a memory bottleneck or a poorly
designed application.

SQLServer:
Buffer Manager

Total
Pages

A low value here can indicate a need to
install additional memory. Typically, a low number here is accompanied by a
low Buffer Cache Hit Ratio.

SQLServer:
Memory Manager

Total
Server Memory (KB)

If this value is relatively high in
comparison to total system memory, it’s a good indicator that you should
install more memory.

If at all possible, you should avoid
running other server applications on a system that’s supporting SQL Server. If
this isn’t possible and if you can’t monitor memory used by the other
application directly, you must infer its memory usage by comparing system
memory and SQL Server memory counters. In most situations, the preferred
configuration is to let SQL Server manage memory use automatically.




Array

No comments yet... Be the first to leave a reply!