Performance com Filestream

Ontem pela manhã o Edvaldo Castro (Blog|Twitter) precisava de uma solução para colocar no SQL Server pequenos arquivos como arquivos txt, doc, pdf, xls e assim por diante. Mas qual a melhor solução?

Depende !!

A Microsoft tem um whitepaper onde explica que arquivos até 1mb o melhor é guardar dentro do SQL Server, após isso a recomendação é filestream.

Pra quem não conhece filestream é uma funcionalidade de que veio no SQL Server 2008 para facilitar o armazenamento de arquivos binarios muitas vezes grandes, fora do SQL Server e por cima de tudo fornecendo o maximo de desempenho. Então vamos aprender como utilizar filestream e ver se ele realmente me traz beneficios?

O primeiro passo então é criamos nossa base de dados. Muita atenção aqui, pois é na criacão da base que você define qual filegroup será o responsavel por armazenar os dados do filestream.

Abaixo temos o script que cria então nossa base de dados.

CREATE DATABASE FilestreamDatabase ON  PRIMARY
( NAME = N’FilestreamDatabase_Data’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FilestreamDatabase.mdf’ ,
SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 16MB ),
FILEGROUP SECONDARY DEFAULT
( NAME = N’FilestreamDatabase_Data2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FilestreamDatabase.ndf’ ,
SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 16MB ),

FILEGROUP Documentos CONTAINS FILESTREAM
( NAME = N’Documentos’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FilestreamDocumentos’ )
LOG ON
( NAME = N’FilestreamDatabase_Log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FilestreamDatabase_Log.ldf’ ,
SIZE = 8MB , MAXSIZE = 2048GB , FILEGROWTH = 16MB )
GO
Notem que o ultimo filegroup criado possui a clausula CONTAINS FILESTREAM, que é responsavel por ativar a função do filestream.

Bom ja temos 50% da nossa solução. E os outro 50? Agora vamos criar uma tabela que utiliza esse filestream.

CREATE TABLE tblDocumentos
(
ID INT IDENTITY(1,1) NOT NULL,
FileStreamData VARBINARY(MAX) FILESTREAM NULL,
[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
DateTime DATETIME DEFAULT GETDATE(),
Usuario varchar(50)
)
ON [PRIMARY]
FILESTREAM_ON Documentos
GO

create clustered index IX_CLUSTER on tblDocumentos(ID)

É meus amigos, realmente é obrigatorio termos uma coluna UNIQUEIDENTIFIER com a opção ROWGUIDCOL.

Beleza, temos a nossa tabela, vamos agora inserir alguns dados na mesma.

insert into tblDocumentos
(FileStreamData)
select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\SQL2008inHyperV2008.docx’,SINGLE_BLOB) as Document

Mas no mundo real raramente inserimos dados assim somente com o documento, geralmente colocamos mais informações no mesmo insert, então para isso eu sugiro você declare uma variavel antes recebendo esse documento, para que assim você possa inserir mais dados.

declare @FilesTream varbinary(max) = (select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\SQL2008inHyperV2008.docx’,SINGLE_BLOB) as Document)
insert into tblDocumentos
(FileStreamData,Usuario)
values(@FilesTream,’Marcos Freccia’)

Beleza, agora ja sabemos como inserir nossos dados. Vamos ver se realmente temos ganho entre utilizar ou não Filestream?

Vamos inserir um arquivo de 16MB.

insert into tblDocumentos
(FileStreamData)
select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\Mastering_Profiler_eBook.pdf’,SINGLE_BLOB) as Document

E se esse arquivo fosse armazenado dentro do SQL Server como era no SQL Server 2005 e anterior?

CREATE TABLE tblDocumentosSemFilestream
(
ID INT IDENTITY(1,1) NOT NULL,
FileStreamData VARBINARY(MAX) NULL,
[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
DateTime DATETIME DEFAULT GETDATE(),
Usuario varchar(50)
)
go
create clustered index IX_CLUSTER_SEMFILESTREAM on tblDocumentosSemFilestream(id)
go

insert into tblDocumentosSemFilestream
(FileStreamData)
select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\Mastering_Profiler_eBook.pdf’,SINGLE_BLOB) as Document

set statistics io on
select * from tblDocumentos
select * from tblDocumentosSemFilestream

Viram como temos diferença na leitura de dados, isso porque o filestream não guarda o dado dentro do base de dados mas sim somente uma referencia dele, ou seja, ele ja sabe onde precisa buscar e faz isso de forma muito melhor do que se tivessemos arquivo todo dentro da base de dados do SQL Server.

Vamos fazer outro teste com um arquivo um pouco maior, algo entre 80 e 85MB.

insert into tblDocumentos
(FileStreamData)
select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\AdventureWorks2008R2_RTM.exe’,SINGLE_BLOB) as Document

insert into tblDocumentosSemFIlestream
(FileStreamData)
select * from openrowset(bulk ‘E:\Users\Freccia\Documents\Banco de dados\AdventureWorks2008R2_RTM.exe’,SINGLE_BLOB) as Document

Que diferença, enquanto no filestream tivemos apenas a leitura de 2 paginas de dados na tabela sem filestream foram lidas 148400 paginas.

Então pessoal, esse pequeno post de sexta-feira serviu para mostrar duas coisas: Como habilitar e utilizar o filestream e como podemos ganhar performance com ele, se nosso objetivo é claro é armazenar arquivos maiores.

Até a proxima.

Marcos Freccia
MCTS SQL Server 2008 Database Development
MCTS SQL Server 2008 Implementation and Maintenance
@SqlFreccia

Update Importante: Agora pela manhã, Luti me falou no twitter que essa questão de performance no filestream possa estar com erros, pois o Access Methods deve utilizar outro método de busca e com isso o STATISTICS IO não estra mostrando o real numero de paginas que ele esta lendo.

Obrigado Luti pela explicação. Para verificar então realmente o que acontece por debaixo dos panos vou pesquisar sobre algum contador no perfmon que possa me ajudar para isso, ou utilizar as ferramentas do Sysinternals.