Script Challenge – 2019 – Post 16


Olá, bom dia amantes de banco de dados…

Tudo bem? Nossa como o tempo esta passando, como você sabe a cada quatro meses nos encontramos em mais um post dedicado especialmente a sessão Script Challenge.

Hoje sabado dia 26/10 a primavera chegou a alguns dias, mas continuamos sofrendo muito com este forte calor,  pode até parecer coincidência mas o post anterior publicado no mês de fevereiro desta sessão também foi iniciado falando do calor. O ano de 2019 esta terminando da mesma forma que começou: Sol forte, Ventos quentes e Temperaturas altas para os padrões nacionais, em especial para minha “grande” São Roque (kkkkk……)

O post de hoje não esta relacionado com as mudanças de temperatura do nosso clima, muito menos com o calor, chuva, ao contrário, ele vai tentar lhe ajudar a identificar possíveis situações que começam a ser apresentadas em nosso ambiente decorrentes de mudança rápida, totalmente inesperada, algo que ocorre em poucos minutos que poderá provocar um estrago, isso é muito comum de acontecer. Não é mesmo?

Pense então, nas situações relacionados ao seu ambiente de banco de dados que em poucos minutos começa a se comportar de forma totalmente desconhecida, é a mesma coisa que sair para dar uma volta e começar a chover e você não tem um guarda-chuva, e ai que fazer, como identificar esta mudança ou o que pode ter provocado isso, mas principalmente a partir de qual momento esse comportamento diferente teve início.

Introdução

Os posts publicados nesta sessão tem o objetivo de desafiar o visitante a descobrir o que um determinado script pode fazer ao ser executado, e não somente isso, mostrar como podemos aprender com o uso da linguagem Transact-SQL e sua vasta coleção de comandos, funções e instruções adicionadas a cada nova versão ou atualização do Microsoft SQL Server.

Se você ainda não conhecia a sessão Script Challenge, fique tranquilo vai ter a possibilidade agora mesmo, como também a cada 4 meses poder desfrutar de um novo desafio e sua respectiva resposta, por isso esta sessão é denominada Script Challenge(Script Desafio ou Desafio do Script), bom a melhor forma de traduzir eu deixo para você escolher.

Seguindo um frente….

Gostaria de destacar mais uma vez duas mudanças iniciais implementadas na sessão Script Challenge tendo como base o post publicado em outubro de 2017, sendo elas:

  1. Como maneira ou forma de dificultar ainda mais o nível de complexidade do código, o bloco de código que representa o Script Challenge selecionado para compor o post será  apresentado no formato de imagem ou figura; e
  2. Algumas partes das linhas de código, trechos, ou partes de código serão omitidas, justamente como forma de tornar este código ainda mais desafiador.

Antes de destacar o script de hoje, não custa nada saber um pouco mais sobre esta sessão, por este motivo, quero lhe contar um pouco da história que cerca os posts relacionadas a ela…

Um pouco de História

Esta é uma sessão criado no final do ano de 2010 e mantida mensalmente até meados de 2012, desde então não me dediquei mais a mesma devido principalmente em não conseguir identificar possíveis scripts dentro da minha biblioteca que poderiam ser apresentados como um scripts considerados como desafio para os profissionais da área de banco de dados.

Mas acredito que nos últimos anos com o crescimento do Microsoft SQL Server, seu nível de evolução, amadurecimento e recursos adicionais, como também, meu nível de experiência profissional e acadêmica também evolui muito, sinto-me preparado para retornar com esta sessão e poder de forma bastante técnica, didática e conceitual poder lançar um “desafio” e responder o mesmo com muito segurança.

Outro detalhe importante, esta é uma sessão que foi criada, pensando em apresentar pequenos blocos de códigos oriundos da linguagem Transact-SQL que possam apresentar um nível de dificuldade e conhecimento técnico um pouco mais elevado, exigindo assim uma análise mais profunda e até mesmo a criação um pequeno cenário de teste para seu entendimento.

Vamos então conhecer o bloco de código selecionado para ser o Script Challenge deste post.


Script Challenge – 16

O bloco de código Transact-SQL selecionado para fazer parte do Script Challenge de hoje, esta relacionado com uma importante atividade exercida por Administradores de Servidores, DBAs e Administradores de Banco de Dados.

