SQL Server ferramentas de monitoramento

Na Parte 1 desta série, abordamos uma série de recursos que o SQL Server oferece para otimizar o seu uso de recursos. Você pode, por exemplo, ajustar a maneira como seus lotes de consulta são compilados, configurar seu cache de buffer para liberar em intervalos diferentes e criar tabelas otimizadas para memória. Aproveitar ao máximo esses recursos requer que você obtenha insights em tempo real sobre a integridade e o desempenho do SQL Server. Aqui nós vamos inquérito SQL Server ferramentas de monitoramento dentro recursos internos e aplicativos comumente usados, incluindo:

• consultas T-SQL: Use o SQL Server query language para reunir internamente dados coletados
• SQL Server Management Studio (SSMS): Obter uma visão em tempo real em seu sistema, diagramas de consultas T-SQL e relatórios por demanda
• Monitor de Desempenho: Correlacionar métricas do SQL Server com dados de seus hosts do Windows

as ferramentas de monitoramento do SQL Server podem ajudá-lo a acessar as métricas que discutimos na Parte 1. Algumas dessas ferramentas relatam as mesmas métricas e você pode preferir uma interface à outra. Por exemplo, você pode usar as consultas Performance Monitor ou T-SQL para obter métricas dos contadores de desempenho do SQL Server. Você pode optar pelos gráficos em tempo real do primeiro versus a capacidade de rotular o último. Neste post, explicaremos como usar as ferramentas de monitoramento do SQL Server para obter uma visão abrangente de sua infraestrutura de banco de dados.

usando consultas T-SQL

você pode monitorar o SQL Server usando sua própria linguagem de consulta, T-SQL, para coletar métricas. As consultas T-SQL são flexíveis. Você pode executá-los com uma ferramenta de gerenciamento gráfico como SSMS ou um utilitário de linha de comando como sqlcmd. E como eles são executados e retornam dados como qualquer outra consulta de banco de dados, você pode incorporá-los facilmente em uma solução de monitoramento automatizado local. Nesta seção, mostraremos como as consultas T-SQL podem ser uma ferramenta poderosa para o monitoramento do SQL Server, esteja você usando visualizações de gerenciamento dinâmico, funções integradas, procedimentos armazenados ou conjuntos de coleta de dados do sistema.

visualizações de gerenciamento dinâmico

o SQL Server rastreia dados sobre sua própria integridade e desempenho e disponibiliza essas informações por meio de visualizações de gerenciamento dinâmico (DMVs). Como os DMVs são exibidos como tabelas virtuais, eles se prestam a consultas ad-hoc e automatizadas. Alguns DMVs retornam o valor atual de uma métrica ou configuração (por exemplo,, o tamanho atual do log de transações em megabytes). Outros, particularmente as métricas para taxas dentro dos contadores de desempenho DMV, medem valores em intervalos regulares e coletam a diferença entre amostras consecutivas (por exemplo, solicitações de lote por segundo). Você pode ler sobre DMVs específicos na documentação do SQL Server.

se você estiver monitorando o SQL Server com visualizações de gerenciamento dinâmico, provavelmente desejará consultar os contadores de desempenho DMV, sys.dm_os_performance_counters. Cada objeto de desempenho do SQL Server (que pode representar qualquer coisa, desde um banco de dados até o cache do plano) mantém seu próprio conjunto de contadores de desempenho, que mapeiam para muitas das categorias de métricas discutidas na Parte 1: Estatísticas SQL, bloqueios e gerenciador de buffer.

por exemplo, você pode consultar os contadores de desempenho DMV para visualizar dados do objeto de desempenho do Gerenciador de buffer e limitar os resultados a métricas com valores diferentes de zero:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name="SQLServer:Buffer Manager" AND cntr_value != 0;GO

você obterá um resultado semelhante a este (mas com muito mais linhas!):

object_name counter_name cntr_value---------------------------------------------------------------------------------------SQLServer:Buffer Manager Buffer cache hit ratio 30SQLServer:Buffer Manager Buffer cache hit ratio base 30SQLServer:Buffer Manager Page lookups/sec 11091500SQLServer:Buffer Manager Database pages 5819SQLServer:Buffer Manager Target pages 212992SQLServer:Buffer Manager Integral Controller Slope 10

