Script Challenge – 17 – A resposta

Salve comunidade e amantes do Microsoft SQL Server.

Tudo bem? Que bom te encontrar por aqui.

Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2020 – Post 17, sendo este respectivamente o sexto post após o retorno desta desafiadora sessão denominada 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 anteriormente. 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 17….


Script Challenge 17

Falando do desafio de número 17, o mesmo foi publicado no mês de junho de 2020, período de data em que praticamente todos nós estamos começando a se preparar para as férias de meio de ano, mas este ano de 2020 as coisas não estão sendo bem assim, seguindo os padrões tradicionais, estamos vivendo o tal do “novo normal”, tudo esta diferente, parece que estamos vivendo cada dia em pequenos pedaços, pequenos fragmentos (huummm aqui já tem um pequena dica da resposta do desafio, kkkk….).

No post de apresentação deste desafio, destaquei que o mesmo estava relacionado a uma importante atividade desempenha pelos DBAs e Administradores de Servidores de forma em geral.

Como de costume deixei algumas dicas para tentar ajudar a encontrar de forma rápida uma possível resposta:

  1. O mesmo, pode ser executado em conjunto com as demais sessões, transações ou querys em execução, sem necessitar que qualquer procedimento de manutenção ou alterações na configuração do SQL Server para sua execução, como também, esta totalmente relacionado com o que venha ou possa estar sendo processado pelo sistema operacional que necessite de dados do SQL Server naquele exato momento; e
  2. Observe as colunas dbpages e used, elas podem contar a resposta para este desafio, pois estão relacionadas com as estruturas que utilizamos em nossos bancos de dados.

Continuando nossa história, quero lhe perguntar: E ai já matou a charada? Acho que ainda não! Não é mesmo?

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 que foram ocultadas anteriormente, 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 17.

E ai, revendo novamente o código publicado no post de abertura deste desafio, você conseguiu checar a alguma conclusão?

Bom chegou a hora de revelar o que exatamente este bloco de código se propõem a fazer, chegou o momento de desvendar este desafio. A seguir apresento a resposta para o Script Challenge 17 e o todo bloco de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Como você pode ter percebido, no decorrer deste post e também no post de lançamento, procurei de uma forma simples mas não explícita exibir ao longo do texto, pequenas dicas que poderiam ajudar, nortear e quem sabe indicar uma possível resposta ou solução para o desafio.

De forma mais direta, a resposta para o Scritp Challenge 17, se entrelaça com o que manipulamos diretamente em nossos bancos de dados, faço referência aos tradicionais procedimentos de Insert, Update e Delete, os quais são considerados por muitos DBAs o conjunto de comandos mais utilizados em um ambiente de CRUD se combinados com o comando Select.

Os quais justamente podem ao longo de sua constante utilização provocar a chamada fragmentação de dados em relação as páginas de dados que armazenam os respectivos valores existentes em nossas colunas, elementos que compõem a estrutura física e lógica das tabelas e índices existentes nos bancos de dados.

Ao longo de anos venho sempre apresentando diversos posts relacionados a fragmentação, desfragmentação, taxas de alocação de dados, distribuição de páginas para tabelas e índices, este desafio consiste mais uma vez em fortalecer e ilustrar como podemos obter possíveis respostas para estes fatos e acontecimentos.

Abaixo apresento o script original que ilustra esta capacidade que o Microsoft SQL Server em conjunto com suas visões de sistema  e funções de gerenciamento dinâmico nos auxilia na obtenção destes dados:

— Script Challenge 17 – A resposta – Identificando a Média do Tamanho da Fragmentação por páginas de dados para cada tabela —

