¿Cuanto ocupan mis tablas y mis vistas indexadas en Sql Server?

A menudo es importante conocer que tablas o vistas indexadas ocupan un mayor espacio en nuestras bases de datos. Es importante sabe que las vistas indexadas ocupan espacio, ya que mantienen una copia de los datos, por lo tanto tambien incrementan el numero de escrituras a discos cuando se realizan inserciones.

Por ejemplo, la información devuelta por este script es muy util para detectar situaciones de 'fugas de datos', en las que tablas que se limpian periodicamente o en respuesta a un evento, dejan de hacerlo y en consecuencia nuestra base de datos crece desmesuradamente. Para una de estas situaciones escribí el script que se encuentra abajo.

Tambien puede ser muy útil en otras situaciones, como por ejemplo detectar las tablas que más ocupan para optimizar su almacenamiento, o para detectar las tablas con más filas, para pensar en su particionado.

–Cursor que contiene todos los objetos que ocupan espacio

DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

      SELECT name FROM

            sysobjects o

      WHERE

            o.xtype = 'S' or –Tablas de sistema

            o.xtype = 'U' or –Tablas de usuario

            o.xtype = 'V' –Vistas (solo las indexadas devuelven tamaño)

–Tabla temporal para albergar los resultados

CREATE TABLE #results

      (name SYSNAME, rows CHAR(11),

      reserved VARCHAR(18), data VARCHAR(18),

      index_size VARCHAR(18),Unused VARCHAR(18))

–Recorremos el cursor obteniendo la información de espacio ocupado

DECLARE @object_name AS SYSNAME

OPEN objects_cursor

FETCH NEXT FROM objects_cursor

INTO @object_name;

WHILE @@FETCH_STATUS = 0

BEGIN

      INSERT INTO #results

            EXEC sp_spaceused @object_name

   

      FETCH NEXT FROM objects_cursor

            INTO @object_name;    

END;

CLOSE objects_cursor;

DEALLOCATE objects_cursor;

— Quitamos "KB" para poder ordenar

UPDATE

  #results

SET

  reserved = LEFT(reserved,LEN(reserved)-3),
  data = LEFT(data,LEN(data)-3)
,
 
index_size = LEFT(index_size,LEN(index_size)-3)
,

  Unused = LEFT(Unused,LEN(Unused)-3)

–Ordenamos la información por el tamaño ocupado

SELECT

  Name,

  reserved AS [Tamaño en Disco (KB)],

  data AS [Datos (KB)],

  index_size AS [Indices (KB)],

  Unused AS [No usado (KB)],

  Rows AS Filas FROM #results

ORDER BY

  CONVERT(bigint, reserved) DESC

–Eliminar la tabla temporal

DROP TABLE #results

Reseñar que el script anterior solo funciona en SQL Server 2000, para que funcione en SQL Server 2005, tenemos que modificar la declaración del cursor. Esto es debido a que en SQL Server, el procedimiento almacenado sp_spaceused espera el nombre de los objetos calificados con el nombre del esquema al que pertnenecen. Además los tipos de objetos que devuelven en SQL Server información sobre su tamaño son más. En SQL Server 2005 debemos hacer la declaración del cursor como sigue:

DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY for

      SELECT s.name + '.' + o.name from sys.schemas s

      INNER JOIN sys.objects o

      ON o.schema_id = s.schema_id

      WHERE

            o.type = 'S' or –Tablas de sistema

            o.type = 'U' or –Tablas de usuario

            o.type = 'V' or –Vistas (solo las indexadas devuelven tamaño)

            o.type = 'SQ' or –Cola de servicio

            o.type = 'IT' — Tablas internas usadas p.e. por el Service Broker o los indices XML

26 comentarios sobre “¿Cuanto ocupan mis tablas y mis vistas indexadas en Sql Server?”

  1. me parce super util este script pero quisiera saber como hago para utilizarlo en todas mis BD sin necesidad de ejecutarlo en cada una, osea que me extraiga la informacion en una sola tabla temporal de todas las tablas de todas las BD de mi servidor, gracias

  2. Gracias Rodrigo,
    realmente me funciono, aunque dejame comentar que estoy utilizando sql 2000 y el script tal cual me dio error, esto debido a la inclusion de vistas indizadas, solamente le quite que me incluyera estas vistas indizadas y me obtuvo la información que requeria. el mensaje que obtenia era «Las presentaciones no tienen espacio asignado».

    Saludos

    Nixon A. Morales
    namorales@hotmail.com

  3. Hola que tal, corri el script en un sql 2000 pero hubo algunos errores que me marco. y despues cheque el espacion de la bd y sigue igual no me afecto nada. me urge disminuir el espacio, que mas puedo hacer.
    mi correo: ingfranciscom@gmail.com cualquier sugerencia es bienvenida.
    Gracias !!!

  4. Viejo Men.. excelente te felicito muy bueno el script. Inicialmente no me funciono porque no tenia vistas indexadas y me salia que las vistas no tienen espacio. Pero lo solucione quitando del where del cursor el filtro de las vistas.

    Mil gracias por este script.

  5. Hola, Como estan. Muy buen script, pero como el dicho dice: «si usas cursores….
    Entonces no sabes SQL…»
    Basandome en ese dicho (y con el aporte de este post, ofcourse) diseñe una consulta uq hace lo mismo y unas cuantas cosas mas.

    Aca se los dejo

    select name
    ,(select top 1 row_count from sys.dm_db_partition_stats where object_id=s.object_id)
    ,modify_date
    ,(select convert(decimal(8,2),(SUM ( CASE
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END )*0.0078125 )) FROM sys.dm_db_partition_stats WHERE object_id = s.object_id) as PESO_TABLA from sys.tables s where type_desc = 'USER_TABLE' order by PESO_TABLA desc Esta diseñado en 2k5, no lo probe antes, aca en el trabajo si cargamos al servidor de produccion nos aplican, asi que nada de eso. Solo Scripts livianos. Bueno se cuidan saludos. Jim!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *