Como monitorar sua instância SQL Server e bases de dados

Monitorar instancias SQL Server e bases de dados prove informações necessárias para diagnosticar e solucionar problemas de performance, como também, executar ajustes finos no SQL Server. O desempenho ideal não é simples de definir e criar, como geralmente existem muitos fatores entre software e hardware, também depende do seu ambiente, requisitos de negócio e política da empresa.

Enquanto relatórios demorados podem ser aceitos em empresas pequenas, estes não são aceitos em grandes corporações onde lentidões, soluços e gargalos afetam um grande número de usuários e pode afetar significativamente o negócio. Os problemas são geralmente inaceitáveis e devem ser corrigidos o mais rápido possível.

Uma vez que a performance do SQL Server esteja ajustada, ela precisa ser constantemente monitorada, como toda a modificação de dado, schema e configuração pode levar a situações de ajustes manuais quando necessário. O exemplo mãos comum é uma estatística obsoleta – uma query executa normalmente e, sem qualquer razão óbvia, fica muito lenta.

Para ter uma instância SQL Server bem ajustada e com ótimo desempenho, o SQL precisa ser proativo e monitorar o desempenho do sistema constantemente, definir tendências e definir indicadores de baseline para o SQL Server, ser notificado sobre indicadores que estão fora da faixa normal e realizar os ajustes necessários.

Quais indicadores monitorar?

Os indicadores que devem ser monitorados dependem das suas metas de desempenho. Entretanto, há uma variedade de indicadores comumente monitorados que fornecem informações suficientes para a resolução de problemas básicos. Baseado nestes valores, mais indicadores específicos podem ser monitorados a fim de encontrar a causa raiz de um problema.

Os indicadores do SQL Server comumente monitorados são memória e utilização de processador, tráfego de rede e utilização de disco.

Além de monitorar os parâmetros do SQL Server, é recomendado monitorar alguns parâmetros para bases específicas, bem como parâmetros do Windows.

Os indicadores comumente monitorados são: tempo de processador, comprimento da fila de processador, leitura e gravação de páginas por segundo, expectativa de vida da página, memória total e alvo do servidor, buffer cache hit ratio, quantidade de requisições, utilização de processador, lazy writes, utilização de rede, conexões de usuários, etc.

O SQL Server prove duas funções de monitoração integradas: Activity Monitor e Data Collector.

Monitor de Atividades (Activity Monitor)

O Monitor de Atividades rastreia apenas os indicadores mais importantes do SQL Server. Para obtê-los, ele executa consultas contra o a instancia SQL Server a cada 10 segundos. O desempenho é monitorado apenas enquanto o Monitor de Atividades está aberto, o que o torna a solução mais leve e com quase nenhuma sobrecarga.

Os indicadores são exibidos em 5 painéis recolhíveis: Visão Geral, Tarefas do Usuário Ativo, Esperas recentes, E/S Arquivo de Dados e Consultas caras recentes.

O painel Visão Geral exibe a porcentagem do tempo de processamento, número de tarefas pendentes, operações de E/S em MB/seg e o número de quantidade de requisições.

O painel Tarefas do Usuário Ativo exibe processos acontecendo atualmente em cada base de dados na instância. As informações exibidas são: login, aplicação, host, estado da tarefa, comando, tempo de espera, etc. A informação da tabela podem ser filtradas especificando um valor na coluna.

O menu de contexto no painel de Tarefas do Usuário Ativo provê uma função útil para uma análise mais profunda e para solução de problemas. No exemplo acima ele está selecionando o processo para ser rastreado no SQL Server Profiler.

O painel Esperas Recentes exibe esperas para diferentes recursos: memória, compilação, rede, etc.

Ele exibe o tempo de espera (o tempo que o SQL Server aguarda por recursos do sistema), tempo de espera recente, tempo de espera acumulado e média de espera do contador.

O painel E/S de Arquivo de dados exibe uma lista de todos os arquivos de banco de dados, MDF, NDF e LDF, o nome, caminho, atividade recente de leitura e gravação e tempo de resposta.

O painel Consultas Caras Recentes exibe as consultas executadas nos últimos 30 segundos que mais requisitaram recursos de hardware: processador, memória, disco e rede. O menu de contexto possibilita abrir a consulta em uma janela no SQL Server Management Studio e também abrir o plano de execução.

Como usar o Monitor de Atividades

O Monitor de Atividades pode ser aberto pelo ícone da barra de ferramentas no SQL Server Management Studio, pelo atalho de teclado Ctrl+Alt+A ou pelo menu de contexto do SQL Server no Pesquisa de Objetos.

