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.
- Dentro do Object Explorer, no SQL Server Management Studio, expanda Management
- No menu de contexto Data Collection selecione Configure Management Data Warehouse
-
Selecione Set up data collection
-
Clique em Next
-
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
-
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.
Useful resources:
Activity Monitor
Data Collection
Monitoring SQL Server Performance
Tradução: Ricardo Leka Roveri
September 2, 2015
