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….

#12 – Para que serve


Boa tarde, boa tarde…. Olá pessoal, tudo bem?

Mais uma semana começando, para alguns volta as aulas (kkkkk)…. é a mamata esta acabando e o futuro deste país tem que voltar para sua realidade, no mundo capitalista que estamos vivendo, sem o mínimo de educação civica e moral não somos nada.

Deixando de lado este pequeno pensamento, seguindo em frente este é o post de número 12 dedicado exclusivamente a sessão Para que serve, que lentamente esta atraindo novos seguidores ao meu Blog.

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar ou demonstrar como  códigos de exemplo, aplicativos, utilitários, enfim recursos relacionados diretamente á banco de dados ou gerenciadores de bancos de dados podem ser utilizados como uma possível solução de problemas, bem como, orientar na sua forma de utilização.

Após esta tradicional saudação, chegou a hora de falar sobre o #12 – Para que serve de hoje, tenho a certeza que você vai gostar.

No post de hoje, vou a destacar uma alteração que a Microsoft introduziu no novo SQL Server 2016, que a partir desta versão alterar de maneira direta o comportamento padrão existente atualmente para alocação de dados e autocrescimento para os bancos de dados de usuário ou para o system database TEMPDB.

Em contra partida, neste post vou destacar um pouco sobre a relação das Trace Flag 1117 e 1118 para com estes dois recursos que compõem o SQL Server, sabendo que durante anos ambas foram recomendadas pelas equipes de engenheiros da Microsoft como técnicas para alterar este comportamento padrão, que a partir da versão 2016 poderá ser realizado de uma maneira bem diferente ou até mesmo de forma automática.

Vamos lá….começa aqui o #12 – Para que serve – Alterando o comportamento padrão para alocação de dados e autocrescimento no Microsoft SQL Server 2016 –

Introdução

Até a versão 2014 o Microsoft SQL Server apresentava o mesmo padrão definido desde a versão 2000 para alocação de dados e autocrescimento de banco de dados, comportamento que poderia ser alterado através do uso de recursos externos entre deles as tão conhecidas e temidas trace flags.

Para um melhor entendimento, vou abordar brevemente os dois conceitos, visando esclarecer um pouco o papel de cada um deles, começando por:

Alocação de Dados: Quando se referimos a alocação de dados em uma instância ou servidor SQL Server, estamos fazendo referência a dois recursos de extrema importância que forma o SQLOS, me refiro ao Database Engine e Storage Engine, sendo estes responsáveis em possibilitar o armazenamento, contenção e consumo de dados manipulados pelo SQL Server.

Como destacado anteriormente o Microsoft SQL Server até a versão 2014 não apresentava a capacidade de criar páginas de dados iniciais ou as primeiras oito páginas de dados conhecidas como extended (extensão) identificadas internamente como páginas ou extensões mista, no qual as primeiras páginas ou extended deveriam se iguais, uniforme, do mesmo tipo e apresentar a mesma estrutura contendo somente informações relacionadas a tabelas ou índices.

Você pode estar se perguntando, mas isso não era possível de ter alterado nas versões mais antigas? A resposta simples e direta é SIM, e para tal finadade eramos obrigados a utilizar a Trace Flags 1118 (se quiser saber mais sobre ela acesse: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)

Mas isso na versão 2016 não é mais necessário, para oferecer e permitir esta mudança de comportamento o time de engenheiros da Microsoft dedicados ao SQL Server aplicaram uma pequena mudança na estrutura da Dynamic Management View: sys.databases existente desde as primeiras versões do produto, na qual foi adicionada uma nova coluna chamada is_mixed_page_allocation_on, que pode ser utilizada através do comando ALTER DATABASE. Falarei um pouco mais sobre esta nova coluna posteriormente.

Dando continuidade, vamos conhecer um pouco sobre AutoGrow (Autocrescimento):

Autocrescimento: Opção aplicada aos bancos de dados que define qual deverá ser a fator e forma de crescimento de um banco de dados, também sofreu algumas mudanças.