você pode encontrar uma lista das visualizações de gerenciamento dinâmico disponíveis organizadas por categoria aqui. Dentro de cada categoria, as visualizações são diversas—enquanto algumas calculam métricas de desempenho e números de saída, outras relatam nomes e propriedades. Você pode listar os nós do SQL Server em um cluster, recuperar dados de uso de índice e obter estatísticas para seus planos de execução, como tempo de conclusão e uso de recursos. E como as visualizações de gerenciamento dinâmico se comportam como tabelas, você pode usar funções integradas para agregar e classificar os dados.

é importante verificar a documentação de qualquer DMV que você planeja usar. Uma razão é que os DMVs exigem permissões diferentes, e a documentação para cada DMV explica o nível necessário. Outro motivo é que os DMVs podem conter milhares de linhas e as colunas podem mudar com novas versões do SQL Server.

funções integradas

o SQL Server também inclui funções integradas para ajudá-lo a acessar as informações do sistema. Ao contrário das visualizações de gerenciamento dinâmico, que retornam dados na forma de tabelas virtuais, as funções integradas retornam dados do sistema como valores numéricos únicos, calculados desde a última inicialização do servidor. Você pode chamar cada função interna como o argumento de uma instrução SELECT. Por exemplo, você pode usar a função interna @@connections para retornar a soma de conexões bem-sucedidas e malsucedidas ao longo do tempo:

SELECT @@connections AS "Total Connections";GO

você receberá uma saída semelhante a:

Total Connections----------------- 1571

as funções integradas às vezes se assemelham a Visualizações de gerenciamento dinâmico. @@connections é semelhante ao contador User Connections dentro do objeto estatísticas gerais. Mas enquanto User Connections rastreia o número de usuários atualmente conectados, @@connections incrementa toda vez que um usuário tenta fazer login (mesmo que a tentativa não tenha êxito).

a única função de Estatística de sistema integrada que não retorna um único valor numérico é sys.fn_virtualfilestats, que retorna uma tabela com dados em E/S de disco para arquivos de banco de dados e produz as mesmas informações que a visualização de gerenciamento dinâmico sys.dm_io_virtual_file_stats.

procedimentos armazenados no sistema

Nota do Editor: O SQL Server usa os Termos “mestre” para identificar seu banco de dados principal. Datadog não usa este termo.

outro recurso integrado que você pode usar para consultar métricas é o procedimento armazenado no sistema. A maioria dos procedimentos armazenados ajuda com tarefas administrativas, como anexar um banco de dados ou adicionar um login, mas alguns procedimentos armazenados relatam métricas. Por exemplo, sp_spaceused mede o consumo de disco dentro de um banco de dados. Você chama procedimentos armazenados no sistema com instruções EXEC em vez de SELECT. Este comando chama o sp_spaceused procedimento armazenado, que irá retornar informações de uso do disco como dois conjuntos de resultados (isto é, duas linhas da tabela, cada linha, incluindo colunas diferentes):

EXEC sp_spaceused;GO

A saída terá um formato semelhante à seguinte:

database_name database_size unallocated space--------------- ----------------- ---------------------master 6.00 MB 0.52 MBreserved data index_size unused--------------- ----------------- ------------------ ------------3568 KB 1536 KB 1600 KB 432 KB

Sistema de coleta de dados define

Se você estiver usando consultas T-SQL para coletar métricas a partir do SQL Server, e você quer ser capaz de armazenar os dados e gerar relatórios, você pode considerar usar o SQL Server coleção de conjuntos. Um conjunto de coleta extrai dados de uma variedade de comandos de relatórios e visualizações de gerenciamento dinâmico e envia os dados para um banco de dados dedicado chamado Data Warehouse de gerenciamento.

o processo depende do SQL Server Integration Services para automatizar a tarefa de consultar o banco de dados e gravar os resultados no Data Warehouse de gerenciamento.