Trata-se de algo que pode ajudar a identificar possíveis situações que venham a atrapalhar o processamento e execução de nossas querys e transações. Ao contrário do Script Challenge 15, este script possui a finalidade de apresentar os custos de processamento que estão sendo demandados pelos processos em execução relacionados diretamente com o Microsoft SQL Server.

Não deveria, mas vou lhe ajudar, apresentando duas dicas sobre este desafio

  1. O mesmo, pode ser executado em conjunto com as demais sessões, transações ou querys em execução, sem necessitar que qualquer procedimento de manutenção ou alterações na configuração do SQL Server para sua execução, como também, esta totalmente relacionado com o que venha ou possa estar sendo processado pelo sistema operacional que necessite de dados do SQL Server naquele exato momento; e
  2. Preste atenção na relação de tags que estão compartilhadas, nelas você poderá encontrar as partes omitidas do bloco de código, bem como, a resposta para a enquente apresentado no final do post.

E ai, ficou curioso para saber o que este script realiza? Qual seria a sua atividade? O conjunto de resultados que ele retornaria? Pois bem, foi fazer o papel de chato, a resposta para estes e outras demais questões não serão apresentadas hoje (hahahahahahaha……), vou deixar com que você quebre um pouco a sua cabeça, afim de tentar matar esta curiosidade, no final do post como de costume você terá a sua disposição uma enquete que lhe permitirá expor tentar encontrar a resposta para este desafio.

A seguir apresento o bloco de código:


Figura 1 – Short Script 16.

Muito bem, nosso Script Challenge esta apresentado. Você preparado para mais este desafio?

Tenho a certeza que sim!


Sua participação

Legal, agora quero conter com a sua participação, me ajudando a responder este post, outra grande novidade que estou adicionando aos posts da sessão Script Challenge será uma pequena enquete com possíveis respostas para identificar qual é a correta, relacionada com o desafio apresentado através do bloco de código selecionado para este post, desta forma, apresento a seguir a Enquete – Script Challenge – 16 e suas alternativas de resposta:

Seguindo em frente, já conhecemos o Script Challenge selecionado, sua enquete e relação de opções de resposta, fica faltando somente a sua participação, algo que eu tenho a certeza que vai acontecer rapidamente.

Durante quanto tempo posso participar…

Outra grande mudança na sessão Script Challenge é seu tempo de publicação “validade”, anteriormente os posts desta sessão eram publicados mensalmente ou bimestralmente, agora serão publicados em um período mais longo, ou seja, a cada 4 meses estarei compartilhando com vocês novos posts dedicados exclusivamente para esta sessão.

Desta forma, você terá mais tempo para participar e me ajudar e encontrar a resposta correta para este desafio, enviando suas possíveis sugestões, críticas e até mesmo alternativas de resposta para minha enquete.

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/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 post apresentado como um possível “desafio” possa ser úteis 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, tratados com “bichos de sete cabeças”.

Um forte abraço nos encontramos em breve nas demais sessões, e especialmente em fevereiro de 2020 em mais um post da sessão Script Challenge, o qual apresentará a resposta para este post.

Fique a vontade para conhecer demais post publicados até o presente momento em todas as sessões do meu blog.

Tenha um ótimo final de semana.

Abraços.

Script Challenge – 2019 – Post 15


Olá, bom dia comunidade…

Tudo bem? Quarta – feira dia 13/02 estamos no verão, sofrendo muito com este super calor que esta “queimando nossas cacholas”, mas hoje aqui em São Roque, amanheceu um dia bem diferente dos últimos, muito chuva, vento e temperaturas bem amenas, podemos dizer que aquele friozinho gostoso esta de volta.

Pois bem, sou suspeito a dizer sobre o frio, ainda mais por ser fã incondicional de temperatura baixas, devido a ter trabalhado muitos anos dentro de os mais variados datacenters de empresas e clientes suportando “aquelas” altas temperaturas de 16 ou 18 graus.

Mas o que esta mudança de tempo tem haver com este novo post da sessão Script Chalenge, bom na minha opinião tem tudo haver, ainda mais dependendo do situação e como diria um velho amigo meu “do nível da problemática da coisa” ele poderá aumentar ou diminuir a sua temperatura em pouso segundo, kkkkkk.

Introdução