Select Object_Name(SSI.object_id) As ‘Objeto Pai’,
SI.Name +
Case SSI.Type
When 0 Then ‘Heap’
When 1 Then ‘Clustered’
When 2 Then ‘NonClustered’
When 3 Then ‘XML’
When 4 Then ‘Espacial’
End As ‘Descrição’,
SI.dpages As ‘Págs. por Dados Alocados em Índices’,
SI.used As ‘Págs por Tabelas + Índices Alocadas’,
SI.rows As Linhas,
Round(SIPS.avg_fragment_size_in_pages,2) As ‘Média do Tamanho de fragmentação por página’
From sys.sysindexes SI Inner Join Sys.indexes SSI
On SI.Name = SSI.Name
Inner Join sys.dm_db_index_physical_stats(DB_ID(‘Aulas23092011’),Null,Null,Null, ‘LIMITED’) SIPS
On SSI.object_id = SIPS.object_id
Where SI.Name Is Not Null
And SIPS.avg_fragmentation_in_percent > 10.0 AND SIPS.index_id > 0
Order By [Média do Tamanho de fragmentação por página] Desc
Go

Então, agora você deve ter gostado deste desafio, não é verdade? Saber que existe a possibilidade de identificar a quantidade de páginas alocadas para nossa tabelas e índices, em adicional os indicadores de média do tamanho da fragmentação, média da fragmentação em porcentagem, média do tamanhos dos registros, entre outros indicadores importantes.

Figura 2 apresentada abaixo, ilustra o conjunto de dados retornados após a execução do Script Challenge – 17:
Figura 2 – Média do Tamanho de Fragmentação por página.

Dentre o conjunto de colunas retornadas pelo Script Challenge 17, destaco algumas:

  • Págs. Por Dados Alocados em índices: Conhecida como dpages, pertencente a visão de sistema sys.sysindexes, a qual tem o objetivo de retornar em tela a contagem de páginas de dados utilizadas por índices ou tabelas, de acordo com o valor apresentado para a colunad IndId, se a mesma for = 0, 1 ou > 1.

  • Págs por Tabelas + Índices Alocadas: Conhecida como used, pertencente a visão de sistema sys.sysindexes, utilizada para retornar em tela a contagem total de páginas usada para todos os índices em combinação com as páginas da própria tabela. de acordo com o valor apresentado para a colunad de acordo com o valor apresentado para a colunad IndId, se a mesma for = 0, 1 ou > 1., se a mesma for = 0, 1 ou > 1.

Importante ressaltar que o resultado retornado por ambas as colunas dpages e used, podem apresentar variáveis ou até mesmo um estouro no limite dos dados apresentados.

Enfim, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 17, fico honrado, feliz por ter conseguido compartilhar este conteúdo com vocês.

Espero que você tenha gostado deste novo post da sessão Script Challenge!


Sua Participação

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

Desta vez não houve uma opção mais votado, tivemos um empate com 40% dos votos válidos entre as opções:

  • “O bloco de código apresentado se relaciona com análise sobre fragmentação de dados em uma tabela.”
  • “O bloco de código apresentado se relaciona com análise sobre fragmentação de dados em um índice.”

Este é um indicador interessante que mostra que os visitantes e participantes deste post, buscaram tentar elaborar o código em seus ambientes como forma de prática e aprendizado, antes de ficar “chutando” as possíveis opções de resposta.

Agradeço a todos que participaram e ajudaram a tornar este post ainda melhor.

Referências

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysindexes-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysindexes-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

Posts anteriores

Caso esta seja a primeira vez que você acessa um post desta sessão, fico muito feliz e aproveito para compartilhar os posts mais recentes:

https://pedrogalvaojunior.wordpress.com/2020/06/25/script-challenge-2020-post-17/

https://pedrogalvaojunior.wordpress.com/2019/10/26/script-challenge-2019-post-16/

https://pedrogalvaojunior.wordpress.com/2019/06/26/script-challenge-15-a-resposta/

https://pedrogalvaojunior.wordpress.com/2019/02/13/script-challenge-2019-post-15/

https://pedrogalvaojunior.wordpress.com/2018/10/06/script-challenge-14-a-resposta/

Agradecimentos

Obrigado por sua visita, espero que este conteúdo aqui apresentado como um possível “desafio” possa ser útil 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 fevereiro de 2021 em mais um post da sessão Script Challenge.

Até mais….