Administración del espacio de archivos para bases de datos en Azure SQL Managed Instance

  • Artículo

Se aplica a:Azure SQL Managed Instance

En este artículo se explica cómo supervisar y administrar archivos en bases de datos de Azure SQL Managed Instance.. Se revisa cómo supervisar el tamaño del archivo de base de datos, reducir el registro de transacciones, ampliar un archivo de registro de transacciones y controlar el crecimiento de un archivo de registro de transacciones.

Este artículo se aplica a Azure SQL Managed Instance. Aunque es muy similar, para obtener información sobre cómo administrar el tamaño de los archivos de registro de transacciones en SQL Server, vea Administración del tamaño del archivo de registro de transacciones.

Descripción de los tipos de espacio de almacenamiento para una base de datos

Comprender las cantidades de espacio de almacenamiento siguientes es importante para administrar el espacio de archivo de una base de datos.

Cantidad de base de datos Definición Comentarios
Espacio de datos usado La cantidad de espacio usado para almacenar los datos de la base de datos. Por lo general, el espacio usado aumenta (disminuciones) en las inserciones (eliminaciones). En algunos casos, el espacio usado no cambia en las inserciones o eliminaciones, según la cantidad y el patrón de datos implicados en la operación y las posibles fragmentaciones. Por ejemplo, al eliminar una fila de cada página de datos no disminuye necesariamente el espacio usado.
Espacio de datos asignado La cantidad de espacio de archivo de formato disponible para almacenar datos de la base de datos. La cantidad de espacio asignado crece automáticamente, pero nunca disminuye después de las eliminaciones. Este comportamiento garantiza que las futuras inserciones son más rápidas puesto que no es necesario volver a formatear el espacio.
Espacio de datos asignado, pero no usado La diferencia entre la cantidad de espacio de datos asignado y el espacio de datos usado. Esta cantidad representa la cantidad máxima de espacio libre que se puede reclamar mediante la reducción de archivos de datos de base de datos.
Tamaño máximo de datos La cantidad máxima de espacio que se puede usar para almacenar datos de base de datos. La cantidad de espacio de datos asignado no puede crecer por encima del tamaño máximo de datos.

En el siguiente diagrama se ilustra la relación entre los diferentes tipos de espacio de almacenamiento para una base de datos.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Consulta de la información de espacio de archivos en una base de datos única

Use la siguiente consulta en sys.database_files para devolver la cantidad de espacio de archivos de base de datos asignado y la cantidad de espacio asignado sin usar. Las unidades de resultado de la consulta están en MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Supervisión del uso del espacio del registro

Supervise el uso del espacio del registro mediante sys.dm_db_log_space_usage. Este DMV devuelve información sobre la cantidad de espacio del registro actualmente en uso e indica cuándo es necesario el truncamiento del registro de transacciones.

Para obtener información sobre el tamaño actual del archivo de registro, su tamaño máximo y la opción de crecimiento automático de este archivo, también puede usar las columnas size, max_size y growth de ese archivo de registro en sys.database_files.

Las métricas de espacio de almacenamiento que se muestran en las API de métricas basadas en Azure Resource Manager solo miden el tamaño de las páginas de datos usadas. Para obtener ejemplos, vea get-metrics de PowerShell.

Reducir el tamaño del archivo de registro

Para reducir el tamaño físico de un archivo de registro físico mediante la eliminación de espacio sin usar, reduzca el archivo de registro. Una reducción solo marca la diferencia cuando un archivo de registro de transacciones contiene espacio sin usar. Si el archivo de registro está lleno, probablemente debido a transacciones abiertas, investigue qué impide el truncamiento del registro de transacciones.

Precaución

Las operaciones de reducción no deben considerarse una operación de mantenimiento normal. Los archivos de datos y de registro que crecen debido a operaciones empresariales periódicas y repetitivas no requieren operaciones de reducción. Los comandos de reducción afectan al rendimiento de la base de datos mientras se está ejecutando y, si es posible, se deben ejecutar durante períodos de poco uso. No se recomienda reducir los archivos de datos si la carga de trabajo normal de la aplicación hará que los archivos vuelvan a tener el mismo tamaño asignado.

Debe tener en cuenta el posible impacto negativo en el rendimiento por la reducción de los archivos de base de datos; vea Mantenimiento de índices después de la reducción. En raras ocasiones, las operaciones de reducción se pueden ver afectadas por copias de seguridad automatizadas de bases de datos. Si es necesario, vuelva a intentar la operación de reducción.

