Categorias
SQL Server Troubleshooting Virtual PASS BR

Transparent Data Encryption and its tricks

[bing_translator]

Transparent Data Encryption – TDE não é mais uma novidade, mas sim uma realidade que devemos usar. Um ponto importante que vale ressaltar é que esse recurso está presente apenas na edição ENTERPRISE do SQL Server.
Existem alguns passos a passos na internet de como implementar, mas eu vou deixar um script que pode ser seguido.

Master Key e Certificados

Uma forma simples de implementar o TDE é utilizar um certificado gerado pelo próprio SQL Server que é protegido pela Master Service Key.
Se você está implementando ou pretende implementar lembre-se que o mais importante de tudo é fazer backup do certificado e das keys!

PARE TUDO e faça BACKUP 

O principal aspecto para utilizar o TDE está relacionado à protecao de chaves incluindo backups separados das chaves de criptografia e a capacitade de restaurar e recuperar esse banco de dados em outro local. Assim como qualquer outro plano de DR, a restauração de bancos de dados de TDE é algo que devemos praticar antes que ocorra o desastre. Você não quer descobrir depois de um evento  que você não estava fazendo o backup da chave TDE corretamente.

Mais uma vez eu utilizei o dbatools para me ajudar a automatizar minha tarefa e quero agradecer ao Claudio Silva  (blog/twitter) por me ajudar. Algumas linhas de powershell são suficientes para isso 🙂

Agora que o background está pronto, o TDE pode ser habilitado.

A diferença quando se está utilizando AlwaysON Availability Group está na preparação dos ambientes antes de habilitar o TDE. Ou seja, você deve criar chaves e certificados em todas réplicas que pertencem ao AG.

/*
  Following steps must be executed on PRIMARY REPLICA
*/
-- Check if MASTER KEY is created
USE master; 
GO

SELECT * FROM sys.symmetric_keys; 
SELECT * FROM sys.certificates;

/*
  Step 1: On Primary Replica - Creation of the Database MASTER KEY (DMK)
  We can skype this step because we already have it
  Make sure that MASTER KEY Backup is working correctly.
        Review backup routine! This is most important step to do 
*/
-- Create a Master Key IF NOT EXISTS
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO

-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_MasterKeyBackup.key'
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO
/*
  Step 2: On Primary Replica - Creation of the CERTIFICATE
  Certificate is named as TDECert and it will use MASTER KEY to en
*/

-- Create Certificate Protected by Master Key
CREATE CERTIFICATE TDECert
WITH
  SUBJECT = 'Transparent Data Encryption Cerficate';
GO

/*
 Backup the Certificate
 Review backup routine. We should back up it to two/three different location
 Pick up a strong password 
*/
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );

/*
  Step 3: On Primary Replica - Creation of Database Encryption Key (DEK)
*/

USE YourDatabase;
GO
/*
 Create a Database Encryption Key
 Choose between AES_128 and AES_256
 Your are not going to encrytp it yet! 
*/

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 -- AES_256 
ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
/*
  Following stes must be executed on SECONDARY REPLICA
*/

-- Check if MASTER KEY is created
USE master;
GO
SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.certificates;

-- Create a Master Key IF NOT EXISTS
USE master;
GO
/*	
  Step 4: On Secondary Replica - Creation of the DMK
  Make sure that MASTER KEY Backup is working correctly.
  Review backup routine.  
*/

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
-- Backup the Master Key on secondary in order we have this when it turns into Primary and we need not worry when recovery event occurs.
-- Review backup routine! This is most important step to do

BACKUP MASTER KEY
TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_MasterKeyBackup.key' -- You can use same folder to store keys (I just change it to make things easy)
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
/*
  Step 5: On Secondary Replica - Creation of the Certificate from the Primary Certificate Backup
*/

-- Create Certificate Protected by Master Key 
CREATE CERTIFICATE Cert_For_TDE
FROM FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,DECRYPTION BY PASSWORD = 'same_strong_password_for_backup' );