A partir do SQL Server 2016 todo processo de autocrescimento e alocação de dados será realizado de forma automática, no qual o Database Engine e parceria com o Storage Engine serão autosuficientes capazes de identificar a necessidade de mudar a forma de alocação e autocrescimento do banco de dados, sem recorrer a necessidade de fazer uso da trace flag 1117.

Desta forma, de acordo com a distribuição dos dados alocados em seus respectivos arquivos de dados ou filegroups permitirá que quando um arquivo de dados crescer todos os demais arquivos relacionados ao banco de dados ou filegroup deverão crescer ao mesmo tempo, sendo este o novo comportamento adotado para este banco de dados, algo revolucionará se levarmos em consideração do processo desempenho pelo Storage Engine para alocar e contar os dados.

Esta mudança de comportamento pode ser considerada uma peça chave para o SQL Server no que se relaciona a performance, pois de maneira simultânea teremos arquivos alocados ao mesmo tempo na mesma transação, oferecendo uma redução no tempo estimado para contenção de alocação de dados, o que no final das contas provacará uma sensível diminuição para o Storage Engine controlar o número de pontos de marcação de dados relacionado ao que está alocado para uso.

MIXED_PAGE_ALLOCATION

Nova opção adicionada ao comando ALTER DATABASE que permite aplicar aos bancos de dados de usuário e system database TEMPDB a nova forma de alocação de dados adotada para a versão 2016 do SQL Server, denominada Mixed Page Allocation ou Alocação de Páginas Mistas, na qual destacado anteriormente será possível alocar para toda estrutura de um banco de dados o uso de páginas ou extended mistas, aplicada de implícita para tabelas e índices.

A coluna is_mixed_page_allocation_on apresenta dois valores, sendo eles:

  • 0 = A estrutura de tabelas e índices será alocada de forma uniforme e não permitirá que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.
  • 1 = A estrutura de tabelas e índices poderá ser alocada de forma mista permitindo que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.

Vale ressaltar que ao realizar uma simples consulta na DMV sys.databases, valor padrão apresentado na coluna is_mixed_page_allocation_on para os bancos de dados de usuário é 0(zero), e para os bancos de dados de sistema: Master, Model e MSDB é 1(Hum) sendo que para este bancos de dados não é permitido alterar a forma de alocação.

Perguntas e respostas

Muito bem, você pode estar coçando a sua cabeça e ainda contendo algumas dúvidas sobre ese possível novo comportamento entre outros conceitos aqui apresentados, no intuito de tentar ajudar, elaborei algumas perguntas:

1. Afinal esta nova opção Mixed_Page_Allocations possui alguma relação com as trace flags 1117 e 1118?
Respondendo de bate pronto: SIM possuem total relação.

2. Esta nova opção substituio uso de ambas as traces flags?
Sim, tem este finalidade mas aplicada somente a partir da versão 2016.

3. Após alterar a forma de alocação para Mixed Page Allocation posso voltar ao formato anterior?
Sim, sem nenhum tipo de risco ou impedimento.

Além das questões a Tabela 1 apresentada abaixo poderá lhe ajudar a entender em qual cenário você poderá fazer uso da mixed_page_allocation ou das trace flags 1117 e 1118:

Database TF 1117 TF 1118
tempdb Não requerida (default) Não requerida (default)
user databases Por padrão será realizada o autocrescimento de forma simples, ou seja, de um único arquivo por vez. Use ALTER DATABASE <dbname> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES para habilitar o crescimento para todos os arquivos de forma simultânea Não requerida. Use ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION  ON para voltar a utilizar alocação mista.
Other system databases (master, model, msdb) -NA- Alocação de páginas em modo mista não pode ser alterada para estes bancos de dados.

Tabela 1 – Cenários para uso da alocação mista ou mudança no autocrescimento.

Referências

