Configure las opciones de tempdb de Azure SQL Managed Instance

Se aplica a: Azure SQL Managed Instance

En este artículo se explica cómo configurar las opciones de tempdb de Azure SQL Managed Instance.

Azure SQL Managed Instance permite configurar lo siguiente:

• Número de archivos tempdb
• Incremento de crecimiento de archivos tempdb
• Tamaño máximo de tempdb

la configuración de tempdb se mantiene después de reiniciar, actualizar o si hay una conmutación por error.

Información general

tempdb es una de las bases de datos del sistema predeterminadas que se incluyen con Azure SQL Managed Instance. La estructura de tempdb es la misma que cualquier otra estructura de base de datos de usuario; la diferencia es que, puesto que tempdb se usa para el almacenamiento no duradero, las transacciones se registran mínimamente.

tempdb no se puede quitar, desasociar, desconectar, cambiar el nombre ni restaurar. Si se intenta cualquiera de estas operaciones, se devuelve un error. tempdb se vuelve a generar tras cada inicio de la instancia del servidor y los objetos que se pueden haber creado en tempdb durante una sesión anterior no se conservan cuando se reinicia el servicio, después de una operación de administración de actualizaciones de instancia o una conmutación por error.

La carga de trabajo en tempdb difiere de las cargas de trabajo de otras bases de datos de usuario; los objetos y los datos se crean y destruyen con frecuencia y hay una simultaneidad extremadamente alta. Solo hay una base de datos tempdb para cada instancia administrada. Incluso si tiene varias bases de datos y aplicaciones que se conectan a la instancia, todas usan la misma base de datos tempdb. Los servicios pueden experimentar contención cuando intentan asignar páginas en un tempdb muy usado. Según el grado de contención, las consultas y las solicitudes que impliquen tempdb podrían dejar de responder. Este es el motivo por el que tempdb es fundamental para el rendimiento del servicio.

Número de archivos tempdb

El aumento del número de archivos de datos tempdb crea una o varias páginas GAM y SGAM para cada archivo de datos, lo que ayuda a mejorar la simultaneidad de tempdb y reduce la contención de páginas PFC. Sin embargo, aumentar el número de archivos de datos tempdb podría tener otras implicaciones de rendimiento, por lo que probar exhaustivamente antes de implementar en producción.

De forma predeterminada, Azure SQL Managed Instance crea 12 archivos de datos tempdb y 1 archivo de registro tempdb, pero es posible modificar esta configuración.

La modificación del número de archivos tempdb tiene las siguientes limitaciones:

• El nombre lógico del nuevo archivo no distingue mayúsculas de minúsculas, con un máximo de 16 caracteres y sin espacios.
• El número máximo de archivos tempdb es 128.

Puede usar tanto SQL Server Management Studio (SSMS) como Transact-SQL (T-SQL) para cambiar el número de archivos tempdb en Azure SQL Managed Instance.

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 crecimiento

El crecimiento de archivos tempdb puede tener un impacto en el rendimiento de las consultas mediante tempdb. Por lo tanto, los incrementos de crecimiento de archivos de datos tempdb demasiado pequeños pueden provocar fragmentación de la extensión, mientras que los incrementos demasiado grandes pueden dar lugar a un crecimiento lento o un error de crecimiento si no hay suficiente espacio para que se produzca el crecimiento. El valor óptimo para los incrementos de crecimiento de archivos tempdb depende de la carga de trabajo.

Los incrementos de crecimiento predeterminados de SQL Managed Instance son de 254 MB para archivos de datos tempdb y 64 MB para los archivos de registro tempdb, pero puede configurar incrementos de crecimiento para adaptarse a la carga de trabajo y ajustar el rendimiento.

Tenga en cuenta lo siguiente.

• El parámetro de crecimiento de archivos admite las siguientes unidades para int_growth_increment: KB, MB, GB, TB y %.
• Los incrementos de crecimiento deben ser los mismos para todos los archivos de datos de tempdb que, de lo contrario, el algoritmo round robin que asigna páginas podría verse afectado.

Puede usar tanto SQL Server Management Studio (SSMS) como Transact-SQL (T-SQL) para cambiar el incremento de crecimiento de los archivos tempdb.

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

Tamaño máximo

El tamaño tempdb es el tamaño total de la suma de todos los archivos tempdb. El tamaño del archivo tempdb es un espacio asignado (a cero) para ese archivo tempdb. El tamaño inicial del archivo para todos los archivos tempdb es de 16 MB, que es el tamaño de todos los archivos tempdb cuando se reinicia la instancia o se conmuta por error. Una vez que el espacio usado de un archivo de datos tempdb alcanza el tamaño del archivo, todos los archivos de datos tempdb crecen automáticamente por sus incrementos de crecimiento configurados.

El espacio usado tempdb es el espacio total de la usma de todos los archivos tempdb. El espacio utilizado por el archivo tempdb es igual a la parte de ese tamaño de archivo tempdb que está ocupado con información distinta de cero. La suma del espacio usado tempdb y el espacio libre tempdb es igual al tamaño de tempdb.

Puede usar T-SQL para determinar el espacio disponible y usado actual para los archivos tempdb.

Para obtener el espacio usado, el espacio libre y el tamaño de los archivos de datos tempdb, ejecute 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

La siguiente captura de pantalla le muestra un ejemplo del resultado:

Para obtener el espacio usado, el espacio libre y el tamaño de los archivos de registro tempdb, ejecute 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

La siguiente captura de pantalla le muestra un ejemplo del resultado:

El tamaño máximo de tempdb es el límite después del cual tempdb no puede crecer más.

El tamaño máximo de tempdb en SQL Managed Instance tiene las siguientes limitaciones:

• En el nivel de servicio De uso general, el tamaño máximo de un tempdb se limita a 24 GB/núcleo virtual (96-1920 GB) y el archivo de registro es de 120 GB.
• En el nivel de servicio Crítico para la empresa, tempdb compite con otras bases de datos para los recursos, por lo que el almacenamiento reservado se comparte entre tempdb y otras bases de datos. El tamaño máximo del archivo de registro tempdb es de 2 TB.

Los archivos tempdb crecen hasta que alcanzan el límite máximo permitido por el nivel de servicio o por el tamaño de archivo máximo tempdb configurado manualmente.

Puede usar tanto SQL Server Management Studio (SSMS) como Transact-SQL (T-SQL) para cambiar el tamaño máximo de los archivos 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])

límites de tempdb

En la tabla siguiente se definen los límites de varias opciones de configuración de tempdb:

Pasos siguientes

• Para más información sobre cómo crear su primera instancia administrada, vea la Guía de inicio rápido.
• Para obtener una lista de características y una comparación, consulte Características comunes de SQL.
• Para obtener más información sobre la configuración de redes virtuales, vea Configuración de una red virtual de Instancia administrada de SQL.
• Para ver un inicio rápido en el que se crea una instancia administrada y se restaura una base de datos desde un archivo de copia de seguridad, consulte Creación de una instancia administrada.
• Para consultar un tutorial sobre el uso de Azure Database Migration Service para la migración, consulte Migración de Instancia administrada de SQL mediante Database Migration Service.
• Para la supervisión avanzada de SQL Managed Instance, consulte monitor de base de datos.
• Para información sobre los precios, consulte Precios de SQL Database.