Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis


 

Olá comunidade, boa tarde.

Tudo bem? Estamos se aproximando dos últimos dias de férias ou recesso para grande maioria dos professores e profissionais de educação espalhados por todo Brasil. E ai, já esta preparado para voltar a luta? Posso dizer tranquilamente que sim, eu estou pronto para voltar a conviver com meus alunos e amigos de trabalho.

Antes de começarmos a falar sobre o post de hoje, nada mais justo começar agradecendo como de costume a você está aqui neste momento acessando meu blog. Espero que possa ter encontrado o que precisa, bem como, esteja gostando do conteúdo publicado, fique a vontade para entrar em contato expressando suas opiniões e demais pensamentos.

Neste novo post da sessão Dica do Mês, vou apresentar um recurso que imagino ser conhecido por muitos ou principalmente pelos profissionais de banco de dados, estou me referindo as Trace Flag ou sinalizador de rastreamento em português.

Você já ouvir falar sobre isso ou já utilizou? Eu imagino que sim pois aqui no meu blog diversos posts e artigos foram publicado ao longo dos últimos anos sobre este tipo de recurso. Hoje mais especificamente vou destacar o uso da Trace Flag 9292, por acaso você já utilizou em algum momento esta trace flag?

Bom independente da sua reposta vamos conhecer um pouco mais sobre ela, sua forma de uso e como poderá nos ajudar a entender ainda mais o funcionamento das estatísticas e seus chamados objetos úteis para análise do plano de execução.

E ai esta curioso para saber um pouco sobre este recurso? Eu estou, sendo assim, vamos em frente, seja bem vindo ao post Dica do Mês – Utilizando a Trace Flag 9292 para exibir objetos estatísticos úteis.

Vamos em frente…..


Introdução

Todos sabemos que as estatísticas desempenham um papel muito importante dentro do SQL Server, como também, sabemos que as estatísticas são usadas pelo otimizador de consultas para gerar o plano de execução para cada consulta.
Então a questão é, podemos verificar qual objeto estatístico é útil para a execução da consulta? Sim, usando o Trace Flag do SQL Server 9292. Este é um do sinalizador de rastreamento que pode ser usado durante a solução de problemas.
Esse sinalizador de rastreamento é usado para obter o relatório sobre objetos de estatísticas considerados como “interessantes” ou “úteis” pelo otimizador de consulta durante a compilação ou recompilação de consulta.

Adicionada ao Microsoft SQL Server 2008 após a instalação do service pack 1 e mantida até as atuais versões, no momento em tomamos a decisão de utilizar a Trace Flag 9292, orientamos o SQL Server a apresentar todos os objetos estatísticos considerados úteis por parte do plano de execução para realizar o processamento e retorno dos dados.

O uso da Trace Flag 9292 dentro de uma sessão ou query específica, nos ajuda a entender e conhecer como as estatísticas e seus elementos podem mudar totalmente a maneira que o plano de execução é idealizado, armazenado e processado.

Através dela podemos obter um relatório sobre as estatíticas para cada objeto envolvido em nossa query, onde estes supostos objetos devem ser considerados úteis, ou melhor dizendo válidos e aplicáveis no decorrer do caminho realizado até a apresentação do resultado.

Esta é uma trace flag que pode ser usada durante a resolução de problemas, onde sua função é apresentar na guia de mensagens do Management Studio, um pequeno cabeçalho contendo informações estatísticas sobre cada componente útil e válido para formas os dados estatísticos de processamento da query. Este cabeçalho é conhecido como Stats header loaded.

Para ativar a trace flag utilize o comando DBCC TraceON (9292) ou DBCC TraceOFF (9292) para desativar, ou se preferir utilize a opção QueryTraceOn em sua respectiva query.

Agora que já conhecemos um pouco mais sobre os conceitos desta trace flag, chegou a hora de colocar as mãos no teclado e conhecer melhor o funcionamento da flag 9292 e de que forma ela poderá impactar o trabalho do Microsoft SQL Server, nos permitindo identificar os objetos úteis processados ou candidatos a processamento por parte do Query Processor em conjunto com Execution Plan.

Preparando o ambiente