https://technet.microsoft.com/pt-br/library/ms190969(v=sql.105).aspx

https://msdn.microsoft.com/en-US/library/bb522682.aspx

https://support.microsoft.com/en-us/kb/2964518

https://msdn.microsoft.com/en-us/library/ms178534.aspx

https://msdn.microsoft.com/en-us/library/bb522469.aspx

https://msdn.microsoft.com/en-us/library/ms187782.aspx

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2016/12/16/11-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/11/15/10-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/10/08/09-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Cuidar da vida de nossos dados é algo muito importante, mas saber como e de que forma estes podem ser armazenados esta bem acima de qualquer outra preocupação, pensando nisso a Microsoft permitiu a partir da versão 2016 alterar de forma simples, rápida e segura a maneira com nossos bancos de dados podem crescer no decorrer do tempo, bem como, as estruturas internas podem ser criadas e alocadas, capacidade que nos permite melhrorar de maneira sensível atividades relacionadas a como nossos dados podem estar alocados para consulta, possibilitando ganhos de processamento de dados.
Neste post você pode mais uma vez observar que o Microsoft SQL Server esta em constante evolução, um dos produtos mais prestigiados pela Microsoft, buscando sempre trazer melhorais e inovações, algo de extrema importância para qualquer profissional que trabalha com esta tecnologia.

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.

Nos encontramos em breve, até lá…..

Material de Apoio – Junho – 2013


Bom dia, bom dia, Sexta – Feira.

Ufa graças a deus mais uma semana chegando ao seu final!!!

Pois bem, mantendo a escrita, vou compartilhar com vocês mais uma relação de Scripts e Códigos de Exemplo que podem servir como Material de Apoio.

Na relação de hoje, vocês vão poder encontrar códigos e exemplos relacionados com os seguintes conteúdos:

  • Auditoria em tempo real;
  • Backup Compression;
  • Criação de Índices Primary e Secundary XML;
  • DMV – Sys.dm_os_buffer_descriptors+sys.dm_os_performance_counters.sql;
  • Formatação de CNPJ e CPF;
  • Função – Fn_Trace_GetInfo;
  • Função – Fn_Trace_GetFilterInfor;
  • Memória consumido por Banco de Dados e Objetos;
  • Refazendo numeração do Identity;
  • Trace Flag 3042;
  • XP_CMDShell com parâmetro de saída, etc…

Segue abaixo a relação de arquivos:

  1. Exemplo – Backup Compression – Utilizando Trace Flag 3042.sql

  2. Exemplo – Básico – Refazendo Numeração Identity.sql

  3. Exemplo – Básico – Repetindo Valores – Função Replicate.sql

  4. Exemplo – Coletar Estatísticas de IO – Nível de Arquivos – fn_virtualfilestats.sql

  5. Exemplo – Criando e Utilizando Primary Index XML.sql

  6. Exemplo – Criando e Utilizando Secundary Index XML.sql

  7. Exemplo – Determinando o uso de Memória por Banco de Dados e Objetos – Sys.dm_os_buffer_descriptors+sys.dm_os_performance_counters.sql

  8. Exemplo – Função Replace – Removendo – Formatação – CNPJ e CPF.sql

  9. Exemplo – Procedure – IP – Utilizando XP_CmdShell e Parâmetro de Saída.sql

  10. Exemplo – Realizando Auditoria – In Live.sql

  11. Exemplo – SysJobSteps x SysJobs – Encontrando – Comando executado pelo Job.sql

  12. Exemplo – Utilizando – Funções – fn_trace_getinto + fn_trace_getfilterinfo.sql

Não se esqueça que todos estão renomeados com a extensão .doc para questões de compatibilidade com o WordPress.com, após o download, basta remover a extensão, além disso, os códigos estão compatíveis com as versões 2005, 2008, 2008 R2 e 2012.

Mais uma vez obrigado por sua visita, fique a vontade para compartilhar com seus contatos e seguidores.

Nos encontramos em breve.

Até mais.