ALTER INDEX RESUMABLE = ON

Fala galera, trazendo uma novidade do SQL Server 2017 que gostei muito e acredito que vocês irão curtir também, a opção RESUMABLE durante uma operação de ALTER INDEX, essa característica foi adicionada no SQL Server 2017 e não está disponível em versões anteriores.

Você esta lá todo feliz rodando seu rebuild e nos 99% ele falha por falta de espaço em disco ou sua operação de rebuild começa a gerar muitos Locks ou seu Transaction Log esta perto de explodir ou seu servidor fica lento por falta de recursos, entre outros motivos que force você a parar sua operação, é triste, pois a execução é abortada e desfeita toda alteração já realizada e você terá que refaze-la novamente em outro momento, no SQL Server 2017 temos uma nova característica RESUMABLE= ON onde você tem a opção de pausar ou abortar uma operação de REBUILD, se você pausar a operação, posteriormente terá a opção de retomar o REBUILD da mesma posição que parou, isso mesmo, se você parou a operação quando ela estava em 99%, no momento em que retomar ela iniciará a partir dos 99% e aquele tempo gasto anteriormente valeu a pena, também é valido para falhas na operação como falta de espaço, ao falhar a operação de rebuild o status de onde parou ficará salvo e você retoma a partir daquele momento, incrível não?!

Outra coisa super legal é que uma vez pausado a operação de Rebuild, seu Transaction Log poderá circular normalmente se não houver nada que o impeça, pois a transação de REBUILD é finalizada e não fica segurando o seu Transaction Log, então em momentos que seu LOG estiver explodindo por causa de um REBUILD, você poderá pausar a operação fazer um backup de Log e deixar ele circular, depois retomar a operação de REBUILD.

Você pode utilizar em conjunto com  outras opções como a WAIT_AT_LOW_PRIORITY, sua operação de REBUILD irá aguardar por bloqueios de baixa prioridade e retomar em seguida, isso também é muito TOP, essa opção foi adicionada no SQL Server 2014, mas agora ela fica mais útil junto da opção RESUMABLE.

Bom vamos para um exemplo na prática:

DROP TABLE IF EXISTS TB_FRAG
GO
CREATE TABLE TB_FRAG (ID INT PRIMARY KEY DEFAULT(RAND() * 1000000000), CAMPO1 VARCHAR(100) DEFAULT(‘TESTE’),CAMPO3 UNIQUEIDENTIFIER DEFAULT NEWID(),CAMPO4 UNIQUEIDENTIFIER DEFAULT NEWID())
GO

INSERT INTO TB_FRAG DEFAULT VALUES
GO 10000000

Preparando o ambiente, foi gerado uma grande massa de dados com valores aleatórios no campo da Primary Key, essa operação pode demorar minutos dependendo do seu disco, pois vai gerar diversas operações de page split, isso irá fragmentar completamente o índice cluster criado pela Primary Key.

SP_SHOWINDEX @TABLE_NAME = ‘TB_FRAG’,@INDEX_FRAGMENTATION = 1
GO

Screen Shot 11-30-17 at 10.53 PM

Executando a procedure SP_SHOWINDEX você poderá ver a quantidade de páginas e qual a fragmentação do seu índice, nosso caso temos 99% de fragmentação.

— sessão 1

ALTER INDEX PK__TB_FRAG__3214EC277274E75A on TB_FRAG REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=2) ;
GO

Na sessão 1 irei iniciar minha operação de REBUILD, informando o parâmetro RESUMABLE=ON, isso irá ativar a gravação de status do índice na nova DMV adicionada no SQL Server 2017 chamada sys.index_resumable_operations.

–sessão 2

SELECT total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count
FROM sys.index_resumable_operations;
GO
SP_SHOWINDEX @TABLE_NAME = ‘TB_FRAG’,@INDEX_FRAGMENTATION = 1
GO

Screen Shot 11-30-17 at 11.00 PM 001

Na sessão 2 (lado direito) posso acompanhar o status da operação através da nova DMV, podemos ver que em 25 segundos foi realizado 34% da operação, mas pela SP_SHOWINDEX ainda mantém 99% de fragmentação.

 

–sessão 2

ALTER INDEX PK__TB_FRAG__3214EC277274E75A on TB_FRAG PAUSE ;
GO

Screen Shot 11-30-17 at 11.02 PM

A operação de REBUILD foi pausada pela sessão 2 e recebemos um erro na sessão 1 que estava executando o REBUILD com RESUMABLE, nesse momento a operação de REBUILD estava em 51%.

Observe que nosso Transaction Log está aguardando um Backup de Log para poder circular.

BACKUP LOG [DB_POWER] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSERVER2017\MSSQL\Backup\DB_POWER_LOG.trn’
WITH NOFORMAT, NOINIT, NAME = N’DB_POWER-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Screen Shot 11-30-17 at 11.05 PM

Nesse momento nada mais impede nosso Transaction Log de circular e mesmo assim  podemos retomar nossa operação de REBUILD.

 

USE [DB_POWER]
GO
DBCC SHRINKFILE (N’DB_POWER_log’ , 5)
GO
USE [master]
GO
ALTER DATABASE [DB_POWER] MODIFY FILE ( NAME = N’DB_POWER_log’, MAXSIZE = 100MB , FILEGROWTH = 10MB )
GO

Antes de retomar o REBUILD reduzi o arquivo de LOG e limitei seu crescimento, isso irá gerar um erro proposital quando eu retomar o REBUILD.

ALTER INDEX PK__TB_FRAG__3214EC2737B0D8AC on TB_FRAG RESUME;

Screen Shot 11-30-17 at 11.16 PM

Retomando a operação de REBUILD, neste momento está em 58%.

Screen Shot 11-30-17 at 11.17 PM

Ops, nosso Transaction Log encheu e não temos mais espaço, claro que você nunca quer que isso aconteça, então pause sua operação de REBUILD antes que ela pare sua base de dados rs. Mas como podemos ver nossa operação de REBUILD ainda continua salva e parou agora em 67%.

 

Screen Shot 11-30-17 at 11.19 PM 001

Após corrigido nosso problema com o espaço no LOG, retomado novamente nossa operação de REBUILD e foi finalizado com sucesso, repare que a DMV sys.index_resumable_operations não possuí mais informações e na SP_SHOWINDEX nossa coluna de fragmentação foi atualizada e está em 14% de fragmentação.

Ah, mas se ao invés de dar um PAUSE eu rodar um ABORT? Simplesmente o status da sua operação será apagado da DMV sys.index_resumable_operations e não será possível retomar a operação.

Bom galera é isso, espero que tenham gostado dessa nova funcionalidade, eu achei muito TOP, um forte abraço e até a próxima.

Referencias:

BOL:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/alter-index-transact-sql

MSSQLTIPS:

https://www.mssqltips.com/sqlservertip/4987/sql-server-2017-resumable-online-index-rebuilds/

Reginaldo Silva

#SP_SHOWINDEX #YourIndexInformation #LoveYourIndexes

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s