Script Challenge – 13 – A resposta….


Salve pessoal, bom dia.

Tudo bem?  Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2017, publicado em outubro de 2017 destacando o retorno da sessão Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados na mesma, caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 13, sendo assim, seja bem vindo a mais um post da sessão Script Challenge.


Script Challenge 13

Falando do desafio de número 13, o mesmo foi publicado no mês de outubro de 2017, período de data que apresenta uma das comemorações mais importantes que ocorrem anualmente em quase todos os países do mundo, mas que especialmente no Estados Unidos da América.

E ai já matou a charada? Eu acredito que sim! Mas para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 13.

Bom chegou a hora de revelar o que exatamente este pequeno bloco de código esta fazendo, chegou o momento de revelar e desvendar este desafio, a seguir apresento a resposta para o Script Challenge 13 e o trecho de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Tanto no post de lançamento do desafio, bem como, neste post de apresenta a resposta para o mesmo, eu deixei algumas pequenas dicas para tentar ajudar a identificar a resposta, dentre as quais a relação do script com uma das datas comemorativas mais tradicionais dos Estados Unidos, neste caso o Halloween(conhecido tradicionalmente como dia das bruxas).

Mesmo assim você pode estar se perguntando, o que Script Challenge 13 tem haver com dia das bruxas, ué tudo haver, pois quando falamos de bruxas, temos também em mente a relação com magia, fantasias, medo, terror e propriamente a fantasmas, isso mesmos fantasmas, algo que também pode acontecer em nossas tabelas com o passar do tempo conforme vamos realizando as manipulações de dados, torna-se possível se deparar com a ocorrência de possíveis dados fantasmas.

Então a resposta para o Script Challenge 13 é justamente a possibilidade que o script apresenta em identificar uma possível ocorrência de dados fantasmas em nossas tabelas e bancos de dados.

Isso mesmo, esta é a resposta, e o script original que apresenta esta funcionalidade apresentado abaixo:

— Script Challenge 13 – A resposta – Identificando a ocorrência de dados fantasmas —

SELECT db_name(database_id),
                object_name(object_id),
                ghost_record_count,
                version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
                                                                            OBJECT_ID(N’GhostTable’),
                                                                            NULL,
                                                                            NULL ,
                                                                            ‘DETAILED’)
GO

Que coisa de louco isso Galvão! Sim realmente parece ser coisa de outro mundo, mas podem acontecer tranquilamente a qualquer momento, o complicado é imaginar, que nossos bancos de dados, podem ser vítimas deste tipo de situação.

Mas isso não é o fim do mundo, muito ao contrário, para este cenário temos uma grande vantagem. Você pode estar se perguntando qual, a resposta é simples, não precisamos chamar os Casas Fantasmas, nós mesmos podemos resolver facilmente isso. Foi pensando justamente nas possibilidades da ocorrência deste tipo de situação, que além da compartilhar a resposta para este desafio, vou deixar também um cenário de simulação de como é possível ocorrência a existência de dados fantasmas, como também a possibilidade de excluir estes “dados”, a seguir:

— Simulando a ocorrência de dados fantasmas —

— Criando o Banco de Dados – GhostDB —
Create Database GhostDB
Go

— Acessando o Banco de Dados —
Use GhostDB
Go

— Criando a Tabela GhostTable —
Create Table GhostTable
(GhostRecord Int)
Go

— Criando um índice clusterizado —
Create Clustered Index Ind_GhostTable_GhostRecord On GhostTable(GhostRecord)
Go

— Inserindo Dados na Tabela GhostTable —
Insert Into GhostTable
Select 100
Go

— Obtendo informações sobre as estatísticas de alocação de dados —
Select object_id,
index_id,
index_depth,
index_level
From sys.dm_db_index_physical_stats(db_id(),
object_id(‘GhostTable’),
object_id(‘Ind_GhostTable_GhostRecord’),
null,
null)
Go

— Obtendo informações sobre o Índice IND_GhostTable_GhostRecord —
Select id, name, root, first
from sys.sysindexes
where id=565577053 — Aqui você vai colocar o ID identificado do índice apresentado na sua máquina —
Go