Nosso ambiente será bastante simples, basicamente criaremos um banco de dados denominado DBTrace9292, constituído de uma tabela denominada TBTrace9292, para tal utilizaremos o Bloco de Código 1 que apresenta a criação dos respectivos objetos:

— Bloco de Código 1 —
— Criando o Banco de Dados DBTrace9292 —
Create Database DBTrace9292
Go

— Acessando —
Use DBTrace9292
Go

— Criando a Tabela TBTrace9292 —
Create Table TBTrace9292
(Codigo Int Identity(1,1) Primary Key,
Valores Int,
Descricao Varchar(100))
Go

Após a criação dos objetos básicos, nosso próximo passo será a criação de índice nonclustered para coluna Valores que nos permitirá fazer o uso de estatísticas de processamento para esta coluna durante o processo de inserção de dados, conforme apresenta o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —
— Criando o Índice NonClustered IND_TBTrace9292Valores —
Create NonClustered Index IND_TBTrace9292Valores on TBTrace9292(Valores)
Go

— Inserindo uma linha de registro na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(2000,’pedrogalvaojunior.wordpress.com’)
Go

— Inserindo 1.000 linhas de registros na Tabela TBTrace9292 —
Insert Into TBTrace9292
Values(4000,’pedrogalvaojunior.wordpress.com’)
Go 1000

Note que existir uma pequena mais importante diferença entre os dois Inserts, no primeiro estamos inserindo uma linha de registro na tabela TBTrace9292. No segundo criamos em tempo de execução um pequeno bloco de inserção de linhas, sendo este processado 1.000 vezes, inserindo então 1.000 linhas.

Seguindo em frente, o Bloco de Código 3 será utilizado para criarmos uma Stored Procedure denominada P_PesquisarValores como forma para buscar os respectivos registros armazenados na tabela TBTrace9292. A seguir apresento o Bloco de Código 3:

— Bloco de Código 3 –
— Criando a Stored Procedure P_PesquisarValores —
Create Procedure P_PesquisarValores @Valor int
As
Begin
Select Descricao from TBTrace9292
Where Valores = @Valor
OPTION (RECOMPILE)
End
Go

Se você for como eu, normalmente gosto de fazer uma análise de código antes de colocar em prática no meu ambiente, sendo assim, vamos lá. Analisando de forma superficial a Stored Procedure P_PesquisarValores a princípio não apresenta nada muito especial ou de grande complexidade em seu código, mas sim o uso de opção Recompile que justamente vai orientar o plano de execução a recompilar a  P_PesquisarValores no momento da sua execução, forçando assim que a cada execução um novo plano de execução seja criado em conjunto com uma nova análise estatística e seus demais elementos.

O próximo passo consiste na pesquisa de um dos valores armazenados na tabela TBTrace9292 através da execução e processamento da Stored Procedure P_PesquisarValores. Para este passo vamos utilizar o Bloco de Código 4 a seguir, antes de sua execução recomendo habilitar a apresentação do Plano de Execução Atual no SQL Server Management Studio através do botão Include Actual Execution Plan ou simplesmente através da tecla de atalho CTRL+M.

— Bloco de Código 4 —
— Habilitando as TraceFlags 9292 e 3604 —
DBCC TraceOn(9292,3604,-1)
Go

Dica: Utilize o comando DBCC TraceStatus WITH NO_INFOMSGS para verificar quais Trace Flags estão habilitadas em qual nível de escopo.

— Execuntando a Stored Procedure P_PesquisarValores —
Exec P_PesquisarValores 4000
Go

Pois bem, após a execução do Bloco de Código 4, o Microsoft SQL Server realizou o processamento da nossa Stored Procedure P_PesquisarValores realizando uma busca de todas as linhas de registros que possuem o valor 4.000, onde obrigatoriamente foram retornadas 1.000 linhas de registros.

Até ai nada de novo ou surpreende, o que justamente eu quero mostrar para vocês é o que o Management Studio apresenta na guia Messages após o processamento do Bloco de Código 4, conforme apresenta a Figura 1 abaixo:

