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.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. 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. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. 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 1995 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, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. 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. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

5 comentários em “Monitorando o progresso de execução de querys em tempo real no Microsoft SQL Server 2014.”

  1. Parabéns Galvão, Excelente post… Sempre contribuindo muito com a comunidade técnica.

    Apenas uma pequena observação, que pode ter passado…

    As opções de SET especificadas, existem desde versões anteriores do SQL Server…

    SET STATISTICS PROFILE (SQL Server 2008)

    SET STATISTICS XML (SQL Server 2005)

    Mais uma versões parabéns pelo excelente trabalho com a comunidade…

    Curtir

    1. Edvaldo, bom dia.

      Obrigado por seu comentário!!! Estou trabalhando fortemente para conseguir melhorar meu conhecimento em relação ao SQL Server e principalmente tentar trazer um conteúdo com maior e melhor qualidade.

      Realmente as opções SET estão presentes deste outras versões, acredito que acabei me esquecendo deste detalhe, vou corrigir isso no artigo.

      Fico muito feliz com o seu feedback, estou sempre acompanhando o seu trabalho e dos demais amigos que trabalham com SQL Server, com certeza tenho muito para evoluir.

      Mais uma vez obrigado.

      Até mais.

      Curtir

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s