Múltiplos arquivos por filegroup

Melhor escrever alguma coisa para vocês saberem que ainda estou por aqui, não é?

Treinamento de internals (errr, Mastering) sempre é massa, muitas perguntas novas, ex-alunos que passaram X meses estudando e voltam com perguntas escabrosas, e um material que não consigo diminuir por dor no coração de deixar alguma coisa bacana de fora. Geek total.

Ontem falávamos de múltiplos arquivos por filegroup, distribuição das operações entre eles, etc., um bom prelúdio para falarmos da tempdb, e aí veio a pergunta: E se eu tiver muitos arquivos dentro do mesmo filegroup, ajuda ou atrapalha o desempenho?

Ter mais de um arquivo pode sim te beneficiar em diversas operações (como um backup ou restore) por conta de você ter mais threads para te ajudar ou ajudar na utilização proporcional/balanceamento, mas a discussão encaminhou para: o overhead para o SQL Server identificar o espaço livre em cada arquivo para uma distribuição proporcional é significativo?

Fiz o teste na minha máquina com um disco local tomando os cuidados: não ter crescimento do arquivo de log (está em auto truncate mode), não ter crescimento dos arquivos de dados (mesmo com IFI) e executar duas baterias de teste para cada banco com a máquina relativamente parada. Eis o resultado para uma inserção de 100.000 registros (um registro por página):

·         5 arquivos de 200MB = 1:24 / 1:20

·         50 arquivos de 20MB = 1:57 / 1:50

·         100 arquivos de 20MB = 2:12 / 2:20

·         500 arquivos de 20MB = 4:12 / 3:59

De aproximadamente 1 minuto e 20 segundos para quatro minutos é uma diferença bem significativa. Então existe um overhead, mesmo para um workload simples, e quanto mais “exagerado” você for com múltiplos arquivos dentro do mesmo filegroup, maior o impacto. O problema aqui é identificar onde é o overhead, o caso mais provável é que seja uma saturação do meu subsistema de I/O e não um overhead no balanceamento, mas independente da causa fica o aviso. 

Note que esse teste foi focado em mostrar o impacto de múltiplos arquivos. Vale ressaltar que ter mais de um arquivo de dados pode beneficiar o seu workload sim! O Paul Randal já escreveu sobre isso: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/ e em seu benchmark 8 arquivos apresentou o melhor desempenho. Então a regra é: faça testes em seu ambiente para encontrar a melhor configuração.

Se quiser brincar no seu ambiente, abaixo está o script que eu criei. E não deixe de compartilhar se encontrar algo novo ou que passou despercebido neste post.

USE master

GO

IF (DB_ID('MultipleFiles') IS NOT NULL)

      DROP DATABASE MultipleFiles

GO

CREATE DATABASE MultipleFiles

  ON PRIMARY

      (NAME = N'MultipleFiles',

      FILENAME = N'C:\Temp\MultipleFiles.mdf',

      SIZE = 15MB,

      MAXSIZE = 1GB,

      FILEGROWTH = 10MB)

  LOG ON

  (NAME = N'MultipleFiles_Log',

      FILENAME = N'C:\Temp\MultipleFiles_log.ldf',

      SIZE = 50MB,

      MAXSIZE = 300MB,

      FILEGROWTH = 100MB)    

go

ALTER DATABASE MultipleFiles

ADD FILEGROUP FG01

GO

DECLARE @i INT = 1

DECLARE @FileSize VARCHAR(10) = '20MB'

DECLARE @sql VARCHAR(8000)

WHILE @i <= 500

BEGIN

      SET @sql = '

      ALTER DATABASE MultipleFiles

      ADD FILE (NAME = N''MultipleFiles_Data' + CAST(@i AS VARCHAR) + ''',

            FILENAME = N''C:\Temp\MultipleFiles_Data' + CAST(@i AS VARCHAR) + '.ndf'',

            SIZE = ' + @FileSize + ',

            MAXSIZE = 1GB,

            FILEGROWTH = 10MB)

      TO FILEGROUP FG01'

      -- PRINT @sql

      EXEC (@sql)

      SET @i = @i + 1

END

GO

ALTER DATABASE MultipleFiles

MODIFY FILEGROUP FG01 DEFAULT

GO

use MultipleFiles

go

SELECT * FROM sys.database_files

SELECT * FROM sys.filegroups

go   

IF (OBJECT_ID('TabelaTeste') IS NOT NULL)

      DROP TABLE TabelaTeste

go

CREATE TABLE TabelaTeste

(Codigo INT IDENTITY NOT NULL,

 Texto CHAR(8000) NOT NULL,

 Hora DATETIME2 NOT NULL DEFAULT SYSDATETIME())

go

-- 5x 200MB = 1:24 / 1:20

-- 50x 20MB = 1:57 / 1:50

-- 100x 20MB = 2:12 / 2:20

-- 500x 20MB = 4:12 / 3:59

INSERT INTO TabelaTeste (Texto) VALUES ('Sr. Nimbus')

GO 100000

Abraços

Luciano [Luti] Caixeta Moreira

luciano.moreira@srnimbus.com.br

http://luticm.blogspot.com/

http://www.twitter.com/luticm

sr. Nimbus Serviços em Tecnologia - www.srnimbus.com.br