Otimizar o armazenamento do banco de dados

Concluído

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

Entender o desempenho de E/S

O desempenho de E/S pode ser crítico para um aplicativo de banco de dados. O SQL Azure abstrai você do posicionamento físico dos arquivos, mas há métodos para garantir que você tenha o desempenho de E/S de que precisa.

O valor de IOPS (entrada/saída por segundo) pode ser importante para seu aplicativo. Certifique-se de que você tenha escolhido a camada de serviço e os vCores corretos para suas necessidades de IOPS. Entenda como medir o IOPS para suas consultas locais se você estiver migrando para o Azure. Se tiver restrições quanto à IOPS, você poderá ver longas esperas de E/S. No modelo de compra do vCore, você pode escalar verticalmente vCores ou migrar para Comercialmente Crítico ou Hiperescala se não tiver IOPS suficiente. Para cargas de trabalho de produção, ao usar a DTU, recomendamos migrar para a camada Premium.

A latência de E/S é outro componente importante para o desempenho de E/S. Para ter uma latência de E/S mais rápida para o Banco de Dados SQL do Azure, considere a camada Comercialmente Crítico ou a Hiperescala. Para ter uma latência de E/S mais rápida para a Instância Gerenciada de SQL, passe para a camada Comercialmente Crítico ou aumente o tamanho do arquivo ou o número de arquivos do banco de dados. Melhorar a latência do log de transações pode exigir que você use transações com várias instruções.

Arquivos e grupos de arquivos

Os profissionais que trabalham com o SQL Server costumam usar arquivos e grupos de arquivo para aprimorar o desempenho de E/S por meio do posicionamento de arquivos físicos. O SQL Azure não permite que os usuários coloquem arquivos em sistemas de disco específicos. No entanto, o SQL do Azure tem compromissos de recursos para desempenho de E/S em relação a taxas, IOPS e latências. Dessa forma, abstrair o usuário do posicionamento físico dos arquivos pode ser benéfico.

O Banco de Dados SQL do Azure tem apenas um arquivo de banco de dados (a Hiperescala geralmente tem vários), e o tamanho máximo é configurado por meio das interfaces do Azure. Não há nenhuma funcionalidade para criar mais arquivos.

A Instância Gerenciada de SQL do Azure suporta a adição de arquivos de banco de dados e a configuração de tamanhos, mas não ao posicionamento físico dos arquivos. Você pode usar o número de arquivos e o tamanho dos arquivos da Instância Gerenciada de SQL para melhorar o desempenho de E/S. Além disso, os grupos de arquivos definidos pelo usuário têm suporte na Instância Gerenciada de SQL para fins de gerenciamento.

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 aproximadamente 512 megabytes. No entanto, nem sempre esse é o caso. O SQL Server distribui dados com base no tamanho de cada arquivo. Por exemplo, se ambos os arquivos de dados forem de 2 gigabytes, os dados serão distribuídos uniformemente. Mas se um arquivo for de 10 gigabytes e o outro for de 1 gigabyte, cerca de 900 MB entrariam no arquivo maior e 100 MB no menor. Esse comportamento é comum em qualquer banco de dados, mas no tempdb com uso intensivo de gravação, um padrão de gravação desigual pode criar um gargalo no arquivo maior, pois lida com mais gravações.

Configurar o Tempdb no SQL Server

O SQL Server detecta 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 tempdb pesadas, pode ser benéfico aumentar o número de arquivos tempdb além de oito, correspondendo ao número de CPUs em seu computador.

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

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ária, operações de classificação e o repositório de versão para controle de versão de linha, entre outras finalidades. Devido a essa utilização abrangente, é crucial colocar o tempdb no menor armazenamento de latência disponível e configurar corretamente seus arquivos de dados.

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

Os profissionais que trabalham com o SQL Server geralmente usam mais de um arquivo de banco de dados para particionar as alocações para tabelas tempdb. 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 contra tempdb, mas você pode configurá-lo alterando a opção de implantação. O tamanho máximo de tempdb é dimensionado de acordo com o número de vCores. Você obtém 12 arquivos com a Instância Gerenciada de SQL, 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, assim como no SQL Server, esses são os padrões recomendados.

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

Configuração do banco de dados

Normalmente, 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 Azure. Consulte a referência T-SQL ALTER DATABASE e ALTER DATABASE SCOPED CONFIGURATION para obter as diferenças entre o SQL Server, o Banco de Dados SQL do Azure e a Instância Gerenciada de 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 não há suporte para registro em log mínimo para operações em massa, exceto para tempdb, em que o registro em log mínimo é permitido.

Configuração de MAXDOP

O grau máximo de paralelismo (MAXDOP) pode afetar o desempenho de consultas individuais. O SQL Server e o SQL do Azure manipulam 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 aumento do paralelismo requer recursos de memória extras, o que pode levar à pressão de memória e afetar o desempenho do armazenamento. Por exemplo, ao compactar rowgroups em um columnstore, o paralelismo requer mais memória, o que pode resultar em pressão de memória e corte de rowgroup.

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

Você pode configurar o MAXDOP no SQL Azure de maneira semelhante à sua configuração no SQL Server, usando as seguintes técnicas:

• ALTER DATABASE SCOPED CONFIGURATION para configurar MAXDOP é compatível com o SQL do Azure.
• O procedimento sp_configure armazenado para "grau máximo de paralelismo" é compatível com a Instância Gerenciada de SQL.
• MAXDOP As dicas de consulta têm suporte total.
• A configuração MAXDOP com o Resource Governor é compatível com a Instância Gerenciada de SQL.