domingo, 18 de marzo de 2007

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

Excelente articulo extraido integramente de http://geeks.ms sitio con muchos temas interesantes.

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

Publicado Tuesday, October 31, 2006 2:25 PM por Rodrigo Corral

No hay comentarios: