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

Microsoft anuncia o SQL Server 2012 Service Pack 4


A Microsoft anunciou no blog SQL Server Release Services que está planejando lançar o SQL Server 2012 Service Pack 4 ainda este ano.

O Service Pack 3 (ou SP3) foi lançado em novembro de 2015 e trouxe novidades como:

• Aprimoramentos de escalabilidade e desempenho para o mecanismo de armazenamento.

• Melhorias no desempenho da verificação de consistência.

• Dicas de consulta para fornecer controle granular enquanto estiver usando o Resource Governor.

• Adicionais recursos por meio de melhorias na DMV, Extended Events e planos de consulta de monitoramento.

• Melhorias no desempenho ao abrir e ler arquivos XEL usando leitor XEvent Linq.

Microsoft anuncia o SQL Server 2012 Service Pack 4

SQL Server 2012 Service Pack 4

De acordo com a Microsoft, o SQL Server 2012 Service Pack 4 será o último Service Pack para a versão 2012 do SQL Server e além de ser uma coleção de hotfixes, ele também trará mais de 20 melhorias com foco na performance, escalabilidade e diagnóstico com base no feedback de clientes e da comunidade.

Mais especificamente, o Service Pack 4 incluirá:

– Todas as correções e atualizações cumulativas para o SQL Server 2012.

– Melhorias na performance e escalabilidade.

– Recursos de monitoramento adicionais através de melhorias no DMV, Extended Events e Query Plans.

– Melhorias com base no feedback da comunidade.

– Melhorias introduzidas originalmente pelo SQL Server 2014 SP2 e SQL Server 2016 SP1.

A Microsoft confirmou que o SQL Server 2012 Service Pack 4 será lançado em setembro deste ano e um anúncio com mais informações sobre a atualização será publicado na época do lançamento.

Fontes e Direitos Autorais: SQL Server Release Services – SourabhAgarwal https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/

Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais


Muito boa tarde pessoal, salve, salve comunidade e amantes de banco de dados.

Tudo bem com vocês? Estou aqui mais uma vez em um novo post do meu blog na sessão Dica do Mês, hoje falando de um assunto que até alguns dias atrás eu sinceramente nunca havia feito uso, mas com base em um post publicado do Ahmad Yaseen no MSSQLTips.com, acabou me servindo como fonte de inspiração para elaborar e compartilhar este post com vocês.

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.

Dando continuidade, vamos conhecer um recurso adicionado na versão 2014 do Microsoft SQL Server a partir do Service Pack 2 e mantido no Microsoft SQL Server 2016 SP1 conhecido como Estatísticas Incrementais ou Incremental Statistics, pode parecer estranho o nome, mas é exatamente isso que este recurso permite, realizar o processo de atualização de estatísticas de maneira incremental, ou para muitos incrementar o processo de atualização de estatísticas aplicadas aos nossos bancos de dados e seus respectivos objetos.

Parece ser coisa de louco isso, mas posso garantir que não é, absolutamente é algo totalmente viável e aplicável a qualquer ambiente que se faça uso do Microsoft SQL Server em conjunto com as funções e scheme de particionamento de dados.

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 – Conhecendo e aplicando o uso de atualização de estatísticas incrementais.

Seguindo….


Introdução

O otimizador de consultas do Microsoft SQL Server depende fortemente das estatísticas na geração a execução de plano de consulta mais eficiente. Estas estatísticas fornecem ao otimizador a distribuição dos valores de colunas na tabela e o número de linhas, também chamada a cardinalidade que resultará da consulta.

A ausência destas estatísticas, ou a existência de estatísticas desatualizadas, proporciona a ocorrência de querys consideradas lentas, neste sentido, o otimizador de consulta “query optimizer” acaba sendo obrigado a utilizar estatísticas imprecisas para criar o plano de execução, que pode ser considerado um plano não ideal para executar a consulta neste caso.

O SQL Server geralmente faz o seu trabalho em manter estas estatísticas atualizadas, mas como um administrador de banco de dados, você deve fazer seu trabalho, em alguns casos, atualizando as estatísticas manualmente. Atualizar estatísticas manualmente em tabelas grandes pode ser como um grande desafio, bem como, em tabelas pequenas pode-se imaginar que a estatística já esteja atualizada, o que em alguns cenários isso acaba não ocorrendo.

Um dos cenários mais impactados pelo uso de estatísticas desatualizadas ou atualizadas parcialmente são as tabelas particionadas. Como destacado anteriormente através do uso das funções de particionamento de dados introduzido no Microsoft SQL Server 2008, temos a capacidade de distribuir nossos dados em partições “pequenos fatias de armazenamento de dados” que nos possibilitar distribuir respectivos valores com base em uma função que análise e identifica o local de armazenamento do mesmo.

Para este tipo de ambiente, o uso de estatísticas como mecanismo para auxiliar no obtenção mais rápida do dado, pode apresentar simultaneamente o papel de herói como também de vilão, isso pode parecer meio confusão, mas não é! Basicamente quando trabalhamos com estatísticas acreditamos que sempre teremos todas as informações armazenados no histograma atualizadas de forma automática de maneira mais precisa possível, algo que não acontece exatamente desta maneira quando trabalhando com particionamento de dados.

Uma das situações mais comuns quando se uso particionamento de dados é a possibilidade de ocorrer a atualização de estatísticas de maneira parcial, ou seja, apena um partição de todo estrutura de partições acaba tendo suas informações de estatísticas atualizadas, o que poderá provocar uma alteração no plano de execução ou a possibilidade de criação de um plano incoerente.

Sabendo desta possibilidade e comportamento, o time de engenheiros e desenvolvedores do Microsoft SQL Server, implementou a partir da versão 2014 SP1 as Estatísticas Incrementais, funcionalidade que nos permite justamente contornar este tipo de situação.

Estatísticas Incrementais – Incremental Statistics

As estatísticas Incrementais, ajudam na atualização de estatísticas para apenas a partição ou partições que você escolher. Em vez de analisar e varrer a tabela inteira para atualizar as estatísticas, a partição selecionada será verificada somente para a atualização, reduzindo o tempo necessário para executar a operação de atualização de estatísticas, atualizando-se apenas a partição modificada.

O outro ponto importante é que a porcentagem de alterações de dados necessário para acionar a atualização automática de estatísticas, sendo este o valor 20% de linhas alteradas, o que proporcionará o uso de atualização de estatísticas no nível da partição, comportamento que não era permitido anteriormente.

Muito legal este novo recurso e principalmente o comportamento do Microsoft SQL Server, agora que já conhecemos conceitualmente como as estatísticas incrementais funcionam, chegou a hora de colocar as mãos no teclado e começar a conhecer de maneira prática esta funcionalidade.