por exemplo, a partir do SQL Server 2008, o conjunto de coleta de uso de disco consulta a visualização de gerenciamento dinâmico sys.dm_io_virtual_file_stat e outras visualizações, como sys.partitions e sys.allocation_units. Você também pode criar um conjunto de coleção personalizado que corrói uma sequência de consultas T-SQL em um trabalho periódico que é executado em segundo plano. Saiba mais sobre como configurar o Data Management Warehouse aqui.

SQL Server Management Studio

SQL Server Management Studio (SSMS) é um ambiente gráfico que ajuda você a monitorar seu sistema de várias maneiras:

• estatísticas em tempo real no Monitor de Atividade
• Uma rica em dados e mapa de uma determinada consulta
• Relatórios que combinar as tabelas, gráficos e texto em um formato amigável para impressão

Para usar o SSMS, você vai precisar para fazer o download em um dos seus anfitriões, abra o instalador e siga as instruções. O software pode monitorar instâncias remotas do SQL Server, incluindo quaisquer instâncias em execução no Linux. Para se conectar a um host, navegue até o menu “Arquivo” e clique em “Conectar o Object Explorer.”No diálogo que se segue, especifique o host e a porta no campo “Nome do servidor”, no formato 0.0.0.0,0000 (observe a vírgula). Selecione “Autenticação do SQL Server “no menu suspenso” autenticação”e preencha o nome de usuário (“Login”) e a senha.

se você se conectou com êxito, verá a janela” Object Explorer ” preencher com uma árvore de arquivos que mostra os Componentes da instância do SQL Server, incluindo bancos de dados. Você poderá monitorar sua instância com os recursos mostrados abaixo.

Activity Monitor

o Activity Monitor permite visualizar métricas do SQL Server em tempo real, com uma galeria de gráficos, uma visão geral dos processos e estatísticas sobre suas consultas. Se você já estiver usando SSMS para tarefas de gerenciamento, como configurar pools de recursos ou criar tabelas, o Monitor de atividades é fácil de adicionar ao seu fluxo de trabalho. Para usar o Monitor de atividades, digite “Ctrl-Alt-A” ou clique no ícone na barra de ferramentas do SSMS.

você pode usar o Monitor de atividades para obter insights em tempo real sobre a demanda em sua instância do SQL Server. A seção “Visão geral” mostra quatro gráficos que exibem métricas de trabalho e recursos em tempo real. Por padrão, essas métricas são atualizadas a cada 10 segundos, mas você pode atualizar o intervalo de Atualização clicando com o botão direito do mouse no painel “Visão Geral”. Embora o intervalo de atualização possa ser tão frequente quanto uma vez por segundo, isso vem com o custo de desempenho de consultas de banco de dados mais frequentes.

o painel “consultas caras recentes” dentro do monitor de atividades pode ajudar a fornecer as informações necessárias para tornar suas consultas mais eficientes. Aqui você encontrará métricas relacionadas a consultas, como execuções por minuto, leituras físicas por segundo e o número de duplicatas de um plano de execução no cache. Se um único plano de execução tiver um alto número de duplicatas ou execuções por minuto, você poderá aumentar o desempenho usando dicas de consulta, conforme discutido na Parte 1.

o Monitor de atividades fornece uma visão geral conveniente de alto nível do seu banco de dados, mas ele tem seus limites. Por um lado, você não pode ajustar os tamanhos dos gráficos ou as métricas que eles mostram. Também não é possível alterar a maneira como o Monitor de atividades agrega suas estatísticas para o desempenho da consulta ou visualizar dados além da janela de exibição predefinida.

visualizar consultas

SSMS pode ajudá-lo a otimizar o desempenho da consulta, permitindo visualizar como o SQL Server executa seus planos de consulta e mostrando o uso de recursos associado à execução de cada etapa de um plano de consulta. Como discutimos na Parte 1, o SQL Server compila lotes de instruções T-SQL usando um otimizador automático para transformar o lote em um plano de execução. Você pode inspecionar um plano de execução no SSMS como um diagrama de etapas computacionais e descobrir exatamente como o otimizador interpretou seu lote. Para visualizar uma consulta, navegue até o painel “consultas caras recentes” do monitor de Atividades, clique com o botão direito em uma das consultas e clique em “Mostrar plano de execução.”A visão que se segue será algo assim:

