Desperdício de espaço das tabelas heap



Durante a execução de alguns scripts eu, acidentalmente, me deparei com um comportamento que não esperava ver em tabelas heap. Acompanhem o script para entender como o SQL Server preenche suas páginas com a inserção de novos registros.


 


USE MASTER


GO


 


IF EXISTS (SELECT * FROM SYSDATABASES WHERE [Name] = 'Inside')


BEGIN


                DROP DATABASE Inside


END


GO


 


CREATE DATABASE Inside


/*


ON


(


Name = 'Inside_data',


FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_data.mdf',


Size = 10MB,


FileGrowth = 5MB,


MaxSize = UNLIMITED


)


LOG ON


(


Name = 'Inside_log',


FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_log.ldf',


Size = 5MB,


FileGrowth = 3MB,


MaxSize = UNLIMITED


)


*/


 


USE Inside


GO


 


/*


                Cria a tabela heap que será analisada.


*/


IF EXISTS(SELECT * FROM sysObjects WHERE [Name] = 'HeapTable' AND XType = 'U')


BEGIN


                DROP TABLE HeapTable


END


 


CREATE TABLE HeapTable


(


PKIdentity INT NOT NULL IDENTITY(1,1),


Name VARCHAR(100)


)


GO


 


/*


                Cria a tabela com índice cluster que também será analisada.


*/


IF EXISTS(SELECT * FROM sysObjects WHERE [Name] = 'ClusteredTable' AND XType = 'U')


BEGIN


                DROP TABLE ClusteredTable


END


 


CREATE TABLE ClusteredTable


(


PKIdentity INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_ClusteredTable PRIMARY KEY,


Name VARCHAR(100)


)


GO


 


/*


                Gera inforamções de alocação para o ExtentInfo


*/


INSERT INTO HeapTable VALUES (Replicate('L', 100))


go


DBCC EXTENTINFO('Inside', 'HeapTable', 0)


go


/*


file_id     page_id     pg_alloc    ext_size    obj_id      index_id    pfs_bytes         


----------- ----------- ----------- ----------- ----------- ----------- ------------------


1           15          1           1           1977058079  0           0x6100000000000000


*/


 


/*


                Gera inforamções de alocação para o ExtentInfo


*/


INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


go


DBCC EXTENTINFO('Inside', 'ClusteredTable', 1)


go


/*


file_id     page_id     pg_alloc    ext_size    obj_id      index_id    pfs_bytes         


----------- ----------- ----------- ----------- ----------- ----------- ------------------


1           28          1           1           1993058136  1           0x6000000000000000


1           30          1           1           1993058136  1           0x6000000000000000


*/


 


Aqui podemos ver a diferença entre as tabelas, enquanto a heap somente alocou uma página para inserir o registro, a tabela com índice cluster alocou uma página para a raiz do índice e uma página de dados (nível folha). Mapeando essa informação com a sysindexes, podemos ver a diferença entre os campos root e first.


 


select id, indid, root, first, rowcnt  from sysindexes where [id] = object_id ('ClusteredTable')


go


/*


id          indid  root           first          rowcnt              


----------- ------ -------------- -------------- --------------------


1993058136  1      0x1C0000000100 0x1E0000000100 1


 


0x1C = 28


0x1E = 30


*/


 


SELECT dbid FROM master..sysdatabases WHERE [Name] = 'Inside'


-- Neste caso o dbid é 9, cada um que executar esse script deve substituir o número 9 pelo DBID do banco criado


 


DBCC TRACEON (3604)


DBCC PAGE(9, 1, 28, 3)        -- Index page (raiz do índice)


DBCC PAGE(9, 1, 30, 3)        -- Data page (primeira página do nível folha)


 


/*


                Olhando a saída do DBCC PAGE nós podemos aprender alguns detalhes sobre o SQL Server


                m_Type = 1 -> Indica página de dado


                m_Type = 2 -> Indica página de índice


*/


 


Para ver como está a utilização de espaço em cada uma das páginas utilizadas (15, 28 e 30), vamos chegar a página de controle PFS (Page Free Space). Veja uma informação sobre PFS retirado do BOL:


Page Free Space (PFS) pages record whether an individual page in a heap or an ntext, text, or image column has been allocated, and the amount of space free on each page. Each PFS page covers approximately 8,000 pages. For each page, the PFS has a bitmap recording whether the page is empty, 1-50% full, 51-80% full, 81-95% full, or 96-100% full.


 