Como mostrado, o monitor de atividades rastreia apenas dados pré-definidos dos mais importantes dos indicadores de performance do SQL Server. Indicadores adicionais não podem ser monitorados, os monitorados não podem ser removidos. Apenas monitoração em tempo real. Não existe opção de salvar o histórico para uso posterior. Logo, o Monitor de Atividades é útil para monitoração corrente de atividades e solução de problemas básicos, para qualquer análise mais profunda e ajustes de desempenho uma ferramenta de monitoração deve ser escolhida, valores limites devem ser definidos e, se necessário, dados históricos devem ser armazenados.

Coletor de Dados (Data Collector)

O Coletor de dados é outra característica embutida no SQL Server Management Studio para monitoração e ajustes. Ele coleta indicadores de desempenho da instancia SQL Server e salva em um repositório local, para que possa ser usado em uma análise posterior. Ele utiliza Data Warehousing, o Agente do SQL Server e o Integration Services.

Diferente do Monitor de Atividades, o Coletor de Dados permite que você especifique indicadores à serem monitorados. Ele oferece três conjuntos de indicadores padrão com os mais importantes e comuns indicadores de desempenho para monitoração. Para monitorar indicadores de desempenho adicionais, coletores de dados personalizados podem ser criados por meio de código T-SQL ou API.

Como usar o Coletor de Dados

Tenha certeza que o SQL Server Integration services e o SQL Server Agent estão instalados e que o gerenciamento do Data Warehouse e o Coletor de Dados estão habilitados.

  1. Dentro do Object Explorer, no SQL Server Management Studio, expanda Management
  2. No menu de contexto Data Collection selecione Configure Management Data Warehouse
  3. Selecione Set up data collection

  4. Clique em Next

  5. Selecione o nome da instancia SQL Server e a base de dados que irá armazenar os dados da coleta e uma pasta local onde os dados serão mantidos em cache

  6. Clique em Next, reveja as configurações e clique em Finish

O Coletor de Dado prove três conjuntos pré-definidos, disponíveis na pasta System Data Collection Sets, em Object Explorer Management | Data Collection: Disk Usage, query Statistics e Server Activity. Cada um possui um reporte.

O Coletor de dados para Disk Usage coleta dados para os arquivos de dados das bases de dados (MDF e NDF), arquivos de transaction log (LDF) e estatísticas de E/S.

O reporte sobre Disk Usage está disponível no menu de contexto do Coletor de Dados. Ele exibe o espaço utilizado pelos arquivos de dados, tendências de crescimento e média de crescimento do dia.

O Coletor de Dados para Query Statistics coleta dados sobre código de query, estatísticas, atividade e planos de execução para as 10 querys mais custosas.

O Coletor de Dados para Server Activity coleta dados sobre processador, memória, E/S de disco e utilização de rede. O reporte exibe CPU, memória, E/S de disco e utilização de rede, esperas do SQL Server e atividades do sistema operacional.

Como mostrado acima, Coletor de Dados tem de ser configurado e iniciado, a fim de capturar os dados. Ele fornece três conjuntos de coleta internos e relatórios adequados. Não há assistente para a adição de métricas adicionais, que deve ser feito por meio de código.

Uma das vantagens do Coletor de Dados é que ele não precisa coletar os dados o tempo todo, mas apenas em períodos pré-definidos, e assim, gera menos sobrecarga. A desvantagem do recurso é que não há nenhuma opção de selecionar os bancos de dados na instância do SQL Server que serão monitorados, todos os bancos de dados serão monitorados, o que adiciona uma sobrecarga, o ruído com os dados coletados, e requer mais espaço em disco.

Esta opção não está disponível em versões anteriores ao SQL Server 2008 e em edições que não sejam Enterprise, Standard, Business Intelligence e Web.

Diferente do Monitor de Atividades, não existe uma opção para visualizar gráficos em tempo real, mas a captura de dados pode ser salva por um número específico de dias. O recuso oferece indicadores básicos de desempenho, mas conhecimento de código será necessário para adicionar outros indicadores.

ApexSQL Monitor

O ApexSQL Monitor é um Software de monitoração SQL Server que monitora sistema, SQL Server e desempenho da base de dados em tempo real com mais de 30 contadores. Ele permite que o usuário possa selecionar a instância SQL Server a base de dados que será monitorada, selecionar os indicadores para cada base, instância SQL Server e máquina, e especificar os indicadores e valores limite que, se ultrapassados, poderão gerar alertas. O ApexSQL Monitor exibe indicadores de performance em gráficos de tempo real e armazena os indicadores correspondentes em uma base de dados centralizada para uso posterior.

Os indicadores de desempenho capturados e as consultas são exibidos como gráficos em seis guias padrões, mas o usuário também pode ciar guias customizadas.

A guia Overview exibe as informações mais importantes do SQL Server, configurações, estatísticas de esperas do SQL Server e indicadores de desempenho (buffer cache hit ratio, full scans por segundo, lazy writes por segundo e lock requests por segundo).

Overview tab - image 1

Overview tab - image 2

A guia System performance exibe os indicadores de performance do Sistema Operacional: média/utilização da fila de processamento do processador, utilização de rede, uso do arquivo de paginação, páginas por segundo, média de bytes por leitura/gravação, fila atual e média de disco, tempo médio de leitura/gravação, leitura/gravação de disco por segundos, espaço livre e memória disponível em MB.

System performance tab

A guia SQL Server performance exibe indicadores relacionados ao desempenho da instância SQL Server: o número de requisições, compilações e re-compilações por segundo, longas transações em execução, full scans, index searchs, foward records, free list stalls, lazy writes, lock requests, deadlocks por segundo, conexões de usuários, memória total e alvo em KBs, memory grants pendentes, buffer cache hit ratio, expectativa de vida da página, gravação/leitura de página e page splits por segundo.

SQL Server performance tab

A guia Database performance exibe detalhes sobre o tamanho dos arquivos de dados e transaction log e números de transações por segundo.

Database Performance tab

A guia Query waits exibe as principais consultas com o maior tempo de espera durante o período de tempo selecionado, incluindo uma representação gráfica das consultas e suas consultas. Além disso, detalhes individuais ou agrupados e planos das consultas podem ser revistos e analisados.

Query waits tab

A guia Query performance exibe as principais consultas que utilizaram recursos durante um período selecionado, incluindo uma apresentação gráfica das consultas mais lentas por média de tempo de CPU, tempo médio de execução e E/S por segundo. O número de consultas pode ser selecionado pelo usuário.

Query performancetab

Custom tabs podem ser moldadas para necessidades específicas. Os usuários podem criar e definir suas próprias guias para exibir indicadores críticos de forma rápida e fácil depois que começar a usar o ApexSQL Monitor. As guias personalizadas podem ser criadas para fornece informações gerais mais importantes do sistema, instância do SQL Server, banco de dados e indicadores organizados de uma forma que pode dar a visão fácil de um problema potencial.

Como usar o ApexSQL Monitor

  1. Inicie o ApexSQL Monitor
  2. Clique em Add no menu para adicionar a instância SQL Server que você quer monitorar. Todas as instâncias monitoradas do SQL Server serão exibidas na lista SQL Server instances na esquerda.
  3. Para configurar os indicadores de desempenho monitorados, clique em Metrics no menu. Os índicadores monitorados podem ser configurados no nível dos dispositivos, bases de dados, instâncias SQL Server e máquina.

    Metrics tab

  4. Na mesma tela, você pode configurar os alertas. Para habilitar um alerta, selecione a caixa Alerting e entre com os valores para limite, ou use os valores de base se os valores de base forem calculados.

  5. Após você configurar os alertas e os indicadores clique em Apply na parte de cima do formulário.

Se existir um alerta, ele será indicado por um ícone ao lado da instância SQL Server na lista Servers. Para visualizar a lista e os detalhes dos alertas ativos, clique em Alerts no menu. Será exibido o indicador de desempenho, horário de início e termino e número de ocorrências para cada nível de severidade.

Alerts information

O ApexSQL Monitor é uma solução de monitoração simples de configurar e começar a usar, e não é necessário nenhum conhecimento de código. Ela provê monitoração granular e, diferente das soluções nativas do SQL Server de monitoração, ele possibilita a seleção de indicadores, tão como a base de dados que será monitorada. Indicadores diferentes podem ser monitorados para cada dispositivo, base de dados, instância SQL Server e Sistema Operacional. Isso reduz a sobrecarga adicionada pelo processo de monitoração.

Diferente do Monitor de Atividades e do Coletor de Dados, o ApexSQL Monitor exibe gráficos de indicadores em tempo real, armazena dados históricos em um repositório central e exibe os dados históricos em gráficos. Aletas podem ser configurados em alguns cliques, com valores limites para alertas baixos, médios e altos.

Useful resources:
Activity Monitor
Data Collection
Monitoring SQL Server Performance

Tradução: Ricardo Leka Roveri

September 2, 2015