Antes de reducir el registro de transacciones, tenga en cuenta los Factores que pueden ralentizar el truncamiento del registro. Si se requiere el espacio de almacenamiento de nuevo después de reducir un registro, el registro de transacciones volverá a crecer y esto implicará una sobrecarga de rendimiento durante las operaciones de ampliación de registro. Para más información, vea las Recomendaciones.

Puede reducir un archivo de registro siempre que la base de datos esté en línea y haya al menos un archivo de registro virtual (VLF) libre. En algunos casos, no será posible reducir el registro hasta el siguiente truncamiento del registro.

Los factores que mantienen activos los VLF por un periodo prolongado de tiempo, como puede ser una transacción de ejecución prolongada, pueden restringir la reducción del registro o incluso impedirla completamente. Para obtener información, vea Factores que pueden ralentizar el truncamiento del registro.

Con la reducción de un archivo de registro se quitan uno o varios VLF que no contienen ninguna parte del registro lógico (es decir, los VLF inactivos). Cuando se reduce un archivo de registro de transacciones, se quitan VLF inactivos del final del archivo de registro para reducirlo aproximadamente al tamaño de destino.

Para más información sobre las operaciones de reducción, revise lo siguiente:

Reducir un archivo de registro (sin reducir los archivos de base de datos)

Supervisar los eventos de reducción de un archivo de registro

Supervisar el espacio del registro

Mantenimiento de índices después de la reducción

Una vez completada una operación de reducción en los archivos de datos, es posible que los índices se fragmenten. Esto reduce la eficacia de optimización del rendimiento para determinadas cargas de trabajo, como consultas que usan exploraciones grandes. Si se produce una degradación del rendimiento una vez completada la operación de reducción, considere la posibilidad de realizar el mantenimiento de índices para volver a generar los índices. Tenga en cuenta que las recompilaciones de índice requieren espacio libre en la base de datos y, por lo tanto, pueden hacer que el espacio asignado aumente, lo que contrarresta el efecto de la reducción.

Para obtener más información sobre el mantenimiento de índices, consulte Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos.

Evaluar la densidad de página de índice

Si truncar archivos de datos no ha dado como resultado una reducción suficiente del espacio asignado, es posible que decida reducir los archivos de datos de base de datos para reclamar el espacio sin usar en ellos. Tenga en cuenta que, como paso opcional pero recomendado, primero debe determinar la densidad media de página de los índices de la base de datos. Para la misma cantidad de datos, la reducción se completará más rápido si la densidad de página es alta, ya que tendrá que mover menos páginas. Si la densidad de página es baja para algunos índices, considere la posibilidad de realizar un mantenimiento en estos índices para aumentar la densidad de página antes de reducir los archivos de datos. Esto también permitirá una mayor reducción del espacio de almacenamiento asignado.

Para determinar la densidad de página de todos los índices de la base de datos, use la consulta siguiente. La densidad de página se notifica en la columna avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Si hay índices con un recuento de páginas alto que tienen una densidad de página inferior al 60-70 %, considere la posibilidad de volver a generar o reorganizar estos índices antes de reducir los archivos de datos.

Nota:

Para bases de datos más grandes, la consulta para determinar la densidad de página puede tardar mucho tiempo (horas) en completarse. Además, la recompilación o reorganización de índices grandes también requiere un uso considerable de tiempo y recursos. Tiene que elegir entre dedicar más tiempo a aumentar la densidad de páginas o disminuir la duración de la reducción y lograr un mayor ahorro de espacio.

Si hay varios índices con baja densidad de página, es posible que pueda reconstruirlos en paralelo en varias sesiones de base de datos para acelerar el proceso. Sin embargo, asegúrese de que no se acerca a los límites de recursos de la base de datos al hacerlo y deje suficiente capacidad de aumento de recursos para las cargas de trabajo de aplicaciones que pueden estar ejecutándose. Supervise el consumo de recursos (CPU, E/S de datos, E/S de registro) en Azure Portal o mediante la vista sys.dm_db_resource_stats e inicie reconstrucciones paralelas adicionales solo si el uso de recursos en cada una de estas dimensiones sigue siendo sustancialmente inferior al 100 %. Si el uso de CPU, E/S de datos o E/S de registro es del 100 %, puede escalar la base de datos para tener más núcleos de CPU y aumentar el rendimiento de E/S. Esto puede permitir que se recompilen paralelos adicionales para completar el proceso más rápido.