Preparando o ambiente

Para entender a atualizar as estatísticas incrementais, vamos preparar um banco de dados de teste com uma tabela particionada. Começamos com a criação de um novo banco de dados denominado IncrementalStatistics, formado por quatro novos grupos de arquivos além de grupo de arquivos primário padrão, para tal vamos utilizar o Bloco de Código 1 apresentado a seguir:

— Bloco de Código 1 —

— Criando o Banco de Dados IncrementalStatistics —
Create Database IncrementalStatistics
Go
— Adicionando os Filegroups —
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo1
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo2
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo3
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo4
Go

 

— Adicionando os Arquivos aos seus respectivos Filegroups —

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo1′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo1-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo1
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo2′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo2-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo2
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo3′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo3-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo3
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo4′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo4-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo4
Go

Uma vez que o banco de dados é criado com os novos grupos de arquivos e arquivos de dados, precisamos prepará-lo para hospedar a tabela particionada. Nosso próximo passo consiste na criação da função particionada PartitionFunctionIncrementalStatistics que classifica os dados de acordo com os quatro trimestres do ano, sendo assim, vamos utilizar o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

— Criando a Partition Function PartitionFunctionIncrementalStatistics —
USE IncrementalStatistics
GO

CREATE PARTITION FUNCTION PartitionFunctionIncrementalStatistics (Int)
AS
RANGE RIGHT FOR VALUES
(20171, 20172, 20173, 20174)
Go

Note que nossa PartitionFunctionIncrementalStatistics é composta por quatro partições de valores subdivididos da seguinte forma: 

  • 20171 – Valor que representa o Primeiro Quartil;
  • 20172 – Valor que representa o Segundo Quartil;
  • 20173 – Valor que representa o Terceiro Quartil do Ano; e
  • 20174 – Valor que representa o Quarto Quartil do Ano.

    Talvez você ainda não esteja entendendo o porque estamos fazendo uso deste tipo de implementação, tenha calma no decorrer do post tudo vai ficar mais claro e você terá total noção do porquê estamos utilizando este recurso.

Continuando com a nossa longa caminhada, você deve saber que para se trabalhar com particionamento de dados devemos além de criar uma Partition Function devemos obrigatoriamente criar um Partition Scheme,  que estará vinculado lógicamente a nossa partition function, sendo assim, este é nosso próximo passo, fazendo uso do Bloco de Código 3:

— Bloco de Código 3 —

— Criando o Partition Scheme PartitionSchemeIncrementalStatistics —
CREATE PARTITION SCHEME PartitionSchemeIncrementalStatistics AS
PARTITION PartitionFunctionIncrementalStatistics
TO
(
IncrementalStatisticsGrupo1,
IncrementalStatisticsGrupo2,
IncrementalStatisticsGrupo3,
IncrementalStatisticsGrupo4,
[PRIMARY])
Go

Esta quase tudo pronto para nossa brincadeira, seguiremos com a criação na nossa tabela TableIncrementalStatistics, este é um ponto importante do nosso ambiente, onde estamos fazendo uso da tabela particionada para ilustrar como as estatísticas incrementais vão realizar o seu papel.

TableIncrementalStatistics será composta por algumas colunas, dentre as quais a coluna Quartil, responsável em armazenar o valor do quartil de acordo com o ano informado, como também, é através desta coluna que estaremos realizando o particionamento dos dados. Para isso utilizaremos o Bloco de Código 4 a seguir:

— Bloco de Código 4 —

— Criando a Tabela TableIncrementalStatistics —

CREATE TABLE TableIncrementalStatistics
(ID Int Null,
Acao NVarchar(40) Default NewID(),
Data DateTime Null,
Quartil  AS (datepart(year,[Data])*(10)+datepart(quarter,[Data])) PERSISTED
) ON PartitionSchemeIncrementalStatistics (Quartil)
Go

Ótimo, toda estrutura para armazenar nossos dados já esta pronta, bem como, a lógica para distribuir e particionar os dados que serão inseridos na tabela TableIncrementalStatistics.

Ufa, ainda temos um bom caminho pela frente, mas já avançamos bastante, agora temos realizar uma alteração nas configurações do nosso banco de dados IncrementalStatistics, sendo esta necessária para podermos aplicara o uso de estatísticas incrementais, estou me referindo a opção Auto Create Statistics muito conhecida, onde vamos alterar o seu valor default para Incremental = On, conforme apresenta o Bloco de Código 5 abaixo:

— Bloco de Código 5 —

— Habilitando o uso de Incremental Statistics —
Alter Database IncrementalStatistics
Set Auto_Create_Statistics On (INCREMENTAL = On)
Go

O próximo passo consiste na criação do índice que iremos utilizar em na TableIncrementalStatistics pois você deve ter notado que realizamos a criação da tabela sem a definição de uma chave primária, desta maneira  utilizamos o Bloco de Código 6 para criação dos respectivo índice em seguida confirmamos se esta tabela esta fazendo uso das estatísticas incrementais habilitada no bloco de código 5:

— Bloco de Código 6 —

— Criação do índice Clustered —
Create Clustered Index Ind_TableIncrementalStatistics_ID
On [TableIncrementalStatistics] (ID)
GO

— Confirmando se as estatísticas incrementais está habilita —
SELECT
OBJECT_NAME(object_id) TableName
,name
,is_incremental
,stats_id
FROM sys.stats
WHERE name = ‘Ind_TableIncrementalStatistics_ID’
Go

Figura 1 – Confirmando o uso das estatísticas incrementais no índice Ind_TableIncrementalStatistics_ID.

Observação: Note que ao executar o Select realizado na visão de sistema sys.stats a coluna Is_Incremental deverá retornar e apresentar o valor igual á 1, isso indica que TableIncrementalStatistics esta neste momento fazendo uso das estatísticas incrementais.

Muito bem, chegou a hora de popular nossas tabelas, realizaremos a inserção de 8.000 linhas de registros, sendo estes particionados em grupos de 2.000 registros para cada partição que forma e compõem a estrutura da nossa tabela. Vamos então utilizar o Bloco de Código 7 apresentado na sequência:

— Bloco de Código 7 —