— Identificando a página de dados que contem os dados inseridos na GhostTable —
SELECT first_page,
(convert(varchar(2), (convert(int, substring(first_page, 6, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 5, 1)))) + ‘:’ + convert(varchar(11),
(convert(int, substring(first_page, 4, 1)) * power(2, 24)) +
(convert(int, substring(first_page, 3, 1)) * power(2, 16)) +
(convert(int, substring(first_page, 2, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 1, 1))))) As Page
FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS
Where first_page = 0x180100000100 — Valor obtido no bloco de código anterior através da coluna root —
Go

— Habilitando a Trace Flag 3604 para apresentar informações sobre as páginas de dados —
DBCC TRACEON (3604)
GO

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,1)
Go

Após obter o resultado do DBCC Page procure pela coluna m_ghostRecCnt, neste momento ela deve esta apresentando o valor m_ghostRecCnt = 0.

— Excluíndo os registros em GhostTable —
Delete from GhostTable
Where GhostRecord=100
Go

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,3)
Go

Agora verifique novamente a coluna m_ghostRecCnt que neste momento deverá apresentar o valor igual á m_ghostRecCnt = 1, este é o indicador da ocorrência de um dado fantasma em nossa tabela.

— Confirmando a existência de um registro fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’), OBJECT_ID(N’GhostTable’), NULL, NULL , ‘DETAILED’)
GO

— Simulando a eliminação de dados fantasmas —
Alter Table GhostTable
Rebuild — Utilize este tipo de procedimento em uma tabela com poucos registros —
Go

— Confirmando a existência de um registros fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
OBJECT_ID(N’GhostTable’),
NULL,
NULL ,
‘DETAILED’)
GO

— Liberando espaço alocado anteriormente em disco pelos registros fantasmas —
Exec sp_clean_db_free_space @dbname=N’GhostDB’
Go

Meu deus, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 13, sinceramente falando achei que não iria conseguir compartilhar este conteúdo com vocês.

Espero que tenham gostado desta da volta desta sessão, como também, a nova maneira que pretendo apresentar os desafios e seus respostas.


Sua Participação

No post de lançamento deste desafio, contei com a participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 13. A seguir apresento o resultado desta enquete:

A opção mais votada com 66,67% dos votos é justamente a resposta correta para este desafio, o qual retorna ao usuário informações relacionadas a identificação de páginas de dados com fragmentação.

Referências

Agradecimentos

Obrigado por sua visita, espero que o retorno desta sessão e o conteúdo aqui apresentado como um possível “desafio” possam ser úteis e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2018 em mais um post da sessão Script Challenge.

Até a próxima…

Conhecendo o “Buffer Cache Database” no Microsoft SQL Server 2012 e 2014.


Boa tarde, Comunidade e Amantes do Microsoft SQL Server.

Meu Deus, como o tempo esta passando de forma avassaladora, já estamos no mês de Setembro e daqui a alguns dias estaremos falando sobre o que aconteceu neste ano de 2014, nossa isso acaba sendo assustador. Mesmo assim, vamos em frente ainda temos muito para conhecer e produzir até o final de 2014.

Falando em conhecer e produzir, estou novamente aqui no meu blog, compartilhando com vocês mais um dos milhares de segredos, recursos, funcionalidades e praticidades que o Fantásico Microsoft SQL Server nos apresenta e permita utilizar.

Dentre estes segredos e recursos, quero mostrar para vocês algo muito simples de ser utilizado e entendido, mas que pode ajudar em muito a vida de Administrador de Banco de Dados em suas atividades diárias, estou me referindo ao “Buffer Cache Database”.

É isso mesmo “Buffer Cache Database”!!! Você conhece? Já teve a necessidade de utilizar ou descobrir para que ele serve? Ele existe? Ou somente é uma expressão ou termo técnico?

Eu mesmo no primeiro momento que ouvi este termo e me apresentaram fiquei um pouco perdido, sem saber o que este elemento pode me apresentar de informações úteis, mas com o passar do tempo e necessidade foi possível analisar e entender o quanto este pequeno recurso pode nos ajudar, principalmente em situações de alocação de uso de memória por parte do SQL Server.

Pensando justamente neste tipo de necessidade, decidi escrever este pequeno artigo, para tentar mostrar a vocês uma possível como podemos obter informações sobre o “Buffer Cache Database” e o quanto este Buffer faz parte do nosso ambiente.

Então mãos a massa, vamos descobrir um pouquinho sobre o “Buffer Cache Database”, saber do que ele composto e como podemos trabalhar com ele.

Introdução

O nome “Buffer Cache Database” a princípio pode parecer um bicho de sete cabeças, mas na verdade esta muito longe disso, basicamente pode ser entendido ou tratado como um Cache: “Uma área de armazenamento onde dados ou processos freqüentemente utilizados são guardados para um acesso futuro mais rápido, poupando tempo e uso desnecessário do seu hardware.”

O SQL Server é composto por um grande conjunto de Caches, um dos mais conhecidos é o Bufer Pool: Buffer reponsável em definir a quantidade possível de memória que poderá ser utilizada pelo SQL Server. Através deste recuros, temos a capacidade de utilizar “endereçar” ou “instanciar” o máximo de memória possível disponível em nosso Sistema Operacional sem afetar sua performance. De uma forma bem simples, o Buffer Pool tem como função realizar um balanceamento “equilíbrio” e “compartilhamento” de memória entre uma instância ou Servidor SQL Server através do seu Sistema Operacional do SQL Server chamado SQLOS em conjunto com os Memory Brokers e o próprio Sistema Operacional.

Mas, então, o que seria o “Buffer Cache Database”? Ele Existe? Ou somente é uma expressão ou termo técnico? Como ele é formado?

A resposta é muito simples e direta. O “Buffer Cache Database” não existe, pode ser entendido ou tratado como uma termo ou expressão técnica se comparado com Database Cache existente no Oracle. Na verdade é a forma natural que o SQL Server trabalha para organizar, estruturar e armazenar em páginas de dados que compõem os bancos de dados. Algo que pode ocorrer em diversos momentos, tanto na inicialização de uma instância ou servidor SQL Server, como também, quando um banco de dados realiza o alocação de suas páginas de dados em memória.

Isso mesmo páginas de dados, pequenas estrutura lógicas que armazenam os dados de forma física e temporária. As páginas de dados são interpretados pelo SQL Server da seguinte forma:

  • As páginas de dados são numeradas em sequência, iniciando com zero (0) para a primeira página do arquivo.
  • O tamanho padrão de uma página é 8 KB. Isso significa que qualquer banco de dados criado no SQL Server terá 128 páginas por megabyte.
  • Cada página começa com um cabeçalho de 96 bytes usado para armazenar informações de sistema sobre a página. Essas informações incluem o número de página, o tipo de página, a quantidade de espaço livre na página e a ID de unidade de alocação do objeto que possui a página.
  • Para identificar de forma exclusiva uma página em um banco de dados, são necessários ID do arquivo e número de página. Cada arquivo em um banco de dados tem um número de ID de arquivo exclusivo. Para tentar ilustrar, o exemplo a Figura 1 seguir mostra os números de página em um banco de dados que tem um arquivo de dados primário de 1 MB:
  • Com isso, podemos dizer que o “Buffer Cache Database” é criado e mantido, decorrente da existência das páginas de dados que forma nosso banco, isso nos permite dizer que a partir do momento que realizamos a criação de um banco de dados com tamanho de 1 MB, já estaremos fazendo uso desta estrutura de alocação e nosso novo  banco já terá um “Buffer Cache Database” inicial de 128 páginas.

Buffer01

Figura 1 – Estrutura de Páginas de Dados que compõem a estrutura de Banco de Dados no Microsoft SQL Server.

Caso você queira saber mais sobre páginas de dados, acesse: http://technet.microsoft.com/pt-br/library/ms190969(v=SQL.105).aspx

 

Obtendo o “Buffer Cache Database”

Para obtermos o “Buffer Cache Database” que atualmente esta sendo gerado e consumido pelo nosso SQL Server, vamos fazer uso do Código denominado Parte 1, apresentando abaixo:

— Parte 1 – Obtendo Buffer Cache por Banco de Dados –

Select DB_NAME(database_id) As ‘Database’,

COUNT(*) * 8 / 1024.0 As ‘Cached Size (MB)’

From sys.dm_os_buffer_descriptors

Where database_id <> 32767

Group By DB_NAME(database_id)

Order By ‘Cached Size (MB)’ Desc

 

A Figura 2 apresentada abaixo ilustra o retorno dos “Buffer Cache Database” obtidos no meu SQL Server, observe que o Banco de Dados que neste momento esta gerando o maior Buffer Cache é o Banco Cars com 1.078 MBs.

Buffer02

Figura 2 – Relação de “Buffer Cache Database”.

Nosso próximo passo, será forçar o SQL Server a gerar um novo “Buffer Cache Database”, neste caso, criando o nosso próprio ambiente, para isso será necessário criar um novo banco de dados, denominado: BufferCacheDatabase e uma Tabela denominada BufferCacheDatabaseTable.

Gerando o “Buffer Cache Database”

Para criarmos nosso ambiente, vamos utilizar o Código denominado Parte 2, conforme apresenta abaixo:

— Parte 2 – Criando Ambiente para Gerar Buffer Cache Databae –

Create Database BufferCacheDatabase

Go

 

Use BufferCacheDatabase

Go

 

Create Table BufferCacheDatabaseTable

(BufferID Int Identity Primary Key Clustered,

BufferDescription Varchar(50) Default ‘Buffer Table’,

BufferDateTime DateTime Default GetDate())

Go

 

Observação: Vale ressaltar que este banco de dados esta criado com o tamanho de 3 Megabyte, pois o banco de dados de sistema Model existente no meu Servidor SQL Server, esta configurado neste momento justamente com este tamanho de arquivo, que representa o tamanho mínimo, sendo assim, os novos bancos de dados criados de forma rápida vão herdar esta configuração.

Legal, legal, nosso ambiente já esta criado e pronto para começarmos a brincar!!!! O próximo passo será obter o quando de Buffer Cache foi gerado após a criação deste novo banco de dados, onde vamos somente consultar o quantidade de cache deste Banco, para isso, iremos utilizar o Código denominado Parte 3.

— Parte 3 – Consultando o “Buffer Cache Database” gerado para este novo Banco –

Select DB_NAME(database_id) As ‘Database’,

COUNT(*) * 8 / 1024.0 As ‘Cached Size (MB)’

From sys.dm_os_buffer_descriptors

Where database_id = DB_ID()

Group By DB_NAME(database_id)

 

Ao executar este código, o SQL Server deverá retornar a quantidade de Buffer Cache, criado e alocado para o Banco de Dados: BufferCacheDatabase, conforme apresenta a Figura 3 a seguir:

Buffer03

Figura 3 – Total de “Buffer Cache Database” alocado para o Banco de Dados – BufferCacheDatabase.

Muito bem, como eu destaquei anteriormente, uma simples transação de criação de um novo banco de dados gera Buffer, neste caso nosso banco de dados esta apresentando o Buffer Cache de 1.304 MBs, isso nos faz pensar o quanto o SQL Server tem que trabalhar para controlar e manter em memória este recurso.

Nossa caminhada ainda não acabou mas esta quase lá, na sequência vamos obter as informações sobre “Buffer Cache Database” no nível de Tabela, onde nossa tabela: BufferCacheDatabaseTabela estará vazia e posteriormente após algumas transações de Inserção de dados.

Comecaremos pela Inserção de 2000 linhas de registro na tabela BufferCacheDatabaseTable, utilizando o Código denominado Parte 4, na sequência vamos consultar total estrutura de páginas de dados que é será alocado para esta tabela, o que nos dará mais comprovações do quanto o “Buffer Cache Database” é mantido pelo SQL Server.

— Parte 4 – Inserindo 2000 linhas na tabela BufferCacheDatabaseTable —

Insert Into BufferCacheDatabaseTable Default Values

Go 2000

 

Como você percebeu acabamos de inserir 2000 linhas de registro em nossa tabela, de forma simples e rápida, agora vamos consultar as informações sobre as páginas de dados que forma esta tabela, começando Código denominado Parte 5, onde teremos alguns detalhes, como quantidade de páginas de dados utilizadas e total de página de dados. A Figura 4, ilustra o resultado do processamento deste código.

— Parte 5 – Consultando informações sobre as páginas de dados –

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Go

Note que estamos utilizando as Visões de Sistema:

  • Sys.tables;
  • Sys.partitions; e
  • Sys. Allocations_Units.

Caso você queira saber mais sobre estas Views de Sistema, consulte as Referências no final deste artigo.

Buffer04

Figura 4 – Informações sobre as páginas de dados que forma a tabela BufferCacheDatabaseTable.

Perfeito, neste momento temos boa parte das informações do nosso ambiente, inclusive alguns detalhes sobre as páginas de dados. Mas se observarmos com cuidado a Figura 4, como podemos ter o Total de Páginas de Dados com o valor 17 se a coluna Páginas de Dados esta com o valor de 10?