Note que o cabeçalho retornado pela Trace Flag 9292 conhecido como Stats header loaded esta apresentando os objetos realmente utilizados para o processamento de nossa query, bem como, os objetos considerados úteis e necessários para criação, compilação e processamento do plano de execução envolvidos na execução, sendo eles:

  • Database: DBTrace9292;
  • Table: TBTrace9292,
  • Index: IND_TBTrace9292Valores, sendo este do tipo Nonclustered;
  • Column: Valores; e
  • EmptyTable: False, representa que a tabela possui linhas de registro.

Perfeito, perfeito, ai esta a prova que a Trace Flag 9292 nos permite identificar de forma simples, coerente e muito intuitiva todos os objetos envolvidos na execução de uma query, stored procedure ou demais elementos que permitem a criação de um plano de execução.

Desta forma, chegamos ao final de mais um post, tendo a sensação de dever cumprido, espero que você tenha gostado, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.


Referências

https://thomaslarock.com/2016/06/sql-server-Trace-flags/

https://pedrogalvaojunior.wordpress.com/2016/02/26/02-para-que-serve/

DBCC TRACEOFF (Transact-SQL)

DBCC TRACEON (Transact-SQL)

DBCC TRACESTATUS (Transact-SQL)

EXECUTE (Transact-SQL)

Query Hints (Transact-SQL)

Post Anteriores

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Muitas vezes temos dificuldade em entender como um determinado recurso ou funcionalidade esta sendo utilizada pelo Microsoft SQL Server, com também, de que maneira este elemento poderá impactar nosso ambiente.

Neste post foi possível apresentar como a Trace Flag 9292 nos permite identificar quais objetos estão sendo utilizando durante o processamento e execução de uma determinada query. Um recurso de fácil configuração tanto para ser ativado como também desativado a qualquer momento ou necessidade.

Recomendo que você realize diversos testes e validações antes de fazer qualquer tipo de uso de uma trace flag em seu ambiente de produção, isso também se aplica a Trace Flag 9292.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos em breve com mais um post da sessão Dica do Mês.

Valeu….

Conhecendo o Live Query Statistics no Microsoft SQL Server 2016


Salve, salve galera, bom dia….. Ufa, sexta – feira!!!!

Para terminar mais uma semana de muito trabalho, gostaria de compartilhar com vocês mais uma novidade que o Microsoft SQL Server 2016 esta nos apresentando, conhecida como Live Query Statistics.

Quando eu ouvi ao sobre esta funcionalidade sinceramente não acreditei, mas é verdade, alias uma grande realidade, conseguir em tempo real de processamento de uma query acompanhar todo seu processamento, entendendo realmente como cada operador é sensibilizado pelo Query Processor durante seu trabalho, e como cada operador interage com os demais.

Tenho a certeza que esta nova funcionalidade vai ajudar em muito a todos os profissionais que de alguma forma utilizam o Microsoft SQL Server em sua atividades.

Espero que você goste deste artigo, vamos em frente.

Introdução

O time de engenheiros e desenvolvedores da Microsoft a cada nova versão vem trazendo novos recursos, funcionalidades e comandos que possibilitam a qualquer profissional da área de Banco de Dados, conseguir acompanhar e entender como o Microsoft SQL Server através do Query Processor e Query Optmizer trabalha, algo que foi evoluíndo desde a versão 2008 com a mudanças de alguns operadores como o antigo Bookmark Lookup que posteriormente veio a ser chamado de Key Lookup, além disso, a introdução do Missing Index dentro do Execution Plan também foi considerado por todos em meados de 2008 um elemente fundamental para se obter ganhos de performance no processamento de nossas querys.

Na versão 2014 a Microsoft deu um grande salto a possibilitar o monitorando o progresso de execução de querys em tempo real através da DMV sys.dm_exec_query_profiles disponível em todas as edições desta versão, inclusive você vai poder encontrar aqui no meu blog, um artigo dedicado exclusivamente a esta funcionalidade, acessando: https://pedrogalvaojunior.wordpress.com/2015/08/12/monitorando-o-progresso-de-execucao-de-querys-em-tempo-real-no-microsoft-sql-server-2014/

