«

»

out 30

Perfmon e SQL Server

O Perfmon do Windows possui muitos contadores de SQL, hoje veremos alguns que ajudam muito em troubleshooting de performance.

Ao fazer uma análise de lentidão é muito importante termos dados armazenados para podermos identificar com maior clareza o que realmente está acontecendo. Portanto, sugiro utilizar sempre o Performance Monitor do Windows para armazenarmos estes dados continuamente. Assim, quando houver um problema de lentidão, será possível comparar estes dados com os dados anteriores ao problema.

Além dos contadores básicos do Windows, podemos adicionar contadores do SQL que com certeza irão nos ajudar muito. Vamos entendê-los:

Page Life Expectancy

Com este indicador podemos observar quanto tempo as páginas ficam em buffer de memória. Um baixo valor, significa que a página teve que ser substituída por outra página utilizada recentemente, ou seja, não havia espaço na memória suficiente para armazenar ambas. Desta forma, podemos concluir que baixos valores indicam gargalo de memória ou memória insuficiente para o SQL Server. É muito comum vermos recomendações de valores como 300 segundos mas vejo muitos ambientes com valores bem acima disso sofrendo pressão interna de memória. O interessante é ver a curva no gráfico do momento que o SQL Server troca as páginas gravando em disco e trazendo outras, indicando que em um determinado momento do dia uma grande quantidade de páginas foi requisitada.

Buffer cache hit ratio

Significa quantas vezes o SQL precisou de uma informação e já tinha pronta em sua memória, ou seja, não foi necessário buscar em disco. Valores em torno de 95% são ideais para a maioria dos ambientes mas pode variar de acordo com as particularidades do seu ambiente. Quanto maior, melhor. Geralmente encontramos este valor em 100% ou 99,998% mas depende do seu ambiente.

Batch Requests/sec

Quantos comandos são executados por segundo. Dentro de uma query podemos ter vários comandos (select, insert, update, alter database, etc.) e este importante contador mostra quantos destes comandos estão sendo executados por segundo. Aqui é importante você saber a quantidade quando o ambiente está normal, quando está sob pressão mas respondendo bem e quando apresenta lentidão. Imagine que seu servidor pode ficar lento por atingir o máximo de sua capacidade ou pode estar lento sem ter atingido o máximo da capacidade. Lembre-se que uma batch pode realizar várias transações.

Page Splits/sec

Tendo em vista que os dados são armazenados em blocos chamados páginas, quando estas estão fragmentadas o número de salto entre as páginas para conseguir uma informação aumenta consideravelmente. Consequentemente serão necessárias mais operações de I/O, ou seja, um número menor é sinal de que temos as páginas cheias de dados ou que os dados
solicitados estão dentro da mesma página. Como exemplo, se dermos um select em uma tabela e os registros estiverem dentro da mesma página, iremos saltar 0 vezes. Se os registros estiverem metade em uma página e metade em outra, iremos saltar 1 vez.

Compilations/sec

Mostra quantas compilações foram executadas por segundo. O SQL quando recebe uma query faz um trabalho de conversão para um plano de execução chamado de “Execution Plan”. Este execution plan mostra quais tabelas verificar, se fará um scan em toda a tabela ou se usará um índice, como vai acessar o dado, etc. Aqui o valor depende pois muitas compilações é sinal de que o cache de execution plans não está sendo eficiente, porém, um servidor com um ótimo hardware é capaz de fazer mais compilações por segundo que um servidor com poucos recursos de hardware.

Lazy Writer/sec

O Lazy Writer é o mecanismo responsável por remover páginas não utilizadas do Data Cache do SQL para dar lugar a novas páginas solicitadas, quando há algum tipo de pressão de memória. Suponhamos que seu SQL já atingiu seu Max Server Memory e você executou uma query solicitando registros que não estão no Data Cache. O Lazy Writer faz uma varredura no Data Cache utilizando um algorítimo LRU-K, procurando por páginas cuja pontuação de utilização chegou a 0. As páginas são então removidas e as demais páginas não utilizadas mas que ainda não estão com 0 são ajustadas tendo sua pontuação reduzida, até que em outra operação do Lazy Writer acabam também chegando a zero e em seguida removidas. Portanto, é importante monitorar este contador para saber se a quantidade de Lazy Writer por segundo aumentou, diminuiu ou até mesmo durante uma operação específica ou momentos de pico. Geralmente encontro este valor zerado (pois é coletado uma vez por segundo) e somente onde há uma pressão de memória que é possível vermos algum valor.

