¿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