Lenda Urbana: tabelas temporárias são escritas diretamente em disco
[em anexo vocês podem fazer o download do artigo em PDF e o script que foi utilizado]
Usualmente eu leio alguns blogs de SQL Server e certa vez eu me deparei com um artigo muito legal e detalhado, onde foram citadas algumas lendas urbanas do SQL Server, veja o distinto aqui: https://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Seguindo esse padrão eu e o Fabricio Catae (Microsoft) quase fizemos algo semelhante no TechEd 2007, durante nossa mesa redonda sobre performance tuning de SQL Server, mas preferirmos por manter um formato diferente. Desde então eu fiquei com essa história de lendas urbanas na cabeça e ontem apareceu uma oportunidade legal para quebrar mais um mito. Vamos à caça de mais uma lenda...
Dizem por aí, que quando você cria uma tabela temporária o banco de dados que recebe o objeto é o tempdb e os bits e bytes da tabela são escritos diretamente em disco. Podemos ver que essa visão é usual através da discussão no fórum de SQL Server: https://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=2555891&SiteID=21.
Da afirmação acima, a primeira parte é verdadeira (o uso da tempdb), já a segunda parte da afirmação não é correta, pois não necessariamente os dados são persistidos em disco. Notamos que até pessoas com muita experiência em SQL Server cometem esse pequeno deslize, que não é algo que vai fazer o SQL Server parar, mas vale a pena ser entendido. E agora, a quebra do mito (estou me sentido na Discovery, estilo MythBusters!).
Para essa experiência eu utilizei:
- Uma instância do SQL Server (estou usando o SQL 2005 com SP2) e permissão para parar e reiniciar o serviço.
- Um editor de hexadecimal (fiz o download de um freeware – Hex Editor 3.12 da HDD Software).
- Um comando não documentado, que infelizmente eu não posso divulgar. Mil desculpas. L
Etapa 01: acabando com o mito
Abra o SQLWB e execute o seguinte script...
USE Master
go
CREATE TABLE #TEMP01
(Codigo INT IDENTITY(1,1) NOT NULL,
Nome VARCHAR(50) NOT NULL)
Go
INSERT INTO #TEMP01 VALUES ('Estou em memória ou disco?')
select * from #TEMP01
GO
Neste momento a tabela temporária foi criada e temos um registro dentro dela, vamos verificar em qual página está o registro que foi inserido (nota: a página pode mudar a cada execução, aí vocês devem verificar qual é a página correta).
use tempdb
go
select object_name(id), *
from sys.sysindexes
where object_name(id) like '%temp%'
-- First: 0x780000000100
dbcc traceon(3604)
dbcc page (2, 1, 120, 1)
/*
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:120)
BUFFER:
BUF @0x03D05504
bpage = 0x058D6000 bhash = 0x00000000 bpageno = (1:120)
bdbid = 2 breferences = 0 bUse1 = 15952
bstat = 0xc0000b blog = 0x32159bb bnext = 0x00000000
PAGE HEADER:
Page @0x058D6000
m_pageId = (1:120) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043432960
Metadata: PartitionId = 72057594038452224 Metadata: IndexId = 0
Metadata: ObjectId = 149575571 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 8053
m_freeData = 137 m_reservedCnt = 0 m_lsn = (22:44:293)
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) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 41, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x48EAC060
00000000: 30000800 01000000 0200fc01 00290045 †0............).E
00000010: 73746f75 20656d20 6d656df3 72696120 †stou em mem.ria
00000020: 6f752064 6973636f 3f†††††††††††††††††ou disco?
OFFSET TABLE:
Row - Offset
0 (0x0) - 96 (0x60)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
O DBCC PAGE nos informa o offset do registro na página, as informações da página em memória (região BUFFER) e como é o dado em hexadecimal. Para nos ajudar a entender a estrutura do arquivo, vamos executar dois outros comandos para vermos o conteúdo da primeira e segunda página (0 e 1).
dbcc traceon(3604)
dbcc page (2, 1, 0, 1)
go
dbcc traceon(3604)
dbcc page (2, 1, 1, 1)
go
Por simplificação eu não coloquei todo o resultado, mas vocês podem conferir isso fazendo por si só ou vendo o script em anexo a esse artigo.
/*
Página 0
00000000: 30000800 00000000 2d000000 00000000 †0.......-.......
00000010: 2c007a00 7a007c00 7e008200 86008a00 †,.z.z.|.~.......
00000020: 8e009800 a200ac00 ac00b000 b400b800 †................
00000030: bc00c600 e200ec00 f6000001 10011a01 †................
00000040: 2a012e01 38013801 46015601 56015601 †*...8.8.F.V.V.V.
00000050: 56015601 56015601 66016601 66017001 †V.V.V.V.f.f.f.p.
00000060: 7a019601 a001b001 cc015153 0a2c60ab †z.........QS.,`.
00000070: d211896a 00c04fd9 374a0100 01000004 †...j..O.7J......
Página 1
00000000: 00009c1f 44444444 00004444 60647060 †....DDDD..DD`dp`
00000010: 74706070 60606060 60707060 40404040 †tp`p`````pp`@@@@
00000020: 40404040 61706070 60606070 60306060 †@@@@ap`p```p`0``
00000030: 60217024 60706060 60606060 40203020 †`!p$`p``````@ 0
00000040: 20202820 60606060 60606070 60606060 † ( ```````p````
00000050: 70203020 61706070 70607060 70703060 †p 0 ap`pp`p`pp0`
00000060: 70203060 70607060 70607060 70607060 †p 0`p`p`p`p`p`p`
00000070: 70603060 60607020 60702030 60306070 †p`0```p `p 0`0`p
00000080: 60702830 60707070 60606070 60706070 †`p(0`ppp```p`p`p
00000090: 60706070 40402020 20202020 60706070 †`p`p@@ `p`p
000000A0: 60706060 64616070 60706070 60706070 †`p``da`p`p`p`p`p
*/
Para que seja possível acessarmos o arquivo de dados do tempdb com o leitor hexadecimal, pare a instância do SQL Server. Depois abra o arquivo (tempdb.mdf) com o leitor hexadecimal e vamos comparar o que temos fisicamente no arquivo com as informações que coletamos através do DBCC PAGE.
Esse procedimento somente funciona porque o SQL Server recria o banco de dados tempdb quando o servidor é inicializado, então tudo que já havia sido escrito fisicamente no arquivo têm que estar lá.
Analisando a primeira página do arquivo .mdf podemos notar que as informações do que nós lemos com o DBCC PAGE e o conteúdo físico são iguais (ou quase).
Importante: Toda página no SQL Server possui um cabeçalho de 96 bytes (60 em hexadecimal), por isso vemos através do comando DBCC PAGE os dados sendo exibidos no offset 0x00000000 e no arquivo físico precisamos olhar o conteúdo deslocado de 96 bytes (0x00000060). Por exemplo, note que a linha 00000050 é igual a 000000b0, sendo iniciada por 56 01 56 01.
Para analisarmos o conteúdo da segunda página, vamos calcular o offset da distinta:
1024 * 8 * 1 = 8192 = 0x2000 (1024 * 8 = tamanho da página / 1 = id da página)
Comparando o resultado do editor hexadecimal no offset 0x00002060 (lembrem do cabeçalho), temos os mesmos dados “00 00 9c 1f 44 44 44 44”:
Vamos analisar agora a página com o conteúdo da nossa tabela temporária (120). Primeiro fazemos o cálculo do local da página (1024 * 8 * 120 = 983.040 = 0xF0000) e depois analisamos o conteúdo físico do arquivo no offset 0x000F0060:
O que temos aqui? Usando o DBCC PAGE nós conseguimos ver o conteúdo da página refletido corretamente, mas quando analisamos o arquivo físico o conteúdo que temos ali dentro é diferente! Alguns podem afirmar que eu calculei errado o offset (será?), pois já temos algum conteúdo na página, mas minha aposta é que essa página já foi utilizada pelo SQL Server para alguma coisa e o que estamos vendo é sujeira que sobrou quando a página foi desalocada (o SQL Server 2005 NÃO sai limpando toda a página fisicamente escrevendo zeros, isso não é eficiente).
Com isso, vemos que o conteúdo da tabela temporária deveria estar em memória, já que não está no arquivo físico. Mas para provar que essa história realmente é um mito, eu preciso mostrar um dado de uma tabela temporária escrita no arquivo usando a mesma técnica descrita até o momento (senão a minha abordagem estaria errada), então vamos à etapa 02...
Etapa 02: comprovando a etapa 01
Iniciei novamente o SQL Server e executei novamente as instruções para criação da tabela temporária, inseri os registros e utilizei o comando DBCC PAGE para verificar a situação da página. Como o número da página foi novamente 120, o resultado até o momento era o que já havíamos conseguido na primeira etapa.
Agora é que vem o problema, para conseguir demonstrar que o conteúdo estava em memória, eu tinha que arranjar uma maneira consistente de garantir que a página seria escrita em disco. Para fazer isso eu usei um comando não documentado, DBCC XXXXXXXXXXXXXXXX, que pega as páginas de dados de um banco específico que estão na memória e as escreve em disco. Infelizmente eu não posso revelar qual foi o comando, apesar de estar morrendo de vontade de fazer isso... Neste ponto, eu quero que vocês levem na fé.
Após ter executado o comando, eu parei a instância do SQL Server e usei o editor hexadecimal para ver o conteúdo do arquivo tempdb.mdf, eis o resultado:
Podemos notar que dessa vez o conteúdo da tabela temporária foi persistida em disco, diferente da primeira vez! Com isso comprovamos que a frase citada no início do artigo é realmente uma lenda urbana.
Conclusão
Antes de terminar, quero discutir a racionalidade por detrás do comportamento do SQL Server.
Em primeiro lugar, o SQL Server não escreve diretamente os dados em disco, ele altera as páginas de controle que possivelmente já estão na memória (PFS, GAM, SGAM, etc.) e aloca novas páginas de dados que ainda não são escritas em disco. Depois, dependendo da atividade do lazy writer, o SQL Server identifica quais são as páginas que estão sujas em memória e as escreve em disco. Para evitar que essas informações sejam perdidas em caso de falha de energia, por exemplo, ele utiliza o log de transação (que possui os dados efetivamente em disco) para refazer o que ainda não havia sido persistido (um processo chamado de redo).
Essa abordagem é extremamente importante para o tempdb, pois imagine a lentidão e o overhead que seria se toda tabela temporária que é criada fosse escrita em disco e (na maioria dos casos), apagada logo depois de ser utilizada. Então, manter esses objetos em memória por um intervalo de tempo (que varia de acordo com a pressão de memória e utilização do servidor) é eficiente e bom para o SQL Server.
Espero que esse artigo tenha nos ajudo a entender um pouco mais o comportamento do SQL Server.
Até uma próxima vez.
[]s
Luciano Caixeta Moreira
=============================================================
This posting is provided "AS IS" with no warranties, and confers no rights
=============================================================
20071215 - Lenda Urbana - Tabelas temporárias escritas em disco.zip
Comments
Anonymous
December 15, 2007
PingBack from http://geeklectures.info/2007/12/16/lenda-urbana-tabelas-temporarias-sao-escritas-diretamente-em-disco/Anonymous
December 18, 2007
A variável do tipo tabela, por definição, utiliza a memória do servidor se disponível escrevendo em disco apenas se for necessário. Eu tinha a informação de que essa era a grande vantagem em relação as tabelas temporárias, mas parece que eu estava com o conceito errado ... Qual seria então a diferença entre as tabelas temporárias e as variáveis do tipo tabela?Anonymous
March 05, 2008
Vitor, você pode usar essa referência para entender melhor a diferença: http://support.microsoft.com/kb/305977/en-us. Além disso, no Saturday Night Code gravado em novembro de 2007, eu comentei sobre as diferenças, então você pode ver a gravação.