-- Backup the Certificate
-- Review backup routine! This is most important step to do
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_Backup.cer' 
WITH PRIVATE KEY ( FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_PrivKey.pvk' 
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );
/*
  Step 6: On Primary Replica – Enabling TDE Encryption
  Following stes must be executed on PRIMARY REPLICA
*/

-- Now you are going to encrypt it
ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO
/*
  Step 7: On the Primary Replica and Secondary Replicas – Monitoring Encryption
  This code must be run in SQLCMD mode
*/

:CONNECT PrimaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO

:CONNECT SecondaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO

Uma vez que você efetive o recurso ele será executado silenciosamente em segundo plano, criptografando o banco de dados enquanto as atividades continuam. Enquanto a criptografia inicial está em execução, podemos verificar a coluna percent_complete na sys.dm_database_encryption_keys para ver progresso (veja o step 7).

http://www.sqlservercentral.com/articles/always+on/135432/

Esse processo seria simples, se não fosse pelo grande volume de transações que eu tenho. Um sistema de telemetria que a todo instante está recebendo e enviando informações de veículos no mundo.

O CPU foi a quase 100% e gerou um performance ruim para o sistema. O que quero dizer é que processos que executavam em 500 milissegundos passaram a executar em 1200/1500 milissegundos.

Se você procurar na documentação só exista a opção de ON e OFF para ENCRYPTION. Uma vez que eu iniciei o processo para criptografar o database eu tenho que esperar terminar. E agora? Quanto tempo isso vai demorar para finalizar no meu database?

Existe uma trace flag documentada (Thanks God Microsft) que pausa o processo e simplesmente não faz rollback! Fantástico pois não perco o que foi criptografado e meu database fica com o status de “encryption in progress”. Ou seja, as páginas que foram criptografadas continuam assim e o as páginas que não foram criptografadas serão lidas normalmente.

https://blogs.msdn.microsoft.com/markweberblog/2017/04/04/transparent-data-encryption-tde-traceflag-5004-and-interrupting-encryption-scanning/

Como qualquer outra TF basta você ativar ou desativar. Para parar o processo ative a TF:

DBCC TRACEON(5004) 
GO

Para reiniciar o processo de onde parou basta desativar a TF e executar o comando novamente para criptografar o database.

DBCC TRACEOFF(5004) 
GO 

ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO

Isso salvou minha vida, pois eu pude habilitar o processo em horários de menor utilização e mesmo assim se impactasse o sistema eu seria capaz de parar.

Backup Compression

Um fator que notamos depois de habilitar o TDE foi a compressão de backups não estava funcionando corretamente, ou seja, simplesmente ignorava a opção e gerava um backup gigantesco.

Pesquisando um pouco achei uma excelente explicação para esse comportamento.

https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/

Depois de entender melhor esse comportamento, foi a hora de verificar o que estava ocorrendo. Para isso utilizei um extended event (backup_restore_progress_trace) que facilita e muito a interpretar o que está acontencendo no momento do backup.

O Edvaldo Castro (blog/twitter) explica muito bem esse novo XE.

https://edvaldocastro.com/benchmarking-teste-do-novo-extended-event/

Você deve querer ver uma mensagem de MaxTransferSize maior de 64 KB:

https://blogs.msdn.microsoft.com/sql_server_team/new-extended-event-to-track-backup-and-restore-progress/#.VgtLzHpVhBd

Nesse momento fizemos duas ações:

1 – Atualizamos para o service pack e cumulative update mais recente visto que estávamos com a versão inferior descrita no link (https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/)

2 – Mudamos nossa rotina de backup para se adequar as necessidades.

E o tempdb?

A partir do primeiro database utilizando TDE o tempdb passa ser criptografado automaticamente. Um curioso caso sobre o tempdb foi desvendado pelo Bob Ward, recomendo a leitura!

https://blogs.msdn.microsoft.com/bobsql/2017/01/26/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/

Eu tenho FileStream, vai criptografar?

A documentação é clara, dados em FileStream não são criptografados. Nossa solução foi utilizar BitLocker

https://docs.microsoft.com/en-us/windows/security/information-protection/bitlocker/bitlocker-how-to-deploy-on-windows-server

