Fala pessoal, hoje vamos continuar na serie de estruturas internas, no ultimo post falei sobre as duas formas de organização de uma tabela Heap or BTree(HOBT), hoje vamos falar sobre as unidades de alocações(Allocation Units).
No ultimo post falei que uma tabela pode estar estruturada no modelo HEAP ou Clustered Index (BTree), as unidades de alocações são utilizadas para gerenciar os dados dentro das estruturas HEAP ou BTree, as unidades de alocações dividem os dados de acordo com seu tipo em suas respectivas paginas, podendo ser dividida em três tipos de alocação:
IN_ROW_DATA
LOB_DATA
ROW_OVERFLOW_DATA
Imagem retirada do BOL(Books Online).
Conforme a ilustração acima, uma tabela consiste de uma ou mais partições, cada partição pode ser organizada como uma HEAP ou Clustered Index (BTree) e os dados são divididos em 3 unidades dentro de uma HEAP ou BTree.
IN_ROW_DATA – Conjunto de paginas de dados e índices, paginas com esse tipo armazenam os dados em si e informações de índices como os níveis intermediários de índices.
LOB_DATA – LOB(Large Objects) Conjunto de paginas para armazenar grandes objetos, tipos de dados que utilizam essa unidade de alocação são: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT).
ROW_OVERFLOW_DATA – Quando colunas de tamanho variáveis (varchar, nvarchar, varbinary, or sql_variant) excedem o tamanho de 8060 bytes que é o tamanho suportado para uma linha, um ponteiro de 24 bytes é criado no lugar dessa linha e essa informação é jogada para outra pagina de dados do tipo ROW_OVERFLOW_DATA.
Vamos ver isso na pratica então, abaixo script para preparar o ambiente de teste.
USE master GO IF(SELECT DB_ID(‘DB_TESTE’)) IS NOT NULL BEGIN DROP DATABASE DB_TESTE END GO CREATE DATABASE DB_TESTE GO USE DB_TESTE GO IF(SELECT OBJECT_ID(‘DADOS’)) IS NOT NULL BEGIN DROP TABLE DADOS END GO CREATE TABLE DADOS (CAMPO1 INT PRIMARY KEY IDENTITY,CAMPO2 VARCHAR(5000) DEFAULT(REPLICATE(‘A’,5000)),CAMPO3 VARCHAR(5000) DEFAULT(REPLICATE(‘A’,5000)),CAMPO4 VARCHAR(MAX) DEFAULT(REPLICATE(‘B’,5000))) GO INSERT INTO DADOS DEFAULT VALUES GO 5
Criamos então um banco de dados chamado DB_TESTE e uma tabela chamada DADOS que possui 4 CAMPOS, essa tabela esta na estrutura de Clustered Index(BTree), logo após fazendo Insert de 5 linhas.
Na estrutura da nossa tabela podemos notar que a nossa linha irá ultrapassar o limite de 8060 bytes, CAMPO1 = 4 Bytes CAMPO2 = 5000 bytes e CAMPO3 = 5000 bytes, total = 10004 bytes, nossa coluna CAMPO4 não entra no calculo por ela ser do tipo LOB(Large Objects) ela é automaticamente direcionada para sua unidade de alocação LOB_DATA, vale lembrar que nossos campos VARCHAR(5000) são variantes ou seja se eu inserir apenas 1 carácter ele irá ocupar apenas 1 byte e não iremos ver o split de IN ROW para OVERFLOW, por isso deixei como DEFAULT o valor de 5000 caracteres.
Essa imagem acima mostra as unidades de alocação da nossa tabela logo após o comando CREATE TABLE, podemos ver que as unidades de alocação já são criadas mesmo sem nenhum dado para essa tabela.
Após a inserção de 5 linhas já podemos ver que nossas unidades de alocação estão populadas, assim podemos ver que as paginas são agrupadas de acordo com seu tipo.
Com a ajuda do ‘DBCC IND’ podemos ver mais a fundo, detalhe para as setas em vermelho onde mostra que temos uma pagina IAM para cada tipo de alocação, formando uma cadeia de paginas IAM, elas são duplamente ligadas entre si.
Podemos ver que no tipo de IN_ROW_DATA temos 7 paginas, sendo 5 paginas de dados (nível folha do índice) 1 pagina IAM e 1 pagina de índice (Nível Raiz). No ROW_OVERFLOW_DATA temos 6 paginas, sendo 5 paginas que contem os dados e 1 pagina IAM. no LOB_DATA temos também 6 paginas, 1 IAM e 5 paginas com os dados.
Com a ajuda do comando ‘DBCC PAGE’ olhando dentro de uma paginas de dados podemos ver que no CAMPO3 não temos os dados e sim um ponteiro de 24 bytes que aponta para a pagina 293 como podemos ver acima no ‘RowId = (1:293:0)’, através da coluna ‘TimeStamp’ ele poderá encontrar a informação nesta pagina que esta em uma unidade de alocação ROW_OVERFLOW_DATA lá iremos encontrar os dados do CAMPO3, a mesma coisa para o CAMPO4 do tipo LOB.
Realizando um Select na tabela DADOS com um Where para trazer apenas a linha com CAMPO1 = 3, podemos ver o Plano de execução onde temos um CLUSTERED INDEX SEEK, olhando o numero de leituras com a ajuda do SET STATISTICS IO, temos 2 leituras logicas e 3 leituras de LOB.
O que o SQL Server esta fazendo internamente é:
Lê a pagina RAIZ do Índice (pagina 301), aqui ele é direcionado para a pagina folha.
Lê a pagina FOLHA do índice (pagina 305), neste momento ele descobre que não possui todas as colunas aqui, pois estão em outra unidade de alocação, até aqui 2 leituras logicas.
Lê a pagina 303(ROW_OVERFLOW_DATA ), 1 leitura LOB.
Lê a pagina IAM 296 (LOB_DATA) e depois a pagina 304(LOB_DATA), 2 leituras LOB, assim formando as 3 leituras LOB.
Bom mas se eu não precisar de todas as colunas posso economizar Reads.
Podemos ver que colocando apenas o CAMPO1 no SELECT ele realiza apenas 2 leituras logicas que é referente ao índice(paginas RAIZ e FOLHA).
Se eu colocar o CAMPO1 e CAMPO3 ele me retorna as 2 leituras logicas e mais 1 leitura LOB referente ao ROW_OVERFLOW_DATA.
Então se atente aos SELECTS que possuem (*), na maioria das vezes você não precisa de todas as colunas, evitando leituras desnecessárias.
Esses conjuntos de alocações (Allocation Units) permitem o SQL Server manter sua organização interna e manter um tamanho fixo padrão de 8 kb para suas paginas.
Bom galera é isso, hoje vimos um pouco mais sobre a estrutura de uma tabela, entendendo como ela é organizada internamente e como o SQL faz para buscar as informações navegando pela estrutura BTree, espero que tenham gostado e até a próxima;
Referencias:
Table and Index Organization
Reginaldo Silva