SQL Server
Cómo medir el tamaño de las tablas de SQL Server
Mide filas, espacio reservado, datos, índices y asignación sin usar, sin contar dos veces las particiones ni las unidades de asignación.
En este artículo
El tamaño de una tabla aporta contexto a una investigación de rendimiento, pero no es un veredicto. Diez millones de filas estrechas pueden ocupar menos espacio que un millón de filas con valores de objetos grandes, y una tabla grande puede rendir bien si sus rutas de acceso se ajustan a la carga. Mide filas y páginas antes de sacar conclusiones sobre scans, mantenimiento, retención o particionamiento.
Problema
Los usuarios informan que una consulta sobre SalesDb se volvió lenta tras varios meses de crecimiento. Necesitas saber qué tablas son las más grandes, cuánto espacio corresponde a datos y cuánto a índices, si hay asignación sin usar y cómo se distribuyen las filas entre las particiones.
SQL Server expone varias granularidades relacionadas: tablas, índices, particiones y unidades de asignación. Unirlas sin cuidado puede multiplicar filas y contar el espacio dos veces. Las primeras consultas usan sys.dm_db_partition_stats, que ya proporciona el recuento de páginas a nivel de partición y permite agregarlas de forma segura.
Diagnóstico rápido: tablas más grandes de la base actual
Ejecuta esto en la base que quieras inspeccionar. Las particiones del heap o del índice clustered (index_id 0 o 1) contienen el recuento de filas de la tabla y los datos in-row, LOB y row-overflow. Los índices nonclustered contribuyen al espacio reservado y usado, pero no deben sumarse al recuento de filas.
;WITH table_space AS
(
SELECT
ps.object_id,
SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
SUM(ps.reserved_page_count) AS reserved_pages,
SUM(ps.used_page_count) AS used_pages,
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
FROM sys.dm_db_partition_stats AS ps
GROUP BY ps.object_id
)
SELECT
DB_NAME() AS database_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
space.row_count,
CAST(space.reserved_pages * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(space.data_pages * 8.0 / 1024 AS decimal(19, 2)) AS data_mb,
CAST(space.nonclustered_index_pages * 8.0 / 1024 AS decimal(19, 2)) AS nonclustered_indexes_mb,
CAST((space.reserved_pages - space.used_pages) * 8.0 / 1024 AS decimal(19, 2)) AS unused_mb,
CAST(space.reserved_pages * 8.0 / 1024 / 1024 AS decimal(19, 2)) AS reserved_gb
FROM table_space AS space
INNER JOIN sys.tables AS t
ON t.object_id = space.object_id
WHERE t.is_ms_shipped = 0
ORDER BY space.reserved_pages DESC, schema_name, table_name;data_mb incluye las páginas in-row, LOB y row-overflow del heap o del índice clustered. nonclustered_indexes_mb muestra las páginas usadas por los índices rowstore secundarios y otros índices con index_id > 1. reserved_mb incluye las páginas asignadas pero sin usar de todos los índices. Son medidas operativas útiles, pero no supongas que constituyen una factura exacta de almacenamiento para todos los tipos de índice especializados.
Consulta: una tabla concreta
Define el esquema y la tabla una sola vez. OBJECT_ID evita tener que editar los nombres de objeto en varios puntos y distingue correctamente los esquemas.
DECLARE @SchemaName sysname = N'dbo';
DECLARE @TableName sysname = N'SalesOrder';
DECLARE @ObjectId int = OBJECT_ID(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName), N'U');
IF @ObjectId IS NULL
THROW 50000, 'The requested user table does not exist in the current database.', 1;
SELECT
DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(ps.object_id) AS schema_name,
OBJECT_NAME(ps.object_id) AS table_name,
SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS used_mb,
CAST(SUM(ps.reserved_page_count - ps.used_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS unused_mb
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = @ObjectId
GROUP BY ps.object_id;Consulta: recuento y tamaño por partición
La distribución de las particiones importa para su eliminación, el mantenimiento de sliding windows y el skew. Eso no significa que una tabla grande deba particionarse automáticamente.
DECLARE @SchemaName sysname = N'dbo';
DECLARE @TableName sysname = N'SalesOrder';
DECLARE @ObjectId int = OBJECT_ID(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName), N'U');
SELECT
i.name AS index_name,
i.index_id,
ps.partition_number,
ps.row_count,
CAST(ps.reserved_page_count * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(ps.used_page_count * 8.0 / 1024 AS decimal(19, 2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i
ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE ps.object_id = @ObjectId
ORDER BY i.index_id, ps.partition_number;La consulta devuelve cada partición de índice por separado. No sumes row_count de todos los índices: las mismas filas lógicas aparecen en la estructura base y en cada índice. Para obtener el recuento de filas de la tabla, suma solo index_id 0 o 1.
Cómo interpretar el resultado
Las páginas de SQL Server tienen 8 KB; multiplicarlas por 8 y dividir entre 1.024 da MB. Divide los MB entre 1.024 para obtener GB. Las conversiones decimales evitan el truncamiento de enteros. Los recuentos de filas de sys.dm_db_partition_stats son metadatos mantenidos y pueden ser aproximados durante actividad concurrente; úsalos para diagnóstico y análisis de capacidad, no para conciliación contable.
El espacio reservado está asignado a un objeto. El espacio usado es la parte ocupada de esa asignación. La diferencia no representa automáticamente espacio recuperable para el sistema operativo, ni una asignación sin usar indica siempre un problema. Los archivos de datos suelen conservar espacio libre para crecer más adelante.
Los valores LOB, como varchar(max), nvarchar(max), varbinary(max), XML y algunas estructuras columnstore, pueden hacer que el tamaño en bytes difiera mucho del recuento de filas. Por eso la primera consulta incluye explícitamente las páginas LOB y row-overflow de la tabla base.
Qué puede significar el resultado
Una tabla que representa la mayor parte del tamaño de datos puede justificar un análisis de retención y patrones de acceso. Que el espacio de índices supere mucho al de los datos base puede ser apropiado para un sistema con muchas lecturas o indicar índices anchos que se solapan. Una asignación sin usar considerable puede deberse a reconstrucciones, eliminaciones o patrones de crecimiento, pero reducir los archivos de forma rutinaria suele ser contraproducente y puede provocar fragmentación y crecimiento repetido.
Un recuento desigual de filas entre particiones puede ser esperable si los periodos recientes tienen más actividad. También puede revelar un problema de límites o distribución de datos. Comprueba en los predicados y planes de ejecución que las consultas importantes realmente aprovechan la eliminación de particiones.
Qué revisar después
Relaciona el tamaño con la consulta lenta. Inspecciona las lecturas lógicas y el plan. Averigua qué índices se usan, cuánto cuesta mantenerlos al escribir y si las estadísticas describen la distribución actual. En tablas muy grandes, analiza retención, compresión, duración del mantenimiento, patrones de escritura y conveniencia de columnstore antes de cambiar el diseño físico.
Errores frecuentes
- Unir
sys.allocation_unitspor varias rutas y contar accidentalmente la misma asignación más de una vez. - Sumar los recuentos de filas de todos los índices.
- Tratar los recuentos de metadatos como cantidades exactas de negocio durante escrituras concurrentes.
- Ignorar las páginas LOB y row-overflow.
- Suponer que una tabla grande debe particionarse.
- Reducir archivos de datos como limpieza rutinaria sin entender el crecimiento posterior y la fragmentación.
Notas de producción
Estas consultas son de solo lectura y normalmente tienen un costo bajo, pero el acceso al catálogo y a las DMV también participa en la concurrencia de metadatos. Ejecútalas en la base objetivo y guarda el nombre de la base junto con los resultados. En grupos de disponibilidad, comprueba qué réplica consultaste y si sus datos están suficientemente actualizados para el propósito.
El tamaño resulta más útil como tendencia. Captúralo con una periodicidad razonable, compara las tasas de crecimiento y relaciónalas con la retención y la carga. Una sola medición indica qué es grande hoy; una tendencia muestra qué podría convertirse en un problema operativo.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Sigue aprendiendo
- Guía rápidaEncontrar consultas con alto consumo de CPUIdentifica las consultas de SQL Server que más CPU consumen ahora y en el historial, distingue el CPU total del CPU por ejecución y elige el siguiente paso de optimización adecuado.Abrir guía rápida
- Hoja de rutaSQL Server Troubleshooting & DiagnosticsMide filas, espacio reservado, datos, índices y asignación sin usar, sin contar dos veces las particiones ni las unidades de asignación.Ver hoja de ruta
¿Qué debería crear después?
Estoy construyendo Javi Builds alrededor de problemas reales que enfrentan los ingenieros de datos. Dime qué tema te gustaría ver después.
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.