se você passar o mouse sobre um nó dentro do diagrama, poderá ver uma breve explicação da etapa que o nó representa, bem como uma leitura rápida do “custo estimado do operador do nó.”Este valor é calculado pelo SQL Server optimizer ao executar a consulta. Como o processo de otimização é automático, isso fornece uma maneira de verificar se seus lotes foram compilados conforme o planejado. E como cada etapa do plano de execução é pontuada por custo, você pode ver em quais etapas você deve se concentrar se quiser aumentar o desempenho.

neste exemplo, podemos ver que Compute Scalar(converter uma string em um float) é mínimo, com custo zero no plano de execução. A operação mais cara é uma técnica de otimização, spooling de tabela, que copia linhas em uma tabela temporária oculta.

relatórios

o SSMS oferece 20 relatórios padrão que fornecem um levantamento de alta altitude da implantação do SQL Server, desde o uso de recursos do banco de dados até dados históricos sobre alterações de esquema e consistência do banco de dados. Você pode encontrar uma análise detalhada dos relatórios aqui.

os relatórios são corrigidos no layout e no conteúdo-eles mostram os dados disponíveis no momento em que você cria o relatório, em vez de atualizar em tempo real. A interatividade nos relatórios padrão é limitada. Você pode classificar algumas tabelas por coluna e expandir outras quando as informações são aninhadas. O layout fixo torna simples a criação de impressões ou documentos (PDF, Word e Excel).

em 2017, A Microsoft adicionou o relatório do Painel de desempenho, que mostra a utilização da CPU, as contagens atuais de sessões de usuário e outras informações do sistema para instâncias do SQL Server.

para gerar um relatório, clique com o botão direito do mouse no nome de um banco de dados no Object Explorer, passe o mouse sobre “relatórios”, depois sobre “Relatórios padrão” e selecione um relatório no menu.

se você não conseguir encontrar a visualização necessária nos relatórios padrão do SSMS, poderá criar um relatório personalizado. Relatórios personalizados são escritos em Report Definition Language (RDL), uma extensão de XML. Depois de especificar a estrutura de um relatório personalizado, você pode preenchê-lo no menu “Relatórios” clicando em “Relatórios Personalizados.”Eles permanecem separados da lista de relatórios padrão.

também vale a pena notar que a Microsoft desenvolveu várias ferramentas para criar relatórios gráficos que vão além da funcionalidade do SSMS. O Power BI pode visualizar dados de várias fontes, incluindo o SQL Server, e vem com um conjunto mais completo de ferramentas de edição visual. O SQL Server Reporting Services (SSRS) é uma ferramenta gráfica de relatórios projetada para o SQL Server que pode gerar relatórios paginados e prontos para PDF, bem como visualizações de dados para dispositivos móveis e web.

Monitor de desempenho

O Windows Performance Monitor ajuda a visualizar o uso de recursos no nível do sistema de seus hosts do Windows e permite correlacionar essas métricas com contadores de desempenho do SQL Server em gráficos de séries temporais.

o Monitor de desempenho está integrado ao Sistema Operacional Windows. Para usá-lo, abra a janela Executar no Menu Iniciar e digite o nome do programa perfmon. Um gráfico em tempo real aparecerá na árvore de navegação em “Ferramentas de monitoramento. Em seguida, você pode selecionar contadores de desempenho do SQL Server e métricas de recursos do sistema que deseja traçar e usar as opções para estilizar seus gráficos.

neste post, mostramos como usar ferramentas de monitoramento do SQL Server e recursos integrados para gerar visões gerais em tempo real de seus bancos de dados, bem como para obter dados detalhados e sob demanda sobre a integridade e o desempenho do SQL Server.

na próxima parte desta série, mostraremos como usar o Datadog para coletar, representar graficamente e alertar sobre métricas históricas e em tempo real do SQL Server. Também mostraremos como configurar painéis com visualizações de arrastar e soltar e correlacionar métricas do SQL Server com dados de toda a sua pilha.