Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Listar tablas por espacio reservado
- 2. Separar el espacio de datos del espacio de índices
- 3. Mostrar el tamaño de los índices de una tabla
- 4. Mostrar el tamaño por partición
- 5. Mostrar las tablas más grandes por número de filas
- 6. Comparar el espacio usado con el reservado
- Qué significa el resultado
- Qué revisar
- Unas pocas tablas dominan la base de datos
- El espacio de índices es cercano o superior al de los datos base
- El número de filas es alto pero el almacenamiento es relativamente modesto
- El almacenamiento es alto pero el número de filas es modesto
- Una partición contiene la mayoría de las filas
- Mucho espacio reservado sin usar
- Notas de producción
- sys.dm_db_partition_stats se basa en metadatos y es ligero
- El tamaño de las tablas no es el tamaño de los archivos de la base de datos
- La compresión cambia la relación entre filas y almacenamiento
- Los datos LOB importan
- Ten cuidado con la limpieza automatizada
- Errores comunes
- 1. Contar las filas una vez por índice
- 2. Tratar el espacio reservado como datos totalmente usados
- 3. Eliminar índices grandes basándose solo en su tamaño
- 4. Reducir archivos porque se limpió una tabla
- 5. Comparar tablas solo por número de filas
- 6. Ignorar la distribución entre particiones
- Siguiente paso
- 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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.