Perform Volume Maintenance Task

Quanto tempo seu usuário quer esperar para criar ou aumentar o tamanho dos arquivos da sua databases?

Obviamente que nenhuma espera é recomendável mas as vezes isso é inevitável, então vamos deixar as coisas mais rápidas…

Um pequeno teste que fiz no meu computar mostra uma grande diferença quando a opção “Perform Volume Maintenance task”está desabilitada e quando esta habilitada.

Criei um database com o tamanho iniciar do MDF com 20GB e o tamanho do LDF com 10GB.

Primeiramente a opção estava desabitada e o tempo para criar esse database foi de 3:10m (total de 190s)

Depois de habilitar a opção o tempo foi para 41s, ou seja, mais de 75% mais rápido!

Essa funcionalidade deve ser habilitada em servidores SQL Server para reduzir o tempo de criação ou expansão de um arquivo (MDF e NDF)somente esses arquivos se beneficiam dessa opção e o arquivo de log (LDF) não se beneficia.

A melhor prática é alocar (reservar) o espaço necessário para cada arquivo, dessa maneira evitando o crescimento automatico. Uma boa hora para por em pratica as tecnicas aprendidas para dimensionar o tamanho de uma database!!!

Para configurar essa opção é super simples.

No menu Start e digite gpedit.msc

PerformVolumeMaintenance-01

O console de Local Group Policy Editor irá aparecer.

  • Expanda Computer Configuration e depois Windows Settings
  • Expanda Security Settings, e depois expanda Local Policies
  • Selecione a pasta User Rights Assignment
  • No painel a direita, localize a opção Perform volume maintenance task e clique com o direito e acesse as suas propriedades.
  • Adicione a conta de serviço que inicializa o SQL Server

PerformVolumeMaintenanceTask -02

Reinicie a instancia do SQL Server para que seja efetivada a opção.

Se você estiver trabalhando com um cluster lembresse de realizar esse procedimento em todos os nós.

Mas qual é a magica que isso faz?

Quando um arquivo é criado ou aumentado ele é automaticamente preenchido com zeros e por isso existe a demora na realização dessa operação.

Existe uma maneira simples de visualizarmos isso: é habilitando algumas traces flags (use as traces flags apenas para fins de estudo, uma vez que elas não são suportadas pela Microsoft)

Trace flag 3605 – simplesmente redireciona a saida para SQL error log.

Trace flag 3004 – é para mostrar informações sobre criação de arquivos e backups.

USE [master]

GO

DBCC TRACEON(3004, 3605, -1)

GO

CREATE DATABASE [PerformVolumeMaintenance]

GO

DBCC TRACEOFF(3004, 3605, -1)

GO

XP_READERRORLOG

Resultado com Perform Volume Maintenance Task desabilitado.

2013-09-19 19:25:26.710 spid52       Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance.mdf from page 0 to 2560000 (0x0 to 0x4e2000000)

2013-09-19 19:26:49.830 spid52       Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance.mdf

2013-09-19 19:26:50.030 spid52       Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf from page 0 to 1280000 (0x0 to 0x271000000)

2013-09-19 19:27:44.420 spid52       Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf

2013-09-19 19:27:44.980 spid52       Starting up database ‘PerformVolumeMaintenance’.

2013-09-19 19:27:45.040 spid52       FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf from 0x5000 to 0x6000.

2013-09-19 19:27:45.040 spid52       Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)

2013-09-19 19:27:45.040 spid52       Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf

Resultado com Perform Volume Maintenance Task habilitado. Você ira ver apenas o arquivo de log sendo preenchido com zeros.

2013-09-19 19:45:44.350 spid54       Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf from page 0 to 1280000 (0x0 to 0x271000000)

2013-09-19 19:46:25.000 spid54       Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2014CTP1\MSSQL\DATA\PerformVolumeMaintenance_log.ldf

Agora faça o teste você mesmo e comprove…

Referencias:

http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

http://www.sqlskills.com/blogs/paul/misconceptions-around-instant-file-initialization/

http://technet.microsoft.com/en-us/library/ms188396.aspx

This is Tiago Balabuch's website, and this is a bit of copy about him. He is enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours a Data Engineer who loves what he does and he is traveling in the cloud and surfing on the wave of the moment called Azure! Originally from Brazil and with encouragement from family and friends, Tiago moved to Europe in 2017 where lives in Ireland. In addition to being a data engineer, he is also active speaker in the SQL PASS events and keeps up to date on the key technologies and technical certifications. Tiago hold these certification MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2016/2014/2012. Simply psychedelic and manic he is just one more freak who likes SQL Server and its new features ...

Responda