Olá, pessoal, bom dia.
Tudo bem? E a i como esta a loucura na sua cidade, devido a esta paralisação dos caminhoneiros em todo Brasil? Posso dizer que aqui em São Roque, interior do estado de São Paulo não esta nada fácil.
Independente da falta de combustível, gás de cozinha, entre outras coisas, não me pode faltar força de vontade e disposição para estar aqui no meu blog, publicando mais um post da sessão Para que serve, sendo este o post de número 21.
É a vida de um DBA e MVP não é fácil, mesmo com o Brasil muito prejudicado e praticamente parado, tenho alguns afazeres para hoje, por este e outros motivos, acordei bem cedo para compartilhar com vocês um dos novos recursos adicionados ao Microsoft SQL Server 2017.
Como todos nós já sabemos, a cada nova versão que a Microsoft disponibilizado do SQL Server, uma nova avalanche de conceitos, funcionalidades, comandos e diversidade de possibilidades são adicionadas ao produto, no post de hoje vou apresentar propriamente uma nova funcionalidade deste fascinante Sistema Gerenciador de Banco de Dados, que veio justamente para ser um divisor de águas em uma das principais atividades de qualquer DBA, o tão temido processo de reindexação(reindex) ou reconstrução(rebuild) de índices em nossos ambientes de bancos de dados.
Acredito que você Administrador de Servidores, DBA ou Profissional de TI, já deve ter se deparado por algum momento em situações que necessitavam ou requeriam o processamento de atividades relacionadas aos procedimentos de manutenção de um ou mais índices existentes em um banco de dados, e ai aquela tão ingrata pergunta.
A que horas vamos realizar este procedimento sem impactar em nossos ambientes? E logicamente você já se deparava em seus pensamentos: “Meu deus, vou ter que passar mais uma noite acordado, fazendo manutenções….” Posso dizer por experiência própria que esta é uma da mais duras realidades que eu já enfrentei nesta minha longa jornada de profissional de TI desde 1994… Mas seguinte em frente, a partir do Microsoft SQL Server 2017 isso mudou, o time de engenheiros e desenvolvedores desta nova versão adicionaram um novo recurso denominado “Resumable Online Index Rebuilds”, em uma simples tradução “Reconstrução Online de Índice resumível”, ou seja, a possibilidade de reconstruir um índice de forma online de acordo com a sua necessidade, tendo a possibilidade de interromper o processo de reconstrução sem correr qualquer risco de perda.
Isso não é coisa de outro mundo? A resposta é não, isso é coisa do Microsoft SQL Server 2017.
Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 21 da sessão Para que serve. Então seja bem vindo ao #21 – Para que serve – Resumable Online Index Rebuilds.
Espero que você goste….
Introdução
Quando decidimos trabalhar na área de tecnologia, em diversos momentos temos que saber que esta é um das diversas áreas profissionais que no decorrer da nossa carreira somos obrigados a praticamente abrir mão de nossa vida sociais, familiar e até mesmo pessoal.
Trabalhar na área de tecnologia da informação, nos dias de hoje tem mudado muito se comparado ao início dos anos 80, 90 e provavelmente a partir dos anos 2000 isso mudou mais ainda, principalmente para aqueles que optaram assim como eu para trabalhar com banco de dados, quem nunca teve que passar horas e horas madrugada a dentro realizando manutenções em seus ambientes de bancos de dados, com a “simples” missão de tudo estar funcionando a partir de um determinado horário, é parece fácil, parece ser algo simples, parece ser algo suportável, mas não é, e pensando nisso(demorou) que a partir da versão 2017 do Microsoft SQL Server, nós Administradores de Bancos de Dados e Profissionais de Tecnologia, temos a possibilidade de realizar algumas das mais preocupantes atividades de administração de bancos de dados de uma maneira mais usual, simples e pode-se dizer “humana” que é a atividade de reconstrução de índice.
As atividades relacionadas a manutenções de bancos de dados, ainda mais aquelas relacionadas diretamente a índices, são por diversas vezes as mais demoradas, atividades que dependem totalmente do uso de CPU e Disco, recursos físicos de hardwares que podem apresentar em algum momento sobrecarga de processamento, ocasionando situações de contenção “gargalos”, lentidão na leitura e escrita de dados, que nos obrigam a ter que interromper as atividades em execução ou planejadas a posterior.
Legal, acredito que você já tenha conhecido um pouco sobre este recurso de forma conceitual, vamos agora colocar a mão nos teclados e conhecer de forma prática como fazer dele, para isso vamos preparar nosso ambiente a partir de agora.
Criando o Ambiente
Para realizar nossa simples prática, começaremos pela execução do Bloco de Código 1, responsável por criar a seguinte estrutura:
- Database: ResumableOnlineIndexRebuilds;
- Table: ResumableOnlineIndexRebuildsTable;
- Clustered Index: PK_ResumableOnlineIndexRebuildsTable_Codigo; e
- Data Compression: Page.
— Bloco de Código 1 —
— Criando o Banco de Dados —
Create Database ResumableOnlineIndexRebuilds
Go
— Acessando o Banco de Dados —
Use ResumableOnlineIndexRebuilds
Go
— Criando a Tabela ResumableOnlineIndexRebuildsTable —
Create TABLE ResumableOnlineIndexRebuildsTable
(Codigo int IDENTITY(1,1) NOT NULL,
Cliente int NOT NULL,
Vendedor varchar(30) NOT NULL,
Quantidade smallint NOT NULL,
Valor numeric(18, 2) NOT NULL,
Data date NOT NULL
Constraint [PK_ResumableOnlineIndexRebuildsTable_Codigo] Primary Key (Codigo))
WITH(Data_Compression=PAGE)
Go
Perfeito, ambiente criado, vamos para o próximo passo, Bloco de Código 2, responsável por inserir um massa de dados aleatória, com uma quantidade de linhas de registros que pode variar de 1 até 1.ooo.ooo(milhão de linhas), contar quantas linhas temos em nossa tabela e seu espaço de alocado:
— Bloco de Código 2 —
— Inserindo a Massa de Dados na Tabela ResumableOnlineIndexRebuildsTable —
Declare @Texto Char(130),
@Posicao TinyInt,
@ContadorLinhas Int
Set @Texto = ‘0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ’ — Existem 130 caracteres neste texto —
Set @ContadorLinhas = Rand()*1000000 — Definir a quantidade de linhas para serem inseridas —
While (@ContadorLinhas >=1)
Begin
Set @Posicao=Rand()*130
If @Posicao <=125
Begin
Insert Into ResumableOnlineIndexRebuildsTable (Cliente, Vendedor, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao+2,2),SubString(@Texto,@Posicao-4,4),SubString(@Texto,@Posicao+2,4)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))
End
Else
Begin
Insert Into ResumableOnlineIndexRebuildsTable (Cliente, Vendedor, Quantidade, Valor, Data)
Values(@ContadorLinhas,
Concat(SubString(@Texto,@Posicao-10,1),SubString(@Texto,@Posicao+4,6),SubString(@Texto,@Posicao-12,3)),
Rand()*1000,
Rand()*100+5,
DATEADD(d, 1000*Rand() ,GetDate()))
End
Set @ContadorLinhas = @ContadorLinhas – 1
End
Observação: A quantidade de linhas e tempo de processamento vai depender única e exclusivamente do hardware que você esta utilizando.
— Contando a quantidade de linhas da Tabela ResumableOnlineIndexRebuildsTable —
Select Count(*) From ResumableOnlineIndexRebuildsTable
Go
— Descobrindo o tamanho da Tabela Pedidos —
Exec sp_spaceused ‘ResumableOnlineIndexRebuildsTable’
Go
Por enquanto nenhuma novidade, acredito que você deve ter conseguido executar os blocos de código de forma simples e tranquilo, nosso próximo passo é conhecer e aplicar o processo de rebuild de índice através deste novo recurso, para isso vamos começar utilizando o Bloco de Código 3 apresentado abaixo:
— Bloco de Código 3 —
Alter Index [PK_ResumableOnlineIndexRebuildsTable_Codigo] ON ResumableOnlineIndexRebuildsTable
Rebuild With(ONLINE=ON, RESUMABLE=ON)
Go
Note que estamos fazendo uso neste procedimento de rebuild de dois novos parâmetros adicionados ao comando Alter Index, sendo eles:
- OnLine: Determina que o processo de rebuild será feito de forma online (por páginas) ou não. Vale ressaltar que o Resumable index rebuild tem suporte somente para o rebuild online, sendo assim, este parâmetro é obrigatório e devemos sempre utilizar o parâmetro ONLINE=ON.
- Resumable: Orienta o Database Engine a definir se o rebuild será feito permitindo ou não o uso da opção de Pause/Resume.
Além destes dois principais parâmetros, foram também adicionados outros três como complementares:
- Max_Duration: Permite definir em minutos, a quantidade de tempo que o rebuild irá executar antes de ser suspenso automaticamente. Esse valor deve ser maior que 0 e menor ou igual a 10080 (1 semana), algo que poderá lhe permitir estabelecer um janela de trabalho e aplicar o procedimento de rebuild de forma programada.
- Pause: Utilizando esse parâmetro, a operação de rebuild será pausada e ficará aguardando uma nova instrução Alter Index para este índice dar continuidade ao processo ou então o comando ABORT, para interromper o rebuild.
- Abort: Parâmetro utilizado para interromper o rebuild do índice.
Importante
Dependendo do conjunto de parâmetros utilizados e seus respectivos valores, o Database Engine poderá apresentar algumas mensagens de erros dentre elas:
Mensagem 1 – Informa que você fez uso do parâmetro Resumable=On, mas o parâmetro Online=Off.
Msg 11438, Level 15, State 1, Line 2
The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’
Mensagem 2 – Orienta e informa caso o tempo limite informado acima seja atingido e processo de rebuild ainda não foi concluído e mesmo será interrompido:
Msg 3643, Level 16, State 1, Line 20
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
Msg 596, Level 21, State 1, Line 19
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 19
A severe error occurred on the current command. The results, if any, should be discarded.
Seguindo em frente, vamos agora similar um processo de resumo (resume) do nosso índice. Vamos então realizar o processo de resume através do Bloco de Código 4:
— Bloco de Código 4 —
Alter Index [PK_ResumableOnlineIndexRebuildsTable_Codigo] ON ResumableOnlineIndexRebuildsTable
Resume
Go
Nota: Uma forma simples e prática de simular um processo de interrupção do Resumable Index é interromper a execução da query clicando no botão Cancel Executing Query.
Outro detalhe importante, estamos fazendo uso do parâmetro Resume o qual deverá informar ao Database Engine que o procedimento de alteração do nosso índice deverá ser resumido. Quando o comando resume for utilizado e no respectivo momento não existir um procedimento de resumable index aplicado, será retornada a seguinte mensagem de erro:
Msg 10638, Level 16, State 1, Line 70
ALTER INDEX ‘RESUME’ failed. There is no pending resumable index operation for the index ‘PK_ResumableOnlineIndexRebuildsTable_Codigo’ on ‘ResumableOnlineIndexRebuildsTable’.
Monitorando através da sys.index_resumable_operations
Cada alteração aplicada aos nossos índices pode ser monitorada em tempo real através do uso da visão de sistema: sys.index_resumable_operations, a qual teve o acréscimo de uma nova coluna denominada is_resumable, que apresenta a função de informar se o respectivo índice possui o procedimento de resumable aplicado.
O próximo passo consiste no procedimento de pausa (pause), ou seja, realizar uma pausa na execução do resumable index aplicado ao nosso índice, para tal vamos utilizar o Bloco de Código 5 abaixo:
— Bloco de Código 5 —
Alter Index [PK_ResumableOnlineIndexRebuildsTable_Codigo] ON ResumableOnlineIndexRebuildsTable
Pause
Go
Ao realizar o procedimento de pause interrompendo o rebuild de um índice, a sessão responsável pela execução da operação de rebuild irá receber a mesma mensagem de erro de quando o rebuild é pausado:
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 16
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 16
A severe error occurred on the current command. The results, if any, should be discarded.
Vale ressaltar que ao realizar o procedimento de pausa (Pause) a um determinado índice, o mesmo será adicionado na visão sys.index_resumable_operations, tendo a coluna state_desc preenchida com o PAUSED, sendo assim, este rótulo será mantido até que uma outra instrução de Resume ou Abort venha a ser aplicado ao mesmo.
Por fim, nosso último passo consiste em similar o processo de interrupção do procedimento de resumable index, fazendo com que o mesmo deixe de ser mantido como um índice resumível de forma online, através da parâmetro Abort, para isso vamos utilizar o Bloco de Código 6 a seguir:
— Bloco de Código 6 —
Alter Index [PK_ResumableOnlineIndexRebuildsTable_Codigo] ON ResumableOnlineIndexRebuildsTable
Abort
Go
De forma idêntica realizada pelo parâmetro Pause, ao realizar o procedimento de Abort, interrompendo o rebuild de um índice, a sessão responsável pela execução da operação de rebuild irá receber a mesma mensagem de erro de quando o rebuild é pausado:
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 16
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 16
A severe error occurred on the current command. The results, if any, should be discarded.
Mas que beleza, conseguimos realizar o procedimento de abort, neste momento nosso índice não esta mais sendo reconhecido como resumable index, desta forma, o mesmo não poderá ser utilizado com um índice online resumível a qualquer momento.
Praticamente chegamos ao final deste post, falta um pouquinho para encerrar, pois, ainda tenho um último detalhe importante para compartilhar com você a seguir.
Limitações
Pois bem, como tudo em nossas vidas, sempre nos deparamos com situações ou condições que podem nos limitar de fazer uso ou realizar determinadas ações e isso não é diferente com o Resumable Online Index Rebuilds:
- Suporta somente índices no formato Row Store;
- Não possui suporte nativo para indexação online aplicada ao system database TEMPDB, ou seja, SORT_IN_TEMPDB do Alter Index não é aplicável;
- Não possui suporte nativo para colunas do tipo TimeStamp;
- Não possui suporte nativo com colunas calculadas (computadas);
- Não é possível utilizar esse recurso em índices desativados; e
- O Resumable OnLine Index Rebuil não pode ser utilizada dentro de uma transação de usuário, somente em transações relacionadas a atividades de manutenção de índices aplicadas diretamente ao escopo de banco de dados.
Agora sim, chegamos ao final, mas que trabalheira danada deu este post.
Espero que você tenha gostado, eu acredito que sim.
Referências
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-resumable-operations
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-index-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-index-transact-sql
Links
Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:
https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2017/12/15/18-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2017/11/24/17-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2017/10/01/16-para-que-serve/
Conclusão
Em algum momentos, a otimização de desempenho do banco de dados é sempre uma tarefa chave para o DBA. A manutenção de índice desempenha um papel vital na otimização do desempenho do banco de dados.
Às vezes, em ambientes OLTP (Online Transaction Processing ou Processamento de Transações em Tempo Real) que apresentam um longo tempo de processamentos, temos janelas de manutenção muito limitada e se um índice é grande, pode não ter tempo suficiente para reconstruir o índice.
Analisando estas situações, o Resumable Online Index Rebuilds se apresenta como uma solução de extrema importância e grande aliada na vida do DBA SQL Server, a sua adoção e aplicabilidade pode melhorar drasticamente as rotinas de reconstrução (rebuild) de índices, no que diz respeito ao seu volume de dados, por consequência seu tamanho e claro o quanto este elemento representa nas tarefas de pesquisa de dados realizadas pelas aplicações que fazem acesso a ele.
Realizar uma boa manutenção em qualquer ambiente de banco de dados, é algo que nos traz tranquilidade, saber que estamos adotando soluções para manter nossos ambientes protegidos, íntegros e organizados e papel fundamental para qualquer profissional responsável direta ou indiretamente por esta área.
Neste post, você pode conhecer este novo recurso adicionado a partir do Microsoft SQL Server 2017, o Resumable Online Index Rebuilds, uma importante melhoria adicionada ao produto, que com certeza vai permitir que muitos profissionais de tecnologia e DBAs possam passar noites de mais tranquilas.
E isso ai, este é o fantástico Microsoft SQL Server, que a cada versão ou atualização também esta preocupado com a qualidade de vida daqueles que assim como eu são apaixonados por este produtos…
Vai SQL Server, Vai SQL Server….
Agradecimentos
Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.
Um forte abraço, até o próximo post da sessão Para que serve…..
Uma ótima segunda – feira e boa semana.
Valeu.
Curtir isso:
Curtir Carregando...
Figura 1 – Código Transact-SQL apresentado no Script Challenge 14.
Figura 2 – Informações relacionadas a estimativa de tamanho do arquivo de backup e espaço livre em disco em megabytes.
