Otimize o armazenamento do banco de dados

Concluído

Para otimizar o armazenamento do banco de dados, você deve considerar o preenchimento proporcional e a configuração tempdb.

Compreender o desempenho de E/S

O desempenho de E/S pode ser crítico para uma aplicação de base de dados. O SQL do Azure abstrai o posicionamento do ficheiro físico, mas existem métodos para garantir que obtém o desempenho de E/O necessário.

As operações de entrada/saída por segundo (IOPS) podem ser importantes para a sua aplicação. Certifique-se de ter escolhido a camada de serviço e vCores certos para suas necessidades de IOPS. Entenda como medir IOPS para suas consultas locais se você estiver migrando para o Azure. Se tiver restrições de IOPS, poderá ver longas esperas de E/S. No modelo de compra vCore, você pode escalar vCores ou mudar para Business Critical ou Hyperscale se não tiver IOPS suficientes. Para cargas de trabalho de produção, ao usar DTU, recomendamos mudar para o nível Premium.

A latência de E/S é outro componente importante para o desempenho de E/S. Para uma latência de E/S mais rápida para a Base de Dados SQL do Azure, considere a opção Crítico para a Empresa ou Hyperscale. Para obter uma latência de E/S mais rápida no SQL Managed Instance, passe para a opção Crítico para a Empresa ou aumente o tamanho do ficheiro ou o número de ficheiros da base de dados. Melhorar a latência do log de transações pode exigir que você use transações de várias instruções.

Ficheiros e grupos de ficheiros

Os profissionais do SQL Server utilizam muitas vezes ficheiros e grupos de ficheiros para melhorar o desempenho de E/S através do posicionamento do ficheiro físico. O SQL do Azure não permite que os utilizadores posicionem ficheiros em sistemas de disco específicos. No entanto, o SQL do Azure tem compromissos de recursos para o desempenho de E/S em relação a taxas, IOPS e latências. Deste modo, abstrair o utilizador do posicionamento físico do ficheiro pode ser uma vantagem.

A Base de Dados SQL do Azure tem apenas um ficheiro de base de dados (por norma, o Hyperscale tem vários) e o tamanho máximo é configurado através de interfaces do Azure. Não há funcionalidade para criar mais arquivos.

A Instância Gerenciada SQL do Azure dá suporte à adição de arquivos de banco de dados e à configuração de tamanhos, mas não ao posicionamento físico de arquivos. Você pode usar o número de arquivos e tamanhos de arquivo para a Instância Gerenciada do SQL para melhorar o desempenho de E/S. Além disso, os grupos de ficheiros definidos por utilizadores são suportados pelo SQL Managed Instance para fins de gestão.

Descrever o preenchimento proporcional

Ao inserir 1 gigabyte de dados em um banco de dados do SQL Server com dois arquivos de dados, você pode esperar que cada arquivo aumente em aproximadamente 512 megabytes. No entanto, nem sempre é assim. O SQL Server distribui dados com base no tamanho de cada arquivo. Por exemplo, se ambos os arquivos de dados são 2 gigabytes, os dados seriam distribuídos uniformemente. Mas se um arquivo é de 10 gigabytes e o outro é de 1 gigabyte, cerca de 900 MB iriam para o arquivo maior e 100 MB para o menor. Esse comportamento é comum em qualquer banco de dados, mas no tempdb de gravação intensiva, um padrão de gravação irregular pode criar um afunilamento no arquivo maior, pois ele lida com mais gravações.

Configurar o Tempdb no SQL Server

O SQL Server deteta o número de CPUs disponíveis durante a instalação e configura o número apropriado de arquivos, até oito, com dimensionamento uniforme. Além disso, os comportamentos dos sinalizadores de rastreamento 1117 e 1118 são integrados ao mecanismo de banco de dados, mas apenas para tempdb. Para cargas de trabalho com cargas de trabalho pesadas em tempdb, pode ser benéfico aumentar o número de arquivos tempdb além de oito, correspondendo ao número de CPUs em sua máquina.

Você usa tempdb da mesma maneira para SQL Server e Azure SQL. Observe, no entanto, que sua capacidade de configuração tempdb é diferente, incluindo o posicionamento de arquivos, o número e o tamanho dos arquivos e tempdb as opções de configuração.