Como ficou a CPU?

Nossos testes mostraram um leve aumento de CPU mas nada que comprometeu  a performance. Monitoramos alguns processos críticos e vimos que o aumento foi em média de 3-5% de CPU e tempo total de duração aumentou 100-150 milissegundos.

Transaction-Log? Hummmm

Eu não vi nenhum tipo de problema com isso. Espero que continue assim 🙂

Alguns recursos que não utilizamos e ainda não testei como Buffer Pool Extention – a documentação diz que não é criptogrado e assim como FileStream uma opção seria usar BitLocker. Quero verificar o quanto isso pode afetar a área de memória ou não.

Certificado expirado

O processo de substituir com segurança o certificado é chamado rotating the encryption key. É importante fazer isso, e o SQL Server torna isso um processo simples e rápido. A substituição dos seus certificados expirados é um processo rápido e simples, e é uma parte importante da manutenção da segurança da sua criptografia

Você também pode usar ou restaurar um certificado expirado no servidor.

https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/

Rotação de chaves

Rotation the encryption key é o processo de passar do certificado antigo para o novo. Nesse caso, tudo o que acontece é que a chave de criptografia do banco de dados é descriptografado com o certificado antigo e criptografado novamente com o novo certificado. Esse novo valor criptografado é armazenado no banco de dados sobrescrevendo o antigo. Por ser um processo rápido, a rotação frequente não é um problema.

Novo certificado

O primeiro passo é criar um novo certificado que será usado para criptografar o banco de dados. Você pode seguir os mesmos passos acima.

Rotação do certificado

Para substituir o certificado usado para TDE, adicione o novo certificado como acima, então execute o comando

USE YourDatabase
GO

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_Rotate_Key;
GO

Faça backup do novo certificado e do database.

Remover TDE

Para remover o TDE é necessário executar o comando abaixo.

ALTER DATABASE yourdatabase SET ENCRYPTION OFF;
GO

Aguarde até que o processo de descriptografia esteja concluído. Use o sys.dm_database_encryption_keys DMV para determinar seu status

USE YourDatabase
GO

DROP DATABASE ENCRYPTION KEY
GO

Faça backup do novo certificado e do database.

Para finalizar, aprenda que o TDE não é apenas um recurso que liga e desliga para entrar em conformidade com órgãos regulamentadores. Você deve se preparar e entender melhor esse recurso antes de utiliza-lo em seu ambiente de producao. Este foi um dos recursos que eu mais gostei de implementar pois me aprofundei em um assunto que eu mesmo tinha deixado de lado.

Fica uma pergunta! Quando falamos de data-in-rest o TDE é suficiente para deixar meus dados seguros ou precisamos fazer um backup com criptografia? Em um próximo post falarei como utilizar backup encryption

https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/

https://blogs.msdn.microsoft.com/sqlsecurity/2016/10/05/feature-spotlight-transparent-data-encryption-tde/

Categorias
Monitoramento SQL Server Troubleshooting Virtual PASS BR

Suspect database – MSDTC in-doubt transaction

Em uma bela madrugada, onde todas as coisas obscuras aparecem, um dos servidores de um cluster falhou e executou um failover para um outro nó. Até esse momento nada de estranho e esse é o comportamento esperado.

Problema

Ao verificar os bancos de dados da instancia que sofreu o failover me deparei com o status de “suspect” em um deles. Nesse ponto começou a investigação de como isso aconteceu e como resolver!

Consegui encontrar no errorlog as seguintes mensagens:

Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

QueryInterface failed for “DTC_GET_TRANSACTION_MANAGER_EX::ITransactionDispenser”: 0x80004005(failed to retrieve text for this error. Reason: 15105).

QueryInterface failed for “ITransactionDispenser”: 0x80004005(failed to retrieve text for this error. Reason: 15105).

Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {07372A47-24B9-4BC3-A651-0260624FFF8E}. Please resolve it following the guideline for Troubleshooting DTC Transactions.

An error occurred while recovering database ‘XXX’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:-222014414). Fix MS DTC, and run recovery again.

