Instant File Initialization x Tempo Restore

Fala Pessoal,

Nesse post vou falar sobre mais uma opção que utilizamos como Best Practices nos nossos clientes.

Confira também o post abaixo com mais algumas configurações que utilizamos:

https://luizlima.net/script-configuracoes-das-databases-best-practices/

Você já tentou fazer um RESTORE de uma base que demorou muito para iniciar e o percentual não apareceu instantaneamente? Sabe porque isso acontece??? Sabia que existe uma configuração que pode te ajudar nesse problema?

O intuito do post de hoje é fazer uma breve explicação sobre o “Instant File Initialization” e mostrar um exemplo prático com o ganho no desempenho ao utilizá-lo. Na maioria das vezes os clientes não conhecem essa opção, com isso, acredito que mais pessoas possam desconhecer também. Então vamos lá!

Instant File Initialization

Quando um arquivo de dados ou log for criado pelo SQL Server, ele precisa substituir todos os dados existentes no espaço que foi alocado no disco. Com isso, esses dados serão preenchidos com zeros, mas essa operação pode ser demorada dependendo do tamanho do arquivo ou da velocidade do disco. Por esse motivo que o percentual pode demorar em alguns casos, pois ele será iniciado APENAS quando essa operação for finalizada.

O “Instant File Initialization” ou IFI (ou traduzindo “Inicialização Instantânea de Arquivo”) permite que um arquivo recupere espaço em disco SEM precisar preencher esse espaço com zeros. Com isso, o conteúdo do disco é substituído à medida que novos dados são gravados nos arquivos. Dessa forma, a inicialização do arquivo será muito mais rápida (como vamos comprovar no Teste 2 mais abaixo).

Observações:

A inicialização instantânea de arquivo está disponível APENAS para os arquivos de DADOS. Os arquivos de LOG sempre serão anulados quando forem criados ou tiverem aumento de tamanho.
O uso de alguns recursos, como o TDE (Transparent Data Encryption), pode impedir a Inicialização Instantânea de Arquivo.
Para fazer essa operação é necessário conceder um privilégio a nível de Sistema Operacional para o usuário do Serviço do SQL Server (explico como fazer isso mais a frente).

Benefícios:

Podemos observar os benefícios da “Inicialização Instantânea de Arquivo” nas seguintes operações no banco de dados:

Criar um banco de dados (CREATE DATABASE), inclusive o TEMPDB quando o SQL Server for inicializado.
Adicionar arquivos de dados a um banco de dados existente.
Aumentar o tamanho de um arquivo existente (inclusive operações de aumento automático – AUTO GROWTH).
Restaurar um banco de dados (RESTORE DATABASE).

Segue abaixo um gráfico muito interessante fazendo uma comparação da inicialização instantânea de arquivos em diferentes tipos de disco. Repare como a diferença pode ser bem grande quando utilizam discos com um desempenho inferior.

Referência: https://sqlperformance.com/2016/03/io-subsystem/ifi-tempdb-setup

Considerações sobre Segurança

Segue um link da própria Microsoft com algumas recomendações:

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

“Ao usar o IFI, o conteúdo excluído do disco é substituído somente à medida que novos dados são gravados nos arquivos, o conteúdo excluído poderá ser acessado por uma entidade de segurança não autorizada, até que outros dados sejam gravados nessa área específica do arquivo de dados.”

Ou seja, mesmo com esses riscos de segurança (que devem ser tratados por sua Equipe de Segurança) a própria Microsoft recomenda essa opção pelos benefícios no desempenho.

Script Validação Instant File Initialization

Segue abaixo dois scripts para verificar se o IFI está habilitado ou não, o primeiro para o SQL Server 2016 em diante e o segundo para as versões anteriores.

OBS: Eles podem ser executados a qualquer momento no seu ambiente sem afetar a performance.

