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, 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…
Figura 1 – Código Transact-SQL apresentado no Script Challenge 14.
Figura 2 – Informações relacionadas a estimativa de tamanho do arquivo de backup e espaço livre em disco em megabytes.
Junior Galvão - MVP - Data Platform 