Os posts publicados nesta sessão tem o objetivo de desafiar o visitante a descobrir o que um determinado script pode fazer ao ser executado, e não somente isso, mostrar como podemos aprender com o uso da linguagem Transact-SQL e sua vasta coleção de comandos, funções e instruções adicionadas a cada nova versão ou atualização do Microsoft SQL Server.

Se você ainda não conhecia a sessão Script Challenge, fique tranquilo vai ter a possibilidade agora mesmo, como também a cada 4 meses poder desfrutar de um novo desafio e sua respectiva resposta, por isso esta sessão é denominada Script Challenge(Script Desafio ou Desafio do Script), bom a melhor forma de traduzir eu deixo para você escolher.

Seguindo um frente….

Gostaria de destacar mais uma vez duas mudanças iniciais implementadas na sessão Script Challenge tendo como base o post publicado em outubro de 2017, sendo elas:

  1. Como maneira ou forma de dificultar ainda mais o nível de complexidade do código, o bloco de código que representa o Script Challenge selecionado para compor o post será  apresentado no formato de imagem ou figura; e
  2. Algumas partes das linhas de código, trechos, ou partes de código serão omitidas, justamente como forma de tornar este código ainda mais desafiador.

Antes de destacar o script de hoje, não custa nada saber um pouco mais sobre esta sessão, por este motivo, quero lhe contar um pouco da história que cerca os posts relacionadas a ela…

Um pouco de História

Esta é uma sessão criado no final do ano de 2010 e mantida mensalmente até meados de 2012, desde então não me dediquei mais a mesma devido principalmente em não conseguir identificar possíveis scripts dentro da minha biblioteca que poderiam ser apresentados como um scripts considerados como desafio para os profissionais da área de banco de dados.

Mas acredito que nos últimos anos com o crescimento do Microsoft SQL Server, seu nível de evolução, amadurecimento e recursos adicionais, como também, meu nível de experiência profissional e acadêmica também evolui muito, sinto-me preparado para retornar com esta sessão e poder de forma bastante técnica, didática e conceitual poder lançar um “desafio” e responder o mesmo com muito segurança.

Outro detalhe importante, esta é uma sessão que foi criada, pensando em apresentar pequenos blocos de códigos oriundos da linguagem Transact-SQL que possam apresentar um nível de dificuldade e conhecimento técnico um pouco mais elevado, exigindo assim uma análise mais profunda e até mesmo a criação um pequeno cenário de teste para seu entendimento.

Vamos então conhecer o bloco de código selecionado para ser o Script Challenge deste post.


Script Challenge – 15

O bloco de código Transact-SQL selecionado para fazer parte do Script Challenge de hoje, esta relacionado com uma importante atividade exercida por Administradores de Servidores,  DBAs e Administradores de Banco de Dados.

Trata-se de algo que pode ajudar a identificar possíveis situações que venham a atrapalhar o processamento e execução de nossas querys e transações. Vale ressaltar que este script, não possui a finalidade de apresentar os custos de processamento que estão sendo demandados pelos processos em execução.

Vou dar uma pequena canja, sobre este desafio: O mesmo, pode ser executado em conjunto com as demais sessões, transações ou querys em execução, sem necessitar que qualquer procedimento de manutenção ou alterações na configuração do SQL Server para sua execução.

E ai, ficou curioso para saber o que este script realiza? Qual seria esta possível atividade? Mesmo assim, não vou lhe responder hoje, vou deixar que você quebre um pouco a sua cabeça, afim de tentar matar esta curiosidade, mas como não sou tão mal assim, no final do post como de costume vou deixar uma enquete para você expor sua opinião.

A seguir apresento o bloco de código:

Figura 1 – Short Script 15.

Muito bem, nosso Script Challenge esta apresentado. Você preparado para mais este desafio?

Tenho a certeza que sim!


Sua participação

Legal, agora quero conter com a sua participação, me ajudando a responder este post, outra grande novidade que estou adicionando aos posts da sessão Script Challenge será uma pequena enquete com possíveis respostas para identificar qual é a correta, relacionada com o desafio apresentado através do bloco de código selecionado para este post, desta forma, apresento a seguir a Enquete – Script Challenge – 15 e suas alternativas de resposta:

Seguindo em frente, já conhecemos o Script Challenge selecionado, sua enquete e relação de opções de resposta, fica faltando somente a sua participação, algo que eu tenho a certeza que vai acontecer rapidamente.