DBCC PAGE(9, 1, 1, 3)          -- Sempre a segunda página do arquivo é a PFS


/*


                Neste momento temos exatamente o esperado, as páginas alocadas em extents mistos.


 


(1:15)       -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext


(1:28)       -              =     ALLOCATED  0_PCT_FULL                     Mixed Ext


(1:30)       -              =     ALLOCATED  0_PCT_FULL                     Mixed Ext


*/


 


** Agora uma perguntinha intrigante para quem está acompanhando o script... Porque 0_PCT_FULL nas páginas 28 e 30?


Ps: acabamos de ver que existe informação nas páginas 28 e 30


 


INSERT INTO HeapTable VALUES (Replicate('L', 100))


INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


DBCC PAGE(9, 1, 1, 3)


go


 


As páginas 28 e 30 continuam marcadas com 0% full. Não posso negar, estou me divertindo muito com o script...


Agora vamos inserir uma pequena massa de dados nas tabelas...


 


Declare @Contador INT


SET @Contador = 0


 


WHILE @Contador < 63


BEGIN


                INSERT INTO HeapTable VALUES (Replicate('L', 100))


                INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


                SET @Contador = @Contador + 1


END


GO


 


DBCC EXTENTINFO('Inside', 'HeapTable', 0)


DBCC EXTENTINFO('Inside', 'ClusteredTable', 1)


go


 


Até o momento as páginas alocadas inicialmente foram suficientes para alocar todos os registros inseridos. E agora, como será que está o PFS?


 


DBCC PAGE(9, 1, 1, 3)


go


 


/*


(1:15)       -              =     ALLOCATED  95_PCT_FULL                     Mixed Ext


(1:28)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext                  


(1:30)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext


 


                !!! ***** 0% FULL ****** !!!


                Com 65 registros na tabela, sinistro.


*/


 


Bom, agora para de falar esses desaforos para o SQL Server que depois eu explico o que está acontecendo. Senta a bunda nessa cadeira e foca no próximo passo...


 


INSERT INTO HeapTable VALUES (Replicate('L', 100))


INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


DBCC PAGE (9, 1, 1, 3)


go


/*


                (1:14)       - (1:15)       =     ALLOCATED 100_PCT_FULL                     Mixed Ext              


                ESQUEÇE DAS PÁGINAS 28 E 30! Parece que não adianta falar....


*/


 


Nesse momento a página 15 passou para a outra faixa de preenchimento controlado pela PFS, estando entre 95% e 100% preenchida.


Agora analise os cabeçalhos das páginas de dados 15 e 30.


 


DBCC PAGE(9, 1, 15, 3)        -- Index page


/*


PAGE: (1:15)


------------


 


BUFFER:


-------


 


BUF @0x0244ABC0


---------------


bpage = 0x438CE000        bhash = 0x00000000        bpageno = (1:15)


bdbid = 9                 breferences = 1           bstat = 0xb


bspin = 0                 bnext = 0x00000000       


 


PAGE HEADER:


------------


 


Page @0x438CE000


----------------


m_pageId = (1:15)         m_headerVersion = 1       m_type = 1


m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x8000


m_objId = 1977058079      m_indexId = 0             m_prevPage = (0:0)


m_nextPage = (0:0)        pminlen = 8               m_slotCnt = 66


m_freeCnt = 374           m_freeData = 7686         m_reservedCnt = 0


m_lsn = (6:172:2)         m_xactReserved = 0        m_xdesId = (0:0)


m_ghostRecCnt = 0         m_tornBits = 0            


 


Allocation Status


-----------------


GAM (1:2) = ALLOCATED     SGAM (1:3) = NOT ALLOCATED


PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL   DIFF (1:6) = CHANGED


ML (1:7) = NOT MIN_LOGGED


 


.....


(DADOS DA PÁGINA)


*/


 


DBCC PAGE(9, 1, 30, 3)        -- Data page