O SQL Server usa tempdb para várias tarefas além de apenas armazenar tabelas temporárias definidas pelo usuário. Ele é usado para tabelas de trabalho que armazenam resultados de consulta intermediários, operações de classificação e armazenamento de versão para controle de versão de linha, entre outras finalidades. Devido a essa utilização extensiva, é crucial colocar o tempdb no armazenamento de menor latência disponível e configurar corretamente seus arquivos de dados.

Os arquivos de banco de dados são sempre armazenados automaticamente em unidades SSD locais, portanto, o desempenho de tempdb E/S não deve ser um problema.

Os profissionais do SQL Server geralmente usam mais de um arquivo de banco de dados para particionar alocações para tempdb tabelas. Para o Banco de Dados SQL do Azure, o número de arquivos é dimensionado com o número de vCores (por exemplo, dois vCores é igual a quatro arquivos) com um máximo de 16. O número de arquivos não é configurável por meio do T-SQL em relação tempdbao , mas você pode configurá-lo alterando a opção de implantação. O tamanho máximo de é dimensionado tempdb por número de vCores. Com o SQL Managed Instance, obtém 12 ficheiros independentemente dos vCores.

A opção MIXED_PAGE_ALLOCATION de banco de dados é definida como OFF e AUTOGROW_ALL_FILES está definida como ON. Você não pode configurar isso, mas, como no SQL Server, esses são os padrões recomendados.

O tempdb recurso de otimização de metadados introduzido no SQL Server 2019, que pode aliviar a contenção de trava pesada, não está atualmente disponível no Banco de Dados SQL do Azure ou na Instância Gerenciada do SQL do Azure.

Configuração da base de dados

Geralmente, você configura um banco de dados com o T-SQL ALTER DATABASE e ALTER DATABASE SCOPED CONFIGURATION instruções. Muitas das opções de configuração de desempenho estão disponíveis para o SQL do Azure. Consulte a referência ALTER DATABASE e ALTER DATABASE SCOPED CONFIGURATION T-SQL para obter as diferenças entre o SQL Server, o Banco de Dados SQL do Azure e a Instância Gerenciada do SQL do Azure.

No Banco de Dados SQL do Azure, o modelo de recuperação padrão é a recuperação completa, o que garante que seu banco de dados possa atender aos SLAs (contratos de nível de serviço) do Azure. Isso significa que o registro mínimo para operações em massa não é suportado, exceto para tempdb, onde o registro mínimo é permitido.

Configuração do MAXDOP

O grau máximo de paralelismo (MAXDOP) pode afetar o desempenho de consultas individuais. O SQL Server e o SQL do Azure lidam MAXDOP da mesma maneira. Quando MAXDOP definido como um valor mais alto, mais threads paralelos são usados por consulta, potencialmente acelerando a execução da consulta. No entanto, esse paralelismo aumentado requer recursos de memória extras, o que pode levar à pressão da memória e afetar o desempenho do armazenamento. Por exemplo, ao compactar grupos de linhas em um columnstore, o paralelismo requer mais memória, o que pode resultar em pressão de memória e corte de grupo de linhas.

Por outro lado, definir MAXDOP para um valor mais baixo pode reduzir a pressão da memória, permitindo que o sistema de armazenamento tenha um desempenho mais eficiente. Isso é importante em ambientes com recursos de memória limitados ou altas demandas de armazenamento. Ao configurar cuidadosamente o MAXDOP, você pode equilibrar o desempenho da consulta e a eficiência do armazenamento, garantindo o uso ideal da CPU e dos recursos de armazenamento.

Pode configurar o MAXDOP no SQL do Azure, tal como no SQL Server, através das seguintes técnicas:

• ALTER DATABASE SCOPED CONFIGURATION para configurar MAXDOP tem suporte para o Azure SQL.
• O procedimento sp_configure armazenado para "grau máximo de paralelismo" é suportado para a Instância Gerenciada SQL.
• MAXDOP As dicas de consulta são totalmente suportadas.
• A configuração MAXDOP com o Administrador de Recursos é suportada para a Instância Gerenciada SQL.