Definir configurações do tempdb para Instância Gerenciada de SQL do Azure

Aplica-se a: Instância Gerenciada de SQL do Azure

Este artigo ensina você a definir suas configurações do tempdb para Instância Gerenciada de SQL do Azure.

A Instância Gerenciada de SQL do Azure permite configurar o seguinte:

• Número de arquivos tempdb
• O incremento de crescimento de arquivos tempdb
• Tamanho máximo do tempdb

As configurações do tempdb persistem depois que sua instância é reiniciada, atualizada ou se houver um failover.

Visão geral

tempdb é um dos bancos de dados do sistema padrão que vem com a Instância Gerenciada de SQL do Azure. A estrutura do tempdb é igual a qualquer outra estrutura de banco de dados de usuário; a diferença é que, já que tempdb é usado para armazenamento não durável, as transações são minimamente registradas.

tempdb não pode ser descartado, desanexado, colocado offline, renomeado ou restaurado. Tentar qualquer uma dessas operações retornará um erro. tempdb é regenerado em cada início da instância do servidor e todos os objetos que podem ter sido criados no tempdb durante uma sessão anterior não persistem quando o serviço é reiniciado, após uma operação de gerenciamento de atualização de instância ou um failover.

A carga de trabalho no tempdb difere das cargas de trabalho em outros bancos de dados de usuário; objetos e dados são frequentemente criados e destruídos e há simultaneidade extremamente alta. Há apenas um tempdb para cada instância gerenciada. Mesmo que você tenha vários bancos de dados e aplicativos se conectando à instância, todos eles usam o mesmo banco de dados tempdb. Os serviços podem enfrentar contenção quando tentam alocar páginas em um tempdb usado excessivamente. Dependendo do grau de contenção, as consultas e solicitações que envolvem tempdb podem deixar de responder. É por isso que tempdb é fundamental para o desempenho do serviço.

Número de arquivos tempdb

Aumentar o número de arquivos de dados tempdb cria uma ou mais páginas GAM e SGAM para cada arquivo de dados, o que ajuda a melhorar a simultaneidade de tempdb e reduz a contenção de página PFC. No entanto, aumentar o número de arquivos de dados tempdb pode ter outras implicações de desempenho, portanto, teste minuciosamente antes de implementar em produção.

Por padrão, a Instância Gerenciada de SQL do Azure cria 12 arquivos de dados tempdb e 1 arquivo de log tempdb, mas é possível modificar essa configuração.

Modificar o número de arquivos tempdb tem as seguintes limitações:

• O nome lógico do novo arquivo não diferencia maiúsculas de minúsculas, com no máximo 16 caracteres e nenhum espaço.
• O número máximo de arquivos tempdb é 128.

Você pode usar o SQL Server Management Studio (SSMS) e Transact-SQL (T-SQL) para alterar o número de arquivos para tempdb em Instância Gerenciada de SQL do Azure.

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files where type = 0

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name')

ALTER DATABASE tempdb REMOVE FILE [file_name]

Incremento de crescimento

O crescimento do arquivo tempdb pode ter um impacto no desempenho das consultas que usam tempdb. Dessa forma, incrementos de crescimento de arquivos de dados tempdb muito pequenos podem causar fragmentação de extensão, enquanto incrementos muito grandes podem resultar em crescimento lento ou falha de crescimento se não houver espaço suficiente para o crescimento acontecer. O valor ideal para incrementos de crescimento de arquivos tempdb depende da carga de trabalho.

Os incrementos de crescimento padrão da Instância Gerenciada de SQL são de 254 MB para arquivos de dados tempdb e 64 MB para arquivos de log tempdb, mas você pode configurar incrementos de crescimento para se adaptar à carga de trabalho e ajustar seu desempenho.

Considere o seguinte:

• O parâmetro de crescimento de arquivo dá suporte às seguintes unidades para int_growth_increment: KB, MB, GB, TB e %.
• Os incrementos de crescimento devem ser os mesmos para todos os arquivos de dados tempdb, caso contrário, o algoritmo round-robin que aloca as páginas pode ser afetado.

Você pode usar o SQL Server Management Studio (SSMS) e Transact-SQL (T-SQL) para alterar o incremento de crescimento de seus arquivos tempdb.

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])

Tamanho máximo