SQL Server 2016 em diante:

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 (%'

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 (%'

instant_file_initialization_enabled: N -> NÃO / Y -> SIM

Observação:

A partir do SQL Server 2012 (11.x) SP4 e do SQL Server 2016 (13.x) SP1 ao SQL Server 2017, a coluna instant_file_initialization_enabled na DMV sys.dm_server_services pode ser usada para identificar se a inicialização instantânea de arquivo está habilitada.

SQL Server 2014 e anteriores

USE master;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --

--Exit if a database named DummyTestDB exists
IF DB_ID('DummyTestDB') IS NOT NULL
BEGIN
  RAISERROR('A database named DummyTestDB already exists, exiting script', 20, 1) WITH LOG
END

--Temptable to hold output from sp_readerrorlog
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL DROP TABLE #SqlLogs
GO
CREATE TABLE #SqlLogs(LogDate datetime2(0), ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

--Turn on trace flags 3004 and 3605
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog
CREATE DATABASE DummyTestDB 
GO

--Turn off trace flags 3004 and 3605
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

--Remove the DummyDB
DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File
--This can take a while if you have a large errorlog file
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS(
           SELECT * FROM #SqlLogs
           WHERE TEXT LIKE 'Zeroing completed%'
            AND TEXT LIKE '%DummyTestDB.mdf%'
            AND LogDate > DATEADD(HOUR, -1, LogDate)
        )
   BEGIN
    PRINT 'We do NOT have instant file initialization.'
    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
   END
ELSE
   BEGIN
    PRINT 'We have instant file initialization.'
   END

USE master;

SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --

--Exit if a database named DummyTestDB exists

IF DB_ID('DummyTestDB') IS NOT NULL

BEGIN

RAISERROR('A database named DummyTestDB already exists, exiting script', 20, 1) WITH LOG

END

--Temptable to hold output from sp_readerrorlog

IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL DROP TABLE #SqlLogs

CREATE TABLE #SqlLogs(LogDate datetime2(0), ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

--Turn on trace flags 3004 and 3605

DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog

CREATE DATABASE DummyTestDB

--Turn off trace flags 3004 and 3605

DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

--Remove the DummyDB

DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File

--This can take a while if you have a large errorlog file

INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)

EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS(

SELECT * FROM #SqlLogs

WHERE TEXT LIKE 'Zeroing completed%'

AND TEXT LIKE '%DummyTestDB.mdf%'

AND LogDate > DATEADD(HOUR, -1, LogDate)

)

BEGIN

PRINT 'We do NOT have instant file initialization.'

PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'

END

ELSE

BEGIN

PRINT 'We have instant file initialization.'

END

Resultados:

Nesse caso, o IFI já está habilitado e você não precisa fazer nenhuma alteração.

Aqui o IFI NÃO está habilitado. Vou explicar a seguir como habilitar essa opção.

Habilitando o Instant File Initialization

Habilitar o IFI é bem simples, mas vai ser necessário fazer um Restart no SQL Server no final do processo. Vamos ao passo a passo:

1) Verifique o usuário que está sendo utilizado pelo Serviço do SQL Server. No meu caso é o usuário “SERVICE”.

2) Aqui você deve incluir o usuário na Política de Segurança do Windows. Segue as opções nas duas linguagens:

Português: “Executar tarefas de manutenção de volume”

Inglês: “Perform Volume Maintenance Tasks”

Clique com o botão direito e depois em “Propriedades”.

Nesse print, o usuário “SERVICE” já estava configurado (é assim que deve ficar depois que fizer a inclusão).

OBS: Dependendo de onde estiver testando, algumas versões do Windows NÃO possuem esse editor instalado como default. Contudo, existem vários links explicando como instalar. Segue abaixo um exemplo:

http://www.dicasparacomputador.com/instalando-ativando-gpeditmsc-group-policy-editor-windows

3) Agora selecione o usuário e clique em OK.

4) Por fim, será necessário fazer o Restart no Serviço do SQL Server (fora do horário de produção!). Entre no Configuration Manager, clique com o botão direito no Serviço do SQL Server e depois em “Restart”.

Pronto! Agora o IFI está habilitado! Utilize os scripts acima para validar.

Observação:

A partir do SQL Server 2016, essa permissão já pode ser concedida no momento da instalação.

Teste Prático – Instant File Initialization

“OK Luiz, você explicou o que é o IFI e como habilitar. Agora, o que nós ganhamos com isso na prática??? Eu só acredito vendo!!!”

Ótimo, então vamos para o teste!!

No meu cenário estou utilizando um HD SSD e uma base de 40 GB. Fiz um backup dessa base e vou fazer dois testes de RESTORE, um com o IFI desabilitado e depois vou habilitar e testar novamente.

TESTE 1 – IFI DESABILITADO

Conforme explicado anteriormente, com o IFI desabilitado o espaço que será alocado precisa ser preenchido com zeros. Nesse cenário, essa operação estava levando mais de 1 minuto APENAS para alocar esse espaço. Repare que o RESTORE nem foi iniciado nesse momento ainda. Ou seja, o Windows está preenchendo 40 GB de espaço com zeros e por isso está demorando.

Nesse teste, o tempo total foi de 1 minuto e 21 segundos. Desse tempo, APENAS 13 segundos foram utilizados para fazer efetivamente o RESTORE e o restante foi utilizado pelo Windows para preencher com zeros o espaço alocado.