Durante quanto tempo posso participar…

Outra grande mudança na sessão Script Challenge é seu tempo de publicação “validade”, anteriormente os posts desta sessão eram publicados mensalmente ou bimestralmente, agora serão publicados em um período mais longo, ou seja, a cada 4 meses estarei compartilhando com vocês novos posts dedicados exclusivamente para esta sessão.

Desta forma, você terá mais tempo para participar e me ajudar e encontrar a resposta correta para este desafio, enviando suas possíveis sugestões, críticas e até mesmo alternativas de resposta para minha enquete.

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/2018/10/06/script-challenge-14-a-resposta/

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

https://pedrogalvaojunior.wordpress.com/2017/10/26/script-challenge-2017-o-retorno/

https://pedrogalvaojunior.wordpress.com/2018/03/01/script-challenge-13-a-resposta/


Agradecimentos

Obrigado por sua visita, espero que post apresentado como um possível “desafio” possa ser úteis 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, tratados com “bichos de sete cabeças”.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2019 em mais um post da sessão Script Challenge, o qual apresentará a resposta para este post.

Sucesso, mais uma vez obrigado por sua visita, espero que você tenha gostado deste post.

Fique a vontade para conhecer demais post publicados até o presente momento em todas as sessões do meu blog.

Abraços.

Monitorando o progresso de execução de querys em tempo real no Microsoft SQL Server 2014.


Olá pessoal, boa tarde!!!

E ai quais as novidades? Estamos se aproximando da primavera, e pensar que nos encontramos no inverno com temperaturas na casa dos 30° graus que coisa, algo bastante fora do comum para esta época do ano, mas é a realidade que estamos vivendo.

Aproveitando esta onda de calor ou melhor dizendo de temperaturas um pouco mais elevados, quero também entrar nesta onda e compartilhar com vocês algo que considero quente para qualquer profissional de banco de dados.

Você pode estar se perguntando, algo quente para um profissional de banco de dados:

  • Será que ele esta falando em processos de restauração de dados?
  • Não, não, o Junior Galvão esta pensando em Failover Cluster, AlwasOn….
  • Putz, não é isso não, ele esta pensando em performance, tunning, estatísticas…

Talvez esta última possibilidade passe perto, na verdade como diria meu pai, “Estou matutando aqui….” sobre monitoramento em tempo real no SQL Server. E ai você diz, “monitoramento em tempo real do SQL Server”, ai isso é fácil, isso já existem a muito tempo, podemos utilizar:

  • O SQL Server Profiler;
  • Podemos fazer uso de Trace Files;
  • Podemos utilizar o Activity Monitor; e
  • Ou até mesmo Extendend Events.

Sim eu sei disso, já fazemos isso de diversas formas e em versões mais antigas do Microsoft SQL Server, mas o que realmente eu estou pensando e quero destacar é uma nova DMV que foi introduzida na versão 2014 do SQL Server e que mudou um pouco para não dizer bastante a maneira que podemos acompanhar em tempo real o que esta acontecendo no SQL Server, mas o quanto cada uma “transações” ou “querys” esta executando.

Então vamos nessa, a brincadeira de hoje é apresentar e destacar a nova DMV sys.dm_exec_query_profiles disponível a partir do Microsoft SQL Server 2014 em todas as suas edições, conforme destaca a documentação oficial da Microsoft: https://msdn.microsoft.com/pt-br/library/dn223301.aspx

 

Introdução

Você tem uma consulta no SQL Server que sempre demora muito para ser executada ou encerrada. Esta é o cenário mais comum que qualquer DBA passa pelo menos uma vez por mês em seu trabalho, e ai diversos e diversos questionamentos, análises, considerações são feitas. Você pensa, eu tenho o plano de execução, vou fazer a análise deste plano para reconhecer e entender o que esta consulta esta fazendo ou deveria fazer.

E ai uma das maiores dúvidas que pode passar sobre sua cabeça, o que será exatamente que esta consulta esta fazendo neste momento? Para tentar ajudar a responder a esta pergunta e muitas outras, foi que o time de desenvolvimento e engenharia do Microsoft SQL Server, teve a fantásitca ideia de liberar na versão 2014 a DMV sys.dm_exec_query_profiles.