Comando de recompilación de índices de ejemplo

A continuación se muestra un comando de ejemplo para recompilar un índice y aumentar su densidad de página mediante la instrucción ALTER INDEX:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Este comando inicia una recompilación de índice en línea y reanudable. Esto permite que las cargas de trabajo simultáneas sigan usando la tabla mientras la reconstrucción está en curso y le permite reanudar la reconstrucción si se interrumpe por cualquier motivo. Sin embargo, este tipo de reconstrucción es más lento que una reconstrucción sin conexión, lo que bloquea el acceso a la tabla. Si no es necesario que otras cargas de trabajo accedan a la tabla durante la reconstrucción, establezca las opciones ONLINE y RESUMABLE en OFF y quite la cláusula WAIT_AT_LOW_PRIORITY.

Para obtener más información sobre el mantenimiento de índices, consulte Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos.

Reducir varios archivos de datos

Como se ha indicado anteriormente, reducir con el movimiento de datos es un proceso de larga duración. Si la base de datos tiene varios archivos de datos, puede acelerar el proceso reduciendo varios archivos de datos en paralelo. Para ello, abra varias sesiones de base de datos y use DBCC SHRINKFILE en cada sesión con un valor file_id diferente. De forma similar a la recompilación de índices anterior, asegúrese de que tiene suficiente capacidad de aumento de recursos (CPU, E/S de datos, E/S de registro) antes de iniciar cada nuevo comando de reducción paralela.

El siguiente comando de ejemplo reduce el archivo de datos con file_id 4, e intentareducir su tamaño asignado a 52 000 MB mediante el movimiento de las páginas dentro del archivo:

DBCC SHRINKFILE (4, 52000);

Si desea reducir el espacio asignado para el archivo al mínimo posible, ejecute la instrucción sin especificar el tamaño de destino:

DBCC SHRINKFILE (4);

Si una carga de trabajo se ejecuta simultáneamente con la reducción, puede empezar a usar el espacio de almacenamiento liberado por la reducción antes de que esta se complete y trunque el archivo. En este caso, la reducción no podrá reducir el espacio asignado al destino especificado.

Puede mitigar esto reduciendo cada archivo en pasos más pequeños. Esto significa que, en el comando DBCC SHRINKFILE, se establece el destino que es ligeramente menor que el espacio asignado actual para el archivo. Por ejemplo, si el espacio asignado para el archivo con file_id 4 es 200 000 MB y desea reducirlo a 100 000 MB, primero puede establecer el destino en 170 000 MB:

DBCC SHRINKFILE (4, 170000);

Una vez completado este comando, habrá truncado el archivo y reducido su tamaño asignado a 170 000 MB. Después puede repetir este comando, estableciendo el destino primero en 140 000 MB, luego en 110 000 MB, etc., hasta que el archivo se rebaje al tamaño deseado. Si el comando se completa pero el archivo no se trunca, use pasos más pequeños, por ejemplo, 15 000 MB en lugar de 30 000 MB.

Para supervisar el progreso de reducción de todas las sesiones de reducción simultáneas, puede usar la consulta siguiente:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Nota:

El progreso de la reducción puede no ser lineal y el valor de la columna percent_complete puede permanecer prácticamente sin cambios durante largos períodos de tiempo, aunque la reducción sigue en curso.

Una vez que se haya completado la reducción de todos los archivos de datos, vuelva a ejecutar la consulta de uso de espacio para determinar la reducción resultante en el tamaño de almacenamiento asignado. Si todavía hay una gran diferencia entre el espacio usado y el espacio asignado, puede recompilar los índices. Esto puede aumentar más el espacio asignado de forma temporal, pero reducir los archivos de datos de nuevo después de recompilar los índices debería producir una reducción más profunda del espacio asignado.

Ampliación de un archivo de registro

En Azure SQL Managed Instance, agregue espacio a un archivo de registro mediante la ampliación del archivo de registro existente (si el espacio en disco lo permite). No se admite la adición de un archivo de registro a la base de datos. Un archivo de registro de transacciones es suficiente, a menos que se esté agotando el espacio del registro y que el espacio en disco también se esté agotando en el volumen que contiene el archivo de registro.

