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.

Nota

No es necesario reiniciar el servidor después de agregar nuevos archivos; sin embargo, los archivos vacíos se rellenarán con mayor prioridad y el algoritmo round robin para asignar páginas se perderá hasta que se reequilibra el sistema.

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.

SQL Server Management Studio (SSMS)

Puede usar SQL Server Management Studio (SSMS) para modificar el número de archivos tempdb. Para ello, siga estos pasos:

1. Conéctese a la instancia administrada en SSMS.

2. Expanda Bases de datos en Explorador de objetos y, a continuación, expanda Bases de datos del sistema.

3. Haga clic con el botón derecho en tempdb y seleccione Propiedades.

4. Seleccione Archivos en Seleccionar una página para ver cuántos archivos de tempdb hay.

5. Para agregar un archivo, elija Agregar y proporcione información sobre el nuevo archivo de datos de la fila.

   

6. Para quitar un archivo tempdb, elija el archivo que desea quitar de la lista de archivos de base de datos y, a continuación, seleccione Quitar.

Transact-SQL (T-SQL)

Puede usar Transact-SQL (T-SQL) para comprobar cuántos archivos tempdb hay y agregar o quitar archivos.

Para contar el número total de archivos tempdb que hay, use el siguiente comando:

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

Para contar solo el número de archivos de datos tempdb que hay, use el siguiente comando:

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

Para agregar un archivo tempdb nuevo, use el comando siguiente:

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

Para quitar un archivo tempdb, use el siguiente comando:

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.

SQL Server Management Studio (SSMS)

Puede usar SQL Server Management Studio (SSMS) para modificar el incremento de crecimiento de archivos tempdb. Para ello, siga estos pasos:

1. Conéctese a la instancia administrada en SSMS.

2. Expanda Bases de datos en Explorador de objetos y, a continuación, expanda Bases de datos del sistema.

3. Haga clic con el botón derecho en tempdb y seleccione Propiedades.

4. Seleccione Archivos en Seleccionar una página para ver cuántos archivos tempdb hay.

5. Elija los puntos suspensivos (...) junto a un archivo de datos para abrir la ventana de diálogo Cambiar propiedades de crecimiento automático.

6. Active la casilla situada junto a Habilitar crecimiento automático y modifique la configuración de crecimiento automático especificando los valores de crecimiento del archivo, en porcentaje o megabytes.

   

7. Seleccione Aceptar para guardar la configuración.

Transact-SQL (T-SQL)

Use el siguiente comando de T-SQL para modificar los incrementos de crecimiento de los archivos de datos 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.

SQL Server Management Studio (SSMS)

Para determinar el tamaño máximo actual de tempdb en SSMS, siga estos pasos:

1. Conéctese a la instancia administrada en SSMS.
2. Expanda Bases de datos en Explorador de objetos y, a continuación, expanda Bases de datos del sistema.
3. Haga clic con el botón derecho en tempdb y seleccione Propiedades.
4. En la Página general, compruebe el valor Tamaño en Base de datos para determinar el tamaño máximo de tempdb. Un valor de -1 indica que el tamaño máximo de tempdb es ilimitado.

Para cambiar el tamaño máximo actual de tempdb en SSMS, siga estos pasos:

1. Conéctese a la instancia administrada en SSMS.
2. Expanda Bases de datos en Explorador de objetos y, a continuación, expanda Bases de datos del sistema.
3. Haga clic con el botón derecho en tempdb y seleccione Propiedades.
4. Seleccione Archivos en Seleccionar una página para ver cuántos archivos tempdb hay.
5. Elija los puntos suspensivos (...) junto a un archivo de datos para abrir la ventana de diálogo Cambiar propiedades de crecimiento automático.
6. Modifique la configuración del tamaño máximo de tempdb cambiando los valores en Tamaño máximo de archivo.
7. Seleccione Aceptar para guardar la configuración.

Transact-SQL (T-SQL)

Para determinar el tamaño máximo de tempdb, ejecute este comando:

USE tempdb
SELECT name, max_size FROM sys.database_files

Para obtener el tamaño total de tempdb en MB, ejecute este comando:

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

La siguiente captura de pantalla muestra un ejemplo de esta salida:

Para establecer el tamaño máximo de un nuevo archivo de datos tempdb, ejecute el siguiente comando:

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

Para cambiar el tamaño máximo de un archivo tempdb existente, ejecute el siguiente comando:

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:

Opción de configuración	Valores
Nombres lógicos de archivos tempdb	16 caracteres como máximo
Número de archivos tempdb	128 archivos como máximo
Número predeterminado de archivos tempdb	13 (1 archivo de registro + 12 archivos de datos)
Tamaño inicial del archivo de datos tempdb	16 MB
Incremento de crecimiento predeterminado de los archivos de datos tempdb	256 MB
Tamaño inicial del archivo de registro tempdb	16 MB
Incremento de crecimiento predeterminado de los archivos de registro tempdb	64 MB
Tamaño máximo inicial de tempdb	-1 (ilimitado)
Tamaño máximo de tempdb	Hasta el tamaño de almacenamiento

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 obtener información sobre la supervisión avanzada del rendimiento de las bases de datos de Instancia administrada de SQL con inteligencia integrada para la solución de problemas, consulte Supervisión de instancias de Azure SQL Database con Azure SQL Analytics.
• Para información sobre los precios, consulte Precios de SQL Database.