Agora na versão 2016 no meu entendimento a Microsoft conseguiu realmente trazer o que estava faltando, permitir ao SQL Server ter a capacidade técnica de apresentar em real-time, isso mesmo, em tempo real todo processo de execução de nossas querys, apresentando de forma visual, gráfica e muito inteligente o Execution Plan estabelecido com seus operadores sendo processados, e não somente mostrar, mas sem necessitar de qualquer ferramenta adicional proporcional dentro do Management Studio clicar em um simples botão e habilitar esta funcionalidade.

As dificuldades do dia a dia

Acredito que uma das maiores dificuldades de qualquer administrador de banco de dados, analista de sistema, desenvolvedores, enfim profissionais que de alguma forma acabam tendo a necessidade de trabalhar com SGBD (Sistemas Gerenciadores de Banco de Dados) é encontram em qual parte do seu ambiente podem estar ocorrendo á chamada “lentidão”, elemente presente dentre de qualquer infraestrutura de tecnologia que deixa todos os envolvidos diretamente ou indiretamente muitas vezes sem respostas.

Com o Live Query Statistics teremos a capacidade de conseguir encontrar de uma forma mais fácil, rápida e prática em qual parte da uma determinada query esta temida “lentidão” pode estar ocorrendo ou posso proporcionar em seguida algum tipo de mudança de comportamento.

Sobre o Live Query Statistics

Introduzido a partir do CTP 2.1 do Microsoft SQL Server 2016, o Live Query Statistics tem como principal objetivo apresentar o plano de execução ao vivo, exibindo:

  1. O progresso de processamento de uma query;
  2. As Estatísticas de tempo de execução de cada operador;
  3. O Tempo de execução geral do processamento da query;
  4. O Número de linhas processadas;
  5. A Sequência de processamento de cada operador;
  6. A interação entre os operadores;
  7. A ordem de processamento de cada operador até o final de execução da query; e
  8. A porcentagem de processamento de cada operador.

Talvez a colocação que eu venha a fazer agora possa ser um pouco exagerada, mas eu acredito que esta feature nos possibilita acompanhar um apresentação ao vivo de todo o trabalho realizado pelo SQL Server, cheguei até pensar que isso poder ser considerado uma “Vídeo aula”, espero não estar exagerando, mas a maneira que é demonstrado a evolução de execução da nossa query nos faz pensar e até mesmo imaginar que estamos assistindo um pequeno vídeo ou webcast.

Outra consideração que podemos fazer em relação ao Live Query Statistics, é a capacidade de permitir uma análise na linear e precisa de cada parte do processament realizado por uma query, bem como, o seu próprio debug se torma mais flexível e inteligente.

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

Para tentar demonstrar como podemos utilizar esta nova funcionalidade, vamos utilizar o mesmo ambiente criado no artigo: https://pedrogalvaojunior.wordpress.com/2015/08/12/monitorando-o-progresso-de-execucao-de-querys-em-tempo-real-no-microsoft-sql-server-2014/. Trabalhando como o Database: DBMonitor e a Table: BigTable.

Em nosso ambiente de teste, estou utilizando a última versão do Management Studio 2015 chamada de Preview September 2015, que você poderá baixar acessando: http://blogs.msdn.com/b/sqlagent/archive/2015/09/30/sql-server-management-studio-september-2015-preview.aspx ou https://msdn.microsoft.com/en-us/library/mt238290.aspx.

Vale ressaltar que este último preview corresponde a versão CTP 2.4 do Microsoft SQL Server 2016, caso você queira saber mais sobre esta versão preview acesse: http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Para começar a brincadeira, vou utilizar o select apresentando abaixo denominado Código 1:

— Código 1 – Consultando os dados da Tabela BigTable —

Use DBMonitor

Go

 

Select Top 100000 OrderID,

CustomerID,

Format(OrderDate, ‘dd/mm/yyyy – hh:mm’) As ‘Date’,

Round(Value,2) As ‘Value’,

CONCAT(‘Teste de execução do Live Query Statistics – ‘,GetDate()) As ‘Mensagem’

From BigTable

Order By CustomerID Desc

Go

 