Para aumentar el archivo de registro, use la cláusula MODIFY FILE de la instrucción ALTER DATABASE, especificando la sintaxis de SIZE y MAXSIZE. Para obtener más información, consulte Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

Para más información, vea las Recomendaciones.

Controlar el crecimiento de un archivo de registro de transacciones

Use las opciones File y Filegroup de la instrucción ALTER DATABASE (Transact-SQL) para administrar el crecimiento de un archivo de registro de transacciones. Tener en cuenta lo siguiente:

  • Para cambiar el tamaño del archivo actual en unidades de KB, MB, GB y TB, use la opción SIZE.
  • Para cambiar el incremento de crecimiento, use la opción FILEGROWTH. El valor 0 indica que el aumento automático se establece en OFF y no se permite ningún espacio adicional.
  • Para controlar el máximo el tamaño de un archivo de registro en unidades de KB, MB, GB y TB, o establecer el crecimiento en UNLIMITED, use la opción MAXSIZE.

Recomendaciones

Estas son algunas recomendaciones generales referentes a los archivos de registro de transacciones:

  • El incremento de crecimiento automático del registro de transacciones, según lo establecido por la opción FILEGROWTH, debe ser lo suficientemente grande como para anticiparse a las necesidades de las transacciones de la carga de trabajo. El incremento del crecimiento de un archivo de registro debe ser lo suficientemente grande para evitar una expansión frecuente. Un buen punto de referencia para ajustar correctamente el tamaño de un registro de transacciones es supervisar la cantidad de registro ocupada durante:

    • El tiempo necesario para ejecutar una copia de seguridad completa, porque no se pueden realizar copias de seguridad del registro hasta que termine.
    • El tiempo necesario para las operaciones de mantenimiento de índice más grandes.
    • El tiempo necesario para ejecutar el lote más grande de una base de datos.

  • Al establecer crecimiento automático para archivos de datos y de registro mediante la opción FILEGROWTH, sería preferible establecerlo en size en lugar depercentage, para permitir un mejor control en la proporción de crecimiento, ya que los porcentajes son una unidad de medida sin límite de crecimiento.

    • En Azure SQL Managed Instance, la inicialización instantánea de archivos puede beneficiar a los eventos de crecimiento del registro de transacciones de hasta 64 MB. El incremento de tamaño de crecimiento automático predeterminado para las bases de datos nuevas es de 64 MB. Los eventos de crecimiento automático del archivo de registro de transacciones mayores de 64 MB no pueden beneficiarse de la inicialización instantánea de archivos.
    • Como práctica recomendada, no establezca el valor de la opción FILEGROWTH por encima de 1024 MB para registros de transacciones.

  • Un aumento de crecimiento automático pequeño puede generar demasiados VLF pequeños y puede reducir el rendimiento. Para determinar la distribución óptima de VLF para el tamaño de registro de transacciones actual de todas las bases de datos en una instancia determinada, así como los incrementos de tamaño necesarios para conseguir el tamaño requerido, vea este script para el análisis y la corrección de VLF, proporcionado por SQL Tiger Team.

  • Un incremento de crecimiento automático grande puede causar dos problemas:

    • Un incremento de crecimiento automático grande puede hacer que la base de datos se detenga mientras se asigna el nuevo espacio, lo que puede provocar tiempos de espera de consulta.
    • Un incremento de crecimiento automático grande puede generar demasiados VLF pequeños y grandes, y también puede afectar al rendimiento. Para determinar la distribución óptima de VLF para el tamaño de registro de transacciones actual de todas las bases de datos en una instancia determinada, así como los incrementos de tamaño necesarios para conseguir el tamaño requerido, vea este script para el análisis y la corrección de VLF, proporcionado por SQL Tiger Team.

  • Incluso con el crecimiento automático habilitado, puede recibir un mensaje de que el registro de transacciones está lleno, si no puede crecer lo suficientemente rápido para satisfacer las necesidades de la consulta. Para más información sobre cómo cambiar el aumento de crecimiento, vea Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

  • Puede configurar los archivos de registro para que se reduzcan automáticamente. Esto no se recomienda y la propiedad de base de datos auto_shrink está establecida en FALSE de manera predeterminada. Si auto_shrink está establecida en TRUE, el proceso de reducción automática solo reduce el tamaño de un archivo cuando más del 25 % de su espacio está sin usar.

Pasos siguientes