Blog – Fabiano Neves Amorim

Extents são unidades de alocação de dados do SQL Server, para cada extent podemos ter 8 pages de dados.

Se você achar melhor antes de continuar a leitura deste post leia um pouco mais sobre extents no artigo que escrevi estes dias.

 

Vamos analisar mais um comportamento bem interessante da Engine do SQL Server.

 

Os dados das tabelas de um Banco de Dados o SQL Server ficam alocados em extents e dentro de cada extent temos 8 pages, o dado físico que você escrever em uma tabela fica salvo nestas pages que por sua vez estão nos extents.

As vezes fica um pouco confuso entender estes tal de Pages e Extents, para tentar simplificar vamos imaginar o seguinte, um livro(file do SQL Server), dentro de um livro temos várias páginas(Pages) e cada página está dentro de um determinado capítulo(extents), o SQL Server é trabalha exatamente como este livro para armazenar os dados.

 

Voltando ao assunto da Engine, o interessante na história é que quando os dados de uma tabela são apagados e novos dados são inseridos o SQL Server não reutiliza os extents que não estão mais sendo utilizados, isso por questão de Performance, o SQL é otimizado para alocar novos extents ao invés de ficar dando manutenção nos extents que seus dados  foram anteriormente excluídos.

 

Para cada arquivo do SQL Server ele mantém um ponteiro dizendo qual é o próximo extent livre, sendo assim ele simplesmente vai no ponteiro e aloca os dados neste extent. E após alocar os dados ele atualiza este ponteiro com a informação do próximo extent disponível. O SQL só irá marcar este ponteiro com a informação do Extent que teve seus dados apagados quanto ele chegar no fim do arquivo do SQL ou então quando não conseguir mais alocar extents por falta de espaço no arquivo ou ocorrer um Auto-Grow por exemplo um ShrinkDatabase.

 

A razão para este comportamento é que se o SQL tiver que ficar voltando para verificar quais extents estão vazios ele iria perder muito mais tempo do que simplesmente alocar outro novo extent e também por questões de fragmentação já que um extent no inicio do arquivo seria utilizado logo depois outro extent no final do arquivo depois outro no meio e por ai vai…

 

Chega de blablabla e vamos ver um pouco de código para comprovar este comportamento:

 

USE Master

 

— Caso exista um banco chamado DBTesteExtent, apaga ele.

IF (SELECT DB_ID(‘DBTesteExtent’)) IS NOT NULL

BEGIN

  USE Master

  ALTER DATABASE DBTesteExtent SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  DROP DATABASE DBTesteExtent

END

GO

 

— Criar um banco de dados chamado DBTesteExtent

IF (SELECT DB_ID(‘DBTesteExtent’)) IS NULL

BEGIN

  CREATE DATABASE DBTesteExtent

END

GO

 

USE DBTesteExtent

 

— Vamos criar uma tabela para utilizar nos testes.

CREATE TABLE TesteExtent(ID Int Identity(1,1),

                         Nome char(8000) default ‘8000 Bytes’)

GO

 

— Vamos inserir uma linha na tabela para verificar as pages e extents que foram alocadas

INSERT INTO TesteExtent DEFAULT VALUES

 

— Utiliza a unDocumented Database Console Commands for SQL Server – DBCC EXTENTINFO

— Podemos visualizar que foi alocada uma page de dados em um extent misto

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

 

— Vamos inserir mais dados para forçar a alocação de um extent uniforme

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

 

— Podemos visualizar que foram alocadas várias páginas e 2 extents

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

— Agora vamos apagar os dados da tabela e verificar novamente os extents

DELETE FROM TesteExtent

 

— Os extents continuam aparecendo, assim como a informação do tamanho da tabela.

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

GO

EXEC sp_spaceUsed TesteExtent

 

— Vamos criar uma nova tabela e inserir alguns registros nela,

— repare que os extents da tabela TesteExtent não são reutilizados

— na verdade o SQL aloca novo extents para a tabela.

CREATE TABLE TesteExtent2(ID Int Identity(1,1),

                          Nome char(8000) default ‘8000 Bytes’)

GO

 

— Vamos inserir uma linha na tabela para verificar as pages e extents que foram alocadas

INSERT INTO TesteExtent2 DEFAULT VALUES

 

— Repare que o page_id é diferente dos page_id da tabela TesteExtent

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent2’,-1)

GO

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

— Ao executar o SHRINKDATABASE os extents da tabela TesteExtent são apagados.

— e a informção do tamanho é atualizada

DBCC SHRINKDATABASE (DBTesteExtent);

 

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

GO

EXEC sp_spaceUsed TesteExtent

GO

 

— Vamos inserir mais dados para forçar a alocação de um extent uniforme na tabela TesteExtent2

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

 

— Verifique que os page_id que pertenciam a tabela TesteExtent foram reutilizados.

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent2’,-1)

 

Fonte – How It Works: SQL Server Page Allocations

_________________________________________________________________
Fabiano Neves Amorim – MCITP DBD / MCTS / MCP – SQLServer

* fabiano@cnpm.com.br – http://fabianosqlserver.spaces.live.com/