PREGUNTAS Y RESPUESTAS-Obtener espacio utilizado por mis tablas

PREGUNTA: Como puedo saber cual es el espacio utilizado por todas mis tablas en una base de datos

RESPUESTA:

NOTA: En todo los artículos del blog, haciendo clic en la imagen obtendrás información ampliada sobre los temas tratados.

Una respuesta inmediata sería recomendar el uso del procedimiento almacenado del sistema sp_spaceused (). Pero, si usamos este procedimiento almacenado para obtener información de tablas, tenemos que usarlo para una tabla a la vez. He encontrado artículos al respecto y sugieren crear un programa que liste todas las tablas de la base de datos para luego hacer un rulo (loop) por cada tabla, pasando el nombre del objeto al procedimiento almacenado sp_spaceused en un query dinámico, almacenar cada pasada del rulo en una tabla temporal y por ultimo listar la tabla resultante.

Muy complicado!

Para saber cuanto registros tienen tus tabla e información detallada de el espacio que ocupan, solo tienes que ejecutar el simple query que presento a continuación.

NOTAS:

El ejemplo mostrado aplica para SQL Server 2005 o mayor.
Estoy usando CTE (Common Table Expresión ) que sí genera un conjunto de datos temporal, pero en el mismo Batch, de modo que no hay que definir un objeto tabla temporal.
Si tienes tablas con campos e índices XML y requieres información muy precisa, alli sí recomiendo el uso de sp_spaceused, ya que en ese procedimiento almacenado se hacen algunos cálculos especiales requeridos para obtener de forma precisa el espacio utilizado cuando se usa XML.

Aqui el query: (hacer clic en la imagen para obtener el script->)

--Opcional para actualizar el conteo de filas y uso de páginas
--DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

--CTE con información por páginas. Al hacer Join con sys.tables solo aparecerán las tablas de usuario
WITH EspacioTablas_CTE (Esquema,Objeto,RowCnt,ResPag,DatPag,UsdPag) 
AS (
SELECT   
  t.schema_id, --Esquema
  p.object_id, --Objeto
  SUM(CASE  
      WHEN p.index_id < 2 
        THEN p.row_count  
        ELSE 0  
      END), --RowCnt
  SUM(p.reserved_page_count), --ResPag
  SUM(CASE  
      WHEN p.index_id<2
        THEN p.in_row_data_page_count+p.lob_used_page_count+p.row_overflow_used_page_count
        ELSE p.lob_used_page_count+p.row_overflow_used_page_count  
      END), --DatPag
  SUM(p.used_page_count) --UsdPag 
FROM sys.dm_db_partition_stats p
  JOIN sys.tables t 
   ON p.object_id=t.object_id
GROUP BY t.schema_id, p.object_id)
--Query final con información en KB, Ordenado por la tabla con mayor cantidad de registro
SELECT  
  SCHEMA_NAME(Esquema) AS Esquema,
  OBJECT_NAME(Objeto) AS Tabla,
  RowCnt AS [Num Registros],  
  (ResPag * 8) AS [KB Reservados],  
  (DatPag * 8) AS [KB Datos],
  CASE 
  WHEN UsdPag > DatPag 
    THEN (UsdPag - DatPag) * 8
    ELSE 0 
  END [KB Indices],  
  CASE 
  WHEN ResPag > UsdPag 
    THEN ResPag - UsdPag
    ELSE 0 
  END [KB Sin Usar] 
FROM EspacioTablas_CTE
ORDER BY [Num Registros] DESC
GO

--Opcional para actualizar el conteo de filas y uso de páginas

--DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

--CTE con información por páginas. Al hacer Join con sys.tables solo aparecerán las tablas de usuario

WITH EspacioTablas_CTE (Esquema,Objeto,RowCnt,ResPag,DatPag,UsdPag)

AS (

SELECT

t.schema_id, --Esquema

p.object_id, --Objeto

SUM(CASE

WHEN p.index_id < 2

THEN p.row_count

ELSE 0

END), --RowCnt

SUM(p.reserved_page_count), --ResPag

SUM(CASE

WHEN p.index_id<2

THEN p.in_row_data_page_count+p.lob_used_page_count+p.row_overflow_used_page_count

ELSE p.lob_used_page_count+p.row_overflow_used_page_count

END), --DatPag

SUM(p.used_page_count) --UsdPag

FROM sys.dm_db_partition_stats p

JOIN sys.tables t

ON p.object_id=t.object_id

GROUP BY t.schema_id, p.object_id)

--Query final con información en KB, Ordenado por la tabla con mayor cantidad de registro

SELECT

SCHEMA_NAME(Esquema) AS Esquema,

OBJECT_NAME(Objeto) AS Tabla,

RowCnt AS [Num Registros],

(ResPag * 8) AS [KB Reservados],

(DatPag * 8) AS [KB Datos],

CASE

WHEN UsdPag > DatPag

THEN (UsdPag - DatPag) * 8

ELSE 0

END [KB Indices],

CASE

WHEN ResPag > UsdPag

THEN ResPag - UsdPag

ELSE 0

END [KB Sin Usar]

FROM EspacioTablas_CTE

ORDER BY [Num Registros] DESC

3 comentarios en “PREGUNTAS Y RESPUESTAS-Obtener espacio utilizado por mis tablas”

Enrique Lopez dice:

31/10/2011 a las 12:10

Mug buen articulo, gracias

Responder
- Alberto De Rossi dice:
  
  31/10/2011 a las 12:49
  
  Gracias Enrique, espero que le puedas sacar provecho de inmediato.
  
  Responder
Enrique Méndez dice:

08/11/2011 a las 18:09

Excelente consulta, ya la probé y tome acciones sobre mi BD, gracias por la publicación.

Responder

Comparte Este artículo:

3 comentarios en “PREGUNTAS Y RESPUESTAS-Obtener espacio utilizado por mis tablas”

Deja un comentario Cancelar respuesta