Contadores do Perfmon

By José Eduardo Fiamengui Júnior (oradeep)

Objetivo: Mostrar alguns contadores interessantes do Perfmon e algumas diretrizes para métricas de desempenho:

Perfmon (métricas e diretrizes)

Capacity Planning: Perfmon

1-) Contadores para os servidores
com Sistema Operacional Windows

a) CPU e Memória

Métrica Contador Recomendável Descrição
Processor

% Processor Time

< = 80 %

Percentage
of elapsed time the processorspends executing
non-idle threads.
System

Processor Queue Length

<=4

Number
of threads waiting for CPU cycles,where
< 12 per CPU is good/fair, < 8 is better,< 4 is best.
Memory

Memory Pages / sec

Ver descrição

Often
referenced in older documentation.Useful
only in combination with PagesInput/Sec,
%Usage, %Usage Peak. 
Memory

Available MBytes

100 MB Unused

Unused
physical memory (not page file)
Memory

Paging File

% Usage < 70

Amount
of Page File in use, which indicatesthe
server is substituting disk space formemory. 

b) Discos

Métrica Contador Recomendável Descrição
Physical Disk

% Disk Time

<=
50 %
This
counter is deceptive because it makes no accommodation for multiple spindles.Thus,
the more spindles (i.e. physical hard disks) you have, the higher the
percentilevalues
can go. Conversely, if these spindles are shared across LUNs or other
services, youmay
have high numbers on this counter without any correlation to SQL Server
activity.

In short, there are
better ways to find out SQL Server’s I/O performance.

Physical Disk

Avg. Disk Queue Length

2 por disco. Por exemplo, se falamos de um Array de 5 discos essa
métrica teria como ideal 10
The
way in which Windows measures disk queues, combined with the amount of cachethat
storage vendors provide with hard disk controllers, SANs, and hard disks
themselvesmeans
that Windows might perceive that data is written all the way to disk, when in
factthe data is actually
sitting in a hardware-level cache somewhere.
Physical Disk

Avg. Disk Sec/Read

< 8ms A
key measure of disk latency representingthe
average time, in milliseconds, ofeach
read to disk where > 20 is poor, <20 isgood/fair,
<12 is better, <8 is best
Physical Disk

Avg. Disk Sec/Write

< 8ms (non
cached) e < 1ms (cached)
A
key measure of disk latency representingthe
average time, in milliseconds, of eachwrite
to disk, where non-cached writes(
> 20 poor, <20 fair, <12 better, <8 best)

differ
significantly from cached writes

(>
4 poor, <4 fair, <2 better, <1 best ).

For
OLTP databases, the lower this number

the
better, especially for disks holding the

transaction log.

c) Redes

Métrica Contador Recomendável Descrição
Network Interface Total Bytes / sec
(SEND + RECEIVED)
Ver descrição The
number of bytes sent and receivedover
a specific network adapter, includingframing
characters. Be sure to recordthe
throughput of your SQL Server’s

NIC
card(s). Watch for this value possibly

exceeding
the NIC’s specifications,

especially
when conducting large and/

or
multiple backups or copies to network

drives.
A high-speed network and/or a NIC

dedicated
to admin processes often alleviates

this
bottleneck. This counter is a sum

of
“Network Interface\\Bytes Received/

sec”
and “Network Interface\\Bytes Sent/

sec”.
In some situations, you may wish to

determine
both inbound and outbound

network
traffic separately.This counter is

particularly
useful in iSCSI environments

where
it can help to measure disk I/O

when the NIC is dedicated to storage.

 

2-) Para os servidores com database SQL Server

Métrica Contador Recomendável Descrição
Sql Server: Buffer Manager Buffer Cache Hit Ratio No máximo 90%, o ideal é 99% Long
a stalwart counter used by SQL Server DBAs, this counter is no longer very
useful.It
monitors the percentage of data requests answer from the buffer cache since
the lastreboot.
However, other counters are much better for showing current memory pressurethat
this one because it blows the curve. For example, PLE (page life expectancy)
might

suddenly
drop from 2000 to 70, while buffer cache hit ration moves only from 98.2 to

98.1.
Only be concerned by this counter if it’s value is regularly below 90 (for
OLTP) or 80

(for very
large OLAP).

Sql Server: Buffer Manager Page life Expectancy Inferior a 300 s Tells, on
average, how many seconds SQL Serverexpects a
data page to stay in cache. The targeton an OLTP
system should be at least 300 (5min). When
under 300, this may indicate poor

index design
(leading to increased disk I/O

and less
effective use of memory) or, simply, a

potential shortage of memory.

Sql Server: General Statistics User Connections Ver descrição The number of
users currently connectedto the SQL
Server. This counter shouldroughly track
with “Batch Requests/Sec”.They should
generally rise and fall together.

For example,
blocking problems could

be revealed
by rising user connections,

lock waits
and lock wait time coupled

with
declining batch requests/sec.

Sql Server: Database BatchRequests/Secv Ver descrição Number of
batch requests received per second,and is a good
general indicator for the activitylevel of the
SQL Server. This counter is highlydependent on
the hardware and quality of code

running on
the server. The more powerful the

hardware, the
higher this number can be, even

on poorly
coded applications. A value of 1000

batch
requests/sec is easily attainable though a

typical
100Mbs NIC can only handle about 3000

batch
requests/sec.Many other counter thresholds

depend upon
batch requests/sec while, in

some cases, a
low (or high) number does not

point to poor
processing power. You should

frequently
use this counter in combination with

other
counters, such as processor utilization or

user
connections.In version 2000, “Transactions/

sec” was the
counter most often used to measure

overall
activity, while versions 2005 and later use

“Batch
Requests/sec”. Versions 2005 prior to SP2,

measure this
counter differently and may lead to

some
misunderstandings. Read the footnote for

more details.

Sql Server: Database Data File (s) size
Kb
Ver descrição Cumulative
size (KB) of all the data files in thedatabase
including any automatic growth.Monitoring
this counter is useful, for example,for
determining the correct size of tempdb.
Sql Server: Database Percent Log Used < 80% Percentage of
space in the log that is in use.Since all
work in an OLTP database stops untilwrites can
occur to the transaction log, it’s avery good
idea to ensure that the log never fills

completely. Hence, the recommendation
to

keep the log
under 80% full.

About these ads

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Uncategorized. ligação permanente.

Deixar uma resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

WordPress.com Logo

Está a comentar usando a sua conta WordPress.com Log Out / Modificar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Log Out / Modificar )

Facebook photo

Está a comentar usando a sua conta Facebook Log Out / Modificar )

Google+ photo

Está a comentar usando a sua conta Google+ Log Out / Modificar )

Connecting to %s