Agora você já deve ter percebido que esta DMV(Dinamyc Management View ou Visão de Gerenciamento Dinâmico) é algo bastante especial, pois mostrar em tempo real o que a sua consulta esta fazendo naquele momento, somente o Microsoft SQL Server pode te oferecer isso.

 

Conhecendo a sys.dm_exec_query_profiles

Como destacado anterior a sys.dm_exec_query_profiles, consiste basicamente em uma das novas DMVs que foram disponibilizadas em conjunto com o Microsoft SQL Server na versão 2014. Por padrão sua finalidade é monitorar o progresso da consulta em tempo real, enquanto a consulta está em execução. Para tal objetivo, ela faz uso de contadores executados em segundo plano, trabalhando como threads.

Os contadores estão organizados em duas categorias:

  • Primeira categoria contadores cumulativos: Como row_count, elapsed_time_ms,
  • Segunda categoria: Conhecidos como “marcadores” ou “carimbos” de data/hora. Os carimbos de data/hora marcam o tempo que certos eventos acontecem e podem ser usados para correlacionar esses eventos com dados externos no SQL Server. Exemplos desses dados são perfmon, XPerf, etc.

Vale ressaltar, que os contadores fornecem dados em uma granularidade maior do que SET STATISTICS IO ON já que eles são por interpretados e tratados como thread.

Pensar como esta dmv trabalha não é algo fácil, mas como o SQL Server é repleto de funcionalidades, torna-se possível entender.

 

Comportamento

A sys.dm_exec_query_profiles durante seu período de execução, realiza a coleta de informações de forma serializada e apresenta estes dados após o final da sua execução no modelo do SHOWPLAN XML, ou seja, os dados são estruturados na forma de plano de execução que podem ser apresentados e analisados diretamente no Management Studio.

 

Considerações

A forma de uso da sys.dm_exec_query_profiles é bastante simples e comum para aqueles já acostumados a trabalhar com as DMVs ou Diretivas SETs existente no SQL Server, o primeiro passo consiste na utilização da SET STATISTICS PROFILE ON(Introduzida no Microsoft SQL Server 2008) ou SET STATISTICS XML ON(Introduzida no Microsoft SQL Server 2005).

Para maiores informações sobre a SET STATISTICS XML ON acesse: https://msdn.microsoft.com/en-us/library/ms176107.aspx

Ao fazer uso da sys.dm_exec_query_profiles por padrão o SQL Server vai disponibilizar o resultado desta DMV através de uma tabela com a seguinte estrutura:

Nome da coluna Tipo de dados Descrição
session_id smallint Identifica a sessão na qual esta consulta é executada. Referencia dm_exec_sessions.session_id.
request_id int Identifica a solicitação de destino. Referencia dm_exec_sessions.request_id.
sql_handle varbinary(64) Identifica a consulta de destino. Referencia dm_exec_query_stats.sql_handle.
plan_handle varbinary(64) Identificar a consulta de destino. Referencia dm_exec_query_stats.plan_handle.
physical_operator_name nvarchar(256) Nome do tipo de operador físico.
node_id int Identifica um nó do operador na árvore de consulta.
thread_id int Distingue os threads (para uma consulta paralela) que pertencem ao mesmo nó do operador de consulta.
task_address varbinary(8) Identifica a tarefa do sistema operacional SQL que esse thread está usando. Referencia dm_os_tasks.task_address.
row_count bigint Número de linhas retornadas pelo operador até o momento.
rewind_count bigint Número de retrocessos até o momento.
rebind_count bigint Número de reassociações até o momento.
end_of_scan_count bigint Número de término de exames até o momento.
first_active_time bigint A hora em que o construtor foi chamado primeiro em milissegundos.
estimate_row_count bigint Número estimado de linhas. Pode ser útil comparar estimated_row_count com o row_count real.
last_active_time bigint A hora em que o construtor foi chamado por último em milissegundos.
open_time bigint Tempo total de CPU (em milissegundos) acumulados por operações do nó de destino (por exemplo abertura/fechamento/getrow) até o momento.
first_row_time bigint Carimbo de data/hora quando aberto (em milissegundos).
last_row_time bigint Carimbo de data/hora quando obtém a primeira linha (em milissegundos).
close_time bigint Carimbo de data/hora quando fechado (em milissegundos).
elapsed_time_ms bigint A última hora em que o nó foi visto no estado de execução até o momento.
cpu_time_ms bigint Tempo total decorrido (em milissegundos) acumulados por operações do nó de destino (por exemplo abertura/fechamento/getrow) até o momento.
database_id smallint O banco de dados no qual o conjunto de linhas é aberto.
object_id int A tabela na qual o conjunto de linhas é aberto.
index_id int O índice (se houver) no qual o conjunto de linhas é aberto.
scan_count bigint Número de verificações de tabela/índice até o momento.
logical_read_count bigint Número de leituras lógicas até o momento.
physical_read_count bigint Número de leituras físicas até o momento.
read_ahead_count bigint Número de read-aheads até o momento.
write_page_count bigint Número de gravações de página até o momento devido ao derramamento.
lob_scan_count bigint Número de verificações de tabela/índice LOB até o momento.
lob_logical_read_count bigint Número de leituras lógicas LOB até o momento.
lob_physical_read_count bigint Número de leituras físicas LOB até o momento.
lob-read_ahead_count bigint Número de read-aheads LOB até o momento.
segment_read_count int Número de read-aheads de segmento até o momento.
segment_skip_count int Número de segmentos ignorados até o momento.