SQLServer:Databases – Transactions/sec

Quantas transações por segundo estão sendo realizadas na instância ou base (pode ser coletado por base). É uma forma de medir a demanda e serve como base para você saber se a TempDB está sendo bem utilizada. Pegue o valor total e compare com o valor de transações por segundo na base TempDB, assim, você saberá se 5%, 30% ou 70% das transações são executadas em TempDB.

SQLServer:Databases – Active Transactions

Este contador permite que você saiba quantas transações estão ativas no momento da coleta. Muito útil pra levantar momentos de grandes e demoradas transações ou saber quando a atividade é mais intensa na TempDB, por exemplo, ou quando temos várias bases no mesmo servidor e precisamos saber quais são mais utilizadas. É comum em ambientes rápidos vermos várias transações por segundo mas 0 transações ativas.

SQLServer:Databases – Data File(s) Size(KB)

Permite visualizar o tamanho do datafile de cada database. Se uma database possui mais de um arquivo de dados, é exibida a soma do tamanho de cada arquivo. Tem sua utilidade, se você armazenar um histórico disso pode registrar em um gráfico o crescimento do arquivo durante um determinado período.

SQLServer:Databases – Log File(s) Size(KB)

Exibe o tamanho do arquivo de Transaction Log de cada base. Se uma database possui mais de um arquivo de Transaction Log, é exibida a soma do tamanho de cada arquivo. Tem sua utilidade, se você armazenar um histórico disso pode registrar em um gráfico o crescimento do arquivo durante um determinado período.

SQLServer:Databases – Log File(s) Used Size(KB)

Funciona da mesma forma, exibe quantos KB estão sendo utilizados do arquivo de Transaction Log. Não é tão útil, ver em porcentagem é bem melhor.

SQLServer:Databases – Percent Log Used

Mais eficiente, mostra em porcentagem a utilização do Transaction Log de cada base. É possível evidenciar quando temos muitos checkpoints em bases com Recovery Model em SIMPLE e arquivo de Transaction Log muito pequeno, lembrando que toda vez que o arquivo de Transaction Log atinge 70% de sua capacidade, o checkpoint é feito possibilitando truncá-lo e evitando seu crescimento.

SQLServer:Databases – Log Flushes/sec

Quantas vezes o cache do Transaction Log descarregou seus dados em disco, por ter atingido seu tamanho máximo (60KB), por ter sido feito um commit da transação ou se houve um rollback. Contador extremamente importante, permite ver qual limite foi atingido.

SQLServer:Databases – Log Bytes Flushed/sec

Quantos bytes estão sendo gravados no Transaction Log a cada segundo. Divida a quantidade de Bytes pela quantidade de Log Flushes e você verá o tamanho de cada Log Flush. Quando pequenos é sinal de muitos commits e muito grandes (~60KB que é o tamanho máximo) você pode estar atingindo o limite de outstanding I/Os se a latência do disco for baixa, o disco for dedicado àquele Transaction Log e a fila de disco bate picos de 32 (Current Disk Queue Lenght).

Este artigo explica bem os limites de Outstanding I/Os:
http://blogs.msdn.com/b/sqlcat/archive/2013/09/10/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx

SQLServer:Databases – Log Growths

Excelente maneira de ver quantas vezes o arquivo de Transaction Log de cada base precisou crescer desde que a instância do SQL Server foi iniciada pela última vez. Útil para evidenciar quando você recomenda um arquivo de Transaction Log com tamanho inicial maior ou melhores configurações de auto-growth e o cliente se mostra resistente.

SQLServer:Databases – Log Shrinks

