SQL Server - Apresentando WAIT_AT_LOW_PRIORITY

Os administradores de banco de dados geralmente têm a tarefa de gerenciar o tamanho de um banco de dados SQL Server. O comando DBCC SHRINKDATABASE é uma ferramenta poderosa em seu arsenal, permitindo reduzir o tamanho físico dos dados e arquivos de log no banco de dados especificado. No entanto, como acontece com todas as ferramentas poderosas, deve ser usado criteriosamente para evitar potenciais problemas de desempenho. Nesta postagem do blog, exploraremos o comando DBCC SHRINKDATABASE e suas opções, com foco no recurso WAIT_AT_LOW_PRIORITY introduzido no SQL Server 2022 e versões posteriores.

Compreendendo o DBCC SHRINKDATABASE

O comando DBCC SHRINKDATABASE ajuda a reduzir o tamanho dos dados e os arquivos de log em um banco de dados especificado. Ele usa o nome ou ID do banco de dados como argumento, com uma porcentagem alvo opcional que denota o espaço livre que você gostaria de manter no arquivo de banco de dados após ele ter sido reduzido.

O comando também fornece duas opções especiais: NOTRUNCATE e TRUNCATEONLY. A opção NOTRUNCATE move as páginas atribuídas do final do arquivo para páginas não atribuídas na frente do arquivo, compactando efetivamente os dados dentro do arquivo, mas não liberando o espaço livre no final de volta para o sistema operacional. Por outro lado, a opção TRUNCATEONLY libera todo o espaço livre no final do arquivo para o sistema operacional sem mover nenhuma página do arquivo.

Apresentando WAIT_AT_LOW_PRIORITY

A opção WAIT_AT_LOW_PRIORITY é um recurso que pode ser usado junto com o comando DBCC SHRINKDATABASE para gerenciar a contenção de bloqueio de forma mais eficaz. Esse recurso é especialmente útil quando uma operação de redução precisa adquirir um bloqueio de modificação de esquema (Sch-M), que muitas vezes pode causar bloqueio significativo no banco de dados.

Quando um comando de redução é executado no modo WAIT_AT_LOW_PRIORITY, novas consultas que exigem bloqueios de estabilidade de esquema (Sch-S) não são bloqueadas pela operação de redução em espera até que a operação de redução pare de esperar e comece a ser executada. Se uma nova operação de redução no modo WAIT_AT_LOW_PRIORITY não puder obter um bloqueio devido a uma consulta de longa execução, a operação de redução eventualmente atingirá o tempo limite após 1 minuto por padrão e será encerrada sem erros.

A opção WAIT_AT_LOW_PRIORITY assume dois valores possíveis para o argumento ABORT_AFTER_WAIT: SELF e BLOCKERS. A opção SELF é o padrão e permite que a operação de redução saia sem realizar nenhuma ação se estiver sendo bloqueada. A opção BLOCKERS, por outro lado, elimina todas as transações do usuário que estão bloqueando a operação de redução, permitindo que ela continue.

DBCC SHRINKDATABASE (Database, 100) 
WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = BLOCKERS);

Considerações finais

Gerenciar o tamanho de um banco de dados SQL Server é uma parte essencial do trabalho de um administrador de banco de dados. DBCC SHRINKDATABASE, quando usado criteriosamente, pode ajudar a gerenciar o tamanho físico do banco de dados de maneira eficaz. A opção WAIT_AT_LOW_PRIORITY aprimora ainda mais isso, gerenciando a contenção de bloqueio de maneira eficaz e garantindo que as operações de redução do banco de dados não bloqueiem desnecessariamente outras consultas.

No entanto, também é importante notar que a redução e o crescimento constantes do banco de dados podem levar à fragmentação e impactar negativamente o desempenho. Portanto, é essencial ter uma estratégia bem pensada para gerenciar o tamanho do banco de dados, e ferramentas como DBCC SHRINKDATABASE devem fazer parte dessa estratégia, e não de toda a estratégia.

SQL Server - Pare de usar DBCC DBREINDEX & Use ALTER INDEX

