Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: Almacenamiento y capacidadNivel: IntermedioActualizado

Comprobar el tamaño de las tablas de SQL Server

Mide el número de filas y el almacenamiento de las tablas de SQL Server, separa el espacio de datos del de índices e identifica qué tablas impulsan el crecimiento de la base de datos.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Listar tablas por espacio reservado
  5. 2. Separar el espacio de datos del espacio de índices
  6. 3. Mostrar el tamaño de los índices de una tabla
  7. 4. Mostrar el tamaño por partición
  8. 5. Mostrar las tablas más grandes por número de filas
  9. 6. Comparar el espacio usado con el reservado
  10. Qué significa el resultado
  11. Qué revisar
  12. Unas pocas tablas dominan la base de datos
  13. El espacio de índices es cercano o superior al de los datos base
  14. El número de filas es alto pero el almacenamiento es relativamente modesto
  15. El almacenamiento es alto pero el número de filas es modesto
  16. Una partición contiene la mayoría de las filas
  17. Mucho espacio reservado sin usar
  18. Notas de producción
  19. sys.dm_db_partition_stats se basa en metadatos y es ligero
  20. El tamaño de las tablas no es el tamaño de los archivos de la base de datos
  21. La compresión cambia la relación entre filas y almacenamiento
  22. Los datos LOB importan
  23. Ten cuidado con la limpieza automatizada
  24. Errores comunes
  25. 1. Contar las filas una vez por índice
  26. 2. Tratar el espacio reservado como datos totalmente usados
  27. 3. Eliminar índices grandes basándose solo en su tamaño
  28. 4. Reducir archivos porque se limpió una tabla
  29. 5. Comparar tablas solo por número de filas
  30. 6. Ignorar la distribución entre particiones
  31. Siguiente paso
  32. Para profundizar

Problema

Una base de datos de SQL Server está creciendo, el almacenamiento está bajo presión, una operación de mantenimiento tarda más de lo esperado o necesitas identificar qué tablas consumen más espacio.

El objetivo es responder:

  • ¿Cuáles son las tablas más grandes?
  • ¿Cuántas filas contienen?
  • ¿Cuánto espacio usan los datos y cuánto los índices?
  • ¿Qué objetos impulsan el crecimiento de la base de datos?
  • ¿Es normal que una tabla sea grande, o su tamaño sugiere problemas de retención, indexación o modelo de datos?

Respuesta rápida

Para obtener un inventario fiable y ligero del número de filas y del espacio asignado, agrega sys.dm_db_partition_stats por tabla.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(
        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                    THEN ps.in_row_data_page_count
                       + ps.lob_used_page_count
                       + ps.row_overflow_used_page_count
                ELSE 0
            END
        ) * 8.0 / 1024
        AS decimal(18,2)
    ) AS data_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

Usa esta consulta como primera pasada cuando necesites entender en qué se está yendo el espacio de la base de datos.

Código / comandos

1. Listar tablas por espacio reservado

Usa esta consulta para obtener rápidamente un inventario ordenado.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

reserved_mb suele ser el primer criterio de ordenación más útil, porque muestra cuánto espacio ha asignado SQL Server al objeto.


2. Separar el espacio de datos del espacio de índices

Esta consulta es más útil cuando una tabla grande podría serlo en realidad por sus índices.

