SQLShack
Introdução
Neste artigo, estamos a passar por muitas das ferramentas que podemos utilizar para monitorizar o desempenho do SQL Server. O SQL Server está repleto de muitos e bons relatórios que permitem a um DBA detectar rapidamente se existe algum estrangulamento de desempenho actual no SQL Server. Muitos destes situam-se em cima de DMVs, mas dão-nos uma forma visualmente interactiva de olhar e trabalhar com os dados. Vamos começar com os Relatórios do Painel de Desempenho do SQL Server.
Relatórios do Painel
Vamos saltar para o SQL Server Management Studio (SSMS) e a primeira coisa que vamos fazer é levar-vos através de relatórios de painel de instrumentos de todos os níveis, prontos a usar. Podem ser encontrados clicando com o botão direito do rato na instância do SQL Server no Object Explorer, e a partir do menu de contexto, encontrará Relatórios > Relatórios Padrão:
Todos os relatórios do painel de controlo são úteis, e não vamos passar por todas elas, pois isso exigiria muito tempo/palavras, mas sinta-se à vontade para verificar todas elas sempre que tiver a oportunidade. Para mostrar um exemplo, escolha o Painel do Servidor a partir do menu de contexto. Este relatório dá-nos muita informação sobre o estado actual das instâncias do SQL Server, incluindo a sua configuração, versão, serviços, e actividade no mesmo:
Aqui também temos opções de configuração não por defeito. Estas são as que foram alteradas a partir da instalação do vanilla:
Também, ao nível da base de dados, se clicarmos com o botão direito do rato numa base de dados, e entrarmos em relatórios, temos todo o tipo de relatórios de utilização de disco, backup e restauração de eventos, transacções de topo, estatísticas de índice, etc.:
Assim, todos estes relatórios de painel são óptimos, são fáceis de consumir e de trabalhar.
Activity Monitor
A seguir, vamos analisar o Activity Monitor que é um monitor em tempo real dentro do SQL Server que podemos utilizar para monitorizar tudo, desde o desempenho até aos custos de E/S a consultas dispendiosas, etc. Para iniciar o Monitor de Actividade, clique com o botão direito do rato na instância do SQL Server no Object Explorer e a partir do menu de contexto escolha Activity Monitor. Pode também lançá-lo a partir da barra de ferramentas Padrão, clicando no ícone do Monitor de Actividade:
Activity Monitor sempre foi uma das ferramentas a utilizar se algo de repente correr mal com o desempenho do SQL Server. A primeira coisa que vemos, quando acendemos o Activity Monitor é o painel Overview. Para além disso, esta ferramenta tem seguido painéis expansível e dobrável: Recursos Espera, E/S de Ficheiros de Dados, Consultas Caras Recentes, e Consultas Caras Activas:
Processos – dá-nos a capacidade de olhar para os processos em execução actualmente para que possamos geri-los. O clique com o botão direito do rato faz surgir o menu de contexto a partir do qual podem ser terminados, rastreados no SQL Server Profiler (mais sobre isso mais tarde), vistos como um plano de execução, e por último, mas não menos importante, os detalhes da sessão que surgem num diálogo mostrando o último lote de comandos T-SQL:
Espera de recursos – mostra informações sobre as esperas de recursos:
Arquivo de dados I/O – mostra a informação actual de I/O do ficheiro de dados que está a acontecer ao nível do ficheiro:
P>P>Pesquisas recentes/activas dispendiosas – mostra as consultas recentes/activas dispendiosas que utilizam muitos recursos (memória, actividade do disco, rede):
Este é um óptimo ponto de partida para descobrir quais as consultas que estão a causar problemas, que utilizam demasiados recursos, etc. porque, uma vez encontradas, podem ser vistas como o plano de execução para que os pontos quentes possam ser facilmente encontrados:
Top DBA DMVs
Moving on, vamos verificar os DMV de topo que todos os DBA devem saber. Estes são os DMV que deve ter sempre no seu bolso. Para obter uma lista rápida de todos os DMO (DMV e DMF) basta consultar o lote abaixo:
|
1
2
3
4
5
6
7
|
— Lista de todos os DMOs (DMVs & DMFs)
SELECT name,
tipo,
tipo_desc
do sistema.system_objects so
WHERE so.name LIKE ‘dm_%’
ORDER BY so.nome;
|
Isto devolverá todos os DMVs & DMFs no SQL Server. Veja a coluna de tipo e note que “V” significa uma vista, e “IF” para uma função:
A consulta acima retornou 243 DMOs num sistema. Aqui está a lista dos mais úteis:
Execução
sys.dm_exec_connections = Ligação estabelecida
sys.dm_exec_sessions = Sessões autenticadas
sys.dm_exec_requests = Pedidos actuais
Execução (relacionados com a consulta)
p>sys.dm_exec_cached_plans = Planos de execução em cache
sys.dm_exec_query_plan = Mostrar plano para um determinado plano_handle em cache
sys.dm_exec_query_stats = Consultar estatísticas de desempenho
sys.dm_exec_sql_text = Texto SQL dado por um sql_handle
Index
sys.dm_db_index_index_physical_stats = Tamanho e fragmentação do índice
sys.dm_db_index_usage_stats = utilização do índice através do optimizador de consulta
sys.dm_db_missing_index_details = Descobrir índices em falta
OS
sys.dm_os_performance_counters = Lista de todos os contadores e valores de desempenho do SQL Server
sys.dm_os_schedulers = Detectar pressão da CPU
sys.dm_os_waiting_tasks = Tarefas à espera nos recursos
sys.dm_os_wait_stats = Todos os tipos de espera e estatísticas
I/O
sys.dm_io_virtual_file_stats = Estatísticas de E/S para dados e ficheiros de registo
sys.dm_io_pending_io_requests = Pedidos de E/S pendentes
CLR
sys.dm_clr_loaded_assemblies = Montagens carregadas
sys.dm_clr_tasks = tarefas relacionadas com o CLR
Os livros online (docs.microsoft.com) têm uma óptima página de visão geral para todos os DMOs. Sinta-se à vontade para copiar qualquer nome da grelha de resultados, colá-lo no browser e pesquisar por ele. Muito provavelmente o primeiro artigo no topo será de MS docs mostrando a visão geral e utilização do T-SQL.
No final, vamos dar uma espreitadela ao SQL Server Profiler para criar e reproduzir um traço. Isso é extremamente útil na resolução de problemas, sejam eles de uma aplicação ou internamente dentro da base de dados. A criação de um rastreio permite-nos capturar uma carga de trabalho. E se essa carga de trabalho for a que causa problemas do que ter a capacidade de reproduzir esse problema tornará muito mais fácil verificar se realmente o resolvemos ao resolver o problema.
Infelizmente, a Microsoft anunciou a depreciação do SQL Server Profiler, mas esta funcionalidade ainda está disponível no SQL Server 2016 e será removida nas futuras versões.
Por isso, passemos ao SQL Server Profiler e vejamos como funciona. Para iniciar o SQL Server Profiler, carregar no ícone Start do Windows ou premir a tecla Windows e introduzir “SQL Server Profiler 17”:
Recordar, o SQL Server Profiler também pode ser inicializado a partir do Activity Monitor, basta clicar no painel Processos, clicar com o botão direito do rato no processo que deseja perfilar, e depois a partir do menu de contexto do botão direito do rato escolher a opção Trace Process in SQL Server Profiler.
Assim, assim que isto se activar, clique no primeiro botão que diz, “New Trace” (Novo Rastreio). Isto fará surgir o diálogo de ligação à sua instância:
Depois de estabelecida a ligação à instância que pretende perfilar e criar vestígios contra, aparecerá a janela Trace Properties. Dê um nome ao traço na caixa Trace name. E uma vez que queremos reproduzir este traço, seleccionar um modelo de traço integrado porque existem alguns requisitos com reproduções, certos eventos e campos precisam de ser capturados e a forma mais fácil de o fazer é escolher o modelo TSQL_Replay a partir da lista Utilizar o modelo:
Por enquanto, guardar isto como ficheiro, verificando a opção Guardar no ficheiro e depois alterar a tabulação para Selecção de Eventos. Uma vez que escolhemos um modelo, ele vai pré-seleccionar muitos eventos. A partir daqui, pode jogar com as opções, mostrar todos os eventos/colunas, adicionar/remover certos eventos, etc.:
Neste caso, vamos clicar no botão Column Filters (Filtros de Coluna) para definir um filtro no nome de uma base de dados. Apenas pretendemos apanhar os eventos que atingem a base de dados AdventureWorks2014. Esta é uma óptima maneira de aparar todo o ruído extra que virá num cenário do mundo real:
Uma vez tudo isto configurado, clique em OK no diálogo Editar Filtro para guardar a configuração e fechá-la. De volta às janelas iniciais, carregar no botão Executar para iniciar o rastreio. Assim, este traço está agora em execução, monitorizando tudo o que está a acontecer na base de dados AdventureWorks2014:
Switch over SSMS e executar algumas consultas ou simplesmente deixá-lo correr em segundo plano durante algum tempo. Vários eventos serão capturados mesmo que nenhuma consulta do utilizador esteja a carregar na base de dados:
Depois de se sentar aí durante algum tempo, parar o traço clicando no botão vermelho de paragem, como mostrado abaixo:
Agora, o que conseguimos capturar dentro daquele ficheiro previamente guardado, podemos, por exemplo, executar esta repetição numa instância diferente. Porquê? Porque pode ser extremamente útil quando se está a ter um problema na produção. É muito improvável que quaisquer grandes problemas possam ser resolvidos directamente num servidor de produção. Neste caso de utilização, a melhor abordagem seria copiar a produção para testar, pegar naquele vestígio que foi capturado em produção, e depois podemos fazer tudo o que quisermos nessa base de dados de teste em termos de resolução de problemas e reparação do problema.
Para tal, abrir o traço no SQL Server Profiler:
Agora, vá para Reproduzir e escolha Iniciar:
Isto irá, mais uma vez, trazer o diálogo de ligação do SQL Server à sua instância:
Que nos levará então à janela de Configuração de Repetição na qual podemos alterar o servidor de repetição se for necessário para o propósito mencionado anteriormente. É bastante simples a partir deste ponto. Basta premir OK para reproduzir o traço:
A primeira linha conterá sempre todo o Evento de Configuração da Repetição. Depois, sempre que houver um evento, ele irá disparar esse evento. O conjunto de resultados apenas indicará que está feito:
Se nos deslocarmos para a direita, qualquer conjunto de resultados terá a duração medida do profiler – quanto tempo demorou a executar um evento:
Mesmo no fim, encontrará o Evento de Estatísticas de Repetição que mostra o total de eventos, erros de fornecedor/internos que ocorreram, estatísticas de taxa de acerto, e o tempo total de repetição:
Mais uma vez, isto é bom para cenários em que há um problema com o desempenho do SQL Server e se pretende recriar o problema. Em primeiro lugar, capture o problema que nos dará então o fluxo de trabalho dentro de um traço que podemos fazer o que for necessário no SQL Server para resolver e corrigir o problema do desempenho do SQL Server e depois, finalmente, podemos reproduzir esse traço, reproduzir esse problema para ver se o corrigimos com sucesso.
Conclusion
Neste artigo, cobrimos quatro ferramentas para monitorizar o desempenho do SQL Server. Esperemos que agora seja possível recolher o desempenho e informação do sistema usando Relatórios de Base de Dados para uma visão visual interactiva sobre o que o SQL Server está a fazer. Depois analisámos uma ferramenta em tempo real para monitorizar o desempenho do SQL Server chamada Activity Monitor. Também analisámos como utilizar DMV e, no final, aprendemos a utilizar o SQL Server Profiler.