Mostra quantas vezes foi feito Shrink no Transaction Log de cada base desde que a instância foi iniciada.Se o Transaction Log está crescendo, um dos motivos é que alguém ou alguma rotina pode estar fazendo Shrink do arquivo.

SQLServer Locks

Adicione todos os contadores desta categoria. Locks são bloqueios realizados pelo próprio SQL Server para garantir a consistência dos dados durante as transações. Por exemplo, ao alterar um registro, todos devem aguardar que a alteração seja concluída até que possam consultá-lo ou alterá-lo novamente. Com este contador você poderá verificar quantas vezes por segundo e qual o tipo de bloqueio que está sendo mais solicitado. O SQL Server pode bloquear um registro, uma página de dados, uma extent, uma tabela, uma base, um range de páginas de índices, além de outros itens.

SQLServer:Locks – Requests/sec

Permite ver por tipo de objeto e a quantidade de locks solicitados por segundo. É interessante fazer este comparativo por objeto como database, extent, página, key (uma linha em um índice), metadados, RID, etc.

SQLServer:Locks – Timeouts/sec

Este contador cruzando com o Locks Requests/sec permite saber quantos dos locks requisitados apresentam timeout e em que período do dia se você estiver coletando estes dados. É só quando roda o relatório do chefe ou é uma constante?

SQLServer:Locks – Number of Deadlocks/sec

Possibilita visualizar a quantidade de deadlocks por segundo. Não queremos e não costumamos ter deadlocks mas coletando este dado durante um período é possível saber quando eles ocorrem, com que frequência, se são muitos ou poucos, se coincide com algum evento, etc.

SQL Server Wait Statistics

O SQL Server trabalha com threads que executam transações. São milhares de threads entrando e saindo dos cores do seu processador, executando suas transações. Só que em alguns momentos, um recurso ou uma condição podem não estar disponíveis e portanto as threads que dependem destes recursos ou condições ficam aguardando. O SQL é capaz de identificar o motivo e o tempo em que cada thread fica aguardando, registrando estas informações em DMVs e nos contadores do Perfmon. Adicione todos os objetos deste contador e veja que você pode obter informações sobre esperas por Lock (preciso bloquear algum objeto para alterá-lo mas não posso pois já está bloqueado por outra thread), Log Write (preciso escrever no Log mas tem thread na fila), Network IO (devolvi os dados para a aplicação mas ela ainda não os consumiu), Page Latch (vou bloquear uma página que já está em memória), Page Latch IO (vou bloquear uma página que ainda não está em memória). Você pode cosultar detalhes sobre bloqueios neste link da Microsoft e pode obter mais informações sobre waits neste outro link do Paul Randal. 

 

Além disso, você deve consultar os contadores de hardware para cruzar as informações.

 

Memory Available MBytes

Quantos MB estão disponíveis no seu servidor.

Page Faults/sec

Quantas vezes seu servidor precisou de um dado que não estava em memória, e foi necessário buscá-lo em disco ou em outro dispositivo.

Processor Privileged Time %

A maioria do tempo, cerca de 99%, o processador trabalha em modo de usuário (processor time). Quando o processador trabalha em modo kernel, o processador utiliza tempo privilegiado. Este contador pode identificar por exemplo, que o antivírus ou algum outro software está atuando. Vemos este contador geralmente beirando 1% a 3%.

Paging File % Usage

Queremos ver sempre em zero mas é muito difícil. Significa quanto do que você configurou como memória virtual está sendo utilizado de fato. Quanto menos, melhor pois os processos não sabem que os dados que estão em memória virtual estão em disco, não são capazes de diferenciar onde fisicamente estão estes dados. É muito importante sabermos se está ocorrendo paginação da memória em disco e quanto está sendo paginado. Se você tem um pagefile de 16GB onde 50% está sendo utilizado, por exemplo, significa que 8GB que poderiam estar na sua memória física estão em disco. Aliado a outros indicadores, serve para evidenciar pressão externa de memória ou o benefício obtido com o “Lock Pages in Memory”.

PhysicalDisk – Avg. Disk sec/Read e Write

