Performance Counter: você sabe o que monitorar?

Monitorar seu ambiente é sempre uma atividade legal, seja ela para achar a causa raiz de um problema (reativo) ou simplesmente para montar uma baseline (proativo). Mas o que eu devo monitorar? O que é importante coletar e como relacionar isso ao meu problema? Primeiramente tudo começa na escolha dos contadores de performance e isso concerteza irá fazer toda diferença no futuro. Aqui estão os contadores que utilizo para gerar uma baseline.

Performance Counter relacionados ao Sistema Operacional

Logical Disks
  • Avg Disk Bytes/Read
  • Avg Disk Bytes/Write
  • Avg Disk Sec/Read
  • Avg Disk Sec/Transfer
  • Avg Disk Sec/Write
  • Current Disk Queue Length
  • Disk Bytes/sec
  • Disk Read Bytes/sec
  • Disk Write Bytes/sec
  • Disk Reads/sec
  • Disk Transfers/sec
  • Disk Writes/sec
Memory
  • %Committed Bytes In Use
  • Available MB
  • Committed Bytes
  • Free System Page Table Entries
  • Pool Nonpaged Bytes
  • Pool Paged Bytes
Network Interfaces
  • Bytes Received/sec
  • Bytes Sent/sec
  • Bytes Total/sec
Processor
  • %Processor Time
  • %Privileged Time
System
  • Context Switches/sec
  • Exception Dispatches/sec
  • Processor Queue Length
  • System Calls/sec

Performance Counter relacionados ao SQL Server

Buffer Manager
  • Buffer cache hit ratio
  • Checkpoint Page/Sec
  • Database pages
  • Free list stalls/sec
  • Free pages (<= 2008R2)
  • Lazy writes/sec
  • Page life expectancy
  • Page lookups/sec
  • Page reads/sec
  • Procedure cache pages
  • Readahead pages/sec
  • Stolen pages (<= 2008R2)
  • Target pages 
  • Total pages (<= 2008R2)
General Statistics
  • Connection Reset/sec
  • Logins/sec
  • Logouts/sec
  • User   Connections
SQL Statistics
  • Batch Requests/sec
  • Safe Auto-Params/sec
  • Forced Parametrizations/sec
  • SQL Compilations/sec
  • SQL Re-Compilations/sec
Memory Manager
  • Free Memory (KB) (=2012)
  • Target Server Memory (KB) (=2012)
  • Stolen Server Memory (KB) (=2012)
  • Total Server Memory (KB) (=2012)

Com esses contadores, consigo montar uma visão geral de como está a saúde do meu ambiente. Esse foi o primeiro post de uma serie sobre monitoramento e o que tenho feito para monitorar meu ambiente.

Establishing a Performance Baseline
http://msdn.microsoft.com/en-us/library/ms190943(v=sql.110).aspx

Monitoring CPU Usage
http://msdn.microsoft.com/en-us/library/aa173932(SQL.80).aspx

Monitoring Memory Usage
http://msdn.microsoft.com/en-us/library/aa905152(SQL.80).aspx

SQL Server: Buffer Manager Object
http://msdn.microsoft.com/en-us/library/ms189628.aspx

SQL Server: General Statistics
http://msdn.microsoft.com/en-us/library/ms190697.aspx

SQL Server: SQL Statistics Object
http://msdn.microsoft.com/en-us/library/ms190911.aspx

This is Tiago Balabuch's website, and this is a bit of copy about him. He is enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours a Data Engineer who loves what he does and he is traveling in the cloud and surfing on the wave of the moment called Azure! Originally from Brazil and with encouragement from family and friends, Tiago moved to Europe in 2017 where lives in Ireland. In addition to being a data engineer, he is also active speaker in the SQL PASS events and keeps up to date on the key technologies and technical certifications. Tiago hold these certification MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2016/2014/2012. Simply psychedelic and manic he is just one more freak who likes SQL Server and its new features ...

6 Responses to “Performance Counter: você sabe o que monitorar?”

  1. Demétrio Silva, Responder

    Fala Tiiago blz?

    Vale deixar aqui também o link para o poster da Quest http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

    Grande abraço,

    Demétrio Silva

  2. Alex Rosa, Responder

    Outros dois links interessantes são:

    Logman
    http://technet.microsoft.com/en-us/library/cc753820.aspx

    Typeperf
    http://technet.microsoft.com/en-us/library/cc753182.aspx

    Usando essas linhas de commando, dificilmente vc irá voltar para interface gráfica para coletar dados.

  3. Eduardo Ramiro de Assis, Responder

    Excelente Tiago!
    Há muitos anos utilizo a seguinte referência para uma análise básica de possíveis gargalos (disco, processador, memória e rede): http://support.microsoft.com/kb/146005/en-us
    É antigo, da época do Windows NT ainda :), mas ajuda bastante porque apresenta os thresholds dos contadores.

Responda