Configuración de la configuración de tempdb para Azure SQL Managed Instance

Se aplica a:Azure SQL Managed Instance

En este artículo se explica cómo configurar las tempdb opciones 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

Las configuraciones tempdb persisten 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 la de cualquier otra base de datos de usuarios; la diferencia es que como tempdb se utiliza para almacenamiento no duradero, las transacciones se registran mínimamente.

No se puede quitar, desasociar, desconectar, desconectar, cambiar el nombre ni restaurar tempdb. Si se intenta cualquiera de estas operaciones, se devuelve un error. tempdb se regenera cada vez que se inicia la instancia del servidor y los objetos que puedan haberse creado en tempdb durante una sesión anterior no persisten cuando se reinicia el servicio, tras una operación de administración de actualización 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 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 utilizado. Según el grado de contención, las consultas y las solicitudes que implican tempdb podrían dejar de responder. Esta es la razón por la que tempdb es fundamental para el rendimiento del servicio.

Número de archivos tempdb

Aumentar el 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 tempdb la simultaneidad 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 el sistema se reequilibra.

Puede usar SQL Server Management Studio (SSMS) y Transact-SQL (T-SQL) para cambiar el número de archivos de 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 su instancia administrada en SSMS.

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

3. Haga clic en tempdb y elija Propiedades.

4. Seleccione Archivos en Seleccionar una página para ver el número de archivos tempdb existente.

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 el número de archivos tempdb existentes y agregar o quitar archivos.

Para contar el número de todos los archivos tempdb existentes, use el siguiente comando:

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

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

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

Para añadir un nuevo archivo tempdb, utilice el siguiente comando:

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

Para eliminar un archivo tempdb existente, use el siguiente comando:

ALTER DATABASE tempdb REMOVE FILE [file_name]

Incremento de crecimiento

El crecimiento del archivo tempdb puede afectar al rendimiento de las consultas que utilizan tempdb. De este modo, los incrementos de crecimiento de archivos de datos tempdb demasiado pequeños pueden provocar la fragmentación de la extensión, mientras que los incrementos demasiado grandes pueden provocar un crecimiento lento o un fallo del crecimiento si no hay espacio suficiente 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 para SQL Managed Instance son de 254 MB para archivos de datos tempdb y 64 MB para 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 tempdb que, de lo contrario, el algoritmo round robin que asigna páginas podría verse afectado.

Puede usar SQL Server Management Studio (SSMS) y 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 los archivos tempdb. Para ello, siga estos pasos:

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

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

3. Haga clic en tempdb y elija Propiedades.

4. Seleccione Archivos en Seleccionar una página para ver el número de archivos tempdb existente.

5. Seleccione el paréntesis (...) situado junto a un archivo de datos para abrir la ventana de diálogo Cambiar propiedades de crecimiento automático.

6. Marque la casilla junto a Activar crecimiento automático y, a continuación, modifique la configuración del crecimiento automático especificando los valores de crecimiento de los archivos, ya sea en porcentaje o en 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

tempdbtamaño es la suma del tamaño de todos los archivos tempdb. tempdb el tamaño del archivo es un espacio asignado (con ceros) 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.

tempdbel espacio utilizado es la suma del espacio utilizado de todos los archivos tempdb. El espacio utilizado del 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 tempdbespacio usado y tempdbel espacio libre 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 espacio usado, espacio libre y 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 aún 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 tempdb está limitado 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 máximo de archivo tempdb configurado manualmente.

Puede usar SQL Server Management Studio (SSMS) y 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 su instancia administrada en SSMS.
2. Expanda Bases de datos en el Explorador de objetos y, a continuación, expanda Bases de datos del sistema.
3. Haga clic en tempdb y elija 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 -1 indica un tamaño ilimitado.

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

1. Conéctese a su instancia administrada en SSMS.
2. Expanda Bases de datos en el Explorador de objetos y, a continuación, expanda Bases de datos del sistema.
3. Haga clic en tempdb y elija Propiedades.
4. Seleccione Archivos en Seleccionar una página para ver el número de archivos tempdb existente.
5. Seleccione el paréntesis (...) situado junto a un archivo de datos para abrir la ventana de diálogo Cambiar propiedades de crecimiento automático.
6. Modifique la tempdb configuración de tamaño máximo 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 tempdb tamaño máximo, ejecute este comando:

USE tempdb
SELECT name, max_size FROM sys.database_files

Para obtener el tamaño total 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:

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 existente tempdb , 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 tempdb:

Opción de configuración	Valores
Nombres lógicos de archivos tempdb	Máximo de 16 caracteres
Número de archivos tempdb	Máximo de 128 archivos
Número predeterminado de archivos tempdb	13 (1 archivo de registro + 12 archivos de datos)
Tamaño inicial de tempdb archivos de datos	16 MB
Incremento de crecimiento predeterminado de tempdb archivos de datos	256 MB
Tamaño inicial de tempdb archivos de registro	16 MB
Incremento de crecimiento predeterminado de tempdb archivos de registro	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.