Será que existe alguma coisa de errado? De forma alguma, este valor esta correto, esta diferença existe porque a coluna data_pages existente na System View: sys.allocation_units, apresenta como valor para quantidade de páginas de dados, somente as páginas que forma a tabela, excluíndo deste valor páginas de índice internas e páginas do gerenciamento de alocações. Por isso este valor, então podemos dizer que nossa tabela possui:

  • O total de 17 páginas de dados considerando páginas para índices e alocações internas; e
  • O Total de 10 páginas somente para armazenamento de dados que forma a tabela.

Para deixar mas claro esta constatação, vamos utilizar o Código denominado Parte 6, onde será possível ver todo detalhamento e distribuição das alocações de páginas de dados, registros por páginas e espaço livre em bytes para armazenamento de dados em cada página, conforme ilustra a Figura 5:

— Parte 6 – Detalhamento da Alocação de Dados por Página de Dados –

Select ST.Name As ‘Tabela’,

SB.page_id As ‘Id Página’,

SB.page_type As ‘Tipo da Página’,

SB.Row_Count As ‘Linhas por Página’,

SB.free_space_in_bytes As ‘Espaço Livre em Bytes’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Order By SB.page_type Asc

Go

 Buffer05

 

 

 

 

 

Figura 5 – Distribuição de dados alocados por página de dados, quantidade de linhas e espaço livre disponível.

Prontinho, sensacional, aqui esta a toda estrutura de alocação e distribuição de dados em suas respectivas páginas de dados que forma a nossa tabela BufferCacheDatabaseTable.

 

Ufa, é isso, conseguimos chegar vivos e salvos ao final de mais esta caminhada. Vou encerrar por aqui mais este artigo, as demais análises e observações vou deixar para você. Um detalhe importante, repita novamente a execução do Código Parte 3 e verifique que o valor calculado do “Buffer Cache Database” provavelmente foi alterado.

Conclusão

Falar deste tipo recurso que na verdade não existe no Microsoft SQL Server, mas esta presente devido a maneira que as páginas de dados são alocadas em memória pode parecer ser fácil, o duro o conseguir encontrar uma forma de demonstrar como ele acontece, de que forma o “Buffer Cache Database” é criado, gerado, alocado e mantido.

Como destacado o grande segredo de qualquer tipo de Cache é saber a forma correta de armazenar o dado ou conjunto de dados para que tanto o Software como também o Hardware não sejam impactos. O “Buffer Cache Database” faz justamente isso de uma forma muito simples, prática e elegante, além disso, ele nos permite saber e entender como o mesmo esta ocorrendo. Em conjunto com o grande conjunto de Visões de Sistemas e Visões de Gerenciamento Dinâmico existentes no SQL Sever desde a versão 2005, este caminho muito ainda mais tranquilo.

Trabalhando de forma invisível como os demais recursos de Buffer, o “Buffer Cache Database”, oferece para o Database Engine e também para toda estrutura de armazenamento de dados do SQL Server, a flexibilidade para pode ser trabalhar com objetos recêm criados, como também, com aqueles que estão em uso ou foram utilizados em um determinado período de tempo, com isso, podemos ter alguns ganhos de performance na busca por dados e funcionalidades do nosso dia-á-dia.

Entender o Buffer Pool e também o Buffer Cache Database é de grande importância para qualquer Administrador de Banco de Dados, pois ambos estão diretamente trabalhando com a memória existente tanto do SQL Server e também o Windows.

É neste cenário que o “Buffer Cache Database” faz o seu papel, instânciado porções de memória para alocação de seus objetos, neste caso, páginas de dados e índices para o melhor funcionamento do SQL Server.

Referências

– sys.tables: http://msdn.microsoft.com/pt-br/library/ms187406.aspx

– sys.allocation_units: http://msdn.microsoft.com/en-us/library/ms189792.aspx

– sys.dm_os_buffer_pool_extension_configuration: http://msdn.microsoft.com/en-us/library/dn133204.aspx

– sys.partitions: http://msdn.microsoft.com/en-us/library/ms175012.aspx

– sys.dm_os_buffer_descriptors:  http://msdn.microsoft.com/en-us/library/ms173442.aspx

——————————————————————————————————————————-

Agradeço a sua visita, espero que você tenha gostado e que este conteúdo possa te ajudar.

Um grande abraço.

Nos encontramos em breve.

Até mais.