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 (https://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