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