Você deve ter observado que o conjunto de dados retornados por esta dmv apresenta uma relação com outras DMVs existentes no SQL Server, dentre elas destaco:

Colocando a mão na massa….na verdade no teclado

Pensando em uma forma bastante simples e prática para simular o uso desta dmv, vamos trabalhar em um cénario muito comum para qualquer profissional da área de banco de dados, desenvolvimento e análise. Basicamente nosso ambiente será estrutura da seguinte forma:

  • Banco de Dados – DBMonitor; e
  • Tabelas – Impostos e Paises.

Na tabela de impostas faremos o cadastro de uma lista de diversos impostos fictícios, informando a taxa base do imposto e uma taxa de equilíbrio. Já a tabela de Países iremos fazer o cadastro de alguns países e posteriormente estabeleceremos um relacionamento entres estas duas tabelas, apresentando seu plano de execução com seus operadores e em seguida vamos utilizar a sys.dm_exec_query_profiles.

Em mãos a obra, começando pelo Código 1:

— Código 1 – Preparando o ambiente –

— Criando o Banco de Dados DBMonitor —

Create Database DBMonitor

Go

— Acessando o Banco de Dados DBMonitor —

Use DBMonitor

Go

Observe que realizamos dois dos mais comuns procedimentos existentes no SQL Server, criar um banco de dados através do comando Create Database e acessar este banco através do comando Use.

Agora vamos criar nossas duas respectivas tabelas: Impostos e Países, conforme apresenta o Código 2:

 

— Código 2 – Criando as Tabelas —

— Criando a Tabela Países —

Create Table Paises

(Id Int Identity(1,1) Primary Key,

Nome Varchar(80))

Go

— Criando a Tabela Impostos —

Create Table Impostos

(ID Int Identity(1,1) Primary Key,

IdPais Int Not Null,

TaxaBase Float,

TaxaEquilibrio As (TaxaBase)*10)

Go

— Adicionando o relacionamento —

Alter Table Impostos

Add Constraint [FK_ImpostosxPaises] Foreign Key (IdPais)

References Paises(Id)

Go

Nosso próximo passo é gerar uma massa de dados para estas tabelas, vale ressaltar que a estrutura das tabelas e seus respectivos dados são totalmente fictícias, não existe nenhum tipo de relação, veracidade ou regra de negócio aplicada neste cenário. Vamos em frente, Código 3:

— Código 3 – Inserindo a massa de dados –

— Inserindo a massa de dados de Países —

SET NOCOUNT ON

Go

Insert Into Paises Values(‘Brasil’),

(‘Jamaica’),

(‘Kiribati’),

(‘Papau Nova Guiné’),

(‘São Tomé e Príncipe’),

(‘Estônia’)

Go

— Inserindo a massa de dados de Impostos —

SET NoCount On

Go

Declare @Contador TinyInt

Set @Contador = 0

While @Contador < 255

Begin

Insert Into Impostos (IdPais, TaxaBase)

Values(IIF(@contador <=50, 1,

IIF(@Contador >=51 And @Contador <=100, 2,

IIF(@Contador >=101 And @Contador <=150, 3,

IIF(@Contador >=151 And @Contador <=200, 4,

IIF(@Contador >=201 And @Contador <=220, 5, 6))))),

RAND()*4)