— Inserindo os dados na TableIncrementalStatistics —
Insert Into TableIncrementalStatistics (ID, Data)
Values (1, ‘2017-11-22’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (2, ‘2017-06-05’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (3, ‘2017-01-25’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (4, ‘2017-08-13’)
Go 2000

Após a inserção das 8.000 linhas de registros, vamos confirmar a distribuição dos dados através do Bloco de Código 8 declarado abaixo, conforme ilustra o resultado da Figura 2:

— Bloco de Código 8 —

— Consultando a distribuição e particionamento dos dados —
Select partition_number, rows
From sys.partitions
Where OBJECT_NAME(OBJECT_ID)=’TableIncrementalStatistics’
Go

Figura 2 – Distribuição dos dados na tabela TableIncrementalStatistics de acordo com o valor e partição.

Estamos chegando no final, agora vamos realizar algumas manipulações no conjunto de dados armazenados na tabela TableIncrementalStatistics afim de forçarmos o processos de atualização das estatísticas, procedimento que vai nos ajudar a entender o processo de incremento na atualização das estatísticas de armazenamento e processamento utilizados pelo Microsoft SQL quando solicitado acesso aos dados armazenados em nossa table, para tal operação vamos utilizar o Bloco de Código 9:

— Bloco de Código 9 —

— Consultando dados na TableIncrementalStatistics —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID = 1
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID >= 2
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 3
Go

Pronto, realizamos algumas operações de Select com intuito de forçar a criação de novas estatísticas, e principalmente a atualização das estatísticas atuais. Por enquanto nada de diferente, na sequência vamos consultar as informações sobre as estatísticas relacionadas a nossa tabela, fazendo uso do Bloco de Código 10 e analisando o resultado apresentado através da Figura 3:

— Bloco de Código 10 —

— Consultando as informações sobre as estatísticas da tabela TableIncrementalStatistics —
Select object_id, stats_id , last_updated , rows , rows_sampled , steps
From sys.dm_db_stats_properties(OBJECT_ID(‘[TableIncrementalStatistics]’),1);
Go

Figura 3 – Dados relacionados a estatísticas da TableIncrementalStatistics.

Como você pode ver, o DMF sys.dm_db_stats_properties mostra-nos que as estatísticas foram atualizadas na data do dia 23/05/2017 ás 16:55, para a tabela que tem 8000 linhas.

Neste momento, podemos nos perguntar: Qual partição da tabela inclui as estatísticas atualizadas?

A resposta para esta sua pergunta vem justamente atráves do uso nova DMF sys.dm_db_incremental_stats_properties já apresentada aqui no meu blog. Sendo esta DMF responsável em apresentar as propriedades estatísticas incremental, recuperando as mesma informação obtida a partir do DMF sys.dm_db_stats_properties, também super conhecida e apresentada no meu blog. Neste caso a sys.dm_db_stats_properties vai apresentar dados de  cada partição da tabela particionada, fornecendo-lhe com os mesmos parâmetros; a identificação do objeto e a identificação de estatísticas.

Caminhando mais um pouco, estamos próximos do final, vamos então formar o SQL Server a justamente realizar o processo de atualização das estatísticas para nossa partição de número 3, realizando o processo de exclusão de 1.500 linhas de registros, em seguida consultando nossa TableIncrementalStatistics, conforme apresenta o Bloco de Código 11:

— Bloco de Código 11 —

— Excluíndo 1.500 linhas —
Delete Top (1500) From TableIncrementalStatistics
Where ID = 2
Go

— Consultando os dados —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 4
Go

Agora vamos novamente consultar os dados estatísticas, sendo assim repita a execução do Bloco de Código 10, observe que você deverá receber um conjunto de valores similares a Figura 4, onde a coluna Last_Updated deverá apresentar a data e hora da última atualização:

Figura 4 – Data e hora da última atualização da estatística.

Ufa, estamos quase lá, agora chegou a hora da verdade, hora de comprovar se realmente o SQL Server esta fazendo as coisas certas, vamos fazer uso da DMF sys.dm_incremental_stats_properties para validar se a estatística da partição 3 foi atualizada, o resultado pode ser analisado através da Figura 5. Para isso vamos utilizar o Bloco de Código 12 a seguir:

— Bloco de Código 12 —

— Consultando as informações sobre as estatísticas incrementais —
Select object_id, stats_id,
partition_number,
last_updated,
rows, rows_sampled,
steps
From sys.dm_db_incremental_stats_properties(OBJECT_ID(‘TableIncrementalStatistics’),1)
Go

Figura 5 – Informações sobre as atualizações de estatísticas, onde a partição 3 foi atualizada de maneira independente das demais.

Sensacional, conseguimos, muito legal este recurso, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.

Referências

Post Anteriores

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

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode conhecer um pouco mais de como o Microsoft SQL Server trabalha de maneira árdua na busca da melhor maneira para encontrar e retornar os dados solicitados em nossas transações. Ao longo de novas versões o produto esta cada vez mais maduro, confiável e inteligente, sempre nos surpreendendo com sua capacidade.

Algo que não poderia ser diferente no uso das Estatísticas Incrementais, recurso que nos permite adotar uma nova maneira de atualização dos dados internos relacionados ao armazenamento das nossas informações, mas principalmente prover um auxílio para próprio Database Engine mas atividades para identificar o melhor caminho para se processar uma query.

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

Valeu….

Microsoft disponibiliza o SQL Server 2017 CTP 2.0


A Microsoft anunciou nesta semana em seu blog a disponibilidade no TechNet Evaluation Center do SQL Server 2017 CTP 2.0, a versão de testes pública mais recente do novo SQL Server.

Performance inigualável
Faça consultas até 100 vezes mais rápido do que o disco com columnstore in-memory e transações até 30 vezes mais rápido com OLTP in-memory.

Menos vulnerável
Melhore a segurança com criptografia em repouso e em movimento. O SQL Server é o banco de dados menos vulnerável nos últimos sete anos no banco de dados de vulnerabilidades do NIST.

Inteligência em tempo real
Obtenha insights transformadores com até 1 milhão de previsões por segundo usando o Python interno e a integração da linguagem R e obtenha o BI móvel completo em qualquer plataforma.

Qualquer aplicativo, em qualquer lugar
Crie aplicativos modernos usando quaisquer dados e qualquer linguagem, na infraestrutura local e na nuvem.

Microsoft disponibiliza o SQL Server 2017 CTP 2.0Faça o download do SQL Server 2017 CTP 2.0
O SQL Server 2017 CTP 2.0 para Windows (64 bits) está disponível para download aqui como uma imagem ISO e como um arquivo CAB nos seguintes idiomas: Inglês, alemão, espanhol, japonês, italiano, francês, russo, chinês simplificado, chinês tradicional, português (Brasil), coreano.
As versões para Linux, Docker, Azure e Mac OS estão disponíveis aqui.

Fontes e Direitos Autorais: Baboo.com.br – 20 abr 2017 | 9:40 am.

#08 – Para que serve


Boa noite comunidade, boa noite amantes do SQL Server e Banco de Dados…..

Hoje dia 03 de Setembro começo de noite de mais um sabádão, estamos vivendo os primeiros dias de mais um mês e se aproximando do final de 2016. Antes de começar a falar sobre o post de hoje, gostaria de compartilhar mais algumas conquistas obtidas neste últimos dias, bem como uma outra que esta por vir.

Há primeira conquista se refere aos meus alunos do Curso de Sistemas de Informação para Internet da Fatec São Roque, tive o imenso prazer em poder lecionar para esta fantástica turma nos últimos 4 semestre, desejo a vocês tudo de bom, felicidades e muitas conquistas.

A próxima ainda mais importante é o aniversário do meu filho mais velho Eduardo Galvão que no próximo dia 06 de Setembro estará completando 15 anos de vida, com certeza um dos momentos mais felizes e esplêndidos da minha vida. Desejo a você meu pequeno grande menino um futuro fantástico, muito saúde, felicidades e alegrias.

Vamos em frente, voltando ao post de hoje da sessão Para que serve, conforme prometido vou dar continuidade ao post anterior onde começamos a conhecer um pouco sobre o conceito de Índice Hipotéticos. Caso você não tenha acessado o post anterior não perca tempo clique no link acima e conheça um pouco sobre este conceito tão surpreende quando a sua forma de uso.


Começa agora o #08 – Para que serve – Índices Hipotéticos – Parte II.

No #08 – Para que serve, daremos início ao processo de criação do nosso ambiente de banco de dados com objetivo de construir um estrutura específica para conhecermos e simularmos o uso dos índices hipotéticos. É isso siga-me os bons, mãos no teclado, acompanhe a sequência de passos apresentandos abaixo:

— Passo 1 – Criando o Banco de Dados HypotheticalDB —

CREATE DATABASE [HypotheticalDB]
ON  PRIMARY
(NAME = ‘HypotheticalDB-Data’,
 FILENAME = ‘C:\SQLServer2016\Data\HypotheticalDB_Data.mdf’ ,
 SIZE = 4MB ,
 MAXSIZE = 4096MB,
 FILEGROWTH = 2MB )
LOG ON
(NAME = ‘HypotheticalDB-Log’,
 FILENAME = ‘C:\SQLServer2016\Log\HypotheticalDB_Log.ldf’ ,
 SIZE = 8MB ,
 MAXSIZE = 2GB ,
 FILEGROWTH = 4MB)
GO

— Passo 2 – Acessando o Banco de Dados —
Use HypotheticalDB
Go
— Passo 3 – Criando as Tabelas —
Drop Table If Exists dbo.Clientes
CREATE TABLE Clientes
(Codigo  INT Identity(1,1) NOT NULL Primary Key Clustered,
 CodigoCategoria TinyInt NOT NULL,
 Nome  VARCHAR(60) NOT NULL,
 Endereco VARCHAR(80) NOT NULL,
 Estado  CHAR(2) NOT NULL,
 DataUltimaCompra  DATETIME)
Go
Drop Table If Exists dbo.ClientesCategorias
CREATE TABLE ClientesCategorias
(Codigo TinyInt NOT NULL,
 Descricao VARCHAR(20) NOT NULL)
Go

Nota: Observe que no passo 3 estamos utilizando uma nova instrução introduzida no Microsoft SQL Server 2016, estou me referindo ao Drop If Exists uma das mais esperadas melhorias a serem adicionados ao Microsoft SQL Server que por muitos anos estava sendo aguardada.

— Passo 4 – Inserindo dados na Tabela ClientesCategorias —
INSERT Into ClientesCategorias (Codigo, Descricao)
 Values (1, ‘Premier’),
             (2, ‘Advanced’),
             (3, ‘Special’)
Go
— Passo 5 – Inserindo dados na Tabela Clientes —
Insert Into Clientes (CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra)
Values (3, ‘José Bonito’,’Rua A’,’SP’,GETDATE()-30),
            (1, ‘Dassaev Silva’,’Rua B’,’SP’,GETDATE()-120),
            (3, ‘Viewer Partes’,’Rua 123′,’RJ’,GETDATE()-720),
            (1, ‘Dino Silva Sauros’,’Avenida Parque dos Dinassauros’,’AM’,GETDATE()-240),
            (2, ‘Fernandino Campos Boyd’,’Estrada Velha’,’MG’,GETDATE()-5),
            (1, ‘Katrina Tornado’,’Rua Storm’,’RG’,GETDATE()-300),
            (2, ‘Washington Wizard’,’Place 1′,’PR’,GETDATE()-1024),
            (3, ‘Chicago Bulls’,’Place 2′,’PR’,GETDATE()-89),
            (2, ‘Denver Nuggets’,’Place 3′,’PR’,GETDATE()-289),
            (2, ‘Los Angeles Lakers’,’Place 4′,’PR’,GETDATE()-390)
Go
— Passo 6 – Consultando os dados —
Select Codigo, Descricao From ClientesCategorias
Go
Select Codigo, CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra From Clientes
Go
Até aqui nada muito diferente do que normalmente utilizamos em nosso dia-á-dia, talvez o uso da Drop If Exists possa ser um diferencial. Dando continuidade os dois próximos passos serão de extrema importância para nosso ambiente, estaremos justamente realizando a criação de três índices:
  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas;
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas; e
  • IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas.

Observe que serão criados dois índices não-clusterizados e um índice clusterizado, todos vinculados a tabela ClientesCategorias para coluna Codigo, onde você vai poder notar que dois índices devem ser criados sem estatísticas o que indica para o SQL Server que este será um índice hipotético existindo somente de maneira lógica e não terá nenhum tipo de vínculo ou estrutura física criada. Então siga em frente, mãos no teclado, a seguir os passos 7 e 8:

— Passo 7 – Criando índices hipotéticos não-clusterizado na tabela ClientesCategorias —
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = 0
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
— Passo 8 – Criando índices hipotéticos clusterizado na tabela ClientesCategorias —
CREATE CLUSTERED INDEX IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
Antes de continuarmos vou apresentar a Figura 1 que ilustra a criação destes índices dentro da estrutura da tabela ClientesCategorias, você vai poder notar na figura que somente existe estatísticas para estes objetos as guias Keys e Indexes estão vazias:
HypotheticalIndex

Figura 1 – Estrutura da Tabela ClientesCategorias.

Vamos que vamos estamos quase lá, falta pouco, agora vamos executar o passo 9 em duas etapas a primeira será a execução do system stored procedure sp_helpindex responsável em apresentar a estrutura de índices existente em uma determinada tabela, neste caso estou me referindo a tabela ClientesCategorias, sendo assim, vamos realizar esta execução:

 

— Passo 9 – Obtendo informações sobre os índices —
Exec sp_helpindex ClientesCategorias
Go

Após a execução o Management Studio vai retornar nossos três índices criados anteriormente, podemos observar a existência de uma coluna chamada index_description, verifique que todos os índices apresentam a seguinte informação: nonclustered, hypothetical

Você pode estar se perguntando, mas nos não criamos um índice clusterizado? A resposta seria sim, criamos ele continua sendo clusterizado, mas como este não apresenta um estrutura física o mesmo é reconhecido e tratado pelo SQL Server como índice não-clusterizado neste momento, a Figura 2 apresentada este resultado:

HypotheticalIndex2

Figura 2 – Relação de índices hipotéticos pertencentes a table ClientesCategorias.

O próximo passo e realizar a segunda parte do passo 9,  onde faremos a execução do comando DBCC Show_Statistics responsável em apresentar informações sobre as estruturas físicas e lógicas vinculadas a estatísticas de um índice, no nosso caso vamos utilizar os índices:

  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas; e
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Vamos então executar o bloco de código abaixo:

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas)

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas)
Go

Observando  a Figura 3 a seguir fica mais fácil  entender que o índice: IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas não apresenta nenhuma informação relacionada a estatísticas, ao contrário do índice:

IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas, onde o SQL Server após o procedimento de crição do mesmo já estabeleceu alguns dados estatísticos que posteriormente será utilizados no processamento das querys de acordo com sua necessidade. Segui a seguir a Figura 3:

HypotheticalIndex3

Figura 3 – Comparativo entre as estruturas dos índices IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas  e IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Falta pouco, pouco mesmo, prometo que estamos no final, vamos executar o passo de número 10, onde estaremos obtendo as informações sobre nossos índices através da catalog view sys.sysindexes, onde nosso índice IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas agora será apresentado como um índice clusterizado, isso nos faz entender que em alguns momentos o SQL Server acaba mudando um pouco o seu comportamento de acordo com a forma de obtenção de informações sobre as estruturas de nossos índices, sinceramente não saberia dizer se isso pode ser considerado uma falha ou até mesmo um possível bug.

Pisando fundo em nosso “acelerador” execute o passo 10 apresenta logo a seguir:

— Passo 10 – Obtendo informações sobre a relação de índices —

SELECT object_id,
             OBJECT_NAME(object_id) AS ‘Tabelas’ ,
             name As ‘Nome do Índice’,
             type_desc,
             is_hypothetical As ‘Índice Hipotético = 1 Não-Hipotético=0’
FROM sys.indexes
WHERE object_id in (object_id(‘ClientesCategorias’), object_id(‘Clientes’))
Go

E agora o tão esperado momento, vamos realmente fazer uso de nossos índices hipotéticos através da execução dos passos 11 e 12 teremos a capacidade técnica de entender o comportamento do SQL Server, principalmente através da comparação dos planos de execução gerados para cada query processada, sendo assim, vamos começar executando o passo 11 a seguir:

— Passo 11 – Executando o Select de maneira clássica sem a diretiva SET AUTOPILOT —
SET SHOWPLAN_XML ON
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
GO
SET SHOWPLAN_XML OFF
Go
Observe que solicitamos ao Management Studio para realizar o plano de execução da nossa query através da diretiva SET SHOWPLAN_XML, onde o mesmo deverá ser gerado no formato XML, recomendo que você salve este plano de execução para que possamos fazer uso do mesmo no último. Agora execute o passo 12, salve o plano de execução gerado em xml e apresentado de forma gráfica.
— Passo 12 – Executando o Select de maneira personalizada ativando a diretiva SET AUTOPILOT  —
SET AUTOPILOT ON — Ativando a diretiva —
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
Go
SET AUTOPILOT OFF — Desativando a diretiva —
GO
Muito bem, conseguimos executar nossos dois selects, espero que você tenha salvado os respectivos planos de execução, agora após a execução do passo 12 você deverá esta visualizando o plano de execução deste select, clique com o botão da direita na parte branca e escolha a opção Comparative ShowPlan, selecione o arquivo que representa o plano de execução criado após a execução do passo 11 em seguida o mesmo deverá ser aberto, conforme a Figura 4 a seguir apresenta:
HypotheticalIndex4
Figura 4 – Comparação entre os planos de execução gerados durante a execução dos passos 11 e 12.
Podemos observar que os dois planos de execução são praticamentes idênticos de maneira geral, mas se realmente analisarmos cada um dos operadores, será possível notar um uma pequena diferença no operador Select, onde a instrução CompileMemory nos mostra uma diferença de 8(oito) compilações a menos realizada no passo 11 em comparação com o passo 12, conforme ilustra a Figura 5 abaixo:
HypotheticalIndex5
Figura 5 – Comparativo de resultados apresentados na instrução CompileMemory.
Poxa vida, analisando friamente esta é uma diferença tão pequena que talvez não seja necessário se preocupar ou até mesmo querer entender o que pode ter acontecido. Mas DBA que é DBA de verdade não gosta de se deparar com estas situações em seu ambiente, menos ficar sem uma possível resposta, por mais que muitas vezes ela até mesmo não exista.
Então se você quiser realmente saber o que pode ter influenciado o SQL Server mais especificamente o Database Engine e seus componentes dentre eles o Query Optimizer e o Execution Plan a apresentar este resultado não deixe de acessar o próximo post dedicado aos índices hipotéticos, então nos vemos no #09 Para que serve.
Até lá………..

É isso ai galera, chegamos ao final de mais post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profissional de banco de dados reconhecido e valorizado, um dos papéis na área de tecnologia mais importantes para qualquer empresa.

Reconher o verdadeiro papel de um DBA dentro de sua estrutura, é reconhecer o verdadeiro valor de seus dados e como eles podem se tornar uma infomação valiosa para sua tomada de decisão.

Caso deseje acessar os posts anteriores desta sessão, utilize os links listados abaixo:

#07 – Para que serve


Boa tarde comunidade, boa tarde Brasil!!!!

Começando mais uma tarde de sábado, neste primeiro final de semana do mês de agosto, clima olímpico e muito feliz em ver que o Brasil foi capaz de fazer uma linda festa ontem na abertura dos Jogos Olímpicos Rio 2016, desejo muito sucesso para todos os participantes principalmente aos atletas brasileiros.

O post dedicado a sessão Para que serve deste mês, também esta no clima olímpico, você pode estar se perguntando o porque eu destaquei na minha abertura este clima. Quando estamos pensando em esporte muitas vezes pensamos que não existem possibilidades ou possíveis situações de um determinado time ou atleta ser superado por outro mais fraco, pode ser definido como algo “Hipotético”, sim “Hipotético” na sua definição com base em diversos dicionários: fictício, figurado, imaginário, suposto. Na área de banco de dados isso também pode ser aplicado, principalmente no SQL Server.

Mas de que maneira podemos pensar em algo hipotético, fictício ou imaginário quando estamos trabalhando com banco de dados? Pergunta que inicialmente pode ser difícil de ser respondida, complexa ou simplesmente hipotético(kkkkk).

Foi então que eu comecei a buscar mais informações em um conceito que pra mim era realmente imaginário de ser adotado, e recentemente em um dos posts publicados nos fóruns de SQL Server aqui no Brasil veio a tona o chamado Índices Hipotéticos.

Essa é uma possível resposta quando estamos trabalhando com banco de dados, fazer uso de índices hipotéticos pode nos ajudar a identificar ou similar possíveis situações de impacto na performance de uma query durante sua execução, ainda mais se estivermos trabalhando com um conjunto volumoso de dados.

Para tentar compartilhar com você um pouco sobre este mistorioso recurso que podemos adotar em nosso ambiente, o post de hoje o próximo da sessão para que serve serão dedicados justamente ao entendimento, criação e uso dos índices hipotéticos.

E como de costume aquelas perguntas já conhecidas dos posts anteriores desta sessão:

E ai, você conhece esta funcionalidade? Já utilizou? Sabe para que ela serve?

Pois bem, estas e outras possíveis perguntas serão respondidas a partir de agora em mais este post da sessão Para que Serve!


Começa agora o #07 – Para que serve – Índices Hipotéticos – Parte I.

Mais um final de semana esta chegando, hoje sexta – feira, você já esta começando a se preparar para desligar sua estação de trabalho, pegar suas coisas e voltar para casa feliz por mais um dia de trabalho duro e gratificante e por saber que fez o melhor possível para manter tudo em ordem em seu local de trabalho, eis que após alguns minutos o seu ramal de telefone toca e no display aparece 2801 – Fernanda Galvão, meu deus você pensa, respira e atende sabendo que ela é a gerente de produção da empresa e para estar ligando no final do dia não deve ser nada muito simples, mesmo assim sabendo dos seus deveres e obrigações realiza o atendimento a ligação e escuta:

Junior Galvão, boa tarde!!!

Aqui é a Fernanda, tudo bem? Estamos com um pequeno problema na emissão do relatório de produção diária, estou aqui com o analista de produção João Pedro, você pode falar com ele?

Junior responde sim, claro!!!

Neste momento, João Pedro apresenta o cenário: Junior, olá boa tarde.

Estou com dificuldades para emitir o relatório de produção diária, ao tentar filtrar os dados por clientes e categoria de clientes, o sistema aparentemente entra em loop de processamento e os dados não são apresentados em tela. 

O que será que pode estar acontecendo? Alguns segundos se passam…. Junior começa a pensar e observa que seu final de semana aparentemente foi por água, ou melhor dizendo o final de semana não vai ser tão tranquilo.

Você responde: João Pedro, boa tarde, vou verificar o que pode esta acontecendo, sei que hoje realizamos algumas mudanças na estrutura da tabela de clientes e categoria de clientes, parece-me que o time de suporte adicionou um novo índice, vou tentar verificar.

João Pedro responde dizendo.

Ok! Junior, fico no aguardo, assim que você tiver uma posição por favor me informe.

Junior responde: Certo, perfeito, deixa comigo, vou verificar o que esta acontecendo garanto que hoje não vou conseguir dar uma resposta mais concreta!

Sabendo justamente que a equipe de suporte esta trabalhando para realizar alguns testes de performance nas tabelas de clientes e categoria de clientes, onde milhares de registros estão sendo processados diariamente, Junior chama um dos seus analistas de suporte Eduardo Galvão e pergunta:

Edu por acaso vocês realizaram alguma alteração na estrutura das tabelas de clientes ou categoria de clientes?

A resposta é simples e direta, sim Junior, estamos fazendo uso de um recurso que até então novo ou supostamente desconhecido para nossa equipe,  pelo que pesquisei é uma funcionalidade conhecida como índices que possuem somente estatíticas mas não existem fisicamente.

Junior responde: Índices somente com estatísticas, índices que não existem, que raio de recurso é esse, por acaso vocês estão se referindo a índices hipotéticos?

Eduardo responde, sim, sim, acredito que seja isso a analista de suporte de banco de dados Maria Luíza, esta fazendo um estudo sobre isso em nosso ambiente de testes e identificou que se fizermos a adoção deste recurso poderemos ter mais facilidade em reconhecer a necessidade de um novo índice ou se o mesmo realmente é útil mesmo após ter sido criado anteriormente.

Junior responde, certo Eduardo, mas este tipo de teste ou implementação deve ser planejada, não podemos simplesmente pesquisar um recurso na internet ou livros e já sair aplicando em nossos ambientes de teste, muito menos em produção, devemos sempre por em prática nosso check-list de boas práticas e principalmente ter um ambiente de contigência caso algo aconteça de errado.

Quero saber qual será a forma para identificar o que esta acontecendo e como vamos resolver este problema até segunda – feira, questiona Junior!!!”

Muito bem, este é nosso cenário, com base, nesta pequena estória que acabamos de conhecer, será criado nosso ambiente de testes para colocar em prática o conceito de índices hipotéticos, antes disso iremos comecer um pouco mais sobre este conceito.

Índices

Falando de uma maneira simples quando criamos um novo índice no SQL Server ou em qualquer outro banco de dados, estamos criando uma estrutura que basicamente servirá como caminho na busca e identificação de um ou mais dados solicitados pelos mecanismos de banco de dados durante o processamento de uma determinada query.

Ao realizar a criação deste elemento normalmente os índices físicos apresentam em sua estrutura os dados, distribuídos de maneira demográfica confome as manipulações são realizadas, além disso, apresentam densidade, granularidade e seletividade de acordo com seu conjunto de valores, com isso, temos um conjunto de informações técnicas conhecidas como estatísticas do índice o que permite servir como elemente auxiliar no obtenção mais ágil e simples dos dados solicitados.

Índices Hipotéticos

Ao se falar de índices hipotéticos, estamos se referindo a uma estrutura completamente oposta, sem qualquer tipo estrutura física, muito menos dados, um índice hipotético é conhecido como algo imaginário que não possue estrutura física, somente estrutura lógica ou seja, somente estatísticas que podem servir como recurso para tentar criar o mecanismo de banco de dados e também o plano de execução por parte do SQL Server na busca de um ou mais dados.

Como podemos criar um índice hipotético?

A partir do SQL Server 2008 R2 a Microsoft adicionou uma opção no comando Create Index conhecido como With Statistics_Only, traduzindo ao pé da letra para o português vamos encontrar ao similar à somente estatísticas. É com base nesta opção não documentada que temos a possibilidade de fazer uso de índices hipotéticos em nossos bancos de dados.

O uso desta opção é muito simples, basta ao final da linha de comando que referencia a criação de um novo índice adicionar a instrução With Statistics_Only = 0, onde o mecanismo de banco de dados vai entender que esta novo índice deverá ser criado possuindo somente uma estrutura lógica controlada e direcionada através dos dados estatísticos coletados durante as manipulações de dados ou execução de querys que fazem uso do mesmo. Quando criamos um novo índice e não informamos esta opção por padrão o mecanismo de banco de dados repassa internamente para processador de querys que este índice deve ser criado da maneira padrão ou seja, um índice que conterá estrutura física e lógica, e o valor correspondente a instrução With Statistics_Only será igual á -1, ou seja:

  • With Statistics_Only = 0 — Indica que o índice deve ser criado de maneira hipotética, índice forma somente por estrutura lógica, conhecida como estatíticas; e
  • With Statistics_Only = -1 — Indica que o índice deve ser criada da maneira clássica, índice formado por estrutura física e lógica.

Uma forma simples é fácil para saber se um ou mais índices apresentam esta diferença pode ser encontrada na visão de sistema sys.indexes através da coluna is_hypothetical, onde a mesma deverá apresentar os valores: 0(zero) ou 1(hum), sendo estes valores que identificam e diferenciam a ocorrência da existência de um ou mais índices clássicos e hipotéticos.

Mas não tudo sem flores como diria meu irmão, a criação de um índice hipotético é fácil, tranquila, sem muitos segredos. Agora, imagine se você deseja orientar otimizador de consultados existentes no SQL Server no uso deste tipo de índice durante o processamento de uma query, ou então se você deseja omitir o seu uso, situação que pode parecer muito comum de ser realizada ou automática, mas não é bem assim.

Temos a necessidade de dirigir isso mesmo, mostrar o caminho que deve ser seguido pelo Database Engine em conjunto com o Query Optimizer e posteriormente o Execution Plan, como deve ser feito o uso de um índice hipotético. Isso parece ser algo bastante complicado, não é bem assim, como sempre existe uma solução que a Microsoft muitas vezes também não reconhece como recurso documentado ou simplesmente não documento, e ai mais uma vez “Mister M de SQL Server” surge para nos ajudar e apresentar ao mundo como uma possível solução pode ser adotada maneira mais suave, mostrando como podemos  resolver este problema e sair desta sinuca de bico.

Pra variar surge para muitos um novo  DBCC – Database Command Console não documentado conhecido como DBCC AutoPilot e uma nova diretiva Set AutoPilot, onde:

  • Set AutoPilot – Orienta o query optimizer a considerar ou não o uso do índice hipotético no momento da criação do plano de execução da query; e
  • DBCC AutoPilot – Orienta o query optimizer fazer uso do índice hipotético de acordo com o conjunto de parâmetros a ser utilizado e posteriormente repassado para o plano de execução.

Preste atenção o nome dele não tem nada haver com piloto de Fórmula 1 (kkkk), vou repetir o seu nome DBCC AutoPilot e ele vai justamente nos ajudar e saber mais sobre os dados que estão relacionados com um determinado índice hipotético.

DBCC AUTOPILOT

Este comando DBCC é mais um dos diversos comandos de console de banco de dados que a Microsoft não reconhece como comando documentado ou suportado nativamente, através do conjunto de instruções “parâmetros” que compõem sua sintaxe o query optimizer vai se comportar de uma determinado maneira ou de outra.

Abaixo apresento a relação de parãmetros que formam o DBCC AutoPilot:

Parâmetro

Descrição
typeid Existem alguns valores, os mais utilizados basicamente são:
Type ID = 5: Iniciar a sessão ou comandos anteriores limpos;
Type ID = 0: Fazer uso de índices não clusterizados; e
Type ID = 6: Usar apenas índices clusterizados.
dbid Id do banco de dados habilitado para executar o comando.
maxQueryCost Supostamente definir um possível custo em relação ao processamento da query. “Sinceramente não entendi bem como usar (kkkk)”
tabid Id da Tabela a ser utilizada.
indid Id do índice a ser utilizado.
pages Ao executar o DBCC AutoPilot simular o comportamento e uso de páginas de dados.
flag Parâmetro desconhecido, não encontrei informações sobre ele….
rowcounts Parâmetro utilizado para definir o número de linhas de execução e processamento para alguns comandos.

Bom vou deixar você agora com um gostinho de quero mais, como destacado anteriormente este é a primeira parte deste Para que serve…. Na segunda parte vamos criar nossos índices hipotéticos e fazer uso da diretiva SET AutoPilot, posteriormente na terceira parte vamos utilizar a não documentada DBCC AutoPilot.


É isso ai galera, chegamos ao final de mais post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profissional de banco de dados reconhecido e valorizado, um dos papéis na área de tecnologia mais importantes para qualquer empresa.

Reconher o verdadeiro papel de um DBA dentro de sua estrutura, é reconhecer o verdadeiro valor de seus dados e como eles podem se tornar uma infomação valiosa para sua tomada de decisão.

Caso deseje acessar os posts anteriores desta sessão, utilize os links listados abaixo:

Dica do Mês – Identificando pressão de memória interna e externa no Microsoft SQL Server


Olá comunidade, muito boa tarde.

Tudo bem? Mais um mês que chega ao final, graças a deus este mês de Julho foi complicado, espero que toda a correria que passei mesmo em período de férias não se repita por um longo tempo. Estamos no sétimo post desta nova sessão lançada no início de 2016, muito legal, já temos um pouco de história para contar em relação a aceitação dos visitantes do meu blog em relação a esta sessão.

Como você já deve estar acostumado quando visita o meu blog, todo final de mês é dedicado a post exclusivos das sessões Dica do Mês e Para que Serve. No último post publicado no mês de Junho, apresentei um conteúdo mais focado para área acadêmica, destaquei um dos conceitos mais importantes quando estamos falando de Modelagem de Banco de Dados, a tão temida e misteriosa Normalização(kkkkk), algo que para muitos é um bicho de sete cabeças, mas na verdade não se passa de um gatinho lindo e manhoço.

Como diria aquela boa e velha “Amanheceu pequeno a viola botei na sacola e fui viajar”, no meu caso seria mais ou menos assim: “Amanheceu entrei no meu carro peguei minha mochila e fui trabalhar, anoiteceu entre no meu carro peguei minha apostila e fui lecionar……”.

Deixando a brincadeira de lado no post de hoje vou falar um pouco sobre como podemos identificar o consumo de memória por parte do SQL Server ou Windows através do Management Studio ou Scripts, estou me referindo a chamada pressão de memória interna ou externa.

Então, seja bem vindo a mais uma dica do mês, começa aqui a Dica do Mês – Identificando pressão de memória interna e externa no SQL Server.


Introdução

Uma das maiores preocupação de qualquer profissional de infraestrutura ou administrador de banco de dados é saber se seus recursos de hardware estão atendendo as demandas solicitadas por suas aplicações e usuários.

Quando se referimos a consumo de memória por parte de um servidor de banco de dados, existem pontos que podem nos direcionar a uma pequena confusão, dentre elas destaco:

  • Identificar o quanto uma sessão ou transação esta consumindo de memória;
  • Identificar o quanto um ou mais bancos de dados estão consumindo de memória; ou
  • Identificar o quanto um servidor ou instância de sql server esta consumindo de memória.

Você pode fazer isso, bom identificar o quanto o SQL Server esta consumindo de memória é fácil, basta eu utilizar as ferramentas Task Manager ou Resource Monitor para obter estes dados, na verdade não é bem assim, ambas as ferramentas podem ajudar a encontra vestígios deste possível consumo, o que particularmente não pode ser considerada uma tarefa complexa nem tão simples.

Mas quando se referimos a pressão de memória, o cenário muda totalmente, trata-se de uma situação onde nosso servidor pode por diversos motivos estar sofrendo uma sobrecarga de processamento interno ou externo que gera um consumo elevado ou até mesmo falta de memória para atender todas as requisições que estão sendo disparadas para ele. Seguindo nesta linha de raciocínio a Microsoft disponibilizou a partir da versão do Microsoft SQL Server 2008 e 2008 R2 duas DMVs – Dynamic Management Views (Visões de Gerenciamento Dinâmico):

  • sys_dm_os_ring_buffers: Retorna um conjunto de rings “anéis” de dados que nos permitem relacionar com outras dmv afim de identificarmos problemas de erros de conectividade, faixa exceções, além disso, utilizada também como mecanismo para identificar a saúde de nosso ambiente de banco de dados, sem proporcionar qualquer tipo de pressão de memória, deadlocked ou mudança no comportamento dos agendadores de tarefas em execução.
  • sys_dm_os_sys_info: Retorna um conjunto de informações relacionadas as recursos de hardware utilizados pelo SQL Server, como por exemplo:
    • CPU_TICKS;
    • CPU_COUNT; e
    • Physical_Memory_KB.

Você pode estar pensando, bom já que a sys_dm_os__sys_info apresenta um conjunto de informações relacionadas a hardware que esta em uso pelo SQL Server, é através dela que eu poderei identificar o consumo de memória. Bom se você me pergunta-se isso neste momento a minha resposta seria NÃO.

Porque não, porque estes dados do que esta sendo consumido de maneira interna pelo SQL Server ou até mesmo externamente pelo Windows que possui relação são para muitos conhecidos como ring_buffers ou melhor dizendo ring “anéis”, e são através destes anéis ou basicamente falando com base nestes neles que temos como através da DMV sys.dm_os_ring_buffers coletar os ring_buffers que estão consumindo recursos de memória.

Então se você me perguntar se através da dmv sys.dm_os_ring_buffers podemos obter informações sobre pressão interna ou externa de memória a resposta será SIM. Pois bem, o segredo foi revelado o pulo do gato para saber detalhes escondidos de como o SQL Server coleta e armazena estes anéis é relavado pela sys.dm_os_ring_buffers.

Agora que sabemos os recuros que vamos utilizar para coletar estes dados e identificar os recursos  que estão consumindo memória em nosso ambiente, para tal utilizaremos o bloco de código 1 apresentado a seguir:

— Bloco de Código 1 – Identificando pressão de memória interna e externa —

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] – tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value(‘(//Record/ResourceMonitor/Notification)[1]’, ‘varchar(30)’) AS [Notification_type],
cast(record as xml).value(‘(//Record/MemoryRecord/MemoryUtilization)[1]’, ‘bigint’) AS [MemoryUtilization %],
cast(record as xml).value(‘(//Record/MemoryNode/@id)[1]’, ‘bigint’) AS [Node Id],
cast(record as xml).value(‘(//Record/ResourceMonitor/IndicatorsProcess)[1]’, ‘int’) AS [Process_Indicator],
cast(record as xml).value(‘(//Record/ResourceMonitor/IndicatorsSystem)[1]’, ‘int’) AS [System_Indicator],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[1]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[2]/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[2]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@type)[1]’, ‘varchar(30)’) AS [type],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@state)[1]’, ‘varchar(30)’) AS [state],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect[3]/@reversed)[1]’, ‘int’) AS [reserved],
cast(record as xml).value(‘(//Record/ResourceMonitor/Effect)[3]’, ‘bigint’) AS [Effect],
cast(record as xml).value(‘(//Record/MemoryNode/ReservedMemory)[1]’, ‘bigint’) AS [SQL_ReservedMemory_KB],
cast(record as xml).value(‘(//Record/MemoryNode/CommittedMemory)[1]’, ‘bigint’) AS [SQL_CommittedMemory_KB],
cast(record as xml).value(‘(//Record/MemoryNode/AWEMemory)[1]’, ‘bigint’) AS [SQL_AWEMemory],
cast(record as xml).value(‘(//Record/MemoryNode/SinglePagesMemory)[1]’, ‘bigint’) AS [SinglePagesMemory],
cast(record as xml).value(‘(//Record/MemoryNode/MultiplePagesMemory)[1]’, ‘bigint’) AS [MultiplePagesMemory],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]’, ‘bigint’) AS [TotalPhysicalMemory_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]’, ‘bigint’) AS [AvailablePhysicalMemory_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalPageFile)[1]’, ‘bigint’) AS [TotalPageFile_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailablePageFile)[1]’, ‘bigint’) AS [AvailablePageFile_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]’, ‘bigint’) AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’, ‘bigint’) AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value(‘(//Record/@id)[1]’, ‘bigint’) AS [Record Id],
cast(record as xml).value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type],
cast(record as xml).value(‘(//Record/@time)[1]’, ‘bigint’) AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’
ORDER BY rbf.timestamp ASC

 

Perfeito, após executar este bloco de código o Management Studio vai retornar as linhas de recursos internos e externos que podem estar gerando uma possível pressão de memória para seu servidor ou instância SQL Server, isso vai variar e depender muito de cada ambiente, hardware, versão de sistema operacional e principalmente versão do SQL Server e service pack instalado.


Sendo assim chegamos ao final de mais uma dica do mês.

Espero que você tenha gostado deste post,  acredito que as informações e exemplo publicadas aqui possam de alguma maneira ajudar e colaborar em suas atividades diárias, profissionais e ou acadêmicas.

Fique ligado nos próximos meses, novas dicas relacionadas a sys.dm_os_ring_buffers serão postadas, todas voltadas para ajudar identificar como solucionar problemas que o SQL Server pode estar apresentando e você ainda não percebeu dentre eles erros de segurança.

Desejo um forte abraço, agradeço mais uma vez a sua visita.

Até mais.