Dessa forma, podemos perceber o tamanho do prejuízo que temos quando o IFI está DESABILITADO.

“Huummm Luiz!!! Agora entendi porque o RESTORE que eu executo demora a iniciar!!! Mas o IFI vai mudar alguma coisa??? O RESTORE vai ficar mais rápido mesmo???”

Fica tranquilo que vou mostrar no próximo teste.

Resumindo:

Tempo Restore: 13 segundos

Tempo Total: 1 minuto e 21 segundos

TESTE 2 – IFI HABILITADO

Habilitei o IFI e fiz o teste novamente. Repare que agora o RESTORE iniciou instantaneamente e executou com apenas 15 segundos! WOW!!! Esse comportamento já era esperado, visto que agora o espaço alocado NÃO está sendo preenchido com zeros e será substituído a medida que o SQL Server for alocando os dados posteriormente.

“WOW!!! Caramba Luiz!!! Então o RESTORE que eu executava e demorava, agora vai iniciar instantaneamente???”

Isso mesmo!!!

Resumindo:

Tempo Restore: 15 segundos

Tempo Total: 15 segundos

Conclusão – Instant File Initialization

TESTE 1 – Tempo Total – IFI DESABILITADO: 1 minuto e 21 segundos

TESTE 2 – Tempo Total – IFI HABILITADO: 15 segundos

Como podemos perceber, a diferença no tempo de execução do RESTORE foi de mais de 1 minuto!

“Ahh Luiz, mas a diferença foi de apenas 1 minuto, isso não é quase nada!!! Dá pra ir pegar um café e voltar tranquilo!”

OK, lembrando que no meu cenário utilizei um HD SSD e uma base de 40 GB apenas. Agora, imagine um outro cenário que tenha um servidor mais antigo que NÃO utilize um HD SSD e tenha uma base com 500 GB ou 1 TB por exemplo. Acredito que o tempo do RESTORE será bem maior e deve executar por vários minutos ou até mesmo horas!!!

Portanto, conseguimos verificar o ganho no desempenho quando o IFI está habilitado.

E você, já validou se o Instant File Initialization está habilitado nos seus ambientes? Corre e confere lá!

Download “Script Validação Instant File Initialization”:

Segue o link para baixar os scripts utilizados nesse Post:

Link: https://github.com/luizvitorf/SQLServer/blob/master/Scripts/Administra%C3%A7%C3%A3o/Script%20Valida%C3%A7%C3%A3o%20Instant%20File%20Initialization.sql

Referências:

Segue abaixo mais algumas referências bem interessantes sobre o assunto:

https://www.brentozar.com/blitz/instant-file-initialization/

https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-330-instant-file-initialization-can-be-controlled-from-within-sql-server/

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

https://www.mssqltips.com/sqlservertip/4304/enable-sql-server-instant-file-initialization-for-time-savings/

https://sqlperformance.com/2016/03/io-subsystem/ifi-tempdb-setup

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

6 comentários em “Instant File Initialization x Tempo Restore”

Pedro Antonio Galvão Junior 10 de setembro de 2018 Responder

Luiz, boa tarde.
Parabéns pelo post

Abraços.
- Luiz Lima Autor do post10 de setembro de 2018 Responder
  
  Show! Valeu!!! Espero que tenha gostado.
  
  Também acompanho o seu Blog que é TOP!!! hehehe #tamojunto
  
  Abraço.
  Luiz Lima
Abraão Henrique 10 de setembro de 2018 Responder

Muito Legal o Post, Parabéns!
- Luiz Lima Autor do post10 de setembro de 2018 Responder
  
  Muito obrigado Abraão! Que bom que gostou e espero que tenha te ajudado!
  
  Abraço.
  Luiz Lima
Pingback: Conhecendo o Processo do SQL Server no Windows e Linux – Parte 2 | The SQL Times
Pingback: Casos do Dia a Dia – Best Practices – Restore Bases Teste – Luiz Lima

Instant File Initialization

Observações:

Benefícios:

Considerações sobre Segurança

Script Validação Instant File Initialization

Habilitando o Instant File Initialization

Teste Prático – Instant File Initialization

TESTE 1 – IFI DESABILITADO

TESTE 2 – IFI HABILITADO

Conclusão – Instant File Initialization

Download “Script Validação Instant File Initialization”:

Referências:

Compartilhe isso:

6 comentários em “Instant File Initialization x Tempo Restore”

Deixe uma respostaCancelar resposta