Set @Contador = @Contador + 1

End

 

Perfeito, já temos nossa massa de dados criada, ambas as tabelas já estão populadas, vamos então simular um junção destes dados, posteriormente ver o plano de execução em seguida faremos uso da dmv sys.dm_exec_query_profiles para verificar em tempo real o processamento da nossa query, seguindo em frente com o Código 4:

 

 

— Código 4 – Inserindo a massa de dados –

Select P.Nome,

Sum(I.TaxaBase) As SomaTaxaBase,

Sum(I.TaxaEquilibrio) As SomaTaxaEquilibrio

From Impostos I Inner Join Paises P

On I.IdPais = P.Id

Group By P.Nome

Order By SomaTaxaEquilibrio

Neste momento acabamos de fazer uma simples consulta solicitando para o SQL Server que retorne a relação de países, com a soma das respectivas taxa base e taxa de equilíbrio. Para ilustrar o resultado desta query apresento a Figura 1:

progresso1

Figura 1 – Resultado da query executada no código 4.

Agora vamos ver o plano de execução utilizado por este mesma query, para isso apresenta a seguir a Figura 2:

progresso2

Figura 2 – Plano de execução utilizado pelo SQL Server para processar a query do código 4.

Mas você pode estar se pergunta, a onde esta a dmv sys.dm_exec_query_profiles, em qual parte do código ela foi utilizada, até agora em nenhuma parte, este será o nosso próximo passo. Para isso utilizaremos o Código 5:

— Código 5 – Monitorando o processamento da nossa query em tempo real —

— Atividando o monitoramento da nossa query —

SET STATISTICS PROFILE ON;

Go

— Executando a query —

Select P.Nome,

Sum(I.TaxaBase) As SomaTaxaBase,

Sum(I.TaxaEquilibrio) As SomaTaxaEquilibrio

From Impostos I Inner Join Paises P

On I.IdPais = P.Id

Group By P.Nome

Order By SomaTaxaEquilibrio Desc

Go

Observe que utilizamos a mesma query declarada na código 4, a diferença é que agora fizemos a declaração da diretiva SET STATISTICS PROFILE ON, onde estamos orientando o SQL Server a fazer todo monitoramente em tempo real da nossa query, sendo assim, teremos o seguinte resultado, conforme apresenta a Figura 3:

progresso3

Figura 3 – Resultado do monitoramento em tempo real do processamento do código 5.

Mas ainda não temos realmente um monitoramente próximo ao que esta sendo executado em nosso SQL Server, para isso acontecer tempo que fazer uso da sys.dm_exec_query_profiles ao mesmo tempo que nossa query esta sendo processada, sendo assim, podemos imaginar que somente querys que venham a demandar um custo de processamento considerável pela SQL Server poderão ser acompanhadas.

Vamos então, turbinar um pouco mais o nosso cenário, vamos criar uma nova tabela chamada BigTable, com base, nesta tablea vamos tentar acompanhar e monitorar nossa query. Vamos então utilizar o Código 6:

— Código 6 – Criando a BigTable –

— Criando a Tabela BigTable —

CREATE TABLE BigTable

(OrderID int NOT NULL IDENTITY(1, 1),

CustomerID int NULL,

OrderDate date NULL,

Value numeric (18, 2) NOT NULL)

GO

— Alterando a Tabela Adicionando Primary Key —

ALTER TABLE BigTable

ADD CONSTRAINT PK_BigTable PRIMARY KEY CLUSTERED  (OrderID)

GO

— Inserindo 5 Milhões de Linhas de Registro —

Insert Into BigTable(CustomerID, OrderDate, Value)

SELECT Top 15000000

ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000))) As CustomerID,

CONVERT(Date, GetDate() – ABS(CONVERT(Int, (CheckSUM(NEWID()) / 10000000)))) As OrderDate,

ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))) As Value

FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d

GO

A BigTable já esta criada e populada com uma grande massa de dados, podemos dar continuidade em nosso estudo, fazendo justamente o monitoramento da nossa query, para isso vamos utilizar o Código 7:

— Código 7 – Consulta pesada –

SELECT a.CustomerID,

CASE (SELECT AVG(b.Value)

FROM BigTable b

WHERE b.CustomerID = a.CustomerID)

