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.

Observação

Você não precisa reiniciar o servidor depois de adicionar novos arquivos; no entanto, os arquivos mais vazios serão preenchidos com prioridade mais alta e o algoritmo round-robin para alocar páginas será perdido até que o sistema seja rebalanceado.

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.

SQL Server Management Studio (SSMS)

Você pode usar o SQL Server Management Studio (SSMS) para modificar o número de arquivos tempdb. Para fazer isso, siga estas etapas:

1. Conecte-se à instância gerenciada no SSMS.

2. Expanda Bancos de dados em Pesquisador de Objetos e, em seguida, expanda Bancos de dados do sistema.

3. Clique com o botão direito do mouse no tempdb e escolha Propriedades.

4. Selecione Arquivos em Selecionar uma página para exibir o número existente de arquivos tempdb.

5. Para adicionar um arquivo, escolha Adicionar e forneça informações sobre o novo arquivo de dados na linha.

   

6. Para remover um arquivo tempdb, escolha o arquivo que você deseja remover da lista de arquivos de banco de dados e selecione Remover.

T-SQL (Transact-SQL)

Use Transact-SQL (T-SQL) para marcar o número de arquivos tempdb existentes e adicionar ou remover arquivos.

Para contar o número de todos os arquivos tempdb existentes, use o comando a seguir:

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

Para contar apenas o número de arquivos de dados tempdb, use o comando a seguir:

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

Para adicionar um novo arquivo tempdb, use o comando a seguir:

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

Para remover um arquivo tempdb existente, use o comando a seguir:

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.

SQL Server Management Studio (SSMS)

Você pode usar o SQL Server Management Studio (SSMS) para modificar o incremento de crescimento de arquivos tempdb. Para fazer isso, siga estas etapas:

1. Conecte-se à instância gerenciada no SSMS.

2. Expanda Bancos de dados em Pesquisador de Objetos e, em seguida, expanda Bancos de dados do sistema.

3. Clique com o botão direito do mouse no tempdb e escolha Propriedades.

4. Selecione Arquivos em Selecionar uma página para exibir o número existente de arquivos tempdb.

5. Escolha as reticências (...) ao lado de um arquivo de dados para abrir a janela de diálogo Alterar as propriedades de crescimento automático.

6. Marque a caixa de seleção ao lado de Habilitar crescimento automático e modifique suas configurações de crescimento automático especificando os valores de crescimento do arquivo, em porcentagem ou megabytes.

   

7. Selecione OK para salvar as configurações.

T-SQL (Transact-SQL)

Use o seguinte comando T-SQL para modificar os incrementos de crescimento de seus arquivos de dados tempdb:

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

Tamanho máximo

tempdbtamanho é 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.

tempdbespaç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 tempdbespaço usado e do tempdbespaço livre é igual ao tamanho 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.

SQL Server Management Studio (SSMS)

Para determinar o tamanho máximo atual do tempdb no SSMS, siga estas etapas:

1. Conecte-se à instância gerenciada no SSMS.
2. Expanda Bancos de dados em Pesquisador de Objetos e, em seguida, expanda Bancos de dados do sistema.
3. Clique com o botão direito do mouse no tempdb e escolha Propriedades.
4. Na página Geral, verifique o valor Tamanho em Banco de dados para determinar o tamanho máximo do tempdb. Um valor de -1 indica que o tamanho máximo do tempdb é ilimitado.

Para alterar o tamanho máximo atual do tempdb no SSMS, siga estas etapas:

1. Conecte-se à instância gerenciada no SSMS.
2. Expanda Bancos de dados em Pesquisador de Objetos e, em seguida, expanda Bancos de dados do sistema.
3. Clique com o botão direito do mouse no tempdb e escolha Propriedades.
4. Selecione Arquivos em Selecionar uma página para exibir o número existente de arquivos tempdb.
5. Escolha as reticências (...) ao lado de um arquivo de dados para abrir a janela de diálogo Alterar as propriedades de crescimento automático.
6. Modifique as configurações de tamanho máximo do tempdb alterando os valores em Tamanho máximo do arquivo.
7. Selecione OK para salvar as configurações.

T-SQL (Transact-SQL)

Para determinar o tamanho máximo do tempdb, execute este comando:

USE tempdb
SELECT name, max_size FROM sys.database_files

Para obter o tamanho total do tempdb em MB, execute este comando:

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

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

Para definir o tamanho máximo de um novo arquivo de dados tempdb, execute o comando a seguir:

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

Para alterar o tamanho máximo de um arquivo tempdb existente, execute o comando a seguir:

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:

Definição de configuração	Valores
Nomes lógicos de arquivos tempdb	Máximo de 16 caracteres
Número de arquivos tempdb	Máximo de 128 arquivos
Número padrão de arquivos tempdb	13 (1 arquivo de log + 12 arquivos de dados)
Tamanho inicial de arquivos de dados tempdb	16 MB
Incremento de crescimento padrão de arquivos de dados tempdb	256 MB
Tamanho inicial de arquivos de log tempdb	16 MB
Incremento de crescimento padrão de arquivos de log tempdb	64 MB
Tamanho máximo inicial de tempdb	-1 (ilimitado)
Tamanho máximo do tempdb	Até o tamanho do armazenamento

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 obter o monitoramento avançado do desempenho de banco de dados da Instância Gerenciada de SQL com inteligência de solução de problemas interna, confira Monitorar a Instância Gerenciada de SQL do Azure usando a Análise de SQL do Azure.
• Para saber mais sobre preços, confira Preços do Banco de Dados SQL.