Analisando de forma superficial o Código 1, você pode observar que estou fazendo uso do comando Top para retornar 100.000(Cem mil) linhas de registros existentes dentro da tabela BigTable, além disos, estou fazendo uso das funções Format e Concat, ambas funções string introduzida no Microsoft SQL Server 2012, que nos permite realizar a formatação de valores e concatenação de textos respectivamente. Até aqui tranquilo, nada de diferente, podemos continuar, nos preparando para execução deste Select, onde antes de clicar no botão Execute ou teclar F5, vamos neste momento habilitar o Live Query Statistics, localizando este botão na Toolbar – SQL Editor existente em nosso Management Studio, conforme apresenta a Figura 1, a seguir:

lqs
Figura 1 – Toolbar – SQL Editor.

Observe que logo após os botão Include Atual Execution Plan, foi adicionado um novo botão, chamado Live Query Statistics, conforme apresenta a Figura 2 abaixo:

lqs1

Figura 2 – Botão – Live Query Statistics.

Agora, basta você clicar neste botão para que o Management Studio realiza a ativação do mesmo e permita sua apresentação durante a execução da nossa query, sendo assim, clique no botão em seguida selecione o nosso bloco de código Select e tecle F5 ou Execute.

 

A partir do momento que o SQL Server começa a executar nossa query, o Management Studio adiciona em sua guia de resultados e mensagens uma nova Guia rotulada: Live Query Statistics, conforme apresenta a Figura 3 a seguir:

lqs2

Figura 3 – Guia – Live Query Statistics apresentando durante o processamento do Código 1.

Se tudo correu bem esta guia esta sendo apresentada neste momento no Management Studio e você vai poder acompanhar em real time, todo fluxo de processamento do nosso select, observe que as linhas que interligam cada operador estão neste momento desenhadas de forma pontilhada, indicado que esta ocorrendo um troca de dados, onde estas linhas e posteriormente as respectivas setas serão preenchidas completamente após a conclusão da execução da nossa query ou de acordo com o termino de processamento de cada operador.

Para ilustrar melhor a execução do Live Query Statistics disponibilizei um vídeo que poderá nos ajudar na compreensão:

Outra maneira de habilitar e acompanhar todo trabalho realizado LQS, pode ser feito através da ferramenta Activity Monitor, onde será apresentada a opção Show Live Execution Plan, dentro da guia Active Expensive Queries conforme apresenta a Figura 4 na sequência:

lqs4

Figura 4 – Activity Monitor – Opção Show Live Execution Plan.

Considerações

Como tudo no mundo nada é 100% perfeito, maravilhoso e principalmente 100% utilizável, isso também se aplica para o Live Query Statistics que até o presente momento possui algumas limitações, sendo elas:

  • Não possui suporte para ColumnStoreIndex;
  • Tabelas do tipo Memory-Optimized não são suportadas; e
  • Compilação de Stored Procedures nativas do SQL Server também não são suportadas.

Outro detalhe muito importante, esta relacionado a saúde das estatísticas existentes em nosso banco de dados, sejam elas estatísticas criadas automaticamente para nossos tabelas e índices, como também, estatísticas internas, caso estes componentes não mantenham-se atualizados o Live Query Statistics também será impactado da mesma forma que o Query Processor e Query Optimizer na identifação e processamento de uma query. Para evitar este tipo de cenário, torna-se recomendável fazer uso do comando Update Statistics ou da System Stored Procedure SP_UpdateStats. Caso você deseja saber mais sobre estas funcionalidades acesse:

Conclusão

Com certeza, o Live Query Statistics nova feature adicionada o novo Microsoft SQL Server 2016, vai proporcionar uma grande revolução no trabalho dos profissionais que trabalham com banco de dados. Esta capacidade de conseguir em tempo real acompanhar todo processamento realizado pelo SQL Server, torna este recurso um ferramenta indispensável na identificação de possível problemas de performance que podem estar relacionados ao SQL Server.

Observar, acompanhar, analisar e compreender o fluxo de processamento realizado pelo Query Processor através de um plano de execução ao vivo, obtendo dados estatísticos processamentos naquele momento trazem uma nova visão aos Administradores de Banco de Dados, onde estes profissionais terão maior precisão e argumentos mais concretos na tomada de decisão de uma possível mudança de arquitetura e infraestrutura.

Espero que você tenha gostado deste artigo, que as informações compartilhadas aqui possam lhe ajudar.

Mais uma vez obrigado.

Até a próxima.