SQL Filestream, acesso T-SQL e a utilização da memória
No último post nós conversamos sobre a utilização do recurso de Filestream no SQL Server de acordo com o tamanho dos arquivos. Agora eu vou demonstrar para vocês o que pode acontecer quando vocês estão manipulando um arquivo grande e o acessam através de instruções T-SQL, ao invés de usar a API do Win32 para fazer essa manipulação.
1 - Criando o banco de dados e o arquivo
Para nossa experiência, vamos criar um banco de dados chamado FileStreamDB e adicionar um arquivo, que deve ficar com aproximandamente 256 MB após as atualizações.
(Listagem 0)
CREATE DATABASE FileStreamDB ON PRIMARY
( NAME = FileStreamDB_data,
FILENAME = N'C:\Temp\Filestream\DBs\FileStreamDB_data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 15%),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = FileStreamDB_CVs,
FILENAME = N'C:\Temp\Filestream\DBs\CVs')
LOG ON
( NAME = 'FileStreamDB_log',
FILENAME = N'C:\Temp\Filestream\DBs\FileStreamDB_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
USE FileStreamDB
go
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'CurriculumVitae')
DROP TABLE CurriculumVitae
GO
CREATE TABLE CurriculumVitae
(Codigo uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Nome VARCHAR(255) NOT NULL,
CV VARBINARY(MAX) FILESTREAM)
GO
INSERT INTO CurriculumVitae (Nome, CV) VALUES ('Luciano Caixeta Moreira',
CAST(REPLICATE('BLABLABLA BLABLABLABLA BLABLA BLA BLABLABLA', 1000) AS VARBINARY(MAX)))
DECLARE @i INT = 0
WHILE @i < 15
BEGIN
UPDATE CurriculumVitae
SET CV = CV + CV
SET @i = @i + 1
END
Com o circo montado, vamos para a nossa análise.
2 - Analisando a utilização de memória
Primeiramente vamos reiniciar a instância do SQL Server, pois queremos que o buffer pool não tenha muita memória comitada, somente reservada. Após a re-inicialização, limpamos os buffers de dados e vemos a situação dos objetos em memória (listagem 1).
(Listagem 1)
DBCC DROPCLEANBUFFERS
GO
select * from sys.dm_os_buffer_descriptors
/*
database_id file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes is_modified numa_node
----------- ----------- ----------- ----------- -------------------- ------------------------------------------------------------ ----------- ------------------- ----------- -----------
2 1 150 0 844424932360192 INDEX_PAGE 57 5574 1 0
2 1 132 0 562949957025792 INDEX_PAGE 322 1978 1 0
6 1 31 0 196608 DATA_PAGE 102 1568 0 0
6 1 252 1 327680 INDEX_PAGE 3 8045 0 0
2 1 101 0 281474978611200 DATA_PAGE 131 4035 1 0
6 1 237 0 72057594039042048 INDEX_PAGE 1 8042 0 0
2 1 157 1 562949956108288 INDEX_PAGE 5 7916 1 0
6 1 7433 0 281474978349056 DATA_PAGE 3 7841 1 0
... (Removido por simplicidade) ...
6 1 235 0 72057594038976512 DATA_PAGE 1 7972 0 0
2 1 167 0 562949956960256 INDEX_PAGE 165 764 1 0
2 1 155 0 562949956108288 IAM_PAGE 2 6 1 0
2 1 78 0 562949956108288 INDEX_PAGE 121 3876 1 0
2 1 29 0 281474978283520 DATA_PAGE 2 7226 1 0
6 1 25 0 281474977955840 DATA_PAGE 2 8038 0 0
(73 row(s) affected)
*/
Notamos aqui que somente temos 73 entradas, vindas de diferentes páginas espalhadas pelos bancos de dados.
Para monitorar a utilização da memória distribuída pelos memory clerks, utilizo o código da listagem 2 que nos permitirá consultar a mudança nas alocações durante a execução de um procedimento específico. Neste caso nós somente queremos analisar um tipo, mas fica aqui o script como referência.
(Listagem 2)
SELECT
[Type],
SUM(single_pages_kb) AS sum_single_pages_kb,
SUM(multi_pages_kb) AS sum_multi_pages_kb,
MAX(virtual_memory_reserved_kb) AS max_virtual_memory_reserved_kb,
MAX(virtual_memory_committed_kb) AS max_virtual_memory_committed_kb,
GETDATE() as DataHora
INTO MemoryClerks
FROM sys.dm_os_memory_clerks
GROUP BY [type]
while 1=1
begin
INSERT INTO MemoryClerks
SELECT
[Type],
SUM(single_pages_kb) AS sum_single_pages_kb,
SUM(multi_pages_kb) AS sum_multi_pages_kb,
MAX(virtual_memory_reserved_kb) AS max_virtual_memory_reserved_kb,
MAX(virtual_memory_committed_kb) AS max_virtual_memory_committed_kb,
GETDATE() as DataHora
FROM sys.dm_os_memory_clerks
GROUP BY [type]
waitfor delay '00:00:05'
END
Após disparado o script da listagem 2, que ficará rodando até que seja interrompido por um comando de cancelamento da execução, em outra conexão execute a consulta (listagem 3) que vai trazer os dados do arquivo que foi criado.
(Listagem 3)
SELECT Codigo, Nome, CAST(CV as varchar(MAX)) AS CV, CV.PathName() AS Arquivo
FROM CurriculumVitae AS C
Você pode deixar os scripts rodando até que a consulta seja finalizada ou interrompê-los no meio da execução, desde que já tenha decorrido um tempo suficiente para o SQL Server começar a processar a consulta e capturar algumas variações nos memory clerks. Como resultado da execução completa da consulta (dados abaixo), podemos realizar novas pesquisas (listagem 4) para nos trazerem informações sobre o estado da memória e mudanças capturadas na tabela MemoryClerks.
(Listagem 4)
select * from sys.dm_os_buffer_descriptors
/*
33226 registros são retornados!
database_id file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes is_modified numa_node
----------- ----------- ----------- ----------- -------------------- ------------------------------------------------------------ ----------- ------------------- ----------- -----------
2 1 26137 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 12684 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 847 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 20983 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 7533 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 29297 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 15870 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
4 1 1390 0 72057594057981952 INDEX_PAGE 1 7959 0 0
2 1 24193 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 10719 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 32500 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 19058 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 5593 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
2 1 14979 0 71916856550359040 TEXT_MIX_PAGE 1 40 0 0
... (Excluído por simplicidade) ...
*/
*/
DBCC TRACEON (3604)
DBCC PAGE (2,1,14979,2)
/*
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:14979)
BUFFER:
BUF @0x04097F5C
bpage = 0x0D19A000 bhash = 0x00000000 bpageno = (1:14979)
bdbid = 2 breferences = 0 bUse1 = 35893
bstat = 0xc00009 blog = 0x321bc bnext = 0x00000000
PAGE HEADER:
Page @0x0D19A000
m_pageId = (1:14979) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8020
m_objId (AllocUnitId.idObj) = -2147483636 m_indexId (AllocUnitId.idInd) = 255
Metadata: AllocUnitId = 71916856550359040 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1
m_freeCnt = 40 m_freeData = 8150 m_reservedCnt = 0
m_lsn = (24:178:3) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:8088) = 0x4 100_PCT_FULL
DIFF (1:6) = NOT CHANGED ML (1:7) = NOT MIN_LOGGED
DATA:
Memory Dump @0x6432C000
6432C000: 01030000 2080ff00 00000000 00000000 †.... .ÿ.........
6432C010: 00000000 00000100 0c000080 2800d61f †............(.Ö.
6432C020: 833a0000 01000000 18000000 b2000000 †ƒ:..........²...
6432C030: 03000000 00000000 00000000 00000000 †................
6432C040: 01000000 00000000 00000000 00000000 †................
6432C050: 00000000 00000000 00000000 00000000 †................
6432C060: 0800761f 0000d107 00000000 03004142 †..v...Ñ.......AB
6432C070: 4c41424c 4120424c 41424c41 424c4142 †LABLA BLABLABLAB
6432C080: 4c412042 4c41424c 4120424c 4120424c †LA BLABLA BLA BL
6432C090: 41424c41 424c4142 4c41424c 41424c41 †ABLABLABLABLABLA
6432C0A0: 20424c41 424c4142 4c41424c 4120424c † BLABLABLABLA BL
6432C0B0: 41424c41 20424c41 20424c41 424c4142 †ABLA BLA BLABLAB
6432C0C0: 4c41424c 41424c41 424c4120 424c4142 †LABLABLABLA BLAB
6432C0D0: 4c41424c 41424c41 20424c41 424c4120 †LABLABLA BLABLA
6432C0E0: 424c4120 424c4142 4c41424c 41424c41 †BLA BLABLABLABLA
6432C0F0: 424c4142 4c412042 4c41424c 41424c41 †BLABLA BLABLABLA
6432C100: 424c4120 424c4142 4c412042 4c412042 †BLA BLABLA BLA B
...... (Excluído por simplicidade) .....
6432DF90: 4c41424c 4120424c 41424c41 20424c41 †LABLA BLABLA BLA
6432DFA0: 20424c41 424c4142 4c41424c 41424c41 † BLABLABLABLABLA
6432DFB0: 424c4120 424c4142 4c41424c 41424c41 †BLA BLABLABLABLA
6432DFC0: 20424c41 424c4120 424c4120 424c4142 † BLABLA BLA BLAB
6432DFD0: 4c41424c 41420000 00000000 00000000 †LABLAB..........
6432DFE0: 00000000 00000000 00000000 00000000 †................
6432DFF0: 00000000 00000000 00000000 00006000 †..............`.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
SELECT
max_virtual_memory_reserved_kb,
max_virtual_memory_committed_kb,
DataHora
FROM MemoryClerks
WHERE [TYPE] = 'MEMORYCLERK_SQLBUFFERPOOL'
/*
max_virtual_memory_reserved_kb max_virtual_memory_committed_kb DataHora
------------------------------ ------------------------------- -----------------------
1654368 37480 2008-11-11 10:22:39.290
1654368 37480 2008-11-11 10:22:22.967
1654368 37480 2008-11-11 10:22:44.320
1654368 45216 2008-11-11 10:22:49.373
1654368 84228 2008-11-11 10:22:54.400
1654368 127652 2008-11-11 10:22:59.420
1654368 172100 2008-11-11 10:23:04.420
1654368 215012 2008-11-11 10:23:09.467
1654368 262020 2008-11-11 10:23:14.503
1654368 281656 2008-11-11 10:23:19.540
1654368 282168 2008-11-11 10:23:24.563
1654368 282168 2008-11-11 10:23:29.577
1654368 282168 2008-11-11 10:23:34.610
1654368 282168 2008-11-11 10:23:39.640
1654368 282168 2008-11-11 10:23:44.663
1654368 282168 2008-11-11 10:23:49.673
1654368 282168 2008-11-11 10:23:54.717
(17 row(s) affected)
*/
Sinistro! Mas intuitivo...
Podemos ver pela análise dos buffers descriptors que agora temos um grande volume de páginas do tipo TEXT_MIX_PAGE armazenadas na TempDB (database_id = 2). Isto é, o SQL Server está utilizando a TempDB para armazenar os dados vindos do nosso arquivo. Isso pode ser verificado quando analisamos uma página qualquer que foi listada (14979 no exemplo) e conseguimos ver uma série de "BLA BLA BLA"s.
Nota: "3 - text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap" (https://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx).
E o que aconteceu com a memória que o SQL Server? Podemos notar pela análise do tipo MEMORYCLERK_SQLBUFFERPOOL que o SQL Server havia reservado os tradicionais 1.6GB (2GB - memtoleave e espaço para thread stack) e somente havia comitado 37.480 KB. Quando executamos nossa consulta para recuperar os dados do arquivo, notamos que o total de memória comitada começa a aumentar até estabilizar em 282.168 KB... Levando em conta que o arquivo possui aproximadamente 256 MB, o resultado não é mera coincidência! O SQL Server está lotando nossa memória com páginas vindas do arquivo, que estão sendo armazenadas na TempDB!
Então não importa se você está armazenando seus dados utilizando o filestream, se você está utilizando uma instrução T-SQL para recuperar o dado (seja no management studio ou na sua aplicação), a informação vai passar pela memória do SQL Server, concorrendo com outras páginas de dados e possivelmente colocando o lazy writer para trabalhar. Quando estiver manipulando esses arquivos grandes, use a classe SqlFileStream e deixe que os dados fluam através do streaming do NTFS.
Conclusão: entenda como o SQL Server trabalha com o Filestream e as diferenças ao acessar o arquivo através de T-SQL ou Win32.
PS: Em anexo está o script para vocês brincarem...
[]s
Luciano Caixeta Moreira
luciano.moreira@microsoft.com
===============================================
This post is provided "AS IS" and confers no right
===============================================