Fala galera, hoje vou falar de um caso muito legal, muita gente já passou por isso e as vezes não soube o porque de acontecer esse fenômeno, em algum momento você deve ter encontrado índices bem fragmentados em seu ambiente, fragmentações como 99% fragmentado e após ao rodar a rotina de Rebuild ele ficou com 70% ainda, um numero alto para fragmentação de índice, e você rodou novamente o rebuild desse índice e ele foi para 80%, rodou novamente ele foi para 65%, um tanto quanto estranho não acha ?
Bom a resposta pra isso provavelmente vai ser que esse índice é bem pequeno, mas pequeno quanto?
Para isso precisamos entender um pouco das estruturas internas do SQL Server, conhecer um pouco sobre Extents, páginas de controle GAM, SGAM, IAM e PFS.
Quando criamos uma tabela ou índice eles são armazenados em páginas de 8Kb, extent é uma unidade composta por 8 páginas de 8Kb logo 64Kb, no SQL Server podemos ter 2 tipos de extent sendo elas extent uniforme e mista.
Extents mista são composta por 8 páginas porém logicamente sem nenhuma ligação, elas podem ser compartilhadas por até 8 objetos diferentes até o objeto crescer e poder se alocar em uma estrutura de extents uniforme.
Extents uniforme é o conjunto de 8 páginas de 8Kb dedicado a um único objeto apenas.
Podemos ver então que para objetos pequenos compensa bastante o modelo de Extent mista, por exemplo uma tabela de configurações que tem poucas linhas pode estar armazenado em uma extent mista ocupando apenas 1 pagina de 8Kb, caso não existisse esse modelo ela estaria ocupando uma extent inteira 8 páginas 64 Kb sem ter necessidade.
Entendemos aqui um pouco sobre extents, precisamos saber que as extents mistas são mapeadas pela página de controle chamada SGAM(Shared Global Allocation Map) e as extents uniforme são mapeadas pelas páginas de controle GAM(Global Allocation Map).
Tudo isso é mapeado pela página de controle chamada IAM(Index Allocation Map), ambas páginas de controle IAM, GAM e SGAM podem rastrear até 4 GB de páginas.
Bom não detalhei tanto sobre essas páginas e estruturas pois vou deixar uns links de referencia para leitura do post não ficar tão extenso e virar uma leitura massacrante.
Bom vamos ao que interessa, como tudo isso influência no rebuild do índice ?
Vamos montar uma demo.
USE MASTER GO IF(SELECT DB_ID(‘TESTEINDICE’)) IS NOT NULL BEGIN ALTER DATABASE TESTEINDICE SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE TESTEINDICE END GO CREATE DATABASE TESTEINDICE GO USE TESTEINDICE GO CREATE TABLE TBINDEX1 (CAMPO1 INT DEFAULT RAND() * 10000000, CAMPO2 INT DEFAULT RAND() * 10000000, CAMPO3 CHAR(50) DEFAULT(‘AA’)) GO CREATE INDEX IDX_TBINDEX1 ON TBINDEX1(CAMPO1) GO INSERT INTO TBINDEX1 DEFAULT VALUES GO 5000
Acima script para simular nosso ambiente de teste, podemos ver que estamos inserindo 5000 registros aleatórios para gerar um pouco de fragmentação no índice.
Vamos utilizar a SP_SHOWINDEX para ajudar na análise.
Podemos ver uma fragmentação de 93,75% sobre 16 páginas, vamos cavar um pouco mais fundo então.
SELECT O.NAME AS TABLE_NAME,P.INDEX_ID, I.NAME AS INDEX_NAME , AU.TYPE_DESC AS ALLOCATION_TYPE, AU.DATA_PAGES, PARTITION_NUMBER FROM SYS.ALLOCATION_UNITS AS AU JOIN SYS.PARTITIONS AS P ON AU.CONTAINER_ID = P.PARTITION_ID JOIN SYS.OBJECTS AS O ON P.OBJECT_ID = O.OBJECT_ID JOIN SYS.INDEXES AS I ON P.INDEX_ID = I.INDEX_ID AND I.OBJECT_ID = P.OBJECT_ID WHERE O.NAME = N’TESTEINDICE’ OR O.NAME = N’TBINDEX1′ ORDER BY O.NAME, P.INDEX_ID; GO DBCC IND (‘TESTEINDICE’, ‘TBINDEX1’ , 1); DBCC IND (‘TESTEINDICE’, ‘TBINDEX1’ , 2); — TRACE PARA MOSTRA O DBCC NA GRID DO SQL SERVER DBCC TRACEON (3604); DBCC PAGE (TESTEINDICE, 1, 90, 3);
Com o primeiro SELECT podemos ver as seguintes informações:
Nossa tabela (HEAP) possuí 44 páginas de dados e nosso índice não cluster possuí 16 páginas de dados. Um pouco mais afundo com DBCC.
DBCC IND (‘TESTEINDICE’, ‘TBINDEX1’ , 2);
Com ajuda do DBCC IND podemos identificar qual é a página IAM que controla esse índice, vamos mais afundo então.
DBCC TRACEON (3604); DBCC PAGE (TESTEINDICE, 1, 89, 3);
Com a ajuda do DBCC PAGE podemos investigar mais afundo e descobrir que nosso índice alocou as primeiras 8 páginas como mistas logo quando não cabia mais ele alocou 2 extents uniforme, como já sabemos extents mista são um conjunto de 8 paginas que podem conter até 8 objetos (tabela, índice) , sendo assim a desfragmentação desse cara é realmente desnecessária, pois são poucas páginas e podendo conter objetos diferentes.
Vamos rodar um Rebuild nesse índice.
ALTER INDEX IDX_TBINDEX1 ON dbo.TBINDEX1 REBUILD
Com a ajuda da nossa amiga SP_SHOWINDEX podemos ver novamente a fragmentação do índice, pra garantir rodei 3 vezes o Rebuild mesmo assim continua 50% fragmentado.
No print anterior vimos que o índice ainda possuí 12 páginas, olhando novamente ná página IAM conseguimos ver que ele ainda esta alocando páginas em uma extent mista, e apenas 1 extent uniforme agora, bom isso desmistifica o porque da fragmentação do índice ainda estar alta, enquanto tivermos uma alocação em extent mista sempre existira um pouco de fragmentação, essa fragmentação quero explicar no próximo post, mas ela é analisada com base nos níveis folha do índice, ela olha para as páginas onde a próxima página não é a sequência (exemplo, página 50 a próxima pagina deveria ser a 51, se a próxima página for a 75 sabemos que o índice não está 100% desfragmentado).
Vamos inserir um pouco mais de registros.
Com o índice bem fragmentado e agora com 51 páginas vamos dar um outro Rebuild nele.
ALTER INDEX IDX_TBINDEX1 ON dbo.TBINDEX1 REBUILD
Podemos ver nossa fragmentação em 0%, mas porque só depois de inserir 10000 registros dar um Rebuild ?
Podemos ver que a quantidade de página do nosso índice após o Rebuild esta com 34 páginas, bom será que ainda vale a pena utilizar uma extent mista com 34 páginas ?
Aparentemente para o SQL Server não vale a pena, podemos ver que agora nosso índice não esta mais alocando uma extent mista e esta diretamente alocando 2 extent uniforme.
Com isso podemos ver que as extents mistas impedem de termos um índice 0% fragmentado, isso não significa que isso é uma coisa ruim, simplesmente seu índice é pequeno e uma alocação em extent mista não irá afetar sua performance assim como uma fragmentação de 90% afetará tão pouco nesse caso, bom gente como sempre dizemos tudo depende nessa nossa área, é claro que cada caso é um caso e precisa ser analisado bem, nesse caso não existe uma recomendação especifica mas eu começaria a me preocupar com índices com pouco mais de 100 páginas que ainda sim parece ser um número pequeno, mas como disse ‘EU’, não é uma via de Regra, cada caso é um caso.
Dica:
No SQL Server 2016 o TraceFlag 1118 já vem ativo por default, então esse tipo de ocasião não acontece, caso queira simular no SQL Server 2016 precisa alterar uma propriedade no banco de dados.
ALTER DATABASE TESTEINDICE SET MIXED_PAGE_ALLOCATION ON
Esse comando seria equivalente a desabilitar o traceflag 1118 em outras versões posteriores.
Pessoal sei que é um assunto um tanto quanto complexo, espero ter explicado em detalhes, mas se ficou dúvida sobre qualquer estrutura deixe seu comentário, me envie um e-mail, pode me chamar sem problemas, as vezes escrevemos coias que são claras pra nós, porém escrevendo é bem difícil de explicar detalhadamente, em posts futuros quero gravar alguns videos.
Bom muito obrigado pela sua atenção e até a próxima.
Referencias :
Inside the Storage Engine: Anatomy of an extent http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-an-extent/ Misconceptions around TF 1118 http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/ Inside the Storage Engine >> Inside the Storage Engine: IAM pages, IAM chains, and allocation units http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/ SQL Server 2016: The Death of the Trace Flag https://www.brentozar.com/archive/2016/03/sql-server-2016-death-trace-flag/
Reginaldo Silva