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

AGRADEÇA AO AUTOR COMPARTILHE!

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.

Example IFI seconds

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

alerta de segurança

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:

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

IFI 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.

sql server configuration

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

politica de seg 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:

IFI habilitado2

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

Referências:

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

AGRADEÇA AO AUTOR COMPARTILHE!

Felipe Portela

Mais artigos deste autor »

25 anos, Bacharel em sistemas de informação, MBA em Administração de banco de dados, e por fim profissional de banco de Dados com ênfase em performance.

Hobby por vídeo-games e futebol.

MTA | ITIL | COBIT | ISO27002 | LPIC-1


7 Comentários

AvatarRodrigo
1

Felipe, obrigado por compartilhar. Eu não conhecia sobre essa opção. Vou testar em meu ambiente de homologação. O meu restore demora hoje cerca de 2 horas. Voltarei aqui para dizer o quanto diminuiu.

AvatarFelipe Portela Autor do Post
2

Obrigado pelo comentário Rodrigo, estou a disposição para maiores esclarecimentos. Aguardo o seu feedback. Grande abraço!

AvatarRenato
5

Muito bom artigo Felipe, muito bem explicado e com ganhos muito bem evidênciados, parabéns!
O IFI é um recurso excelente, e que também traz ganhos não apenas no RESTORE mas também quando qualquer arquivo de *dados* é criado (não funciona para arquivos de log).
Uma observação: o campo novo que indica que o IFI está atualizado não se encontra em versões antigas, sei que existe no 2017 mas não tem no 2014. No 2016 não sei.
Abraço!

AvatarFelipe Portela Autor do Post
6

Oi Renato, obrigado pelo comentário e pelas observações. Realmente, para ter informações do IFI para versões anteriores a 2017/2016 é necessário usar um outro script que fornece essa informação.

De qualquer forma, muito obrigado pelas observações! Grande abraço.

Carlos H3Carlos H3
7

Ótimo artigo Felipe, acredito que vai agregar bastante para aqueles que lidam com esse tipo de processo no dia a dia.

Deixe seu comentário

Seu endereço de e-mail não será publicado. Campos com * são obrigatórios!

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">