Script Challenge – 16 – A resposta

Obtendo o histórico de consumo de CPU durante um intervalo de tempo em minutos….

Bom dia, bom dia, bom dia….

Tudo bem? Que bom te encontrar tão cedo por aqui….

Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2019 – Post 16, sendo este respectivamente o quinto post após o retorno desta desafiadora sessão denominada Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados anteriormente. Caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 16….


Script Challenge 16

Falando do desafio de número 16, o mesmo foi publicado no mês de outubro de 2019, período de data em que praticamente todos nós estamos começando a preparar para o encerramento do ano, as festas de final de ano estão se aproximando.

Da mesma forma que o início do ano não é algo muito fácil, o final também não, normalmente estamos no nosso limite físico, emocional, psicológico também, dos últimos dois meses parecem que duram o ano todo, mas não podemos nos abater e temos que serguir em frente, o mesmo foi feito aqui no meu blog.

Pois bem, o Script Challenge 16 possui um pouco relação ou conexão com toda esta loucura, não necessariamente com o natal ou final de ano, muito menos com presentes e toda aquela mesa farta com diversas comidas.

Na verdade, sabemos que existem determinadas situações, ações, procedimentos e demais atividades que se não cumprimos da maneira que estava preestabelecida acabamos de alguma forma sendo notificados ou até mesmo penalizados, pensando justamente desta forma, o Script Challenge 16 se relaciona.

Continuando nossa história, quero lhe perguntar: E ai já matou a charada? Acho que ainda não! Não é mesmo?

Para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 16.

E ai, revendo novamente o código publicado no post de abertura deste desafio, você conseguiu checar a alguma conclusão?

Bom chegou a hora de revelar o que exatamente este bloco de código se propõem a fazer, chegou o momento de revelar e desvendar este desafio. A seguir apresento a resposta para o Script Challenge 16 e o todo bloco de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Como você pode ter percebido, no decorrer deste post e também no post de lançamento, procurei de uma forma simples mas não explícita exibir ao longo do texto, pequenas dicas que poderiam ajudar, nortear e quem sabe indicar uma possível resposta ou solução para o desafio.

Falando mais diretamente, a resposta para o Script Challenge 16, esta totalmente relacionada as nossas atividades diárias, mas não no mundo real, mas sim no mundo virtual, estou me referindo ao histórico de tudo aquilo que fazemos durante um período ou intervalo de tempo, e foi pensando justamente neste tipo de situação que o Script Challenge 16 foi criado.

Mas se mesmo assim, você ainda não conseguiu adivinhar ou até mesmo pode estar se perguntando: Qual a relação do Script Challenge – 16 tem haver com histórico? A resposta para seu questionamento é muito simples, para qualquer Administrador de Banco de Dados, Administrador de Servidores, Desenvolvedor, enfim um profissional de tecnologia, tudo o que fazemos basicamente em um computador deve de alguma gerar dados históricos, gerar registros que evidenciam o que foi feito, quando, como, e até mesmo o porque, e como todos devem saber, um histórico nada mais é que uma relação de itens e ações registradas ao longo do tempo que ilustram de fato estes acontecimentos.

Então a resposta para o Script Challenge 16 se relaciona com a possibilidade que o script apresenta em nos ajudar a saber como foi o consumo de CPU utilizado em nosso servidor ou instância SQL Server durante um determinado intervalo de tempo, neste caso levando-se em consideração um período de tempo de 30 minutos, com intervalos pequenos do registro de histórico de processamento a cada um minuto.

Abaixo apresento o script original que ilustra esta capacidade que o Microsoft SQL Server em conjunto com suas visões e funções de gerenciamento dinâmico possui:

— Script Challenge 16 – A resposta – Realizando Auditoria em tempo real (In Live) —

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info);
SELECT TOP(30)
               SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Event Time]
FROM (
              SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id,
                           record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS [SystemIdle],
                           record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,’int’) AS [SQLProcessUtilization],
                           [timestamp]
FROM (
             SELECT [timestamp], CONVERT(xml, record) AS [record]
             FROM sys.dm_os_ring_buffers
             WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
             AND record LIKE N’%<SystemHealth>%’) AS x
            ) AS y
ORDER BY record_id DESC
Go