Já faz mais de uma década que a instrução DBCC DBREINDEX foi descontinuado, no entanto, de vez em quando ainda os encontro em alguns clientes. Na semana passada, ao revisar o plano de manutenção de um dos clientes, notamos que eles ainda estão usando a sintaxe mais antiga, em vez de usar a nova sintaxe do ALTER INDEX.

Diga Não ao DBCC DBREINDEX

Microsoft sempre muito clara por anos que, se algum recurso é marcado como obsoleto e o recurso de substituição aparece, é preciso começar a planejar a transição. Realmente não faz sentido continuar usando o recurso que será removido pela equipe do produto nas futuras versões.

No entanto, geralmente recebo um pouco de resistência quando tentamos solicitar aos desenvolvedores ou analistas que usem um novo recurso, em vez do recurso que eles estão usando há muitos anos. Eu entendo totalmente a filosofia de Se não está quebrado, não conserte. 

Mas há muitos motivos para mudar do DBCC DBREINDEX e usar o ALTER INDEX. 
Estas são as três limitações principais do DBCC DBREINDEX.

• Ele não suporta a opção de reconstrução online
• Sem suporte para índices recuperáveis
• Não há suporte para compactação de dados

Não é que ele não suporte apenas as três opções acima, mas muitos outros aprimoramentos desde o lançamento do SQL Server 2008.

Sintaxe do ALTER INDEX

Aqui está a sintaxe do índice ALTER INDEX Rebuilding.

1	ALTER INDEX IndexName ON TableName REBUILD;

É uma sintaxe muito simples. Aqui está outra sintaxe para reorganizar o índice.

1	ALTER INDEX IndexName ON TableName REORGANIZE;

Bem é isso. Esta ainda é a minha pergunta para você - você ainda usa o DBCC DBREINDEX
para recriar seus índices. Existe algum motivo específico para continuar usando o recurso
que foi marcado como obsoleto por tantos anos? Será útil saber o motivo de todos
e podemos compartilhar se você publicar sua resposta como um comentário do blog.

Grande abraços

Referência:  Pinal Dave

SQL SERVER – Diferenças entre Clean Cache and Clean Buffer ?

DBCC FREEPROCCACHE é executado limpar o cache do procedimento. A liberação do cache do procedimento causaria, por exemplo, uma instrução SQL ad-hoc a ser recompilada em vez de reutilizar o cache. Se observar através do SQL Profiler, pode-se assistir os eventos Cache Remove ocorrem enquanto DBCC FREEPROCCACHE vai para o trabalho. DBCC FREEPROCCACHE invalidará todos os planos de procedimentos armazenados que o otimizador armazenou na memória e forçará o SQL Server a compilar novos planos na próxima vez que esses procedimentos forem executados.

DBCC DROPCLEANBUFFERS é usado para testar consultas com um cache de buffer frio sem desligar e reiniciar o servidor. DBCC DROPCLEANBUFFERS serve para esvaziar o cache de dados. Todos os dados carregados no cache do buffer devido à execução prévia de uma consulta são removidos.

Exemplo uso:

1 2	DBCC FREEPROCCACHE GO

1 2	DBCC DROPCLEANBUFFERS GO

ATENÇÃO: Risco de perda de performance, usem com cuidado.

Scripts: Clear Cache (buffers) SQL SERVER

O cache de memória é uma área reservada pelo SQL Server, com o objetivo de acelerar a execução de Stored Procedures, ou transações que podem estar sendo processadas com uma maior frequência de requisições; através dos comandos  DBCC (Database Console Commands) podemos realizar os seguintes procedimentos:

DBCC DropCleanBuffers   (Eliminar as páginas de buffer limpas)
DBCC FreeProcChace      (Eliminar todas as entradas do CACHE de Procedures)
DBCC FreeSystemCache  (Limpar as entradas de Cache não utilizadas)

syntax:
DBCC DROPCLEANBUFFERS           
GO
DBCC FREEPROCCACHE                  
GO
DBCC FREESYSTEMCACHE ('ALL')    
GO

returns:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions*

Requires membership in the sysadmin fixed server role.