/*


PAGE: (1:30)


------------


 


BUFFER:


-------


 


BUF @0x0244A600


---------------


bpage = 0x438A0000        bhash = 0x00000000        bpageno = (1:30)


bdbid = 9                 breferences = 1           bstat = 0xb


bspin = 0                 bnext = 0x00000000       


 


PAGE HEADER:


------------


 


Page @0x438A0000


----------------


m_pageId = (1:30)         m_headerVersion = 1       m_type = 1


m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x8000


m_objId = 1993058136      m_indexId = 0             m_prevPage = (0:0)


m_nextPage = (0:0)        pminlen = 8               m_slotCnt = 66


m_freeCnt = 374           m_freeData = 7686         m_reservedCnt = 0


m_lsn = (6:173:2)         m_xactReserved = 0        m_xdesId = (0:0)


m_ghostRecCnt = 0         m_tornBits = 0           


 


Allocation Status


-----------------


GAM (1:2) = ALLOCATED     SGAM (1:3) = ALLOCATED   


PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL   DIFF (1:6) = CHANGED


ML (1:7) = NOT MIN_LOGGED


 


.....


(DADOS DA PÁGINA)


*/


 


Se os cabeçalhos das páginas forem comparados veremos que são praticamente idênticos, tirando uma informação ou outra relacionada ao objeto, log sequence number e número da página, entre outros. Isso indica que ambos os objetos possuem a mesma quantidade de bytes utilizados em suas páginas.


Inserindo mais um registro em cada uma das tabelas, podemos ver uma diferença sensível entre a tabela heap e o índice cluster.


 


INSERT INTO HeapTable VALUES (Replicate('L', 100))


INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


DBCC EXTENTINFO('Inside', 'HeapTable', 0)


DBCC EXTENTINFO('Inside', 'ClusteredTable', 1)


go


 


/*


file_id     page_id     pg_alloc    ext_size    obj_id      index_id    pfs_bytes         


----------- ----------- ----------- ----------- ----------- ----------- ------------------


1           15          1           1           1977058079  0           0x6400000000000000


1           31          1           1           1977058079  0           0x6100000000000000


 


file_id     page_id     pg_alloc    ext_size    obj_id      index_id    pfs_bytes         


----------- ----------- ----------- ----------- ----------- ----------- ------------------


1           28          1           1           1993058136  1           0x6000000000000000


1           30          1           1           1993058136  1           0x6000000000000000


*/


 


Acabamos de ver o SQL Server alocando uma nova página para inserir os registros na HeapTable. Mas se a utilização das duas páginas estava igual, porque o SQL Server não inseriu o registro na página 15 ao invés de alocar uma nova página (31)?


Agora imaginando que o banco de dados continue recebendo dados da aplicação...


 


Declare @Contador INT


SET @Contador = 0


 


WHILE @Contador < 10000


BEGIN


                INSERT INTO HeapTable VALUES (Replicate('L', 100))


                INSERT INTO ClusteredTable VALUES (Replicate('L', 100))


                SET @Contador = @Contador + 1


END


GO


 


DBCC SHOWCONTIG (HeapTable, 0)


go


 


/*


DBCC SHOWCONTIG scanning 'HeapTable' table...


Table: 'HeapTable' (1977058079); index ID: 0, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 153


- Extents Scanned..............................: 25


- Extent Switches..............................: 24


- Avg. Pages per Extent........................: 6.1


- Scan Density [Best Count:Actual Count].......: 80.00% [20:25]


- Extent Scan Fragmentation ...................: 80.00%


- Avg. Bytes Free per Page.....................: 397.7


- Avg. Page Density (full).....................: 95.09%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


DBCC SHOWCONTIG (ClusteredTable, 1)


go


/*


DBCC SHOWCONTIG scanning 'ClusteredTable' table...


Table: 'ClusteredTable' (1993058136); index ID: 1, database ID: 9


TABLE level scan performed.


- Pages Scanned................................: 146


- Extents Scanned..............................: 23


- Extent Switches..............................: 22


- Avg. Pages per Extent........................: 6.3


- Scan Density [Best Count:Actual Count].......: 82.61% [19:23]


- Logical Scan Fragmentation ..................: 0.00%


- Extent Scan Fragmentation ...................: 78.26%


- Avg. Bytes Free per Page.....................: 28.6


- Avg. Page Density (full).....................: 99.65%


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


*/


 


Vendo o resultado do DBCC SHOWCONTIG podemos notar que a tabela HEAP está desperdiçando espaço, deixando de alocar quase 400 bytes por página (o que equivale a quase 5% da página de 8K), o que acredito não ser mera coincidência com o faixa 95%-100%. Fazendo um cálculo superficial estimamos o seguinte desperdício...


 


1)      397,7 - 28,6 = 369,1 bytes (comparado com a tabela clusterizada)