tempdb tamanho é o tamanho da soma de todos os arquivos tempdb. O tamanho do arquivo tempdb é um espaço alocado (zerado) para esse arquivo tempdb. O tamanho inicial do arquivo para todos os arquivos tempdb é de 16 MB, que é o tamanho de todos os arquivos tempdb quando a instância é reiniciada ou faz failover. Depois que o espaço usado de um arquivo de dados tempdb atingir o tamanho do arquivo, todos os arquivos de dados tempdb aumentarão automaticamente pelos incrementos de crescimento configurados.

tempdb espaço usado é a soma do espaço usado de todos os arquivos tempdb. O espaço usado do arquivo tempdb é igual à parte desse tamanho de arquivo tempdb que está ocupada com informações diferentes de zero. A soma do espaço usado de tempdb e do espaço livre de tempdb é igual ao tamanho de tempdb.

Você pode usar o T-SQL para determinar o espaço atual usado e livre de seus arquivos tempdb.

Para obter espaço usado, espaço livre e tamanho dos arquivos de dados tempdb, execute este comando:

USE tempdb
SELECT SUM((allocated_extent_page_count)*1.0/128) AS TempDB_used_data_space_inMB, 
	SUM((unallocated_extent_page_count)*1.0/128) AS TempDB_free_data_space_inMB, 
	SUM(total_page_count*1.0/128) AS TempDB_data_size_inMB 
FROM sys.dm_db_file_space_usage

A captura de tela a seguir mostra um exemplo de saída:

Para obter o espaço usado, espaço livre e tamanho dos arquivos de log tempdb, execute este comando:

USE tempdb
SELECT used_log_space_in_bytes*1.0/1024/1024 AS TempDB_used_log_space_inMB,
     (total_log_size_in_bytes- used_log_space_in_bytes)*1.0/1024/1024 AS TempDB_free_log_space_inMB,
     total_log_size_in_bytes*1.0/1024/1024 AS TempDB_log_size_inMB
FROM sys.dm_db_log_space_usage

A captura de tela a seguir mostra um exemplo de saída:

O tamanho máximo do tempdb é o limite após o qual tempdb não é possível aumentar ainda mais.

O tamanho máximo tempdb na Instância Gerenciada de SQL tem as seguintes limitações:

• Na camada de serviço Uso Geral, o tamanho máximo do tempdb é limitado a 24 GB/vCore (96-1920 GB) e o arquivo de log é de 120 GB.
• Na camada de serviço Comercialmente Crítico, tempdb compete com outros bancos de dados por recursos, portanto, o armazenamento reservado é compartilhado entre tempdb e outros bancos de dados. O tamanho máximo do arquivo de log tempdb é de 2 TB.

Os arquivos tempdb crescem até atingirem o limite máximo permitido pela camada de serviço ou pelo tamanho máximo do arquivo tempdb configurado manualmente.

Você pode usar o SQL Server Management Studio (SSMS) e Transact-SQL (T-SQL) para alterar o tamanho máximo de seus arquivos tempdb.

USE tempdb
SELECT name, max_size FROM sys.database_files

USE tempdb
SELECT (SUM(size)*1.0/128) AS TempDB_size_InMB FROM sys.database_files

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', MAXSIZE = int_maxsize[KB|MB|GB|TB])

ALTER DATABASE tempdb MODIFY FILE (NAME = file_name, MAXSIZE = int_maxsize[KB|MB|GB|TB])

limites do tempdb

A tabela a seguir define limites para várias definições de configuração do tempdb:

Próximas etapas

• Para saber como criar sua primeira instância gerenciada, confira o Guia de início rápido.
• Para obter uma lista de recursos e de comparação, consulte Recursos comuns do SQL.
• Para obter mais informações sobre a configuração de VNet, confira Configuração de VNet de Instância Gerenciada de SQL.
• Para obter um início rápido que cria uma instância gerenciada e restaura um banco de dados de um arquivo de backup, confira Criar uma instância gerenciada.
• Para obter um tutorial sobre como usar o Serviço de Migração de Banco de Dados do Azure para migração, confira Migração de Instância Gerenciada de SQL usando o Serviço de Migração de Banco de Dados.
• Para monitoramento avançado da Instância Gerenciada de SQL, consulte observador de banco de dados.
• Para saber mais sobre preços, confira Preços do Banco de Dados SQL.