An error occurred during recovery, preventing the database ‘XXX’ (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

A aplicação que utilizava essa database também utilizava Microsoft Distributed Transaction Coordinator (MSDTC) e tudo indicava que alguma coisa se perdeu no caminho da comunicação entre o MSDTC da aplicação e do banco de dados.

No meu caso eu tinha um agravante: o cluster não possuía uma instância do MSDTC, ou seja, utilizava o MSDTC local.

Quando uma instância do SQL Server 2008 ou superior é inicializada em um cluster, ela tenta encontrar uma instância do MSDTC para comunicação na seguinte ordem:

  • Dentro do grupo do cluster onde reside o recurso do SQL Server
  • Dentro de outros grupos do cluster
  • Instância MSDTC local

Resolução

Eu não sabia o que tinha acontecido com MSDTC e resolvi tomar uma ação para deixar meu banco de dados online e depois tentar resolver qualquer problema.

Utilizei o comando abaixo:

sp_configure ‘show advanced options’, 1
go
reconfigure
go
sp_configure ‘in-doubt xact resolution’, 2
go
reconfigure
go
sp_configure ‘show advanced options’, 0
go
reconfigure
go

E depois trouxe o banco de dados ONLINE.

Podem existir outras formas de resolver esse problema, porem essa ação permitiu que meu banco dados ficasse online e pude executar um DBCC CHECKDB que para meu alivio retornou sem nenhum erro!

Antes de liberar meu banco de dados para produção novamente voltei a configuração padrao

sp_configure ‘in-doubt xact resolution’, 0
go
reconfigure
go

Outra alterativa seria vizualizar a view sys.dm_tran_active_transactions, essa view mostra algumas informações sobre  as transações com uso do MSDTC.

Os campos: transaction_uow, transaction_state, dtc_state são campos que devem ser olhados com cuidado em uma analise pois contem informaçoes importantes.

MDCTS-01

O campo transaction_uow pode ser relacionado com o Unit of Work ID dentro da lista de transações do MSDTC.

MDCTS-02

Assim você pode identificar o status da sua transação e pode tomar a ação necessária que cabe ao seu ambiente.

Conclusão

Quando a instância SQL Server executou um failover para o outro nó do cluster, a instância passou a se comunicar com o MSDTC local do novo nó, que não tinha informações das transações registradas no nó anterior.

Ao tentar iniciar o processo de recovery do banco de dados, o SQL Server encontrou informações sobre transações distribuídas que não haviam sido terminadas (confirmadas ou abortadas) antes da falha. O SQL Server entrou no processo de validar as informações do LOG e questionou o MSDTC a respeito das transações, e o novo MSDTC local não tinha informações a respeito destas transações. Por esse motivo o SQL Server não foi capaz de resolve-las e desta forma interrompeu o processo de recovery ocasionando o estado de “suspect”

A melhor solução para que isso não ocorra é ter uma instancia do MSDTC dentro do cluster.

Referências:

How to configure DTC for SQL Server in a Windows 2008 cluster

Opção de configuração de servidor in-doubt xact resolution

sys.dm_tran_active_transactions

 

Categorias
SQL Server Virtual PASS BR

Eu devo utilizar as DMV?

Você já deve ter ouvido falar nas DMV, mas voce já utilizou alguma?

Primeiramente, DMV é o nome mais comum para views (Dynamic Management Views) e functions (Dynamic Management Functions). DMVs são metadados que ficam armazenadas no schema sys, iniciam com sys.dm_* e permitem explorar o comportamento interno do SQL Server com comando SELECT.

As DMVs foram implementadas no SQL Server 2005 e hoje já são popularmente conhecidas, mas eu ainda vejo algumas pessoas lutarem contra elas e continuarem utilizando formas antigas de recuperar informações internas do SQL Server, por exemplo: Utilizar a stored procedure de sistema SP_WHO ou SP_WHO2 para trazer informações do que esta acontecendo e quais processos estão sendo executados na instancia.

Essas procedures  de sistemas são uteis e trazem algumas informações que você com certeza pode identificar o que está acontecendo em seu ambiente porem, na minha visão, elas tem limitações extremas e trazem poucas informações.

Com as DMVs você pode ser mais especifico em procurar um problema ou informação, não dependendo de um conjunto de resultado pré-estabelecido que não pode ser modificado.

As DMVs contem muitas informações adicionais que não fazem parte de procedures de sistemas e com detalhes riquíssimos onde podemos cruzar informações entre varias DMVs e obter um diagnostico preciso sobre o que você procura.

Em uma breve conversa com alguns DBAs da minha equipe, percebi  que o fato de que alguns deles não usarem as DMVs é por falta de conhecimento e por “preguiça”… Sim a comodidade de escrever poucas palavras e não escrever um SELECT é o principal ponto que encontrei…

Mas vamos lá… Por que eu devo utilizar as DMVs?

Na minha opinião é pelo fato delas conterem muitas informações relevantes para N tipos de situações, seja uma simples consulta de propriedades até um diagnostico completo de  problemas. Com as DMVs a facilidade de informação está ao nosso lado, então porque desperdiçar?

Conhecer todas as DMVs é um desafio, mas basta começar à conhece-las que você irá se acostumar facilmente e isso acontecerá porque as DMVs estão categorizadas em grupos específicos baseados nas áreas que a informação é exposta.

Algumas dessas áreas são:

dm_exec_* 

Nessa sessão você tem informação relacionada a execução de código e conexão associada.  Por exemplo: sys.dm_exec_session voce tem informação sobre as sessões existentes na instancia.

Aqui vale a pena um atenção especial! Na minha opinião essa sessão deve ser bem explorada pois tudo o que esta sendo executado na sua instancia pode ser encontrado aqui.

dm_db_*

Nessa sessão você tem informação sobre detalhes do database e seus objetos. Por exemplo: sys.dm_db_missing_index_details você tem informações detalhadas sobre índices ausentes

dm_io_*

Nessa sessão voce tem informação sobre atividade e I/O. Por exemplo: sys.dm_io_pending_io_requests retorna uma linha para cada solicitação de I/O pendente.

dm_os_*

Nessa sessão você tem informação em baixo nível do sistema. Por exemplo: sys.dm_os_schedulers que retorna uma linha para cada scheduler.

dm_tran_*

Nesse sessão você tem informação sobre transações corrente. Por exemplo: sys.dm_tran_active_transactions você tem informação sobre transações da instância.

E assim por diante!! Para obter mais informações sobre as DMVs acesse o link: http://technet.microsoft.com/en-us/library/ms188754.aspx

Complemento:

A Microsoft SQL Server DMV/DMF Cheat-Sheet

SQL Server 2008 System Views poster is now available for download!

O link abaixo achei super interessante, pois monstra uma forma diferente de visualizar as DMVs!!!

The SQL Server 2012 Periodic Table of Dynamic Management Objects

Categorias
Monitoramento SQL Server Virtual PASS BR

Performance Counter: Rede

 

 

Comunicação de Rede

As requisições de cliente SQL utilizam o protocolo Tabular Data Stream (TDS) para comunicação com o servidor de banco de dados. Para saber mais sobre TDS, consulte o link.

Os contadores de performance utilizados são:

Network Interface: Bytes Sent/sec e Network Interface: Received/sec – medem respectivamente a quantidade de dados enviados e recebidos pelo servidor através da placa de rede correspondente.

Network-Throughput

Gráfico 1

A capacidade máxima de uma rede gira em torno de 60-80% da sua capacidade total. O gráfico 1 mostra que não tenho problemas de trafico de dados, isto porque minha placa de rede tem a capacidade de throughput de 1GB/s.

Nesse momentos estou validando apenas se não existe uma sobrecarga na minha placa de rede referente ao volume de dados trafegado. Em um proximo post irei detalhar No post – Problemas de Rede: ASYNC_NETWORK_IO detalho melhor um problema de rede que me deparei onde irei aprofundar melhor a analise.

Referencias:

Network Protocols and TDS Endpoints

http://technet.microsoft.com/en-us/library/ms191220(v=sql.105).aspx

Network Interface Object

http://msdn.microsoft.com/en-us/library/ms803962.aspx

Categorias
Monitoramento SQL Server Virtual PASS BR

Performance Counter: Subsistema de Discos

Os administradores de storages estão constantemente tentando maximizar o desempenho de acesso ao disco e problemas podem ser o resultado de qualquer coisa a partir de um componente configurado incorretamente até uma volume de carga extramamente grande. E aqui entra, na minha visão, a “briga” entre o DBA e o Administrator de Storage mas essa é uma historia longa. Voltando ao assunto, os bancos de dados estão armazenados em discos (ou pelo menos até a chegada do SQL Server 2014, o recurso de IN-Memory OLT, conhecido tambem por HEKATON, passa a armazenas dados em memoria) que ao contrário da memória RAM, são mídias não-voláteis.

Utilização de Disco

A utilização do recurso de disco pode ser medida através de:

  • Operações de I/O por segundo (IOPS) – Número de operações realizadas por segundo
  • Taxa de Transferência (Throughput) – Quantidade de dados (em Megabytes) transferidos

Os contadores utilizados para medir as informações são:

Logical Disk: Disk Reads/sec e Disk Write/sec – São respectivamente ao número de operações de leitura e escrita realizadas realizadas no volume ou disco. Valores abaixo de 100 IOPS são considerados baixos.
Logical Disk: Avg. Disk Read Bytes/Sec e Avg. Disk Write Bytes/Sec – Taxas de transferência para escrita e leitura no subsistema de disco. Valores abaixo de 20MB são considerados baixos.

TotalIOPSGráfico 1 – Total de IOPS

O gráfico 1 mostra o consumo de IO do subsistema de disco, onde a utilização maxima chega perto dos 20 mil IOPS. Também é possivel notar que a maior concentração na utilização do subsistema de disco para leitura, visto que a gravação de dados consome pouco recurso.

DiscoThroughput

Figura 2 – Total de Throughput

Tempo de Resposta

Podemos definir latência ou tempo tempo de resposta como: uma medida de dealy(tempo de atraso) desde o momento de uma requisição de IO é criado, até ao momento em que a requisição de IO é completada.

Através de indicadores abaixo é possível dimensionar o impacto da utilização do subsistema de disco.

Logical Disk: Avg. Disk sec/Read e Avg. Disk sec/Write – É o tempo médio gasto em leitura e escrita de uma operação de I/O no disco. Esse indicador corresponde ao tempo de resposta do disco, sendo recomendado valores inferiores a 0.020 – equivalente a 20 milissegundos.

Logical Disk: Current Disk Queue Lenght – Informa a fila de cada volume ou disco. O valor ideal da fila de disco é zero, porém, existe uma tolerância de até 2 operações de I/O por disco físico.

DiscoTempoResposta

Gráfico 3 – Tempo de Resposta

O gráfico 3 mostra que a fila de disco está alta porém o tempo de resposta do subsistema de disco nao ultrapassa 8 milisegundos ou 0.08. Nesse caso o enfileiramento de disco não está atrapalhando o tempo de resposta de IO.

Referências:

Windows Performance Monitor Disk Counters Explained

http://blogs.technet.com/b/askcore/archive/2012/03/16/windows-performance-monitor-disk-counters-explained.aspx

Measuring Disk Latency with Windows Performance Monitor (Perfmon)

http://blogs.technet.com/b/askcore/archive/2012/02/07/measuring-disk-latency-with-windows-performance-monitor-perfmon.aspx

Categorias
Monitoramento SQL Server Virtual PASS BR

Performance Counter: Processador

Além de saber o que monitorar em um ambiente (veja o post: Performance Counter: você sabe o que monitorar?“) é nessario saber o significado de cada contador e como relacionar um contador com outro.

Nesse primeiro momento vamos falar um pouco sobre processador.

O processamento é responsável pela execução de instruções de máquina, sendo limitado pelo número de processadores (CPU) e seu clock de operação. Qualquer comando no servidor requer uma cota mínima de processamento e a falta desse recurso pode causar enfileiramento de requições e espera em fila.

O Ivan Lima (@SQLInsane) está com uma serie sobre “Inside The Machine” que recomendo a leitura caso queira saber mais sobre hardware!

O acompanhamento do consumo de CPU pode ser realizado através dos seguintes contadores do Performance Monitor:

Processor: %Processor Time – Este é o principal indicador de processamento na máquina. Altos valores podem não ser necessariamente ruins, desde que reflitam o processamento de consultas ao banco de dados. Entretanto, fatores externos podem consumir o recurso do processador e degradar o desempenho do SQL Server. O valor ideal recomendado é abaixo de 80% do poder de processamento máximo disponível.

Processor: %Privilege Time – Privilege Time ou Kernel Time é o tempo gasto pelo processador servindo às atividades do núcleo do Sistema Operacional, que corresponde às operações internas do Windows e Drivers de sistema.  O valor ideal recomendado é que se mantenha abaixo de 30% em relação ao processamento de máquina indicado por “%Processor Time”. ProcessUtilzation

Gráfico 1 – Servidor com 32 CPU

O gráfico 1 mostra que temos um consumo de CPU razoavelmente alto com tendência de alta e em alguns periodos com quase 100% de utilização.  O tempo de Kernel se manteve dentro do esperado: abaixo do número máximo recomendado de 30% da utilização do processador.

Process(sqlservr): % Processor Time: Este contador mostra qual a percentagem de tempo de processador está sendo usado para executar o processo Sqlservr (database engine). Idealmente, o tempo do processador deve ter um média abaixo de 50%  Se exceder uma média de 80% por um tempo contínuo (cinco minutos ou mais), então existe um afunilamento de CPU durante esse tempo, e você deve investigar a causa raiz.

Process(sqlservr): % Privileged Time – Este contador mostra qual a percentagem de tempo de processador “em modo kernel” está sendo usado para executar o processo Sqlservr (database engine). A recomendação é a mesma do Processor: %Privilege Time, ou seja, menor que 30% of Total %Processor

System: Processor Queue Lenght – É o tamanho da fila do processador, que equivale ao número total de threads enfileiradas esperando pelo recurso de CPU. O enfileiramento ocorre como consequência do alto consumo de processador e se tornam um indicativo do impacto do SQL Server em relação a máquina. O valor ideal recomendado é que essa fila não ultrapasse 2 vezes o número de CPU. QueueLegth

Gráfico 2 – Servidor com 32 CPU

O gráfico 2 mostra que apesar do consumo alto de CPU, não há problemas relacionados ao enfileiramento no processador.

System: Context Switches/sec – A quantidade de troca de contexto em um SQL Server deve se manter baixa mesmo sob carga. Um aumento no indicador Context Switches/sec ocorre devido a execução de threads de alta prioridade, como as Interrupções (INT), Asynchronous Procedure Call (APC) e Deferred Procedure Call (DPC) ou se houver um grande número de chamadas ao Kernel (System Calls/sec) ou disparo de exceções (Exception Dispatches/sec). O valor ideal recomendado é de no máximo 10000 por CPU.

ContextSwitch Gráfico 3 – Servidor com 32 CPU

O número referente ao Context Switche se manteve dentro dos parametros esperados, sendo um valor aceitável até 320 mil trocas por segundo.

System: Exception Dispatches/sec – Corresponde ao mecanismo de controle de tratamento de erros. Sempre que um evento de exceção é gerado, ocorre uma interrupção em Kernel para iniciar esse tratamento. Esse indicador deve ser próximo de zero.

System: System Calls/sec – As chamadas de sistema ocorrem quando uma aplicação realiza uma chamada a uma rotina de sistema, realizando a troca de contexto entre uma thread em modo usuário para modo Kernel. Não encontrei um valor ideal para esse indicador.

Referências:

Monitoring CPU Usage

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

Establishing a Performance Baseline

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

Categorias
Monitoramento Troubleshooting Virtual PASS BR

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