WHEN 1000 THEN ‘Média = 1 mil’

WHEN 2000 THEN ‘Média = 2 mil’

WHEN 3000 THEN ‘Média = 3 mil’

WHEN 4000 THEN ‘Média = 4 mil’

WHEN 5000 THEN ‘Média = 5 mil’

ELSE ‘Não é número exato’

END AS Sts

FROM BigTable AS a

GROUP BY a.CustomerID

ORDER BY a.CustomerID

OPTION (MAXDOP 1)

Note que nossa query utiliza o operador Case, algo que normalmente consumo muito do SQL Server, além disso, estamos fazendo agrupamento de dados, organização e removendo o uso do paralelismo com a opção MaxDop 1.

Agora vamos para os finalmente, abra uma nova query, volta para a query que você declarou o código 7 faça com que o SQL Server execute esta query, retorne para nova query e execute o Código 8, apresentado a seguir:

— Código 8 – Monitorando nossa query pesada em tempo de execução –

SELECT  session_id ,

node_id ,

physical_operator_name ,

SUM(row_count) row_count ,

SUM(estimate_row_count) AS estimate_row_count ,

IIF(COUNT(thread_id) = 0, 1, COUNT(thread_id)) [Threads] ,

CAST(SUM(row_count) * 100. / SUM(estimate_row_count) AS DECIMAL(30, 2)) [% Complete] ,

CONVERT(TIME, DATEADD(ms, MAX(elapsed_time_ms), 0)) [Operator time] ,

DB_NAME(database_id) + ‘.’ + OBJECT_SCHEMA_NAME(QP.object_id,

qp.database_id) + ‘.’

+ OBJECT_NAME(QP.object_id, qp.database_id) [Object Name]

FROM    sys.dm_exec_query_profiles QP

GROUP BY session_id , node_id , physical_operator_name ,

qp.database_id , QP.OBJECT_ID , QP.index_id

ORDER BY session_id , node_id

GO

Pronto, era justamente isso que precisavamos, neste momento enquanto nossa query pessada esta sendo processada, o Microsoft SQL Server 2014, através da dmv sys.dm_exec_query_profiles encontra-se em tempo real realizando o monitoramento do progresso de execução do nosso Código 7, para ilustrar e comprovar apresento a Figura 4:

progresso4

Figura 4 – Monitoramento em tempo real da execução do Código 7.

Caso você não tenha notado, existe uma coluna declarada como % Complete, esta coluna é um dos principais marcadores ou melhor indicadores do acompanhamento e evolução do progresso de execução da nossa query. Para realmente comprovar este monitoramento, repita mais duas ou três vezes a execução do Código 8.

Provavelmente a nossa query ainda encontra-se em execução e o SQL Server poderá retornar novas informações sobre o andamento de processamento a qualquer momento, desta forma, chegamos ao final de mais este post.

Conclusão

Neste post foi possível destacar como o Microsoft SQL Server 2014 esta a frente das demais versões, principalmente no que se relacionamento a monitoramento em tempo real, através da Visão de Gerenciamento Dinâmico – Sys.dm_exec_query_profiles, a Microsoft introduziu um novo mecanismos que nos permite obter dados importantes do que nossa query ou transação esta fazendo naquele exatamente.

Acredito que esta funcionalidade venho justamente como forma de possibilitar aos profissionais de banco de dados e aqueles que trabalham com este tipo de tecnologia, como é possível entender e identificar em qual ponto do processamento de nossas querys o SQL Server esta apresentando maiores dificuldades ou até mesmo lentidão.

Além dos recursos já existentes, dentro eles o SQL Server Profiler, a sys.dm_exec_query_profiles, nos garante de forma simples, fácil, rápida e prática uma outra maneira de interpretar e reconhecer todos os passos necessário que o SQL Server usa para conseguir coletar e resultar os dados solicitados á ele.

Mais uma vez a Microsoft inovou, mostrou como é possível evoluir uma ferramenta tão fantástica quando o SQL Server, sem impactar em sua estrutura, forma de trabalho e configurações, com certeza é um grande passo alcançado com esta nova feature.

Agradeço a você por sua visita, espero que tenha gostado deste artigo, fique a vontade para compartilhar com seus contatos.

Nos encontramos em breve.

Até mais.