WITH table_space AS
(
    SELECT
        ps.object_id,

        SUM(ps.row_count) AS row_count,

        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                    THEN ps.in_row_data_page_count
                       + ps.lob_used_page_count
                       + ps.row_overflow_used_page_count
                ELSE 0
            END
        ) AS data_pages,

        SUM(
            CASE
                WHEN ps.index_id > 1
                    THEN ps.used_page_count
                ELSE 0
            END
        ) AS nonclustered_index_pages,

        SUM(ps.reserved_page_count) AS reserved_pages,
        SUM(ps.used_page_count) AS used_pages
    FROM sys.dm_db_partition_stats AS ps
    GROUP BY
        ps.object_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    ts.row_count,
    CAST(ts.data_pages * 8.0 / 1024 AS decimal(18,2)) AS data_mb,
    CAST(ts.nonclustered_index_pages * 8.0 / 1024 AS decimal(18,2)) AS nonclustered_index_mb,
    CAST(ts.used_pages * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(ts.reserved_pages * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM table_space AS ts
INNER JOIN sys.tables AS t
    ON t.object_id = ts.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
ORDER BY
    reserved_mb DESC;

Úsala cuando necesites saber si el almacenamiento está dominado por la tabla base o por los índices secundarios.


3. Mostrar el tamaño de los índices de una tabla

Una vez que encuentres una tabla grande, analiza sus índices en detalle.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps
    ON ps.object_id = i.object_id
   AND ps.index_id = i.index_id
WHERE
    i.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
    i.index_id,
    i.name,
    i.type_desc
ORDER BY
    reserved_mb DESC;

Es útil cuando:

  • el mantenimiento de índices es lento
  • las copias de seguridad están creciendo
  • el costo de escritura es alto
  • sospechas que hay índices duplicados o sobredimensionados

No elimines un índice solo porque sea grande. Primero verifica si la carga de trabajo lo usa.


4. Mostrar el tamaño por partición

En las tablas particionadas, los totales a nivel de tabla pueden ocultar dónde están realmente los datos.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourPartitionedTable';

SELECT
    ps.partition_number,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.dm_db_partition_stats AS ps
WHERE
    ps.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
    ps.partition_number
ORDER BY
    ps.partition_number;

Usa esta consulta para comprobar si los datos se distribuyen entre las particiones como se esperaba.

Una estrategia de particionamiento muy desequilibrada puede comprometer tanto los objetivos de rendimiento como los de mantenimiento.


5. Mostrar las tablas más grandes por número de filas

El espacio y el número de filas responden a preguntas distintas.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
    AND ps.index_id IN (0, 1)
GROUP BY
    s.name,
    t.name
ORDER BY
    row_count DESC;

Filtra por las filas del montón o del índice clúster (index_id 0 o 1) para no contar las mismas filas de la tabla una vez por cada índice no clúster.


6. Comparar el espacio usado con el reservado

Una diferencia grande entre el espacio reservado y el usado puede merecer una investigación.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(
        (SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8.0 / 1024
        AS decimal(18,2)
    ) AS unused_reserved_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    unused_reserved_mb DESC;

No reduzcas la base de datos de inmediato porque un objeto tenga páginas reservadas sin usar. Primero entiende su patrón de crecimiento.

Qué significa el resultado

Campo Por qué importa
row_count Número de filas actual aproximado, obtenido de los metadatos de partición. Útil para comparaciones de capacidad y escala.
reserved_mb Espacio asignado al objeto, incluidas las páginas asignadas que actualmente no se usan.
used_mb Parte del espacio reservado que se usa actualmente.
data_mb Espacio usado por los datos del montón o de la tabla con índice clúster, incluidas las páginas LOB y de desbordamiento de fila (row-overflow) en los ejemplos anteriores.
nonclustered_index_mb Espacio usado por los índices no clúster.
unused_reserved_mb Espacio asignado pero que actualmente no se usa.
partition_number Permite examinar la distribución entre las particiones de la tabla.
index_id Identifica las estructuras de montón, índice clúster e índice no clúster.

Qué revisar

Unas pocas tablas dominan la base de datos

Esto suele significar que la planificación de capacidad debe centrarse primero en esos objetos.

Investiga:

  • la política de retención
  • el ritmo de crecimiento
  • la estrategia de archivado
  • el particionamiento
  • la compresión
  • la huella de los índices

El espacio de índices es cercano o superior al de los datos base

Esto no es necesariamente malo.

Las tablas anchas y las cargas de trabajo con muchas lecturas pueden requerir legítimamente un espacio de índices considerable.

Pero es una buena señal para revisar:

  • índices sin usar
  • índices que se solapan
  • columnas INCLUDE redundantes
  • rutas de acceso duplicadas
  • amplificación de escrituras

El número de filas es alto pero el almacenamiento es relativamente modesto

Entre las causas posibles están:

  • filas estrechas
  • compresión
  • poco uso de LOB
  • un diseño de esquema eficiente

Un número de filas alto no implica automáticamente presión sobre el almacenamiento.

El almacenamiento es alto pero el número de filas es modesto

Busca:

  • varchar(max) / nvarchar(max)
  • XML
  • datos binarios grandes
  • datos de desbordamiento de fila (row-overflow)
  • índices anchos
  • esquemas dispersos (sparse) o anchos

Una partición contiene la mayoría de las filas

Interpretaciones posibles:

  • concentración esperada en el período actual
  • desequilibrio en la clave de partición
  • particiones históricas que no se gestionan como se pretendía
  • una estrategia de particionamiento que no se ajusta a los patrones de acceso

Mucho espacio reservado sin usar

Entre las razones posibles están:

  • eliminaciones recientes
  • actividad de reconstrucción de índices
  • patrones de asignación
  • el historial de crecimiento del objeto

No trates el espacio reservado sin usar como una orden para reducir los archivos.

Notas de producción

sys.dm_db_partition_stats se basa en metadatos y es ligero

Para inventariar el tamaño de las tablas, suele ser preferible a recorrer la tabla o ejecutar COUNT(*) sobre objetos de producción grandes.

Los números de filas sirven para el diagnóstico operativo y el trabajo de capacidad, pero no equivalen a ejecutar un COUNT(*) transaccional exacto con la semántica de aislamiento de tu carga de trabajo.

El tamaño de las tablas no es el tamaño de los archivos de la base de datos

La asignación de objetos responde a:

¿Qué objetos consumen páginas de la base de datos?

No responde a:

  • cuánto espacio libre queda dentro del archivo de datos
  • cuánto espacio en disco ocupan los archivos de la base de datos
  • el tamaño del registro de transacciones
  • el tamaño de las copias de seguridad

Para eso se necesitan consultas independientes a nivel de archivo o de base de datos.

La compresión cambia la relación entre filas y almacenamiento

La compresión de filas o de páginas y columnstore pueden dificultar la comparación directa del número bruto de filas entre tablas.

Para planificar el almacenamiento, usa las páginas realmente asignadas, no solo el número de filas.

Los datos LOB importan

Los datos de gran tamaño pueden consumir un almacenamiento fuera de fila (out-of-row) considerable.

Una tabla puede parecer pequeña por número de filas pero grande en páginas debido a:

  • varchar(max)
  • nvarchar(max)
  • varbinary(max)
  • XML
  • otras asignaciones LOB

Ten cuidado con la limpieza automatizada

Las tablas grandes suelen estar en el centro de los procesos de negocio.

Antes de eliminar, archivar o purgar datos, valida:

  • los requisitos de retención
  • las claves externas
  • los informes posteriores (downstream)
  • las implicaciones para la replicación o CDC
  • las expectativas de copia de seguridad y recuperación
  • el impacto de los lotes en el registro de transacciones

Errores comunes

1. Contar las filas una vez por índice

Si sumas row_count en todos los ID de índice, puedes sobrestimar las filas de la tabla, porque los índices no clúster también contienen filas.

Para el número de filas de una tabla, usa las estructuras de montón o de índice clúster (index_id IN (0,1)) o estructura tu agregación con cuidado.

2. Tratar el espacio reservado como datos totalmente usados

Las páginas reservadas incluyen páginas asignadas pero sin usar.

Compara reserved_page_count con used_page_count.

3. Eliminar índices grandes basándose solo en su tamaño

Un índice grande puede seguir siendo crítico.

Revisa el uso del índice y los planes de consulta antes de eliminarlo.

4. Reducir archivos porque se limpió una tabla

Los ciclos frecuentes de reducción y crecimiento pueden generar fragmentación y trabajo operativo innecesario.

Recupera espacio de los archivos solo cuando haya un motivo de capacidad claro y un plan deliberado.

5. Comparar tablas solo por número de filas

Las filas varían enormemente en ancho y en características de almacenamiento.

Combina siempre el número de filas con el uso real de páginas.

6. Ignorar la distribución entre particiones

Una tabla particionada puede ser grande por motivos legítimos y aun así estar mal distribuida.

Revisa el número de filas y de páginas por partición antes de suponer que la estrategia de particionamiento funciona.

Siguiente paso

Usa el perfil de tamaño para decidir dónde seguir investigando.

  • Una tabla domina el almacenamiento → revisa la retención, el ritmo de crecimiento, el ancho del esquema, el uso de LOB y el particionamiento.
  • Los índices dominan el espacio → revisa el uso de índices, los duplicados y la sobrecarga de escritura.
  • Una partición domina → valida la elección de la clave de partición y la gestión del ciclo de vida.
  • Mucho espacio reservado sin usar → entiende el historial de crecimiento y eliminación del objeto antes de plantearte recuperar espacio.
  • La tabla crece rápidamente → añade monitoreo de tendencias en lugar de depender de una instantánea puntual.
  • El archivo de la base de datos sigue siendo mucho mayor que el total de las tablas → revisa el espacio libre, los objetos internos y la asignación a nivel de archivo.
  • El almacenamiento está bien pero el rendimiento es malo → vuelve al diagnóstico de la carga de trabajo, las esperas, las consultas y los índices en lugar del análisis de capacidad.

← Todas las guías rápidas