ApsaraDB RDS:Insufficient space issues for RDS for SQL Server

La base de datos tempdb es una base de datos del sistema en SQL Server que almacena datos temporales. El espacio de los archivos de datos en tempdb se utiliza con frecuencia en diversos escenarios, como los siguientes:

• Objetos de usuario: tablas temporales creadas por los usuarios.

• Objetos internos: tablas temporales generadas internamente por SQL Server.

• Almacén de versiones: cuando se habilita el aislamiento de instantáneas o la instantánea de lectura confirmada para una base de datos, la información de versiones se almacena en tempdb.

Si ciertas operaciones consumen mucho espacio, como las transacciones de larga duración, la creación de numerosas tablas temporales o el aislamiento de instantáneas, el archivo de datos puede crecer de forma desproporcionada. Esto significa que su tamaño aumenta significativamente más allá de su rango normal. For more information, see el tutorial oficial de Microsoft sobre la base de datos tempdb.

Archivado o eliminación de datos

Elimine o migre los datos a los que rara vez se accede (por ejemplo, registros históricos antiguos) a otra instancia o a un formato de archivo. Esto reduce directamente el espacio de datos utilizado y constituye la solución más eficaz a largo plazo, aunque requiere cambios en la lógica de la aplicación y en el diseño de la base de datos.

Compresión de datos

Las instancias que ejecutan SQL Server 2016 o versiones posteriores, así como las instancias Enterprise Edition con versiones anteriores, admiten compresión de filas y de páginas a nivel de tabla, índice o partición. Para obtener más información, consulte Compresión de datos.

El ahorro varía considerablemente (del 10 al 90 %) según el esquema, los tipos de columna y la distribución de los datos. Utilice sp_estimate_data_compression_savings para estimar el ahorro antes de habilitar la compresión.

Cambiar la configuración de compresión es una operación de lenguaje de definición de datos (DDL). En tablas grandes, esto puede provocar bloqueos prolongados. Ejecútela durante las horas de menor actividad.

En instancias Enterprise Edition, establezca ONLINE = ON para minimizar el impacto en el negocio.

La compresión incrementa la sobrecarga de CPU. Habilítela solo en tablas grandes donde el ahorro de espacio justifique el costo.

Desfragmentación de índices

Una alta fragmentación de índices ralentiza las consultas y desperdicia almacenamiento. Para ver las tasas de fragmentación, vaya a Autonomy Services > Performance Optimization y haga clic en la pestaña Index Usage. La consola muestra la tasa de fragmentación por tabla y sugiere si conviene reconstruir o reorganizar cada índice.

La tasa de fragmentación de índices mide el porcentaje de páginas de índice lógicamente adyacentes que no lo están físicamente. Esto difiere del porcentaje de espacio libre dentro de las páginas. Para medir el espacio libre promedio por página, consulte sys.dm_db_index_physical_stats en modo SAMPLED o DETAILED y revise la columna avg_page_space_used_in_percent . Para obtener más información, consulte sys.dm_db_index_physical_stats (Transact-SQL) . Esta consulta lee muchas páginas de índice, por lo que debe ejecutarla durante las horas de menor actividad.

Reconstrucción de índices (REBUILD): recrea el índice por completo. Resulta más eficaz que la reorganización para índices con alta fragmentación. De forma predeterminada, bloquea la tabla durante la operación. En instancias Enterprise Edition, establezca ONLINE = ON para evitar bloqueos prolongados de la tabla.

ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

Después de la reconstrucción, la consola actualiza las estadísticas de índices de forma asíncrona. Haga clic en Recollect para obtener los datos más recientes y luego haga clic en Export Script para descargar y comprobar la tasa de fragmentación.

Reorganización de índices (REORGANIZE): reestructura las páginas de nivel hoja sin bloquear la tabla. Es menos eficaz que la reconstrucción, pero adecuada para índices con baja fragmentación.

-- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before running.
DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Database name
DECLARE @fileName NVARCHAR(128)               -- Data file name
DECLARE @targetSize INT = 2000                -- Target size (MB)
DECLARE @shrinkSize INT = 5120                -- Size to shrink per iteration (MB). 5 GB recommended.
DECLARE @currentSize INT                      -- Current file size
DECLARE @freeSize INT                         -- Unallocated space
DECLARE @usedSize INT                         -- Used space

DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10                    -- Wait between iterations (seconds)

-- Get the data file name
SELECT @fileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@dbName)
AND type_desc = 'ROWS'

