Explore as verificações de manutenção do banco de dados
O otimizador de consulta utiliza informações estatísticas dos índices para tentar construir o plano de execução mais ideal.
No Azure, as tarefas de manutenção do SQL, como backups e verificações de integridade, são tratadas para você e, embora você possa se safar com atualizações automáticas mantendo suas estatísticas atualizadas, às vezes não é suficiente.
Ter índices e estatísticas saudáveis garantirá que qualquer plano terá um desempenho ótimo. A manutenção do índice deve ser realizada regularmente à medida que os dados em seus bancos de dados mudam ao longo do tempo. Você pode alterar sua estratégia de manutenção de índice com base na frequência de modificações em seus dados.
Reconstruir e reorganizar
A fragmentação do índice ocorre quando a ordenação lógica dentro das páginas de índice não corresponde à ordem física. As páginas podem ficar fora de ordem durante instruções de modificação de dados de rotina, como UPDATE, DELETEe INSERT. A fragmentação pode introduzir problemas de desempenho devido à E/S extra necessária para localizar os dados que estão sendo referenciados pelos ponteiros nas páginas de índice.
À medida que os dados são inseridos, atualizados e excluídos dos índices, a ordem lógica no índice não corresponderá mais à ordem física dentro das páginas e entre as páginas, compondo os índices. Além disso, com o tempo, as modificações de dados podem fazer com que os dados fiquem dispersos ou fragmentados no banco de dados. A fragmentação pode degradar o desempenho da consulta quando o mecanismo de banco de dados precisa ler páginas extras para localizar os dados necessários.
Uma reorganização de um índice é uma operação on-line que desfragmentará o nível de folha do índice (agrupado e não agrupado). Este processo de desfragmentação irá reordenar fisicamente as páginas de nível de folha para corresponder à ordem lógica dos nós da esquerda para a direita. Durante esse processo, as páginas de índice também são compactadas com base no valor de fator de preenchimento configurado.
Uma reconstrução pode ser online ou offline, dependendo do comando executado ou da edição do SQL Server que está sendo utilizada. Um processo de reconstrução offline será descartado e recriará o próprio índice. Se você puder fazer isso on-line, um novo índice será construído em paralelo com o índice existente. Uma vez que o novo índice tenha sido construído, o existente será descartado e, em seguida, o novo será renomeado para corresponder ao nome do índice antigo. Tenha em mente que a versão online exigirá mais espaço, pois o novo índice é construído em paralelo ao índice existente.
A orientação comum para a manutenção do índice é:
> 5% mas < 30% - Reorganizar o índice
>30% - Reconstruir o índice
Utilize estes números como recomendações gerais. Dependendo da carga de trabalho e dos dados, talvez seja necessário ser mais assertivo ou, em alguns casos, adiar a manutenção do índice para bancos de dados que executam principalmente consultas que buscam páginas específicas.
As plataformas SQL Server e SQL do Azure oferecem DMVs que permitem detetar fragmentação em seus objetos. Os DMVs mais usados para esta finalidade são sys.dm_db_index_physical_stats para índices b-tree e sys.dm_db_column_store_row_group_physical_stats para columnstore indexes.
Uma outra coisa a notar é que as reconstruções do índice fazem com que as estatísticas do índice sejam atualizadas, o que pode ajudar ainda mais o desempenho. A reorganização do índice não atualiza as estatísticas.
A Microsoft introduziu operações de índice de reconstrução retomáveis com o SQL Server 2017. A opção de operações de índice de reconstrução retomáveis fornece mais flexibilidade no controle de quanto tempo uma operação de reconstrução pode impor a uma determinada instância. Com o SQL Server 2019, a capacidade de controlar um grau máximo de paralelismo associado foi introduzida, fornecendo ainda mais controle granular aos administradores de banco de dados.
Estatísticas
Ao fazer o ajuste de desempenho no SQL do Azure, entender a importância das estatísticas é fundamental.
As estatísticas são armazenadas no banco de dados do usuário como objetos binários grandes (blobs). Esses blobs contêm informações estatísticas sobre a distribuição de valores de dados em uma ou mais colunas de uma tabela ou exibição indexada.
As estatísticas contêm informações sobre a distribuição dos valores de dados dentro de uma coluna. O otimizador de consulta usa estatísticas de coluna e índice para determinar a cardinalidade, que é o número de linhas que uma consulta deve retornar.
As estimativas de cardinalidade são então usadas pelo otimizador de consulta para gerar o plano de execução. As estimativas de cardinalidade também ajudam o otimizador a determinar que tipo de operação (por exemplo, busca de índice ou varredura) usar para recuperar os dados solicitados.
Para ver a lista de estatísticas definidas pelo usuário com a data da última atualização, execute a consulta abaixo:
SELECT sp.stats_id,
name,
last_updated,
rows,
rows_sampled
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1
Criar estatísticas
Quando você tem AUTO_CREATE_STATISTICS a opção de ON, o otimizador de consulta cria estatísticas na coluna indexada por padrão. O otimizador de consulta também cria estatísticas para colunas únicas em predicados de consulta.
Esses métodos fornecem planos de consulta de alta qualidade para a maioria das consultas. Às vezes, talvez seja necessário criar mais estatísticas usando CREATE STATISTICS a instrução para melhorar planos de consulta específicos.
Recomenda-se manter a opção ativada AUTO_CREATE_STATISTICS , pois ela permitirá que o otimizador de consulta crie estatísticas para colunas de predicados de consulta automaticamente.
Sempre que se deparar com as seguintes situações, considere a criação de estatísticas:
- O Orientador de Otimização do Mecanismo de Banco de Dados sugere a criação de estatísticas
- O predicado de consulta contém várias colunas que ainda não estão no mesmo índice
- A consulta seleciona a partir de um subconjunto de dados
- A consulta tem estatísticas em falta
Automatização de tarefas de manutenção
O SQL do Azure fornece ferramentas nativas para executar tarefas de manutenção de banco de dados para fins de automação. Diferentes ferramentas estão disponíveis dependendo da plataforma onde o banco de dados está sendo executado.
SQL Server em uma máquina virtual do Azure
Você tem acesso a serviços de agendamento, como o SQL Agent ou o Agendador de Tarefas do Windows. Essas ferramentas de automação podem ajudar a manter a quantidade de fragmentação dentro dos índices a um mínimo. Com bancos de dados maiores, um equilíbrio entre uma reconstrução e uma reorganização de índices deve ser encontrado para garantir um desempenho ideal. A flexibilidade fornecida pelo SQL Agent ou pelo Agendador de Tarefas permite executar trabalhos personalizados.
Base de Dados SQL do Azure
Devido à natureza do Banco de Dados SQL do Azure, você não tem acesso ao SQL Server Agent nem ao Agendador de Tarefas do Windows. Sem esses serviços, a manutenção do índice deve ser criada usando outros métodos. Há três maneiras de gerenciar operações de manutenção para o Banco de dados SQL:
Runbooks da Automatização do Azure
Trabalho do SQL Agent do SQL Server em uma máquina virtual do Azure (chamada remota)
Trabalhos elásticos do SQL do Azure
Instância Gerida do Azure SQL
Assim como acontece com o SQL Server em uma Máquina Virtual do Azure, você pode agendar trabalhos em uma Instância Gerenciada do SQL por meio do SQL Server Agent. O uso do SQL Server Agent oferece flexibilidade para executar código projetado para reduzir a fragmentação dentro dos índices no banco de dados.