É o tempo em milisegundos de latência do disco para operações de leitura ou de escrita. Informação importantíssima para saber se o disco está respondendo a contento ou se está lento. Para leitura gostamos de vê-lo abaixo de 10ms e para escrita 0ms se tivermos uma storage com cache ou algo em torno de 5ms para outras soluções. É comum vermos ambientes com latência alta ou picos e é importante sabermos que o SQL Server utiliza esta informação durante o Checkpoint para fazer suas requisições de I/O.

PhysicalDisk – Avg. Disk Bytes/Read e Write

A média da quantidade de Bytes lidos e escritos é importante cruzando com outros contadores. Fila de disco sem aumento na quantidade de Bytes significa aumento de operações de I/O pequenas, o que pode ser utilizado para fortalecer uma teoria que possibilita foco mais assertivo na análise, apesar de não ser uma informação conclusiva.

PhysicalDisk – Disk Reads/sec e Writes/sec

Mostra quantas operações de I/O estão sendo feitas de leitura e escrita. Também não é uma informação conclusiva mas permite observarmos se estamos atingindo o consumo de IOPS de um disco, por exemplo. Combinando com o Avg. Disk Bytes, podemos saber se são operações de I/O grandes ou pequenas.

PhysicalDisk – Disk Transfers/sec

Este é o contador utilizado para medir IOPS (quantidade de operações de IO por segundo) no Windows. Seu disco ou arranjo de discos em RAID possuem uma capacidade de IOPS que devemos acompanhar e relacionar com o próximo contador da lista. Existe também o Avg Disk Transfers/sec que já exibe uma média da quantidade de IOPS.

PhysicalDisk – Avg Disk Queue Length

É o tamanho da fila de requisições de disco, na média. O contador Current Disk Queue Length mostra no momento da coleta como estava a fila de requisições de disco mas varia tanto que serve mais para identificar picos. Para identificar um gargalo mesmo o AVG é melhor pois já exibe uma média. Esta média é ideal menor que 1 mas depende do ambiente e do tempo de resposta que é esperado.

% Disk Time

É um contador parecido com o % CPU Time mas especificamente do disco. Quanto tempo o disco fica ocupado recebendo e entregando requisições.

Com estes contadores você já consegue montar um baseline bacana, ir monitorando enquanto está tudo bem para que quando houver um problema você poder comparar os resultados e pelo menos se nortear por onde deve começar a analisar. Obviamente não substituem uma análise nos registros das DMVs do SQL Server mas já serve para guardar um histórico interessante. Ao alterar configurações, procure verificar os dados do Perfmon para identificar as alterações no comportamento. Com um baseline como este, por exemplo, pude perceber um aumento na utilização da TempDB e de IO quando mudei a base para utilizar Read Commited Snapshot Isolation, ou quando ativei o Lock Pages in Memory e percebi que o pagefile estava sendo bem menos utilizado.

 

Veja mais sobre Locks, Waits e Latches:

 

 

Se quiser contribuir, comente!

 

.

3 comentários

  1. Matheus Kamphorst

    Muito bom, geralmente utilizava aquela opção do management studio de diagnostico.

    Agora vou aprofundar o troubleshoot de perfomance…

  2. Matheus

    Boa Tarde.
    Li o seu post, achei de extrema valia, estou com um problema de performance no meu SQL e preciso tentar descobrir algo mais concreto.

    Porém, como não tenho muito conhecimento em SQL esses gráficos não fazem muito sentido, não tenho com o que comparar para saber se está normal ou não.

    Você me daria uma dica de como devo proceder ??

  3. Luiz Mercante

    Caro Matheus, assim de longe, com tão poucas informações e em semana de provas fica muito difícil te ajudar de forma decente. Dê uma olhada neste artigo, tem 10 dicas de performance, com certeza você vai te dar mais habilidade pra resolver seu problema.

    http://sqldicas.com.br/dicas/10-dicas-para-otimizar-o-sql-server-2008-r2/

    Espero que tenha ajudado, abraços.

Deixe uma resposta