-- Shrink in a loop
WHILE 1 = 1
BEGIN
    -- Get current size and space breakdown
    DECLARE @sql0 NVARCHAR(MAX) = N'
    USE [' + @dbName + '];
    SELECT
      @currentSize = (SUM(total_page_count) * 1.0 / 128),
      @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
    FROM sys.dm_db_file_space_usage
    WHERE database_id = DB_ID();'

    EXEC sp_executesql
      @sql0,
      N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
      @currentSize OUTPUT,
      @freesize OUTPUT

    PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
    PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'

    SET @usedSize = @currentSize - @freeSize
    PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'

    -- Validate target size
    IF @targetSize <= @usedSize
    BEGIN
        PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Exit when target is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Calculate the new size for this iteration
    DECLARE @newSize INT = @currentSize - @shrinkSize
    IF @newSize < @targetSize
        SET @newSize = @targetSize

    -- Run the shrink
    SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
    PRINT 'Executing shrink: ' + @sql
    EXEC (@sql)

    -- Wait before the next iteration
    PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
    WAITFOR DELAY '00:00:10'
END

DBCC SHRINKFILE(<File ID>, <Target size in MB>)

Solución de problemas comunes de espacio de datos

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Escenario 1: tempdb tiene un tamaño grande actualmente (instancia en ejecución)

Paso 1: compruebe el espacio por tipo de objeto

Ejecute la siguiente consulta para ver cuánto espacio consume cada categoría (objetos de usuario, objetos internos y almacén de versiones):

SELECT
    SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space (MB)],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_file_space_usage;

Como referencia, consulte sys.dm_db_file_space_usage.

Paso 2a: si los objetos de usuario o internos son grandes

Identifique qué sesiones utilizan más espacio en tempdb:

SELECT
    session_id,
    SUM(user_objects_alloc_page_count) AS [user object pages used],
    (SUM(user_objects_alloc_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_objects_alloc_page_count) AS [internal object pages used],
    (SUM(internal_objects_alloc_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_session_space_usage
GROUP BY
    session_id;

Como referencia, consulte sys.dm_db_session_space_usage.

Después, use el valor de session_id para encontrar la instrucción SQL que ejecuta la sesión:

SELECT
    r.session_id AS [SPID],
    r.start_time AS [Start Time],
    r.status AS [Status],
    r.command AS [Command Type],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.last_wait_type AS [Last Wait Type],
    t.text AS [Executing Statement]
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE
    r.session_id = <session_id>; -- Replace with the session_id from the previous query

Paso 2b: si el almacén de versiones es grande

Es probable que la base de datos tenga habilitado el aislamiento de instantáneas y que existan transacciones de larga duración que impidan la limpieza.

Compruebe qué bases de datos tienen habilitado el aislamiento de instantáneas:

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state
FROM sys.databases;

Si is_read_committed_snapshot_on o snapshot_isolation_state es 1, el aislamiento de instantáneas está activo.

Busque las sesiones con transacciones no confirmadas de larga duración que bloquean la limpieza del almacén de versiones:

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Utilice el valor de session_id para revisar qué hace la sesión:

SELECT * FROM sys.sysprocesses WHERE spid = <session_id>;

Si el estado de la sesión es sleeping, compruebe la última instrucción ejecutada:

DBCC INPUTBUFFER(<session_id>);

Escenario 2: investigación de un crecimiento anterior de tempdb después de un reinicio

Si la instancia ya se reinició y los datos en tiempo real no están disponibles, utilice las sesiones activas promedio (AAS), los registros de consultas lentas y los registros de auditoría para reconstruir lo sucedido.

Paso 1: identifique la ventana de crecimiento

En la página de detalles de la instancia, vaya a Monitoring and Alerts > Standard Monitoring. En la sección Instance Storage, anote cuándo comenzó y terminó de subir la métrica tmp_size. Tenga en cuenta que tempdb puede reutilizar primero el espacio libre existente. La expansión real del archivo ocurre solo cuando se agota dicho espacio, por lo que el crecimiento en la gráfica podría aparecer después del inicio de la operación SQL.

Paso 2: analice mediante AAS

Vaya a Autonomy Services > Performance Optimization > Performance Insight. Seleccione la ventana de tiempo y extienda la hora de inicio hacia atrás para capturar todas las operaciones relacionadas. Busque instrucciones SQL que utilicen de forma intensiva tablas temporales basadas en disco (por ejemplo, creación frecuente de tablas #).

Paso 3: filtre los registros de consultas lentas por palabra clave

Vaya a Autonomy Services > Slow Query Logs. Realice la búsqueda por palabra clave (por ejemplo, el nombre de una tabla temporal). Revise las horas de inicio y la duración de las consultas para determinar si la hora de finalización coincide con el momento en que tempdb dejó de crecer.

USE tempdb;
DBCC SHRINKFILE(templog, 1);
GO