SQL Server: usando o IFI para ganhar performance no tempo de restore

Nos tempos atuais muito se fala em tuning de aplicações, infraestrutura e afins. O mercado tem cada vez mais se preocupado na proposição e aplicação de mudanças visando otimizar o desempenho na recuperação ou atualização de dados. Hoje venho compartilhar uma pequena técnica de otimização no SQL Server.

Você que é profissional de banco de dados SQL Server já deve ter notado como um o restore, dependendo do tamanho da base de dados, demora bastante, não é mesmo? Há uma maneira de obter um ganho prático no desempenho em relação ao tempo de restore no SQL Server.

Instant File Initialization (IFI)

Na criação ou restauração de um banco de dados, um arquivo de dados ou log é criado pelo SQL Server. Nesse momento, o SQL Server precisa substituir todos os dados existentes no espaço que foi alocado no disco, então ele faz isso preenchendo esses espaços com vários zeros. Geralmente os restores de bases de dados demoram por que a restauração de fato só irá iniciar depois que esse procedimento de preenchimento dos espaços estiver totalmente concluído.

O Instant File Initialization (IFI) permite que este processo seja desabilitado, logo, o arquivo irá recuperar o espaço alocado sem precisar preencher tudo com zero. O Conteúdo em disco, então, irá ser substituído na medida em que novos dados são gravados. Dessa forma, o processo será muito mais rápido. Na imagem abaixo temos alguns processos de restore com o IFI habilitado e desabilitado.

Exemplo com e sem IFI

Benefícios do Instant File Initialization

• Ganho performático na Restauração e criação de um banco de dados (RESTORE E CREATE DATABASE).
• Ganho performático no aumento do tamanho de um banco de dados inclusive o AUTO GROWTH.

Alerta sobre a segurança

Mesmo com um possível risco de segurança, a própria Microsoft recomenda as alterações a seguir pelo benefício do desempenho. Entretanto, é passível de uma análise com a sua equipe de segurança.

Referência: docs.microsoft.com/pt-br/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

Verificação do IFI no ambiente

O primeiro passo para habilitar o IFI no seu ambiente de banco de dados, é validar se o mesmo já está ativo. Conseguimos realizar essa validação através do seguinte script:

SELECT 
    @@SERVERNAME AS [Server Name] ,
    service_account ,
    instant_file_initialization_enabled,
    RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info],
    LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' ' + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version]       
FROM  sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'

Ao executar o script podemos verificar que a coluna chamada “instant_file_initialization_enabled”  irá informar se o IFI já está ou não habilitado.

Habilitando o IFI no seu ambiente

Para habilitar o IFI no seu ambiente, será necessário agendar uma janela de manutenção por que será necessário reiniciar o serviço do SQL Server.

1. No “SQL Server Configuration” é necessário validar qual usuário está sendo utilizado pelos serviços do SQL Server. No meu caso, é o usuário Service.

2. Você deve adicionar esse usuário nas políticas de segurança Local do Windows

Depois de adicionar o usuário para este grupo, reinicie o serviço do SQL Server.

Após reiniciar o serviço do SQL Server, execute o script de validação do IFI e vamos perceber que agora o mesmo está ativo:

Ao finalizar as etapas acima, perceba que a partir de agora, principalmente o processo de restore, terá imensos ganhos de performance.

Referências:

• https://techcommunity.microsoft.com/t5/Premier-Field-Engineering/How-and-Why-to-Enable-Instant-File-Initialization/ba-p/370329
• https://blog.sqlauthority.com/2018/07/31/sql-server-how-to-turn-on-enable-instant-file-initialization/

Caso tenha qualquer complemento ou observação, por favor, deixe seu comentário abaixo