Monitorización del Espacio Utilizado en Tablas SQL: Un Script Esencial para DBAs
Monitorización del Espacio Utilizado en Tablas SQL, en la administración de bases de datos, uno de los aspectos más críticos es la gestión eficiente del espacio de almacenamiento. El script SQL que exploraremos en este blog es una herramienta poderosa para los DBAs, permitiendo obtener una visión detallada del uso del espacio en las tablas de una base de datos. Este script no solo proporciona información sobre el espacio reservado y utilizado, sino que también desglosa estos datos en componentes clave como el tamaño de los índices y el espacio no utilizado. Vamos a desglosar su estructura, funcionamiento y la importancia de cada componente.
sql:
SELECT a3.name + '.' + a2.name AS [name],
a1.rows AS [rows],
(a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS [Reserved KB],
a1.data * 8 AS [Data KB],
(CASE
WHEN(a1.used + ISNULL(a4.used, 0)) > a1.data
THEN(a1.used + ISNULL(a4.used, 0)) - a1.data
ELSE 0
END) * 8 AS [Index_size KB],
(CASE
WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
ELSE 0
END) * 8 AS [unused KB],
CONVERT(DECIMAL(18, 2), (((a1.reserved + ISNULL(a4.reserved, 0)) * 8) - ((CASE
WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
ELSE 0
END) * 8)) / 1024.0 / 1024.0) AS [Table_used_Space GB]
FROM
(
SELECT ps.object_id,
SUM(CASE
WHEN(ps.index_id < 2)
THEN row_count
ELSE 0
END) AS [rows],
SUM(ps.reserved_page_count) AS reserved,
SUM(CASE
WHEN(ps.index_id < 2)
THEN(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE(ps.lob_used_page_count + ps.row_overflow_used_page_count)
END) AS data,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(
SELECT it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON(it.object_id = ps.object_id)
WHERE it.internal_type IN(202, 204)
GROUP BY it.parent_id
) AS a4 ON(a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON(a1.object_id = a2.object_id)
INNER JOIN sys.schemas a3 ON(a2.schema_id = a3.schema_id)
WHERE a2.type != N'S'
AND a2.type != N'IT'
ORDER BY [Table_used_Space GB] DESC, [rows] desc ;
1. Descripción del Script y su Funcionalidad
El script en cuestión realiza una consulta a las vistas de sistema de SQL Server para obtener información detallada sobre el uso del espacio en las tablas de la base de datos. La consulta principal utiliza las vistas sys.dm_db_partition_stats, sys.all_objects y sys.schemas para recoger datos esenciales como:
- Nombre completo de la tabla: Incluyendo el esquema.
- Número de filas: Contando las filas presentes en cada tabla.
- Espacio reservado: Cantidad de espacio reservado para cada tabla, en KB.
- Espacio utilizado para datos: Espacio real utilizado por los datos.
- Tamaño de los índices: Espacio ocupado por los índices de las tablas.
- Espacio no utilizado: Espacio reservado pero no utilizado.
- Espacio total utilizado por la tabla en GB: Una conversión del espacio total reservado menos el espacio no utilizado.
2. Importancia del Script para un DBA
Este script es una herramienta indispensable para cualquier DBA por varias razones:
- Optimización del Almacenamiento: Permite identificar tablas que están utilizando más espacio del necesario. Esto es crucial en entornos donde el almacenamiento es costoso o limitado.
- Monitoreo de Crecimiento de Tablas: Ayuda a monitorear el crecimiento de las tablas a lo largo del tiempo, permitiendo prever cuándo será necesario realizar tareas de mantenimiento, como la reorganización de índices o la limpieza de datos no utilizados.
- Identificación de Ineficiencias: Al separar el espacio ocupado por índices del espacio ocupado por datos, los DBAs pueden identificar si ciertos índices están consumiendo más espacio de lo que deberían, lo que puede indicar la necesidad de reevaluar la estrategia de indexación.
- Mantenimiento Proactivo: El script facilita la identificación de tablas con grandes cantidades de espacio no utilizado, lo que puede ser un signo de ineficiencias que necesitan ser abordadas a través de la reorganización de tablas o la actualización de estadísticas.
3. Desglose de la Consulta SQL
Selección y Agregación de Datos
La consulta comienza con dos subconsultas que agregan datos a nivel de particiones y tablas internas:
- Primera Subconsulta: Se agrupan los datos por
object_idpara obtener el número de filas, el espacio reservado y utilizado, y el espacio ocupado por los datos en las tablas. - Segunda Subconsulta: Se enfoca en tablas internas específicas, identificando el espacio reservado y utilizado por ellas.
Combinación y Filtrado de Resultados
Los resultados de estas subconsultas se combinan usando una unión externa (LEFT JOIN) y se integran con las tablas de objetos (sys.all_objects) y esquemas (sys.schemas) para obtener los nombres de las tablas completos.
Cálculos de Espacio
El script realiza cálculos clave, incluyendo:
- Espacio Total Reservado en KB: Considera tanto el espacio reservado por la tabla como por las estructuras internas asociadas.
- Tamaño de los Índices: Calculado como la diferencia entre el espacio total utilizado y el espacio utilizado por los datos.
- Espacio No Utilizado: Calculado como la diferencia entre el espacio reservado y el espacio utilizado.
Ordenamiento de los Resultados
Finalmente, los resultados se ordenan por el espacio total utilizado en GB, seguido por el número de filas, lo que permite identificar rápidamente las tablas que están utilizando más espacio.
4. Conclusión
El uso eficiente del almacenamiento es un componente clave de la administración de bases de datos. El script SQL presentado aquí es una herramienta poderosa que permite a los DBAs obtener una visión completa del uso del espacio en sus bases de datos. Al proporcionar detalles granulares sobre cómo se utiliza el espacio en cada tabla, los DBAs pueden tomar decisiones informadas para optimizar el rendimiento y la eficiencia de la base de datos.
Conociendo las Trazas Habilitadas en SQL Server: Guía DBAs
SQL login failed for user ‘NT AUTHORITY \ ANONYMOUS LOGIN’