2)      Como acabamos de ver no script acima, cada página da tabela heap terá 66 registros (pode contar, é verdade!!). Então 10.000.000 / 66 = 151.515 páginas


3)      Tabela com 151.515 páginas = 369,1 * 151.515 = 55.924.186 bytes de desperdício


4)      55.924.186 bytes = +/- 53,3 MB


 


Um desperdício de quase 54 MB para uma tabela com 10 milhões de registros não parece muita coisa quando comparado com o índice cluster, mas se somada a outras tabelas heap o desperdício pode ser considerável (imagine um ambiente de data staging). Além disso, temos o problema de manutenção da tabela heap, que não é simples como em um índice cluster, como vemos nessa dica: Rebuilding an Index on a Heap Table (http://www.sqlmag.com/articles/index.cfm?articleid=46468&)


 


Minha conclusão deste cenário é que quando uma página da tabela heap fica com preenchimento entre 95% e 100% (controlado pelo PFS), o SQL Server prefere alocar uma nova página para inserir o registro ao invés tentar inserir o registro no espaço atual. Se não fosse dessa forma, o registro pode não caber na página e forçar o SQL Server a fazer uma nova busca por uma página com espaço livre. Aqui vemos claramente uma troca entre desperdício de espaço e eficiência na inserção do registro.


Fiz os testes com o SQL Server 2000 SP4 e SQL Server 2005 SP2, usando limites fixos para inserção (colunas CHAR) e inserções de tamanhos variáveis (colunas VARCHAR). Os resultados foram os mesmos.


 


Baseado neste artigo, eu gostaria de iniciar mais uma vez essa famosa discussão: Porque utilizar tabelas heap?


 


Importante: Vale notar que esta informação não é baseada em um documento público ou informação interna sobre o SQL Server, mas somente nos testes que conduzi. Então cada um deve tirar suas próprias conclusões e concordar ou não, com este que vos escreve.


 


Segue em anexo o script do artigo e também resolvi colocar o PDF.


 


Ah, e para aqueles que estão curiosos, no próximo artigo eu explico porque a PFS não atualizou a quantidade de espaço alocado (na PFS) para o índice cluster.


 


============= Adendo – 01/03/2007 ================


Acabei de nomear o Nilton como meu revisor técnico (ele nem sabe disso ainda!), pois sempre acha um errinho nos meus artigos. Obrigado, continue sempre me ajudando.


Esta aí o meu exemplo para provar que a pressa é inimiga da perfeição...


 


!!! DON’T DO DRUGS !!!


================================================


 


[]s


Luciano Caixeta Moreira


lmoreira@microsoft.com

Desperdício de espaço das tabelas heap.rar

Comments (3)

  1. Fernando says:

    Cara parabéns pelo seu site,isso que voce está escrevendo no seu site é uma obra cara,geralmente nós ficamos só na teoria sobre page split,pfs,gam,sgam,em geral ficamos na teoria sobre os pontos mais importantes de como saber como funciona internamente o sql server,muito obrigado,seus artigos são de extrema ajuda e importancia…..continue assim…

    Abraços…

    E parabéns por estar na Microsoft nesse cargo,voce faz por merecer…

    Abraços…

  2. Olá Moreira,

    Mais uma vez parabéns pelo artigo…muito bom!!

    Apenas uma pergunta…Considerando que o valor de 369.1 é por página, para chegar ao valor real não teríamos antes que encontrar o número de página alocadas para os 10.000.000 de registros e só então multiplicar pelos 369,1?

    Para o exemplo podemos ter +- 80 registros por página (varchar(100)* 80 = 8000 bytes – arredondando ok )… no cálculo de espaço perdido vejo que vc está considerando que 1 registro ocupa uma página….

          2) Tabela com 10.000.000 registros = 369,1 * 10.000.000 =  3.691.000.000 bytes

    Penso que teríamos algo como:

    10.000.000 / 80 (+- número de registros por página) = 125.000 páginas

    125.000 * 369.1 = 46137500 bytes = +- 44MB

    Um abraço

    Nilton Pinheiro

    http://www.mcdbabrasil.com.br

    niltonpinheiro@msn.com

  3. MSDN Archive says:

    Oi Nilton, você está certo. Já fiz a correção no artigo.

    Não usei como base os 80 registros por página, pois temos que levar em conta o desperdício de 5% e os quebradinhos do varchar e int.

    Então preferi fazer a contagem exata.

    []s

    Luti

Skip to main content