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" (http://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
===============================================

Filestream_MemoryUsage.zip

Skip to main content