Então, agora você deve ter gostado deste desafio, não é verdade? Saber que existe a possibilidade de identificar como foi o consumo de CPU utilizado por nosso SQL Server durante um período de tempo, com certeza é algo que poderá nos ajudar muito a entender possíveis mudanças de comportamento em nosso ambiente.

Figura 2 apresentada abaixo, ilustra o conjunto de dados retornados após a execução do Script Challenge – 16:

Figura 2 – Histórico de execução e consumo de CPU.

Dentre o conjunto de colunas retornadas pelo Script Challenge 15, destaco algumas:

  • SQL Server Process CPU Utilization: Apresenta os valores relacionados a quantidade de CPU consumida pelo SQL Server de acordo com a linha de histórico retornada dentro do intervalo de tempo registrado. Esta coluna é gerada atráves processo Scheduler Monitor Event / System Health / Process Utilization, o qual é parte da camada do sistema operacional existente no SQL Server conhecido como SQLOS, em conjunto com o próprio sistema operacional e sua camada de monitoramento de eventos; e
  • Other Process CPU Utilization: Apresenta os valores relacionados a quantidade de CPU consumida por todos os outros serviços e recursos em execução de acordo com a linha de histórico retornada dentro do intervalo de tempo registrado. Esta coluna é gerada atráves do cálculo: 100 –
    SystemIdle – SQLProcessUtilization, onde a coluna SystemIdle é concebida através do processo Scheduler Monitor Event / System Health / System Idle, sendo este também pertencente parte da camada do sistema operacional existente no SQL Server.

Enfim, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 16, fico honrado, feliz por ter conseguido compartilhar este conteúdo com vocês.

Espero que você tenha gostado deste novo post da sessão Script Challenge!


Sua Participação

No post de lançamento deste desafio, contei com a sua participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 16. A seguir apresento o resultado desta enquete:

A opção mais votada com 66% dos votos é justamente a resposta correta para este desafio: “O bloco de código apresentado se relacionado com a possibilidade de realizar o monitoramento do consumo cpu como parte dos processos e aplicações que estão acessando o SQL Server em um longo período de tempo.”

Referências

https://www.mssqltips.com/sqlservertip/1915/how-to-automatically-monitor-windows-event-log-from-sql-server/

https://docs.microsoft.com/en-us/sql/ssms/agent/monitor-and-respond-to-events

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-schedulers-transact-sql

https://www.mssqltips.com/sqlservertip/4403/understanding-sql-server-schedulers-workers-and-tasks/

https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-buffer-descriptors-transact-sql

https://gallery.technet.microsoft.com/SQL-Server-Monitoramento-ea6dd632

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sql-server-operating-system-related-dynamic-management-views-transact-sql

Posts anteriores

Caso esta seja a primeira vez que você acessa um post desta sessão, fico muito feliz e aproveito para compartilhar os posts mais recentes:

https://pedrogalvaojunior.wordpress.com/2019/10/26/script-challenge-2019-post-16/

https://pedrogalvaojunior.wordpress.com/2019/06/26/script-challenge-15-a-resposta/

https://pedrogalvaojunior.wordpress.com/2019/02/13/script-challenge-2019-post-15/

https://pedrogalvaojunior.wordpress.com/2018/10/06/script-challenge-14-a-resposta/

https://pedrogalvaojunior.wordpress.com/2018/06/29/script-challenge-2018-post-14/

Agradecimentos

Obrigado por sua visita, espero que este conteúdo aqui apresentado como um possível “desafio” possa ser útil e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2020 em mais um post da sessão Script Challenge.

Até mais….

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP – Faculdade de Informática e Administração Paulista de São Paulo. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove – Campus São Roque. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1994 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator – Administrador de Banco de Dados – SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, entre outros recursos. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, etc. Trabalhei entre 2011 e 2017 como Administrador de Banco de Dados e Coordenador de TI no FIT – Instituto de Tecnologia da Flextronics, atualmente exerço a função de Professor Universitário na FATEC São Roque. CTO da Galvão Tecnologia, consultoria especializada em Gestão de TI, Administração de Servidores Windows Server, Bancos de Dados Microsoft SQL Server e Virtualização. Possuo titulação Oficial Microsoft MVP e reconhecimentos: MCC, MSTC e MIE.